ORA-01704 string literal too longCause: The string literal is longer than 4000 characters.Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables我查www.oracle.com 怎么解决呢???
LONG, RAW, LONG RAW, VARCHAR2 You can use the piecewise capabilities provided by OCIBindByName(), OCIBindByPos(), OCIDefineByPos(), OCIStmtGetPieceInfo() and OCIStmtSetPieceInfo() to perform inserts, updates or fetches involving column data of these types. 你用OCI的话可以用上面的Bind的方法， 你用jdbc则要用流来输入。 下面JDBC用文件流读写LONG字段的例子: /* * This example shows how to stream data from the database */import java.sql.*; import java.io.*;class StreamExample { public static void main (String args []) throws SQLException, IOException { // Load the driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // It's faster when you don't commit automatically conn.setAutoCommit (false); // Create a Statement Statement stmt = conn.createStatement (); // Create the example table try { stmt.execute ("drop table streamexample"); } catch (SQLException e) { // An exception would be raised if the table did not exist // We just ignore it } // Create the table stmt.execute ("create table streamexample (NAME varchar2 (256), DATA long)"); // Let's insert some data into it. We'll put the source code // for this very test in the database. File file = new File ("StreamExample.java"); InputStream is = new FileInputStream ("StreamExample.java"); PreparedStatement pstmt = conn.prepareStatement ("insert into streamexample (data, name) values (?, ?)"); pstmt.setAsciiStream (1, is, (int)file.length ()); pstmt.setString (2, "StreamExample"); pstmt.execute (); // Do a query to get the row with NAME 'StreamExample' ResultSet rset = stmt.executeQuery ("select DATA from streamexample where NAME='StreamExample'");
// Get the first row if (rset.next ()) { // Get the data as a Stream from Oracle to the client InputStream gif_data = rset.getAsciiStream (1); // Open a file to store the gif data FileOutputStream os = new FileOutputStream ("example.out");
// Loop, reading from the gif stream and writing to the file int c; while ((c = gif_data.read ()) != -1) os.write (c); // Close the file os.close (); }
// Close all the resources if (rset != null) rset.close();
if (stmt != null) stmt.close();
if (pstmt != null) pstmt.close(); if (conn != null) conn.close(); } }
是吗???
我的程序已经写好了
怎么解决呢???
You can use the piecewise capabilities provided by OCIBindByName(), OCIBindByPos(), OCIDefineByPos(), OCIStmtGetPieceInfo() and OCIStmtSetPieceInfo() to perform inserts, updates or fetches involving column data of these types.
你用OCI的话可以用上面的Bind的方法，
你用jdbc则要用流来输入。
下面JDBC用文件流读写LONG字段的例子:
/*
* This example shows how to stream data from the database
*/import java.sql.*;
import java.io.*;class StreamExample
{
public static void main (String args [])
throws SQLException, IOException
{
// Load the driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database
// You can put a database name after the @ sign in the connection URL.
Connection conn =
DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // It's faster when you don't commit automatically
conn.setAutoCommit (false); // Create a Statement
Statement stmt = conn.createStatement (); // Create the example table
try
{
stmt.execute ("drop table streamexample");
}
catch (SQLException e)
{
// An exception would be raised if the table did not exist
// We just ignore it
} // Create the table
stmt.execute ("create table streamexample (NAME varchar2 (256), DATA long)"); // Let's insert some data into it. We'll put the source code
// for this very test in the database.
File file = new File ("StreamExample.java");
InputStream is = new FileInputStream ("StreamExample.java");
PreparedStatement pstmt =
conn.prepareStatement ("insert into streamexample (data, name) values (?, ?)");
pstmt.setAsciiStream (1, is, (int)file.length ());
pstmt.setString (2, "StreamExample");
pstmt.execute (); // Do a query to get the row with NAME 'StreamExample'
ResultSet rset =
stmt.executeQuery ("select DATA from streamexample where NAME='StreamExample'");
// Get the first row
if (rset.next ())
{
// Get the data as a Stream from Oracle to the client
InputStream gif_data = rset.getAsciiStream (1); // Open a file to store the gif data
FileOutputStream os = new FileOutputStream ("example.out");
// Loop, reading from the gif stream and writing to the file
int c;
while ((c = gif_data.read ()) != -1)
os.write (c); // Close the file
os.close ();
}
// Close all the resources
if (rset != null)
rset.close();
if (stmt != null)
stmt.close();
if (pstmt != null)
pstmt.close(); if (conn != null)
conn.close();
}
}
这样写也可以：
PreparedStatement p=con.prepareStatement("insert...values(?)");
String str="大字符串";
p.setCharacterStream(1,new java.io.StringReader(str),str.length());
p.executeUpdate();
TO： ldgs(钢锁)为什么直接用Statement.executeQuery("insert ...")不行啊！？请教！
PreparedStatement 对象用于执行带或不带 IN 参数的预编译 SQL 语句；
用较小的块传递大型的数据,让程序在数据库内部运行得更高效，可通过将 IN 参数设置为 Java 输入流来完成。
当语句执行时，JDBC 驱动程序将重复调用该输入流，读取其内容并将它们当作实际参数数据传输。
-----摘自JDK document
stmt = conn.prepareStatement(sSql);
String content="你的内容";
byte[] pic = content.getBytes();
//ByteArrayInputStream bais = new ByteArrayInputStream(pic);
InputStreamReader bais = new InputStreamReader(new ByteArrayInputStream(pic));
stmt.setCharacterStream(1, bais, pic.length);
stmt.execute();当超过4000个字符时就不能直接用insert了
要用流式输入
上面的代码就是用来解决此问题明天结帐
欢迎大家继续讨论
了解到oracle好笨啊
因为long作为参数的时候，最多只能是32k
PreparedStatement pstmt = con.prepareStatement(qry);
StringReader rd = new StringReader(content);
pstmt.setCharacterStream (1, rd, content.length());
pstmt.executeUpdate();
pstmt.close();