File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Oracle Stored Procedure Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle Stored Procedure Problem" Watch "Oracle Stored Procedure Problem" New topic

Oracle Stored Procedure Problem

k chillar

Joined: Oct 12, 2003
Posts: 2
Hi I am, trying to run a oracle 9.i stored procedure from jboss It is giving me the following error, Can any one suggest in what direction should I look for solving this problem
Thank you.
My Stored procedure : ( 33 parameters )
"{call InsCustTxnLookupDtls(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
Argument List:
1 i_session_id IN VARCHAR2,
2 i_cust_session_id IN VARCHAR2,
3 i_query_id IN NUMBER,
4 i_rec_id IN NUMBER,
5 i_pd_receipt_no IN NUMBER,
6 i_ext_acct_no IN NUMBER,
7 i_txn_status IN VARCHAR2,
8 i_txn_date IN DATE,
9 i_desc IN VARCHAR2,
10 i_message IN VARCHAR2,
11 i_message_title IN VARCHAR2,
12 i_sendccy IN VARCHAR2,
13 i_receiveccy IN VARCHAR2,
14 i_sendccyamt IN NUMBER,
15 i_receiveccyamt IN NUMBER,
16 i_rate IN NUMBER,
17 i_sendfee IN NUMBER,
18 i_totalamt IN NUMBER,
19 i_receive_country IN VARCHAR2,
20 i_fname IN VARCHAR2,
21 i_minitial IN VARCHAR2,
22 i_lname IN VARCHAR2,
23 i_addr_line1 IN VARCHAR2,
24 i_addr_line2 IN VARCHAR2,
25 i_city IN VARCHAR2,
26 i_state IN VARCHAR2,
27 i_zip1 IN VARCHAR2,
28 i_zip2 IN VARCHAR2,
29 i_country IN VARCHAR2,
30 i_phone IN VARCHAR2,
31 i_email_id IN VARCHAR2,
32 o_error_code OUT NUMBER,
33 o_error_desc OUT VARCHAR2)
I tried setting all numbers to zero and strings to null, but I keep
getting the same error .

18:54:43,746 INFO [STDOUT] Inserting Record 1
18:54:43,773 ERROR [STDERR] java.sql.SQLException: ORA-06550: line 1, column 96:
PLS-00103: Encountered the symbol "" when expecting one of the following:
. ( ) , * @ % & | = - + < / > at in is mod not range rem =>
.. <an exponent (**)> <> or != or ~= >= <= <> and or like
between || indicator
The symbol "(" was substituted for "" to continue.
ORA-06550: line 1, column 156:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( ) , * % & | = - + < / > at in is mod not range rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol ")" was substi
18:54:43,778 ERROR [STDERR] at oracle.jdbc.dbaccess.DBError.throwSqlException(
18:54:43,779 ERROR [STDERR] at oracle.jdbc.ttc7.TTIoer.processError(
18:54:43,779 ERROR [STDERR] at oracle.jdbc.ttc7.Oall7.receive(
18:54:43,779 ERROR [STDERR] at oracle.jdbc.ttc7.TTC7Protocol.doOall7(
18:54:43,780 ERROR [STDERR] at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(
18:54:43,780 ERROR [STDERR] at oracle.jdbc.driver.OracleStatement.executeNonQuery(
18:54:43,781 ERROR [STDERR] at oracle.jdbc.driver.OracleStatement.doExecuteOther(
18:54:43,781 ERROR [STDERR] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(
18:54:43,782 ERROR [STDERR] at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepare
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
It's hard to know w/out seeing your code, but I'd check a couple of things. First, make sure you are using a "CallableStatement", not a "PreparedStatement" or "Statement". Second, make sure you are calling "registerOutParameter(...)" on the last two parameters (o_error_code and o_error_desc) to let the call know to expect an int and String return, respectively.
If these don't help, please share some of your code.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Hi k,
According to the "oerr" utility, the cause of your ORA-06550 error code is

Usually a PL/SQL compilation error.

So it would seem that the problem has to do with the definition of the InsCustTxnLookupDtls stored procedure, and not with your java code.
I presume you can continue from here.
By the way, wouldn't the JDBC Forum be more suitable for this question?
Good Luck,
Jim Yingst

Joined: Jan 30, 2000
Posts: 18671
Yes, I'm moving this to JDBC...

"I'm not back." - Bill Harding, Twister
I agree. Here's the link:
subject: Oracle Stored Procedure Problem