Home » SQL & PL/SQL » SQL & PL/SQL » Ref Cursor Problem ... Urgent
Ref Cursor Problem ... Urgent [message #203199] Tue, 14 November 2006 05:23 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi frns,

I am getting the following err:


CREATE OR REPLACE PACKAGE mypack
AS
TYPE resultset IS REF CURSOR;
PROCEDURE p1 (v_rs OUT resultset);
END;
/

CREATE OR REPLACE PACKAGE BODY mypack
AS
PROCEDURE p1 (v_rs OUT resultset)
AS
qry VARCHAR2 (100);
BEGIN
qry := 'select * from scott.emp';
OPEN v_rs FOR qry;
END;
END;
/

var rs refcursor
exec mypack.p1(:rs)

Err : Table or view does not exist

And try to sort the problem.
SQL> select object_name,object_type from all_objects where object_name='EMP';

------------------------
EMP SYNONYM
EMP TABLE

Then i came to conclusion that 1st preference is given to SYNONYM

But i want table to be selected.
How to do that.

Regards,
frank

Re: Ref Cursor Problem ... Urgent [message #203201 is a reply to message #203199] Tue, 14 November 2006 05:32 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
An attempt to access SCOTT.EMP is urgent ?

It may be that your access to the table is through a role rather than a direct grant. If you only have access through a role, you won't be able to create any objects such as views or stored procedures based on that table. Grant select explicitly on the table and retry.
Re: Ref Cursor Problem ... Urgent [message #203202 is a reply to message #203199] Tue, 14 November 2006 05:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you check that the EMP table actually exists in the SCOTT schema that you have told it to look in.

You can't have a private synonym and a table in your schema with the same name.

If you have a table in your schema and a public synonym with the same name, then the Table (local object) takes precedence.

Ps, In what way is this Urgent?

[Drat - beaten to it, and with almost identical content. The Shame!!! Embarassed ]

[Updated on: Tue, 14 November 2006 05:40]

Report message to a moderator

Re: Ref Cursor Problem ... Urgent [message #203920 is a reply to message #203202] Thu, 16 November 2006 23:35 Go to previous messageGo to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi Row,

It was actually a different context in the Production box. u know. I just tried to simulate the same on the test environment and tried. That's it. The urgency meant that not the scott schema.

Anyways Thank You. I identified the problem due to some security issues. That's it. Later Fixed that.

Regards,
Frank
Re: Ref Cursor Problem ... Urgent [message #203980 is a reply to message #203199] Fri, 17 November 2006 03:30 Go to previous messageGo to next message
moparthy99
Messages: 13
Registered: July 2006
Junior Member
CREATE OR REPLACE PACKAGE BODY mypack
AS
PROCEDURE p1 (c1 OUT rf)
AS
rec emp%rowtype;
c2 rf;
st varchar(200);
begin
st:='select * from emp';
open c2 for st;
loop
fetch c2 into rec;
exit when c2%notfound ;
dbms_output.put_line(rec.ename);
end loop;
close c2;
end;
end;


declare
w mypack.rf;
begin
mypack.p1(w);
end;



Re: Ref Cursor Problem ... Urgent [message #204078 is a reply to message #203980] Fri, 17 November 2006 12:32 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi,

Its is working fine thanks.

Bye
Ashu
Previous Topic: Setting data_precision with SQL
Next Topic: problem with ordimage
Goto Forum:
  


Current Time: Tue Dec 06 00:18:36 CST 2016

Total time taken to generate the page: 0.07881 seconds