Home » Developer & Programmer » JDeveloper, Java & XML » problem inserting 32k longvarchar data via JDBC
| problem inserting 32k longvarchar data via JDBC [message #144664] |
Thu, 27 October 2005 10:56 |
caiohleite
Messages: 1
Registered: October 2005
|
Junior Member |
|
|
Hello,
I'm not an English native speaker, so maybe you don't understand exactly what my problem is. If so, please, reply and I'll try to explain it better.
I have a Java Application that reads data from a XML file and inserts them into an Oracle 9i table, which has, among others, a LONG type field.
At some point, I need to insert some text into the LONG field using CallableStatement.setAsciiStream. But, when I try to "CallableStatment.execute", if the data I've inserted are bigger than 32700 bytes, the application raises an SQLException with the Oracle error "ORA-01460: unimplemented or unreasonable conversion requested". When I call the setAsciiStream, it works fine, but when I execute the CallableStatement, the exception raises.
I've tried to "force" the setAsciiStream length to be limited to, say, 32000 bytes in order to test, and, with this limit, the application works fine, but obviously truncates the text I want to insert into the LONG field.
It seems that I cannot execute a SQL Statement containing more than 32k, i.e. the length of my whole sentence can't exceed 32k...
Does anybody knows anything about that? Is there a way to work around this problem?
Thank you very much
Caio
--> I've posted this topic on another forum, so, i put below the evolution of the topic
Reply: "Oracle's suggested work around is to convert the LONG to a CLOB. There is a FAQ on the limits with various data types at http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#08_02"
My Answer to that reply, and part of the code where the error happens:
"
It'll be a really hard work to convert the LONG to CLOB, and I'm not sure that it will work. I'll try to explain:
The data I'm inserting is almost 32k long, but doesn't reach this limit. I need to insert a 32760 bytes long plain-text in a LONG column. It works when I execute the statement as a PL/SQL procedure, like this:
"Declare
vLong varchar2 (32767);
Begin
vLong := '...'; -- [32760 characters typed here]
--
insert into test_table(long_column) values (vLong)
--
End;"
If I do so, it works. But i need to insert the data via Java, using a CallableStatement. My statement sql has the same code above, the applications calls the "SetAsciiStream" method with no errors, but, when I try to execute the CallableStatement, it raises "ORA-1460 unimplemented or unreasonable conversion requested". It seems, like I said above, that the whole statement cannot exceed 32k, cause the problem happens ONLY when my text is almost 32k long. But I haven't found any documentation about this.... and, believe me, I'm searching for it! 
note: I've tested with CLOB: same error, same problem.
"
The code:
----------
It's a very long code, cause the application makes a kind of "replication" from one database to another. Source database generates a XML file and target database reads the XML, so it dynamically generates the statements.
I'll try to put only the part of the code necessary for the operation I need, any doubts about the code or the variable names (I'll try to translate them to English), please ask.
//CODE
->The SQL text:
Note: This is the original code, there was no "Declare" there. The commented lines are the code I've typed to make the tests. Both, the original code and the original+commented raise the same exception. When I use the commented code, I need to change the way variables are bound (i haven't put this code here cause it doesn't seem to have problems -after "a thousand hours" of debugging)
StringBuffer sql = new StringBuffer();
//sql.append("Declare ");
//sql.append("vLong varchar2(32767);");
sql.append("Begin ");
//sql.append(" vLong := ?; ");
sql.append(" insert into ");
sql.append(registro.getTabela());
sql.append(" (");
StringBuffer values = new StringBuffer();
values.append(") values (");
//Here the application sets as many "?" as necessary, according to the Table columns.
-> Binding values to the CallableStatement:
//Application gets sqlType from table metadata.
if (sqlType == Types.LONGVARCHAR || sqlType == Types.CLOB || sqlType == Types.LONGVARBINARY || sqlType == Types.BLOB) {
int size = ((String) etr.getValue()).length();
//etr is a "Map.Entry etr = ((Map.Entry) itr.next());" It gets the column name and text ( [[!CDATA]]) from the XML
/* following "IF" -> if I use this commented code below, application works with no errors, but obviously truncates the text
when the bug happens, size = 32762
if (size > 32300){
size = 32300;
}*/
//stmt is a CallableStatement
stmt.setAsciiStream(
++iBinds,
new ByteArrayInputStream(
((String) etr.getValue()).getBytes()),size);
//Even here I can "getString(iBinds)" from stmt with no problem, but when I EXECUTE (stmt.execute()), it raises "ORA 1460 unimplemented or unreasonable conversion requested".
--
I'd like to obtain the exact sql statement contained in my stmt and try to execute it in SQL Plus (or something like that), but I haven't found how to do this when debugging... if u know, please, tell me!
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 09:55:50 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
