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

Help. Problem inserting Clob into Oracle 9i

Joseph Urbanek
Greenhorn

Joined: Sep 17, 2003
Posts: 3
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
Greenhorn

Joined: Sep 17, 2003
Posts: 11
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:

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

Tim
Tim Morrow
Greenhorn

Joined: Sep 17, 2003
Posts: 11
Someone helpfully posted a link to an Oracle page with plenty of example code in the reply to your previous question.

Your previous question
 
 
subject: Help. Problem inserting Clob into Oracle 9i