This week's book / license giveaways are in the JDBC and Relational Databases and Java in General forums.
We're giving away four copies each of PostGIS in Action and Java Advanced Topics Training and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and Relational Databases and the fly likes Error calling stored procedure !! 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 "Error calling stored procedure !!" Watch "Error calling stored procedure !!" New topic
Author

Error calling stored procedure !!

clement valentine
Greenhorn

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);
statement.registerOutParameter(13,java.sql.Types.NUMERIC);
statement.execute();
BigDecimal expno = statement.getBigDecimal(13);
--------------------------------------------------
ANd this is my procedure
-------------------------------------------------
CREATE OR REPLACE PROCEDURE "DBS1"."INS_EXPENSE"
( 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
)
IS
BEGIN
select max(EXPENSE_NO) into expense_no from DBS1.EXPENSE;
expense_no := expense_no + 1;
insert into DBS1.EXPENSE (EXPENSE_NO,EXPENSE_TYPE,PARTY_PAYED_TO
,EXPENSE_AMOUNT,BILL_DATE,BOOKED_DATE,PAYED_FROM_ACC,PAYED_TO_ACC
,PAYMENT_MODE,STATUS,COMMENT,ENTRY_TIMESTAMP,ENTRY_USERID
,LAST_CHANGE_TIMESTAMP,LAST_CHANGE_USERID) VALUES
(expense_no,expense_type,party_payed_to,expense_amount,TO_DATE(bill_date,'yyyy-mm-dd')
,TO_DATE(booked_date,'yyyy-mm-dd'),payed_from_acc,payed_to_acc,payment_mode,status
,comment,SYSDATE,entry_userid,SYSDATE,last_chg_userid);
END INS_EXPENSE;
--------------------------------------------------
Line no. 20 points to the insert inside the stored proc. but i can't figure out whats wrong??
Any help would be appreciated.
TIA
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
SCHEMA.TABLE NAME .
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.
cheers,
Raj
clement valentine
Greenhorn

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
Clement,
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,
CREATE OR REPLACE PROCEDURE "DBS1"."INS_EXPENSE"
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
Greenhorn

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..
Cheers
Braj Prasad
Greenhorn

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
Greenhorn

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: http://aspose.com/file-tools
 
subject: Error calling stored procedure !!