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 Oracle9i 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.

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.

What Does DBMS_LOB.ERASE Do?

Question

What is DBMS_LOB.ERASE doing?

Answer

It's just "clearing" a segment of the CLOB. It does *not* shorten the CLOB. So the length of the CLOB is the same before and after the ERASE. You can use DBMS_LOB.TRIM to make a CLOB shorter.

Can I Use putChars()?

Question

Can I use oracle.sql.CLOB.putChars()?

Answer

Yes, you can, but you have to make sure that the position and length arguments are correct. You can also use the recommended OutputStream interface which in turn will call putChars for you.

Manipulating CLOB CharSetId in JDBC

Question

OCI provides function to manipulate a CLOB CharSetId. What is the JDBC equivalent?

Answer

In JDBC CLOBs are *always* in USC2, which is the Oracle character set corresponding to the Java "char" type. So there is no equivalent for the OCI CLOB CharSetId.

Why is Inserting into BLOBs Slower than into LONG Raws?

Question

Why is writing into BLOBs slower than inserting into LONG RAWs?

Answer

It is true that inserting data in BLOBs with JDBC Thin is slower as it still uses the DBMS_LOB package. With JDBC OCI the inserts are faster as native LOB APIs are used.

Why Do I Get an ORA-03127 Error with LobLength on a LONG Column?

Question

Why am I getting an ORA-03127 error when getting the LobLength on a LONG column?

Answer

This is the correct behavior. LONG columns are not 'fetched' in-place (aka in-row). They are fetched out of place and exists in the pipe until you read them explicitly. In this case, we got the LobLocator (getBlob()) and then we are trying to get the length of this LOB before we read the LONG column. Since the pipe is not clear we are getting the above exception. The solution would be to complete reading the LONG column before you do any operation on the BLOB.

How Do I Create a CLOB Object in a Java Program?

Question

Here is what I'm trying to do with CLOBs through JDBC:

1. Create a CLOB object in a Java program

2. Populate the CLOB with the characters in a String passed into my program

3. Prepare a call to a stored procedure that receives a CLOB as a parameter.

4. Set the parameter with the Java CLOB

5. Execute

I was looking at the method SQLUtil.makeOracleDatum(), but that doesn't work. I get an invalid type error message. The only Oracle examples I've seen have the CLOB object created by reading it in from Oracle through a SQL object. I need to create the CLOB in the Java program.

Answer

This cannot be done as you describe here. The oracle.sql.CLOB class encapsulates a CLOB locator, not the actual data for the CLOB, and the CLOB locator must come from the database. There is no way currently to construct a CLOB locator in the client. You need to insert an empty_clob() into the table, retrieve the locator, and then write the data to the CLOB.

PLSQL procedures can be poor vehicles for this particular functionality.

If you make the PLSQL parameter of the CLOB type, it represents the CLOB locator and you still have to use some other interface to write the data to the CLOB. And, passing all the data to PLSQL as a VARCHAR2 or LONG parameter is also a problem because PLSQL parameters are limited to 32K, which is rarely enough to be practically useful in this context.

I would recommend just using the standard JDBC API's for dealing with the CLOB.

You need to encapsulate the entire functionality required to insert a CLOB, in a single stored procedure invoked from a client applicatiLoading LOBs and Data Into LOBs.

How do I Load a 1Mb File into a CLOB Column?

Question

How do I insert a file of 1Mb which is stored on disk, into a CLOB column of my table. I thought DBMS_LOB.LOADFROMFILE should do the trick, but, the document says it is valid for BFILE only. How do I do this?

Answer

You can use SQL*Loader. See Oracle9i Utilities or in this manual, hapter 4, "Managing LOBs", Using SQL*Loader to Load LOBson .

You can use loadfromfile() to load data into a CLOB, but the data is transferred from the BFILE as raw data -- i.e., no character set conversions are performed. It is up to you to do the character set conversions yourself before calling loadfromfile().

Use OCILobWrite() with a callback. The callback can read from the operating system (OS) file and convert the data to the database character set (if it's different than the OS file's character set) and then write the data to the CLOB.

How Do We Improve BLOB and CLOB Performance When Using JDBC Driver To Load?

Question

We are facing a performance problem concerning BLOBs and CLOBs. Much time is consumed when loading data into the BLOB or CLOB using JDBC Driver.

Answer

It's true that inserting data into LOBs using JDBC Thin driver is slower as it still uses the DBMS_LOB package and this adds the overhead of a full JDBC CallableStatement execution for each LOB operation.

With the JDBC OCI and JDBC server-side internal drivers, the inserts are faster because native LOB APIs are used. There is no extra overhead from JDBC driver implementation.

It's recommended that you use InputStream and OutputStream for accessing and manipulating LOB data. By using streaming access of LOBs, JDBC driver will handle the buffering of the LOB data properly to reduce the number of network round-trips and ensure that each database operation uses a data size as a multiple of the LOB's natural chunk size.

Here is an example that uses OutputStream to write data to a BLOB:

/*

* This sample writes the GIF file john.gif to a BLOB.

*/

import java.sql.*;
import java.io.*;
import java.util.*;

// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.driver.*;

//needed for new CLOB and BLOB classes
import oracle.sql.*;

public class LobExample
{
  public static void main (String args [])
       throws Exception
  {
    // Register the Oracle JDBC 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 auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("drop table persons");
    }
    catch (SQLException e)
    {
      // An exception could be raised here if the table did not exist already.
    }

    // Create a table containing a BLOB and a CLOB
    stmt.execute ("create table persons (name varchar2 (30), picture blob)");
    
    // Populate the table
    stmt.execute ("insert into persons values ('John', EMPTY_BLOB())");
    
    // Select the BLOB 
    ResultSet rset = stmt.executeQuery ("select picture from persons where name 
= 'John'");
    if (rset.next ())
    {
      // Get the BLOB locator from the table
      BLOB blob = ((OracleResultSet)rset).getBLOB (1);

      // Declare a file handler for the john.gif file
      File binaryFile = new File ("john.gif");

      // Create a FileInputStream object to read the contents of the GIF file
      FileInputStream istream = new FileInputStream (binaryFile);

      // Create an OutputStram object to write the BLOB as a stream
      OutputStream ostream = blob.getBinaryOutputStream ();

      // Create a tempory buffer  
      byte[] buffer = new byte[1024];
      int length = 0;

      // Use the read() method to read the GIF file to the byte 
      // array buffer, then use the write() method to write it to 
      // the BLOB.
      while ((length = istream.read(buffer)) != -1)
        ostream.write(buffer, 0, length);

      // Close the inputstream and outputstream
      istream.close();
      ostream.close();

      // Check the BLOB size
      System.out.println ("Number of bytes written = "+blob.length());
    }

    // Close all resources
    rset.close();
    stmt.close();
    conn.close(); 
  }
}

Note that you'll get even better performance if you use DBMS_LOB.LOADFROMFILE() instead of using DBMS_LOB.WRITE().

In order to be able to use DBMS_LOB.LOADFROMFILE(), the data to be written into the LOB must be in a server-side file.