JDBC, JPublisher and LOBs

How Do I Insert a Row With Empty LOB Locator into Table Using JDBC?

Question

Is it possible to insert a row with an empty LOB locator into a table using JDBC?

Answer

You can use the EMPTY_BLOB() in JDBC also.

 Statement stmt = conn.createStatement() ;  
   try {  
   stmt.execute ("insert into lobtable values (empty_blob())");  
   }  
   catch{ ...}  

Another example is:

  stmt.execute ("drop table lobtran_table");  
  stmt.execute ("create table lobtran_table (b1 blob, b2 blob, c1 clob,  
               c2 clob, f1 bfile, f2 bfile)");  
  stmt.execute ("insert into lobtran_table values  
         ('010101010101010101010101010101', empty_blob(),
          'onetwothreefour', empty_clob(), 
          bfilename('TEST_DIR','tkpjobLOB11.dat'), 
          bfilename ('TEST_DIR','tkpjobLOB12.dat'))");  

How Do I setData to EMPTY_BLOB() Using JPublisher?

Question

How do I setData to EMPTY_BLOB() Using JPublisher? Is there something like EMPTY_BLOB() and EMPTY_CLOB() in a Java statement, not a SQL statement processed by JDBC? How do we setData to an EMPTY_BLOB() using JPublisher?

Answer

One way to build an empty LOB in JPublisher would be as follows:

BLOB b1 = new BLOB(conn, null) ;  

You can use b1 in set method for data column.

JDBC: Do OracleBlob and OracleClob Work in 8.1.x?

Question

Do OracleBlob and OracleClob work in 8.1.x?

Answer

OracleBlob and OracleClob were Oracle specific functions used in JDBC 8.0.x drivers to access LOB data. In 8.1.x and future releases, OracleBlob and OracleClob are deprecated.

If you use OracleBlob or OracleClob to access LOB data, you will receive the following typical error message, for example, when attempting to manipulate LOBs with Oracle8i release 8.1.5 JDBC Thin Driver :

"Dumping lobs java.sql.SQLException: ORA-03115: unsupported network datatype or 
representation etc."

See release 8.1.5 Oracle8i JDBC Developer's Guide and Reference for a description of these non-supported functions and alternative and improved JDBC methods.

For further ideas on working with LOBs with Java, refer to the LOB Example sample shipped with Oracle8i or get a LOB example from http://www.oracle.com/java/jdbc.

How Do I Manipulate LOBs With the 8.1.5 JDBC Thin Driver?

Question

Has anyone come across the following error when attempting to manipulate LOBs with the 8.1.5 JDBC Thin Driver:

Dumping lobs
java.sql.SQLException: ORA-03115: unsupported network datatype or representation
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:181)
at oracle.jdbc.ttc7.Odscrarr.receive(Compiled Code)
at oracle.jdbc.ttc7.TTC7Protocol.describe(Compiled Code)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java: 516)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1002)
at oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1163)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme 
nt.java:1211)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java: 201)
at LobExample.main(Compiled Code)

---------------------------------

The code I'm using is the LobExample.java shipped with 8.0.5. This sample was initially and OCI8 sample. One difference is that I am using the 8.1.5 Thin Driver against an 8.1.5 instance.

Answer

You are using a wrong sample. OracleBlob and OracleClob have been deprecated and they no longer work. Try with the LobExample sample with Oracle8i or you can get it from http://www.oracle.com/java/jdbc

Is the FOR UPDATE Clause Needed on SELECT When Writing to a LOB?

Question

I am running a Java stored procedure that writes a CLOB and am getting an exception as follows:

ORA-22920: row containing the LOB value is not locked

ORA-06512: at "SYS.DBMS_LOB", line 708

ORA-06512: at line 1

Once I added a 'FOR UPDATE' clause to my SELECT statement, this exception did not occur.

I feel that the JDBC Developer's Guide and Reference(8.1.5) should be updated to reflect the need for the 'FOR UPDATE' clause on the SELECT. Specifically, I think the two sections under Working with LOBs, Getting BLOB and CLOB Locators (page 4-46 to 4-47) and Creating and Populating a BLOB or CLOB Column (pages 4-52 to 4-54), should be updated.

Answer

This is not a JDBC issue in specific. This is how LOBs work! This got manifested in the JSP because by default autoCommit is false. You would also see the same exception when autoCommit is set to false on the client side. You didn't see the exception when used with 'For Update' because locks are acquired explicitly.