Solved

REFCURSOR getting through getCursor in CallableStatement

Posted on 2001-06-16
16
891 Views
Last Modified: 2007-12-19
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.OracleCallableStatement to use the
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
0
Comment
Question by:rgeeta
  • 9
  • 6
16 Comments
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6200988
Hi Geeta

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



0
 

Author Comment

by:rgeeta
ID: 6202167
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
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6202203
Hi Geeta

Let me try in my machine and let u know within no time
Hold on

Shyam
0
 

Author Comment

by:rgeeta
ID: 6202205
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
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6202247
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
0
 

Author Comment

by:rgeeta
ID: 6202285
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
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6202319
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
0
 

Author Comment

by:rgeeta
ID: 6202348
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.

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 3

Accepted Solution

by:
shyamkumarreddy earned 100 total points
ID: 6202364
Hello Geeta

Yes Madame it is giving me PL/SQL Procedure executed successfully

Okay

For check up i logged in as Object and checked up i can see an row is inserted.


You can execute procedure even if u don't have privilege on the table using the procedure

And Please don't loose control. I am here to help u not to put u in trouble.
Please be patient


Shyam
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6202366
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
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6202397
Hi Geeta
I have to leave now to home
Catch u tomorrow
Bye Good Night


Shyam
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6206114
Hello Geeta
Got a solution

Shyam
0
 

Author Comment

by:rgeeta
ID: 6206926
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.
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6213428
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
0
 

Author Comment

by:rgeeta
ID: 6215114
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;
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 8950678


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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now