PLS-001003 when calling stored procedure using JDBC ..

[Sumukh]

Hi all,
We are facing weired problem when we try to execute stored procedures
using JDBC.
Package was not getting compiled at all. We were getting

PLS-00103: Encountered the symbol "" when expecting one of the following:

Later I found a workaround of adding comment (--) after every line
in Stored proc.

Same stored procedure works without any problems, if we execute it on
the sql prompt. With JDBC only it gives errors.
We are using Oracle 8.1.7 and JDK 1.3 and classes12.zip

Has anyone faced this problem earlier ? How to split long lines in
stored procedure so that it can work with JDBC ?


Thanks

Sumukh

 

[steve]

Hi all,
We are facing weired problem when we try to execute stored procedures
using JDBC.
Package was not getting compiled at all. We were getting

PLS-00103: Encountered the symbol "" when expecting one of the following:

Later I found a workaround of adding comment (--) after every line
in Stored proc.

Same stored procedure works without any problems, if we execute it on
the sql prompt. With JDBC only it gives errors.
We are using Oracle 8.1.7 and JDK 1.3 and classes12.zip

Has anyone faced this problem earlier ? How to split long lines in
stored procedure so that it can work with JDBC ?


Thanks

Sumukh

simple!!
you cannot nest ' or '" in a database call , UNLESS you delimit them!!

EG.
a stupid way to code sql for oracle ( see nested ')
String The_qry =
"{?=call qareports.RETURN_oneinsprephed_rec("+recordIndex+")}";
cstmt =
(OracleCallableStatement)dbconn.prepareCall(The_qry);
cstmt.registerOutParameter(1, OracleTypes.CURSOR); bind
variable 1


I can break the above subquery by doing :
recordIndex="ZASF' "; // note the nested '




the CORRECT WAY ( ?=place holder for bind variable)

String The_qry = //used to identify this supplier,
externally
"{?=call qareports.RETURN_oneinsprephed_rec(?)}";
cstmt =
(OracleCallableStatement)dbconn.prepareCall(The_qry);
cstmt.registerOutParameter(1, OracleTypes.CURSOR); bind
variable 1
cstmt.setString(2, recordIndex); // bind variable 2

using the above oracle automatically delimits the " ' for me.


I would need to see the sql call to give you a code solution.


Steve