User problem with store procedure call
I have a stored procedure which returns the ref cursor. If I connect as the package owner say x I can execute the procedure and get the resultset easily. However if I login as the user y, which has the execute permission on the package I get java.sql.sqlexception (ora-00942). I can execute the same procedure in sql plus as x and y with no problem. I can execute other packages as y with no problem also. Any help at this point is greatly appreciated.
Thanks,
[480 byte] By [
ambarj] at [2007-9-26 4:31:39]
in Oracle's documentation, they say to(among other things):
"If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it. "
other than the above issues, you can try:
referencing the table name by SCHEMA.TABLENAME.
Don't know if you have already done this, but other than security/naming issues, this is all that comes to mind
Jamie
Hi,
Thanks for your response. The problem I have is, related with ref cursor. If the procedure returns the ref_cursor then It is not running under user y. Since y has only execute permission on the package but not on the tables. If I grant the permission to the tables it all works fine. However I can not do that. I am using jdbc thin driver provided by Oracle. I was under the impression that if you have execute permission on package it should execute everything within the package but not with thin drivers, I guess.
IS some one else having this problem? Is there any workaorund with this.
It is getting urgent for me now.
Thanks,
ambarj at 2007-6-29 17:45:15 >
