Error calling stored procedure !!

[clement valentine]

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]

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]

Hi Raj,
Thanks for the suggestion.. Tried that out but still the same result.

[Rajendar Goud]

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]

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]

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]

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!!