• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Oracle Stored Procedure Problem

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Wanderer
Posts: 18671
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, I'm moving this to JDBC...
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic