aspose file tools*
The moose likes JDBC and the fly likes Oracle Clob error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle Clob error" Watch "Oracle Clob error" New topic
Author

Oracle Clob error

Adrien Ruffie
Ranch Hand

Joined: Jan 14, 2009
Posts: 90
Hello all, I have the following error:

java.sql.SQLException: ORA-01461: une valeur 'LONG' ne peut être liée que dans une colonne de type 'LONG'

I use ojdbc14.jar in my application and my code is like:

mdocPStmt is a PrepareStatement

mdocPStmt.setCharacterStream(index++, new StringReader(mdocBean.getMocModifySQL()), mdocBean.getMocModifySQL().length());
mdocPStmt.executeUpdate();

But when the executeUpdate is performed, the previous exception is raised.

I have found several solutions like:

oracle.jdbc.RetainV9LongBindBehavior=true

Or

SetBigStringTryClob=true

I have try to use .setClob or setNClob instead setCharacterStream but nothing work fine ...
My column MocModifySQL have following structure in my table:

MOCMODIFYSQL --> NCLOB

It's very instresting because I have a second column MOCROLLBACKSQL --> NCLOB
In same table and that second column work fine with .setCharacterStream() or setString() ...

Anyone have an idea ?

Thank you,

Adryen


SCJP 5, SCDJWS 5
http://adrien-ruffie.blogspot.com
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

The PreparedStatement.setCharacterStream method creates a parameter of type LONG, not CLOB. You generally cannot assign a LONG to CLOB. I cannot comment on why it works with one column and not with another, but generally that is not a way to go.

The way to insert CLOB in an Oracle database has been recently discussed here: http://www.coderanch.com/t/573312/JDBC/java/Conversion-SQL-Result-Clob-String. Try it out and let us know if you encounter any problems.
Adrien Ruffie
Ranch Hand

Joined: Jan 14, 2009
Posts: 90
thank I have try, it's work much fine but I get the following error now:

java.sql.SQLException: ORA-00604: une erreur s'est produite au niveau SQL récursif 1
ORA-01000: nombre maximum de curseurs ouverts dépassé
ORA-00604: une erreur s'est produite au niveau SQL récursif 1
ORA-01000: nombre maximum de curseurs ouverts dépassé
ORA-01000: nombre maximum de curseurs ouverts dépassé
ORA-01000: nombre maximum de curseurs ouverts dépassé

An idea ? I have found on google this problem can be solved by trying to re-compile DBMS_CDC_PUBLISH package using SYS
But I can't consider previous solution, because the client don't agree to touch on database
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Please post here the code you're using now, including the text of the query and the structure of the table (ideally the DDL used to create the table).

The ORA-01000 error usually means you're not releasing your database resources properly. Do you always close the resultsets, statements and connections in the finally blocks? Another (not very probable) possibility is that you're using a connection pool which does not go well with Oracle database.
Adrien Ruffie
Ranch Hand

Joined: Jan 14, 2009
Posts: 90

The DataTable struct is:

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I've added code tags to your post, it makes it more readable. You can do so yourself by using the Code button above.

You're trying to use the empty_clob() SQL function, but you're stuffing it in as a parameter:

This actually sets the parameter to a VARCHAR2 with value empty_clob(). It then probably gets converted to a NCLOB and stored in the database, so in principle this could accidentally work, though it is not the way you should do so.

Try to start with one of the examples in the threads I've linked to above and gradually modify it to do what you need. Feel free to ask if you don't understand any particular piece of these examples.
Adrien Ruffie
Ranch Hand

Joined: Jan 14, 2009
Posts: 90
Thank but I have solve the problem, I poste the following method which work fine, if some one need:

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Thanks for sharing your solution, Adrien!

I'd suggest a few modifications though:

1) There is no need to use reflection to call the putString method on Oracle's CLOB. You should be able to cast the instance to oracle.sql.CLOB and call the putString method directly. I'm not sure, though, which advantages this method has over the standard Clob.setString method, which you could use right away.

2) You don't need to execute the third SQL statement (the one stored in variable reqUpdateClob). When you obtain the Clob from a database table (as you do using the reqLock statement), any modifications you do to this Clob are automatically propagated to the database (and stored permanently when you commit). Clob keeps a handle which links it to the data stored in the table. (If the Clob was temporary, you'd need to set it to a table using and update (or insert) statement, but this isn't the case). You can find more information in the documentation.

3) When you catch errors, you log them (which is good), but you don't throw the error further up the callers' chain, which allows your code to simply continue despite the error. This way the code which call your method does not have a way to know that an exception has occurred and will continue its processing even though your method actually failed. Unless you're able to recover from the error (which is quite uncommon), you should always throw a new exception from within the catch clause.
Adrien Ruffie
Ranch Hand

Joined: Jan 14, 2009
Posts: 90
Well I have apply several of your tip,

but now I have the following error:

java.sql.SQLException: Il n'y a plus de données à lire dans le socket
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1118)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1070)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:478)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:790)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1038)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1133)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1273)

Do you have see this exception ?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

This is strange. If I understand it well, the error says "There is no more data to read from the socket". That might indicate an abrupt end of processing at the database side. Do you get this result constantly?

It might be worth to post your updated code again.

I suppose you've seen the examples in the posts I've linked to before; I use these methods in our system for a long time without any problems.
Adrien Ruffie
Ranch Hand

Joined: Jan 14, 2009
Posts: 90
Yes I think is a connection time problem, because the exception occured outside this part of application (because is a webapp), this error occured during
the data clob processing action (but not from this action), another action for managing the users of the webapp ...

I have 2 usecase for processing (clob), I provide a data file which convert several file line in clob, first usecase with first file work fine
and the second take much longer (time) and fails after around five minutes ...
Adrien Ruffie
Ranch Hand

Joined: Jan 14, 2009
Posts: 90
I have found a solution, but I don't know if I can to do this with a more elegant way.

The previous bug occured in following lines:

Snippet in function _parseDifferences()--------- -->

******************* some code line before *********************

if (saveToDB) {
try {
String mfcID = context.getIdFactory().getID();
int index = 1;
mdfcPStmt.setString(index++, mfcID);
mdfcPStmt.setString(index++, mdocID);
mdfcPStmt.setString(index++, fldID);
mdfcPStmt.setString(index++, newFldID);
mdfcPStmt.setString(index++, diff.destValue);
mdfcPStmt.setString(index++, diff.srcValue);

start = System.currentTimeMillis();
mdfcPStmt.executeUpdate();
end = System.currentTimeMillis();
totalBeanSave += (end - start);
} catch (Exception e) {
msg = "Error processing differences for " + mdEnumStr + " - " + e.toString();
status = "KO";
count = "";
throw new FrontlineException(e.toString(), e);
}
}
}

Because this snippet is in a big and long in duration function the mdfcPStmt is create before calling the current function like:

query = new StringBuilder();
query.append("INSERT INTO MetaDataFieldChange ");
query.append("(MfcID, MfcMocID, MfcFldID, MfcNewFldID, MfcOldValue, MfcNewValue,");
query.append("MfcCreID, MfcModID, MfcCrDt, MfcUpd");
query.append(") VALUES (");
query.append("?, ?, ?, ?, ?, ?, ");
query.append(Utils.formatToSQL(context, context.getSystemID()) + ", ");
query.append(Utils.formatToSQL(context, context.getSystemID()) + ", ");
query.append(Utils.formatToSQL(context, new Date()) + ", ");
query.append(Utils.formatToSQL(context, new Date()) + ")");

mdfcPStmt = conn.prepareStatement(query.toString());
rtrn = _parseDifferences(saveToDB, mdocPStmt, mdfcPStmt);

Do you think the exception java.sql.SQLException: Il n'y a plus de données à lire dans le socket occured because the PrepareStatement was created like 5-10 min before its executeUpdate() ?


the solution I have found is to create the prepare statement at the moment:

query = new StringBuilder();
query.append("INSERT INTO MetaDataFieldChange ");
query.append("(MfcID, MfcMocID, MfcFldID, MfcNewFldID, MfcOldValue, MfcNewValue,");
query.append("MfcCreID, MfcModID, MfcCrDt, MfcUpd");
query.append(") VALUES (");
query.append("?, ?, ?, ?, ?, ?, ");
query.append(Utils.formatToSQL(context, context.getSystemID()) + ", ");
query.append(Utils.formatToSQL(context, context.getSystemID()) + ", ");
query.append(Utils.formatToSQL(context, new Date()) + ", ");
query.append(Utils.formatToSQL(context, new Date()) + ")");

rtrn = _parseDifferences(saveToDB, mdocPStmt, query);

and in _parseDifferences():

if (saveToDB) {
final Connection conn2 = context.getConnection();
try {
String mfcID = context.getIdFactory().getID();
int index = 1;
PreparedStatement mdfcPStmtStatement = conn2.prepareStatement(query.toString());
mdfcPStmtStatement.setString(index++, mfcID);
mdfcPStmtStatement.setString(index++, mdocID);
mdfcPStmtStatement.setString(index++, fldID);
mdfcPStmtStatement.setString(index++, newFldID);
mdfcPStmtStatement.setString(index++, diff.destValue);
mdfcPStmtStatement.setString(index++, diff.srcValue);

start = System.currentTimeMillis();
mdfcPStmtStatement.executeUpdate();
end = System.currentTimeMillis();
totalBeanSave += (end - start);
} catch (Exception e) {
msg = "Error processing differences for " + mdEnumStr + " - " + e.toString();
status = "KO";
count = "";
throw new FrontlineException(e.toString(), e);
} finally {
conn2.close();
}
 
Consider Paul's rocket mass heater.
 
subject: Oracle Clob error