Help. Problem inserting Clob into Oracle 9i

[Joseph Urbanek]

I am attempting to preform an SQL insert of a Clob into an Oracle 9i database.
My Environment;
I am using WebSphere Studio Application Developer version: 5.0.1
java.vm.info=J2RE 1.3.1 IBM Windows 32 build cn131-20020710 (JIT
enabled: jitc)
I am including my code. Any assistance is appreciated.
I am getting the following error:
java.sql.SQLException: ORA-01006: bind variable does not exist
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at SearchSchedTaskDataTest.process(SearchSchedTaskDataTest.java:165)
at SearchSchedTaskDataTest.main(SearchSchedTaskDataTest.java:201)
**************************************************************************
/**************************************************************************
**************************************************************************
**************************************************************************
**************************************************************************/
/**
*This tests add/updates and deletes of SearchSchedTask Data from the database
*/
public class SearchSchedTaskDataTest {
Connection conn;
SearchSchedTaskData searchSchedTaskData;
int rowCount;
/*private static final String psAddSearchSchedTask =
"INSERT INTO SEARCH_SCHED_TASK "
+ "( search_sched_task_id, GROUP_ID, client_id, search_payload, "
+ "target_name, search_fequency, active, answer_limit, task_creation_time, "
+ "last_run_time, task_modified_time, task_last_result_time, title) "
+ "VALUES( ?, ?, ?, empty_clob(), ?, ?, ?, ?, ?, ?, ?, ?, ? )";*/
private static final String psAddSearchSchedTask =
"INSERT INTO SEARCH_SCHED_TASK "
+ "( search_sched_task_id, GROUP_ID, client_id, search_payload) "
+ "VALUES( ?, ?, ?, empty_clob() )";
/**
* @see java.lang.Object#Object()
*/
public SearchSchedTaskDataTest() {
super();
}
/**
* Method process.
* @throws IOException
* @throws SQLException
*/
public void process() {
searchSchedTaskData = new SearchSchedTaskData();
searchSchedTaskData.setScheduleSearchTaskID(
new BigInteger(Long.toString(System.currentTimeMillis())));
searchSchedTaskData.setGroupID("1111111111");
searchSchedTaskData.setClientID("1111111111");
searchSchedTaskData.setSearchPayload(
"<Bob><Clothing><ShoeSize>"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "000000000000000000000000000000000000000000000000000000000"
+ "</ShoeSize></Clothing></Bob>");
/*searchSchedTaskData.setTargetName("Joe Schmoe");
searchSchedTaskData.setSearchFrequency("DAILY");
searchSchedTaskData.setActive("T");
searchSchedTaskData.setAnswerLimit(new Integer("1"));
Timestamp currentTime = new Timestamp(System.currentTimeMillis());
searchSchedTaskData.setTaskCreationTime(currentTime);
searchSchedTaskData.setLastRunTime(currentTime);
searchSchedTaskData.setTaskModifiedTime(currentTime);
searchSchedTaskData.setTaskLastResultTime(currentTime);
searchSchedTaskData.setTitle("A Scientific study of Joe Schmoe");*/
ResultSet rslt = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
PreparedStatement pstmt3 = null;
StringReader sr = null;
CLOB tempClob = null;
try {
//Class.forName("oracle.jdbc.driver.OracleDriver");
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn =
DriverManager.getConnection(
"jdbc racle:thin:@dvdb7512:1521 VWALDO",
"SSSdev",
"SSSdev");
conn.setAutoCommit(false);
pstmt1 = conn.prepareStatement(psAddSearchSchedTask);
/*"INSERT INTO SEARCH_SCHED_TASK "
+ "( 1 search_sched_task_id, 2 GROUP_ID, 3 client_id, 4 search_payload, "
+ "5 target_name, 6 search_fequency, 7 active, 8 answer_limit, 9 task_creation_time, "
+ "10 last_run_time, 11 task_modified_time, 12 task_last_result_time, 13 title) "
+ "VALUES( ?, ?, ?, empty_clob(), ?, ?, ?, ?, ?, ?, ?, ?, ? )"; */
int payloadLength = searchSchedTaskData.getSearchPayload().length();
sr = new StringReader(searchSchedTaskData.getSearchPayload());
pstmt1.setLong(
1,
searchSchedTaskData.getScheduleSearchTaskID().longValue());
pstmt1.setString(2, searchSchedTaskData.getGroupID().trim());
pstmt1.setString(3, searchSchedTaskData.getClientID().trim());
pstmt1.setCharacterStream(4, sr, payloadLength);
/*pstmt1.setString(5, searchSchedTaskData.getTargetName().trim());
pstmt1.setString(
6,
searchSchedTaskData.getSearchFrequency().trim());
pstmt1.setString(7, searchSchedTaskData.getActive().trim());
pstmt1.setInt(8, searchSchedTaskData.getAnswerLimit().intValue());
pstmt1.setTimestamp(9, searchSchedTaskData.getTaskCreationTime());
pstmt1.setTimestamp(10, searchSchedTaskData.getLastRunTime());
pstmt1.setTimestamp(11, searchSchedTaskData.getTaskModifiedTime());
pstmt1.setTimestamp(
12,
searchSchedTaskData.getTaskLastResultTime());
pstmt1.setString(13, searchSchedTaskData.getTitle().trim());*/
rowCount = pstmt1.executeUpdate();
System.out.println(
rowCount + " rows inserted into SEARCH_SCHED_TASK Table");
conn.commit();
//sr.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rslt != null)
try {
rslt.close();
rslt = null;
} catch (SQLException ignore) {
}
if (pstmt1 != null)
try {
pstmt1.close();
} catch (SQLException ignore) {
}
if (conn != null)
try {
conn.close();
} catch (SQLException ignore) {
}
}
}
/**
* Method main.
* @param args
*/
public static void main(String[] args) {
try {
new SearchSchedTaskDataTest().process();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/*************************************************************************/
import java.math.BigInteger;
import java.sql.Clob;
import java.sql.Timestamp;
/**
* Search Schedule Task business object that maps to rows in the
* SEARCH_SCHED_TASK database table. Contains getters/setters for all table
* columns as well as toString()..
*/
public class SearchSchedTaskData {

private BigInteger ScheduleSearchTaskID;
private String m_groupID;
private String m_clientID;
private String m_searchPayload;
private String m_targetName;
private String m_searchFrequency;
private String m_active;
private Integer m_answerLimit;
private Timestamp m_taskCreationTime;
private Timestamp m_lastRunTime;
private Timestamp m_taskModifiedTime;
private Timestamp m_taskLastResultTime;
private String m_title;

public SearchSchedTaskData() {
super();
}

/**
* Returns the active.
* @return String
*/
public String getActive() {
return m_active;
}
/**
* Returns the answerLimit.
* @return Integer
*/
public Integer getAnswerLimit() {
return m_answerLimit;
}
/**
* Returns the clientID.
* @return String
*/
public String getClientID() {
return m_clientID;
}
/**
* Returns the groupID.
* @return String
*/
public String getGroupID() {
return m_groupID;
}
/**
* Returns the searchFrequency.
* @return String
*/
public String getSearchFrequency() {
return m_searchFrequency;
}
/**
* Returns the searchPayload.
* @return String
*/
public String getSearchPayload() {
return m_searchPayload;
}
/**
* Returns the targetName.
* @return String
*/
public String getTargetName() {
return m_targetName;
}
/**
* Returns the taskCreationTime.
* @return Timestamp
*/
public Timestamp getTaskCreationTime() {
return m_taskCreationTime;
}
/**
* Returns the taskLastResultTime.
* @return Timestamp
*/
public Timestamp getTaskLastResultTime() {
return m_taskLastResultTime;
}
/**
* Returns the taskModifiedTime.
* @return Timestamp
*/
public Timestamp getTaskModifiedTime() {
return m_taskModifiedTime;
}
/**
* Returns the title.
* @return String
*/
public String getTitle() {
return m_title;
}
/**
* Returns the scheduleSearchTaskID.
* @return BigInteger
*/
public BigInteger getScheduleSearchTaskID() {
return ScheduleSearchTaskID;
}
/**
* Sets the active.
* @param active The active to set
*/
public void setActive(String active) {
m_active = active;
}
/**
* Sets the answerLimit.
* @param answerLimit The answerLimit to set
*/
public void setAnswerLimit(Integer answerLimit) {
m_answerLimit = answerLimit;
}
/**
* Sets the clientID.
* @param clientID The clientID to set
*/
public void setClientID(String clientID) {
m_clientID = clientID;
}
/**
* Sets the groupID.
* @param groupID The groupID to set
*/
public void setGroupID(String groupID) {
m_groupID = groupID;
}
/**
* Sets the searchFrequency.
* @param searchFrequency The searchFrequency to set
*/
public void setSearchFrequency(String searchFrequency) {
m_searchFrequency = searchFrequency;
}
/**
* Sets the searchPayload.
* @param searchPayload The searchPayload to set
*/
public void setSearchPayload(String searchPayload) {
m_searchPayload = searchPayload;
}
/**
* Sets the targetName.
* @param targetName The targetName to set
*/
public void setTargetName(String targetName) {
m_targetName = targetName;
}
/**
* Sets the taskCreationTime.
* @param taskCreationTime The taskCreationTime to set
*/
public void setTaskCreationTime(Timestamp taskCreationTime) {
m_taskCreationTime = taskCreationTime;
}
/**
* Sets the taskLastResultTime.
* @param taskLastResultTime The taskLastResultTime to set
*/
public void setTaskLastResultTime(Timestamp taskLastResultTime) {
m_taskLastResultTime = taskLastResultTime;
}
/**
* Sets the taskModifiedTime.
* @param taskModifiedTime The taskModifiedTime to set
*/
public void setTaskModifiedTime(Timestamp taskModifiedTime) {
m_taskModifiedTime = taskModifiedTime;
}
/**
* Sets the title.
* @param title The title to set
*/
public void setTitle(String title) {
m_title = title;
}
/**
* Sets the scheduleSearchTaskID.
* @param scheduleSearchTaskID The scheduleSearchTaskID to set
*/
public void setScheduleSearchTaskID(BigInteger scheduleSearchTaskID) {
ScheduleSearchTaskID = scheduleSearchTaskID;
}
/**
* Returns the lastRunTime.
* @return Timestamp
*/
public Timestamp getLastRunTime() {
return m_lastRunTime;
}
/**
* Sets the lastRunTime.
* @param lastRunTime The lastRunTime to set
*/
public void setLastRunTime(Timestamp lastRunTime) {
m_lastRunTime = lastRunTime;
}
}
/*DATBASE SCHEMA
************************************************************************************************************************************************************************************************************************************/

ALTER TABLE SEARCH_SCHED_TASK DROP PRIMARY KEY CASCADE;
DROP TABLE SEARCH_SCHED_TASK CASCADE CONSTRAINTS;
CREATE TABLE SEARCH_SCHED_TASK
(
SEARCH_SCHED_TASK_ID NUMBER,
GROUP_ID VARCHAR2(255 BYTE),
CLIENT_ID VARCHAR2(10 BYTE),
SEARCH_PAYLOAD CLOB,
TARGET_NAME VARCHAR2(10 BYTE),
SEARCH_FEQUENCY VARCHAR2(5 BYTE),
ACTIVE VARCHAR2(1 BYTE),
ANSWER_LIMIT NUMBER,
TASK_CREATION_TIME DATE,
LAST_RUN_TIME DATE,
TASK_MODIFIED_TIME DATE,
TASK_LAST_RESULT_TIME DATE,
TITLE VARCHAR2(255 BYTE)
)
TABLESPACE WALDO_DATA_01
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
LOB (SEARCH_PAYLOAD) STORE AS
( TABLESPACE WALDO_DATA_01
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL;
COMMENT ON COLUMN SEARCH_SCHED_TASK.SEARCH_SCHED_TASK_ID IS 'SEARCH_SCHED_TASK_ID identifies SEARCH_SCHED_TASK';
COMMENT ON COLUMN SEARCH_SCHED_TASK.GROUP_ID IS 'GROUP_ID IS AN INDEXED FIELD FOR GROUPING SEARCH TASKS';
COMMENT ON COLUMN SEARCH_SCHED_TASK.CLIENT_ID IS 'CLIENT_ID (idx)';
COMMENT ON COLUMN SEARCH_SCHED_TASK.SEARCH_PAYLOAD IS '*SEARCH_PAYLOAD IS A BIG XML STRING TO HOLD THE ACTUAL SEARCH TO SUBMIT TO THE SEARCH TARGET';
COMMENT ON COLUMN SEARCH_SCHED_TASK.TARGET_NAME IS 'SEARCH_TARGET IS A FK TO SEARCH_TARGET_REF';
COMMENT ON COLUMN SEARCH_SCHED_TASK.SEARCH_FEQUENCY IS 'SEARCH_FEQUENCY (CONSTRAIN: DAY,WEEK,MONTH)';
COMMENT ON COLUMN SEARCH_SCHED_TASK.ACTIVE IS 'ACTIVE(CONSTRAIN:Y/N)';
COMMENT ON COLUMN SEARCH_SCHED_TASK.ANSWER_LIMIT IS 'ANSWER_LIMIT (0=no limit)';
COMMENT ON COLUMN SEARCH_SCHED_TASK.TASK_CREATION_TIME IS 'TASK_CREATION_TIME is of SEARCH_SCHED_TASK';
COMMENT ON COLUMN SEARCH_SCHED_TASK.LAST_RUN_TIME IS 'Last time the Task was run';
COMMENT ON COLUMN SEARCH_SCHED_TASK.TASK_MODIFIED_TIME IS 'TASK_MODIFIED_TIME is of SEARCH_SCHED_TASK';
COMMENT ON COLUMN SEARCH_SCHED_TASK.TASK_LAST_RESULT_TIME IS 'TASK_LAST_RESULT_TIME is of SEARCH_SCHED_TASK';
COMMENT ON COLUMN SEARCH_SCHED_TASK.TITLE IS 'HUMAN READABLE NAME FOR THIS TASK';

CREATE INDEX I1_GROUP_ID ON SEARCH_SCHED_TASK
(GROUP_ID)
LOGGING
TABLESPACE WALDO_DATA_01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE INDEX I2_CLIENT_ID ON SEARCH_SCHED_TASK
(CLIENT_ID)
LOGGING
TABLESPACE WALDO_DATA_01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE UNIQUE INDEX PK_SEARCH_SCHED_TASK ON SEARCH_SCHED_TASK
(SEARCH_SCHED_TASK_ID)
LOGGING
TABLESPACE WALDO_DATA_01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

ALTER TABLE SEARCH_SCHED_TASK ADD (
CONSTRAINT PK_SEARCH_SCHED_TASK PRIMARY KEY (SEARCH_SCHED_TASK_ID)
USING INDEX
TABLESPACE WALDO_DATA_01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));

ALTER TABLE SEARCH_SCHED_TASK ADD (
CONSTRAINT FK1_TARGET_NAME FOREIGN KEY (TARGET_NAME)
REFERENCES SEARCH_SCHED_TARGET_REF (TARGET_NAME));

ALTER TABLE SSSDEV.SEARCH_SCHED_TRANS ADD (
CONSTRAINT FK1_SEARCH_SCHED_TASK_ID FOREIGN KEY (SEARCH_SCHED_TASK_ID)
REFERENCES SSSDEV.SEARCH_SCHED_TASK (SEARCH_SCHED_TASK_ID));
ALTER TABLE SSSDEV.SEARCH_SCHED_RESULT ADD (
CONSTRAINT FK1_SEARCH_TASK_ID FOREIGN KEY (SEARCH_TASK_ID)
REFERENCES SSSDEV.SEARCH_SCHED_TASK (SEARCH_SCHED_TASK_ID));
ALTER TABLE SSSDEV.SEARCH_SCHED_CL_METADATA ADD (
CONSTRAINT FK2_SEARCH_SCHED_TASK_ID FOREIGN KEY (SEARCH_SCHED_TASK_ID)
REFERENCES SSSDEV.SEARCH_SCHED_TASK (SEARCH_SCHED_TASK_ID));

[Tim Morrow]

Unfortunately you cannot insert a clob in one statement. You have to insert the empty_clob() then select ti back and write the data to the stream.
Disclaimer: Just typing this in here; there may be spelling / syntax errors.
1) Insert the rest of the data, creating the empty clob. You can use the same statement as you've already got:

"INSERT INTO SEARCH_SCHED_TASK "
            + "( search_sched_task_id, GROUP_ID, client_id, search_payload) "
            + "VALUES( ?, ?, ?, empty_clob() )"

2) Select the clob locate back by executing something like this:

oracle.sql.CLOB clob;
PreparedStatement pstmt = conn.prepareStatement("SELECT search_payload FROM SEARCH_SCHED_TASK WHERE search_sched_task_id = ? FOR UPDATE NOWAIT);
pstmt.setLong(1, searchSchedTaskData.getScheduleSearchTaskID().longValue());
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
    clob = ((oracle.jdbc.OracleResultSet) rs).getCLOB("search_payload");
}
3) Now you can write your data back.  I personally haven't used the JDBC 3.0 "setCharacterOutputStream" method yet, so my example uses Oracle's proprietary "getCharacterOutputStream":
[CODE]
BufferedWriter writer = new BufferedWriter(clob.getCharacterOutputStream());
writer.write(searchSchedTaskData.getSearchPayload().toString());
writer.flush();

Tim

[Tim Morrow]

Someone helpfully posted a link to an Oracle page with plenty of example code in the reply to your previous question.

Your previous question