| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> ORA - 1008 error when trying to insert data into a table
We are having a table TestSite with the following fields.
Name Null? Type
----------------------------------------- -------- ------------
PK_SITE_ID NOT NULL NUMBER
FK_CATEGORY NOT NULL NUMBER
TITLE CLOB
URL NOT NULL
VARCHAR2(4000)
DESCRIPTION CLOB
STATUS NOT NULL NUMBER
PAGE_HITS NOT NULL NUMBER
EDITOR_CHOICE VARCHAR2(10)
One of our developers has written code for inserting the data into this
table using
Earlier code was with normal Statement class in Java. It was working
fine without any errors. Could any of you please let me know as to what the
problem
could be?
The java code is as follows:
import java.io.*;
import java.sql.*;
import oracle.sql.CLOB; import oracle.jdbc.driver.OracleResultSet; import oracle.jdbc.driver.OracleCallableStatement;
public class TestPreparedStatement
{
private static void setSiteData(int pk_site_id, int fk_category, String title, String url, String description, int siteStatus, int page_hits, String editor_choice)
{
ResultSet resultSet = null;
//Statement stmt = null;
PreparedStatement stmt = null;
Connection con = null;
oracle.sql.CLOB clobTitle = null;
oracle.sql.CLOB clobDescription = null;
String strSQL = "",strSelectSQL = "";
int nTemp1=0;
int nTemp2=0;
int k1 = 0;
int k2 = 0;
try
{
con=getConnection();
con.setAutoCommit(false);
/*
stmt = con.createStatement();
strSQL = "INSERT INTO SITE VALUES (" +pk_site_id +",
"+fk_category +",
stmt.execute (strSQL);
strSelectSQL = "SELECT * FROM TESTSITE where pk_site_id=" +
pk_site_id;
ResultSet rset = stmt.executeQuery(strSelectSQL);
*/
strSQL = "INSERT INTO TESTSITE VALUES (?, ?, empty_clob(),
?,empty_clob(), ?, ?, ?)";
stmt = con.prepareStatement(strSQL);
stmt.setInt(1,pk_site_id);
stmt.setInt(2,fk_category);
stmt.setString(3,url);
stmt.setInt(4,siteStatus);
stmt.setInt(5,page_hits);
stmt.setString(6,editor_choice);
stmt.executeUpdate(strSQL);
strSelectSQL = "SELECT * FROM TESTSITE where pk_site_id = ?";
stmt = con.prepareStatement(strSQL);
stmt.setInt(1,pk_site_id);
ResultSet rset = stmt.executeQuery(strSelectSQL);
while(rset.next())
{
clobTitle =
((OracleResultSet)rset).getCLOB(3);
clobDescription = ((OracleResultSet)rset).getCLOB(5);
break;
}
// if the size of the title and description is greater than
60,000
// then copy the contents into a file and update the database
after
// the contents from the file. Else update the database
directly from the
int nTitle = title.length();
int nDescription = description.length();
java.io.Writer titleWriter, descriptionWriter;
if(nTitle > 60000)
{
File titleFile = new File("Site"+pk_site_id+".txt");
// copy the contents of the of the string into the file
BufferedWriter brWriterTitle = new BufferedWriter(new
FileWriter(titleFile));
brWriterTitle.write(title,0,nTitle);
brWriterTitle.flush();
brWriterTitle.close();
FileInputStream instreamTitle = new
FileInputStream(titleFile);
titleWriter =
((CLOB)clobTitle).getCharacterOutputStream();
int size = clobTitle.getBufferSize();
BufferedReader br = new BufferedReader(new
InputStreamReader(instreamTitle));
while(true)
{
nTemp1=0;
k1=0;
char cData[] = new char[size];
for(;nTemp1<size; nTemp1+=k1)
{
k1 = br.read(cData,nTemp1,size-nTemp1);
if(k1==-1) break;
}
titleWriter.write(cData,0,size);
if(k1==-1) break;
}
instreamTitle.close();
titleWriter.flush();
titleWriter.close();
// delete the created file.
titleFile.delete();
}
else
{
StringBuffer strBuff = new StringBuffer(title);
titleWriter =
((CLOB)clobTitle).getCharacterOutputStream();
titleWriter.write(strBuff.toString());
titleWriter.flush();
titleWriter.close();
}
if(nDescription > 60000)
{
File descriptionFile = new
File("Site"+pk_site_id+".txt");
// copy the contents of the of the string into the file
BufferedWriter brWriterDescription = new
BufferedWriter(new
brWriterDescription.write(description,0,nDescription);
brWriterDescription.flush();
brWriterDescription.close();
FileInputStream instreamDescription = new
FileInputStream(descriptionFile);
descriptionWriter =
((CLOB)clobDescription).getCharacterOutputStream();
int size = clobDescription.getBufferSize();
BufferedReader br = new BufferedReader(new
InputStreamReader(instreamDescription));
while(true)
{
nTemp2=0;
k2=0;
char cData[] = new char[size];
for(;nTemp2<size; nTemp2+=k2)
{
k2 = br.read(cData,nTemp2,size-nTemp2);
if(k2==-1) break;
}
descriptionWriter.write(cData,0,size);
if(k2==-1) break;
}
instreamDescription.close();
descriptionWriter.flush();
descriptionWriter.close();
// delete the created file.
descriptionFile.delete();
}
else{
StringBuffer data = new StringBuffer(description);
descriptionWriter =
((CLOB)clobDescription).getCharacterOutputStream();
descriptionWriter.write(data.toString());
descriptionWriter.flush();
descriptionWriter.close();
}
if(con != null)
con.commit();
}
catch(SQLException se)
{
System.out.println("method=setSiteData() FOCUS: " +
se.toString());
}
catch(Exception e)
{
System.out.println("method=setSiteData() FOCUS: " +
e.toString());
}
finally
{
try
{
stmt.close();
if(con != null)
{
con.setAutoCommit(true);
con.close();
con=null;
}
}
catch(Exception e)
{
System.out.println("method=setSiteData() FOCUS: " +
e.toString());
}
}
}
public static void main(String args[])
{
try
{
int pk_site_id = 0;
int fk_category =1;
String title = "Sample Title";
String url = "Sample URL";
String description = "Sample Description";
int siteStatus = 0;
int page_hits = 0;
String editor_choice = "Y";
setSiteData(pk_site_id, fk_category, title, url, description,
siteStatus,
}
catch(Exception e)
{
System.out.println("Exception in Main : " + e.toString());
}
}
private static Connection getConnection() throws Exception
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
String dburl = "jdbc:oracle:thin:@172.19.24.123:1521:DMOZ";
Connection dbcon =
DriverManager.getConnection(dburl,"system","manager");
return dbcon;
}
catch(SQLException nExp)
{
throw new Exception(nExp.toString());
}
}
Any help in this regard will be greatly appreciated.
TIA and Regards,
Ranganath
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: ranganathk_at_subexgroup.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Apr 30 2001 - 05:07:55 CDT
 |
 |