This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Error calling stored procedure !! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error calling stored procedure !!" Watch "Error calling stored procedure !!" New topic

Error calling stored procedure !!

clement valentine

Joined: Apr 04, 2002
Posts: 19
Hello all,
Iam trying to call a stored proc. in Oracle from my program when I get the following error message:
java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification
ORA-06512: at "DBS1.INS_EXPENSE", line 20
ORA-06512: at line 1
This my code snippet for calling the proc.
CallableStatement statement= connection.prepareCall("{CALL INS_EXPENSE(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
statement.setString(1, expenseType);
statement.setString(2, partyPayedTo);
statement.setBigDecimal(3, expenseAmount);
statement.setDate(4, billDate); //java.sql.Date
statement.setDate(5, bookedDate); // java.sql.Date
statement.setString(6, payedFromAcc);
statement.setString(7, payedToAcc);
statement.setString(8, paymentMode);
statement.setString(9, status);
statement.setString(10, comment);
statement.setString(11, entryUserid);
statement.setString(12, lastChangeUserid);
BigDecimal expno = statement.getBigDecimal(13);
ANd this is my procedure
( expense_type in VARCHAR2
, party_payed_to in VARCHAR2
, expense_amount in NUMBER
, bill_date in DATE
, booked_date in DATE
, payed_from_acc in VARCHAR2
, payed_to_acc in VARCHAR2
, payment_mode in VARCHAR2
, status in VARCHAR2
, comment in VARCHAR2
, entry_userid in VARCHAR2
, last_chg_userid in VARCHAR2
, expense_no out NUMBER
select max(EXPENSE_NO) into expense_no from DBS1.EXPENSE;
expense_no := expense_no + 1;
Line no. 20 points to the insert inside the stored proc. but i can't figure out whats wrong??
Any help would be appreciated.
Rajendar Goud
Ranch Hand

Joined: Mar 06, 2002
Posts: 220
Hi ,
Looking at the error description u mentioned,
it seems like Java is not able to pick the table
at all.It may be b/c of the DBS1.EXPENSE().
Generally,the representation will be like
so check out ur schema name ,is it DBS1 or not.
iam not fully sure ,but i may be wrong also,
its just a guess.
clement valentine

Joined: Apr 04, 2002
Posts: 19
Hi Raj,
Thanks for the suggestion.. Tried that out but still the same result.
Rajendar Goud
Ranch Hand

Joined: Mar 06, 2002
Posts: 220
in the procedure u wrote,
u called the procedure like
CallableStatement statement= connection.prepareCall("{CALL INS_EXPENSE(?,?,?,?,?,?,?,?,?,?,?,?,?)}");

where INS_EXPENSE is the procedure name.And while creating the procedure u wrote it as,
instead of "DBS1"."INS_EXPENSE"
try using "DBS1.EXPENSE". i dont know why u used double quotes seperately for schema and procedure name.
just try this out.
clement valentine

Joined: Apr 04, 2002
Posts: 19
Hi again,
Tried that one too without any luck. The "SCHEMA".Procedure_name occurs because of using some tool to create the SP logged in as another user with SYSDBA privilege. My doubt is at statement 20 (ORA-06512) of the proc. at the insertion of the 2 dates..Is there some problem with that?
I have even tried using oracle.sql.DATE and OracleCallableStatment with the same result..
Braj Prasad

Joined: Apr 08, 2002
Posts: 16
Could you check if the user-id that is being used to execute the stored procedure has write privilege to the table DBS1.INS_EXPENSE ?
If so, try writing an exception handler in the stored procedure for this condition.
Hope it helps.
clement valentine

Joined: Apr 04, 2002
Posts: 19
Gotcha!!...I was using a field named 'COMMENT' in the table which seems to be an oracle keyword..Everything works fine after I changed it. Strange though how it allowed me to create one in the first place...Thanks a lot all you folks!!
I agree. Here's the link:
subject: Error calling stored procedure !!
Similar Threads
SQLException Cursor Closed
java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric
sql query problem
Working with Dates
Using setDate(column,Date) to insert date in sybase database