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
PreparedStatement class in Java. But he is getting "ORA-01008 - Not All
Variables
Bound" when using the code.
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 +",
empty_clob(), '"+url+"', empty_clob(), "+siteStatus+", "+page_hits+",
'"+editor_choice+"')";
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
characters,
// then copy the contents into a file and update the database
after
reading the
// the contents from the file. Else update the database
directly from the
string.
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
FileWriter(descriptionFile));
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,
page_hits, editor_choice);
}
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).