By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,504 Members | 1,903 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,504 IT Pros & Developers. It's quick & easy.

procedures and table scope... PLS-00201

P: n/a
I'm having trouble writing a simple procedure.

I'm trying to simply select against a table in a different schema.

I first wrote a simple procedure that selected against a table in my
schema, which worked.

CREATE OR REPLACE PROCEDURE test
IS
CURSOR cur_test
IS
select *
from tablename;
BEGIN
FOR itemrec IN cur_test
LOOP
DBMS_OUTPUT.PUT_LINE('record!');

END LOOP;
END test;

A simple change to this is to change my select line to:

select *
from otherTablespace.tablename;

This gives me an error saying "otherTablespace.tablename must be
declared."

My first thought was to create a public synonym, so I did.

CREATE PUBLIC SYNONYM mySyn FOR otherTablespace.tablename

I also change my select line to read:

select *
from mySyn

PLS-00201: identifier mySyn' must be declared.

What am I missing? Why can't I "see" the other schemas table in a
procedure even when explicitly referenced using the
tablespace.tablename?

I am able to access the table through SQLPlus...?

Thanks.

_Am
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
VC
Hello Andrew,

A privilege ('select/update/delete') has to be granted directly to the
procedure executor, not via a role.

sqlplus can read the table because the access is granted via a role.

Rgds.

"Andrew Metcalfe" <ch*********@hotmail.com> wrote in message
news:a2*************************@posting.google.co m...
I'm having trouble writing a simple procedure.

I'm trying to simply select against a table in a different schema.

I first wrote a simple procedure that selected against a table in my
schema, which worked.

CREATE OR REPLACE PROCEDURE test
IS
CURSOR cur_test
IS
select *
from tablename;
BEGIN
FOR itemrec IN cur_test
LOOP
DBMS_OUTPUT.PUT_LINE('record!');

END LOOP;
END test;

A simple change to this is to change my select line to:

select *
from otherTablespace.tablename;

This gives me an error saying "otherTablespace.tablename must be
declared."

My first thought was to create a public synonym, so I did.

CREATE PUBLIC SYNONYM mySyn FOR otherTablespace.tablename

I also change my select line to read:

select *
from mySyn

PLS-00201: identifier mySyn' must be declared.

What am I missing? Why can't I "see" the other schemas table in a
procedure even when explicitly referenced using the
tablespace.tablename?

I am able to access the table through SQLPlus...?

Thanks.

_Am

Jul 19 '05 #2

P: n/a
When are you refering a table in Stored procedure from other schema
you need to grant the select on that table directly without involving
any role.

Faheem

"
VC" <bo*******@hotmail.com> wrote in message news:<m8oCb.375468$ao4.1254277@attbi_s51>...
Hello Andrew,

A privilege ('select/update/delete') has to be granted directly to the
procedure executor, not via a role.

sqlplus can read the table because the access is granted via a role.

Rgds.

"Andrew Metcalfe" <ch*********@hotmail.com> wrote in message
news:a2*************************@posting.google.co m...
I'm having trouble writing a simple procedure.

I'm trying to simply select against a table in a different schema.

I first wrote a simple procedure that selected against a table in my
schema, which worked.

CREATE OR REPLACE PROCEDURE test
IS
CURSOR cur_test
IS
select *
from tablename;
BEGIN
FOR itemrec IN cur_test
LOOP
DBMS_OUTPUT.PUT_LINE('record!');

END LOOP;
END test;

A simple change to this is to change my select line to:

select *
from otherTablespace.tablename;

This gives me an error saying "otherTablespace.tablename must be
declared."

My first thought was to create a public synonym, so I did.

CREATE PUBLIC SYNONYM mySyn FOR otherTablespace.tablename

I also change my select line to read:

select *
from mySyn

PLS-00201: identifier mySyn' must be declared.

What am I missing? Why can't I "see" the other schemas table in a
procedure even when explicitly referenced using the
tablespace.tablename?

I am able to access the table through SQLPlus...?

Thanks.

_Am

Jul 19 '05 #3

P: n/a
Thanks, Rgds and Faheem you both hit the nail straight on.

_Am

fa*******@yahoo.com (FaheemRao) wrote in message news:<43*************************@posting.google.c om>...
When are you refering a table in Stored procedure from other schema
you need to grant the select on that table directly without involving
any role.

Faheem

Jul 19 '05 #4

P: n/a
Thanks, Rgds and Faheem you both hit the nail straight on.

_Am

fa*******@yahoo.com (FaheemRao) wrote in message news:<43*************************@posting.google.c om>...
When are you refering a table in Stored procedure from other schema
you need to grant the select on that table directly without involving
any role.

Faheem

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.