using empty_clob() is not creating a pointer in Oracle DB

[Susan Mincey]

This works if I go straight to TOAD and enter the SQL statement insert manually. But going from my app doesn't work. I am using Vignette portal on Weblogic 8.1, Oracle DB 10g, and ojdbc14.jar for the connection pool. I have read countless examples and no one else seems to have issues - maybe someone can spot my error. I am getting an exception but the exception message is NULL. I am not finding any errors in the log files. Here's the code:
// Create SQL statement
sSQL="INSERT INTO PORTALTABLE (ID, DATE, TITLE, LEADER, "
+ "PATH, BODY "
+ ") VALUES ("
+ "SEQ_ID.nextval, "
+ "TO_DATE('" + sEnteredDate + "','MM/DD/YYYY HH24:MI'), "
+ "?,?,?,empty_clob())";

// Insert the new record into DB
try
{
initCtx = new InitialContext();
ds = (DataSource)initCtx.lookup(JDBCConn);
conn = ds.getConnection();
PreparedStatement ps = null;
ps.setString(1, story.getStoryTitle());
ps.setString(2, story.getStoryLeader());
ps.setString(3, story.getPathColorClip());
ps = conn.prepareStatement(sSQL);
ps.executeUpdate() ;
plog.log("InsertStory : empty_clob inserted");

[Fatih Keles]

Hi Susan,

You are setting values before initializing PreparedStatement with ps = conn.prepareStatement(sSQL).

empty_clob() is working fine within jdbc, as Oracle documentation says and as far as I have tested.

Database:

 
drop table xxcm.xxcm_clob_test

create table xxcm.xxcm_clob_test(test_id number,test_source varchar2(200), clob_col clob)

insert into xxcm.xxcm_clob_test(test_id,test_source,clob_col)values(1,'PL/SQL',empty_clob())

Java:

    public static void main(String[] args) throws SQLException {
        // Load the Oracle JDBC driver
        DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ());
        // Connect to the database: 
        Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1522:TEST", "xxcm", "xxcm");
        
        PreparedStatement ps = conn.prepareStatement("insert into xxcm.xxcm_clob_test(test_id,test_source,clob_col)values(?,?,empty_clob())");
        ps.setInt(1,2);
        ps.setString(2,"JDBC");
        ps.executeUpdate();
        ps.close();
        conn.close();
    }

and the control select statement:

select t.*,dbms_lob.getlength(clob_col)
  from xxcm.xxcm_clob_test t;

TEST_ID	TEST_SOURCE	CLOB_COL	DBMS_LOB.GETLENGTH(CLOB_COL)
1	PL/SQL	<CLOB>	0
2	JDBC	<CLOB>	0

Regards,

Fatih.

[Susan Mincey]

Hello Fatih,
You are absolutely correct! I can't believe I did not "see" that. It just shows that sometimes we get too close and cannot see the details. I have corrected my code and of course it works just fine. I appreciate your input and I will try not to repeat that mistake.
Have a great week.
Susan