procedures and table scope... PLS-00201

[Andrew Metcalfe]

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

[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

[FaheemRao]

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

[Andrew Metcalfe]

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

[Andrew Metcalfe]

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