Don't know about the Java code...
ora- 1008 is normally encountered because of undefined global variable or
missing grants on the concerned table..
Please check from your end...
Thanks,
Amar Kumar Padhi.
-----Original Message-----
Sent: Monday, April 30, 2001 2:41 PM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Amar Kumar Padhi
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).
