Oracle Stored Procedure Problem

[k chillar]

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:
/*
CREATE OR REPLACE PROCEDURE InsCustTxnLookupDtls(
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(DBError.java:134)
18:54:43,779 ERROR [STDERR] at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
18:54:43,779 ERROR [STDERR] at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
18:54:43,779 ERROR [STDERR] at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
18:54:43,780 ERROR [STDERR] at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
18:54:43,780 ERROR [STDERR] at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
18:54:43,781 ERROR [STDERR] at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
18:54:43,781 ERROR [STDERR] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
18:54:43,782 ERROR [STDERR] at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepare

[Wayne L Johnson]

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]

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,
Avi.

[Jim Yingst]

Yes, I'm moving this to JDBC...