rgeeta
asked on
REFCURSOR getting through getCursor in CallableStatement
Hello,
I am calling a stored package procedure which returns
REFCursor (i am using oracle version 8.1.6) through
jdbc
am importing the Oracle JDBC drivers which support
bind variables of type REFCURSOR.(classes from the
oracle.jdbc.driver package(driver 8.1.7)
Used the getCursor method of the CallableStatement to
convert a REFCURSOR value returned by a PL/SQL block
into a ResultSet.
Casting the corresponding CallableStatement to
oracle.jdbc.driver.OracleC
getCursor method.
The problem is this works absolutely fine if i connect
as the owner of the package procedure. but if i create
a public syonym for the same package and grant execute
on package to other user.
And now if i connect as this other user through jdbc
and run the same program it gives me SQLException
ora-00942 table or view does not exit.
(and the same thing works fine from sql*plus when the
package is tested from thsi other) just to inorm you
that no grants / rights issues over here.
I am just unable to understand how do i proceed in
this case, as no way i can have the package procedure
being created in the same user, also doesn't sound
implementable in live environment. Please help me.
I hope i have given required info to get an answer.
Can any body confirm is it a bug in jdbc driver 8.1.7
Thanks. Geeta
I am calling a stored package procedure which returns
REFCursor (i am using oracle version 8.1.6) through
jdbc
am importing the Oracle JDBC drivers which support
bind variables of type REFCURSOR.(classes from the
oracle.jdbc.driver package(driver 8.1.7)
Used the getCursor method of the CallableStatement to
convert a REFCURSOR value returned by a PL/SQL block
into a ResultSet.
Casting the corresponding CallableStatement to
oracle.jdbc.driver.OracleC
getCursor method.
The problem is this works absolutely fine if i connect
as the owner of the package procedure. but if i create
a public syonym for the same package and grant execute
on package to other user.
And now if i connect as this other user through jdbc
and run the same program it gives me SQLException
ora-00942 table or view does not exit.
(and the same thing works fine from sql*plus when the
package is tested from thsi other) just to inorm you
that no grants / rights issues over here.
I am just unable to understand how do i proceed in
this case, as no way i can have the package procedure
being created in the same user, also doesn't sound
implementable in live environment. Please help me.
I hope i have given required info to get an answer.
Can any body confirm is it a bug in jdbc driver 8.1.7
Thanks. Geeta
ASKER
Hi shyam,
Thanks for all your observations,
one more thing that i observed about this is let us say my procedure is querying emp table (owner of the emp table as well as the procedure is same let us say user1), and that procedure is given an execute rights to user2.
As per earlier mail, it just doesn't work fine if i give the rights to user2 on procedure, but if i give select rights on the emp table to user1 and now call the procedure it works fine.
I hope you ot my point here, the select on the emp table is also by creating the synonym and then grating a select on emp.
But doing this is rediculus, as if i give the select rights
on the table to this user, it is of no use having all the code in procedures.
I would try what u have suggested and let you know about the same, but as far as i see , had tried all the options to make it work, let me retry as i don't recollect properly if i have done the same or not.
Thanks,
Geeta
Thanks for all your observations,
one more thing that i observed about this is let us say my procedure is querying emp table (owner of the emp table as well as the procedure is same let us say user1), and that procedure is given an execute rights to user2.
As per earlier mail, it just doesn't work fine if i give the rights to user2 on procedure, but if i give select rights on the emp table to user1 and now call the procedure it works fine.
I hope you ot my point here, the select on the emp table is also by creating the synonym and then grating a select on emp.
But doing this is rediculus, as if i give the select rights
on the table to this user, it is of no use having all the code in procedures.
I would try what u have suggested and let you know about the same, but as far as i see , had tried all the options to make it work, let me retry as i don't recollect properly if i have done the same or not.
Thanks,
Geeta
Hi Geeta
Let me try in my machine and let u know within no time
Hold on
Shyam
Let me try in my machine and let u know within no time
Hold on
Shyam
ASKER
Hi shyam,
Thanks for all your observations,
one more thing that i observed about this is let us say my procedure is querying emp table (owner of the emp table as well as the procedure is same let us say user1), and that procedure is given an execute rights to user2.
As per earlier mail, it just doesn't work fine if i give the rights to user2 on procedure, but if i give select rights on the emp table to user1 and now call the procedure it works fine.
I hope you ot my point here, the select on the emp table is also by creating the synonym and then grating a select on emp.
But doing this is rediculus, as if i give the select rights
on the table to this user, it is of no use having all the code in procedures.
I would try what u have suggested and let you know about the same, but as far as i see , had tried all the options to make it work, let me retry as i don't recollect properly if i have done the same or not.
Thanks,
Geeta
Thanks for all your observations,
one more thing that i observed about this is let us say my procedure is querying emp table (owner of the emp table as well as the procedure is same let us say user1), and that procedure is given an execute rights to user2.
As per earlier mail, it just doesn't work fine if i give the rights to user2 on procedure, but if i give select rights on the emp table to user1 and now call the procedure it works fine.
I hope you ot my point here, the select on the emp table is also by creating the synonym and then grating a select on emp.
But doing this is rediculus, as if i give the select rights
on the table to this user, it is of no use having all the code in procedures.
I would try what u have suggested and let you know about the same, but as far as i see , had tried all the options to make it work, let me retry as i don't recollect properly if i have done the same or not.
Thanks,
Geeta
Hi Geeta
I did these things see whether it will be help full to you.
I have a users Object and Scott
I created table temp on object and grant select on temp to scott
then from scott i can access it
Now i created a proc on object and grant all on proc to scott
Then went to the scott and can access it
But u need to access all the objects of object from scott using this only
object.temp or object.proc
Otherwise it is not the object of it self
Is it making sense for you
Any comments any other thing u r trying to explaining me
I don't know whether am i in right target of urs in solution
Cheers
Shyam
I did these things see whether it will be help full to you.
I have a users Object and Scott
I created table temp on object and grant select on temp to scott
then from scott i can access it
Now i created a proc on object and grant all on proc to scott
Then went to the scott and can access it
But u need to access all the objects of object from scott using this only
object.temp or object.proc
Otherwise it is not the object of it self
Is it making sense for you
Any comments any other thing u r trying to explaining me
I don't know whether am i in right target of urs in solution
Cheers
Shyam
ASKER
Would u do one thing now,
see i am sitting in a cyber cafe , hence telling u to do this, i think u don't mind.
connect as object
and revoke select on temp from scott;
after this try accessing this package from scott and do let me know the result please.
Also could you query the user_tab_privs from scott user and see what does granting all on a package procedure means.
Regards,
Geeta
see i am sitting in a cyber cafe , hence telling u to do this, i think u don't mind.
connect as object
and revoke select on temp from scott;
after this try accessing this package from scott and do let me know the result please.
Also could you query the user_tab_privs from scott user and see what does granting all on a package procedure means.
Regards,
Geeta
Hi Geeta
I have don't what u have said
I got the following result
1) I revoke select on scott for object temp;
Then i run proc from scott. using Object.proc
It executed
And i can see the result from object only not from scott
2) And u asked to type the content of user_privs_tab this is the result
GRANTEE OWNER
--------------------------
TABLE_NAME GRANTOR
--------------------------
PRIVILEGE GRA
--------------------------
SCOTT OBJECT
PROC1 OBJECT
EXECUTE NO
This is the result
Cheers
Shyam
I have don't what u have said
I got the following result
1) I revoke select on scott for object temp;
Then i run proc from scott. using Object.proc
It executed
And i can see the result from object only not from scott
2) And u asked to type the content of user_privs_tab this is the result
GRANTEE OWNER
--------------------------
TABLE_NAME GRANTOR
--------------------------
PRIVILEGE GRA
--------------------------
SCOTT OBJECT
PROC1 OBJECT
EXECUTE NO
This is the result
Cheers
Shyam
ASKER
Hello, i did not get your 1st point,
are u saying that after revoke when u executed the proc from scott as object.proc you are able to see the result
from scott ?
and if no, is it giving you any error.
are u saying that after revoke when u executed the proc from scott as object.proc you are able to see the result
from scott ?
and if no, is it giving you any error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use select * from object.temp from scott. Since u don't have privlege on it . it raises u an error
Just be cool Okay
Shyam
Just be cool Okay
Shyam
Hi Geeta
I have to leave now to home
Catch u tomorrow
Bye Good Night
Shyam
I have to leave now to home
Catch u tomorrow
Bye Good Night
Shyam
Hello Geeta
Got a solution
Shyam
Got a solution
Shyam
ASKER
Hi,
Shyam, thanks for all that help.
No actaully, i think what proc you wrote was to insert a row , i hve got a proc, which selects rows in refcursor and returns the same refcursor as out parameter,
now if i call the same from scott user as getObject or get cusor in callablestatment , i get an error as table or view does not exist, but i can get the rows from the callable statment if i run the same as owner of the package.
Now if i grant a select on this table to scott, and run the same proc, i get the row from callablestatment, but i don't want this , since if scott user has got the direct select on the same table, there is no point putting the logic in procedure and only granting the execute on the procedure.
I hope you got my problem.
Actually i don't have the access to metalink option of otn, where one can check the bugs in the oracle products version wise, as i want to check the same if this is a bug in oracle jdbc 8.1.7 driver.
Can anybody help please.
Shyam, thanks for all that help.
No actaully, i think what proc you wrote was to insert a row , i hve got a proc, which selects rows in refcursor and returns the same refcursor as out parameter,
now if i call the same from scott user as getObject or get cusor in callablestatment , i get an error as table or view does not exist, but i can get the rows from the callable statment if i run the same as owner of the package.
Now if i grant a select on this table to scott, and run the same proc, i get the row from callablestatment, but i don't want this , since if scott user has got the direct select on the same table, there is no point putting the logic in procedure and only granting the execute on the procedure.
I hope you got my problem.
Actually i don't have the access to metalink option of otn, where one can check the bugs in the oracle products version wise, as i want to check the same if this is a bug in oracle jdbc 8.1.7 driver.
Can anybody help please.
Hi Geeta
Can u list the Proc which u have written i will try and let u know
Any way I will search for the link once i get it
I will display it
Cheers
Shyam
Can u list the Proc which u have written i will try and let u know
Any way I will search for the link once i get it
I will display it
Cheers
Shyam
ASKER
Hi Shyam,
The proc goes like below,i can not list the actaul one as it is not with me currently and i can not acces the net from the office where i have it.
package specs
type x is record of (deptno dept.deptno%type);
type y is refcursor return x;
pacage body
proc getdptno(outparam out y)
is
begin
open outparma for select deptno from dept;
end;
The proc goes like below,i can not list the actaul one as it is not with me currently and i can not acces the net from the office where i have it.
package specs
type x is record of (deptno dept.deptno%type);
type y is refcursor return x;
pacage body
proc getdptno(outparam out y)
is
begin
open outparma for select deptno from dept;
end;
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
-- points to shyamkumarreddy
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
sudhakar_koundinya
EE Cleanup Volunteer
---------------------
If you feel that your question was not properly addressed, or that none of the comments received were appropriate answers, please post your concern in THIS thread.
From you question. I can see few things.
You way of handling things are great and that is the procedure to do it. I think u r following oracle documentation. That is good
Okay Coming to the probs.
You main probs is that it cann't identify your public synonym right.
It works in SQLPlus but not in JDBC right.
Trying using the like this and let me know the result and i will the comments abt it.
Say Scott has some Procedure Proc1 and and u have given grant on it
Say Geeta has permission on Proc1.
Try using like this
Geeta.Proc1
And let me know what happened. I am wrong is understanding your question. Let me know what is the probs exactly.
Since i have few doubts.
Abt synomyn and execution. Mail me then
Shyam