473,322 Members | 1,405 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

procedures and table scope... PLS-00201

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
4 27920
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: serge | last post by:
How can i delete all user stored procedures and all table triggers very fast in a single database? Thank you
7
by: pkruti | last post by:
Below is a stored procedure i am working with and i am trying to drop the yesno_holding table if it exists but how do i add it back? Meaning i want it to drop if it exists but i want to add it back...
7
by: BlueDragon | last post by:
The place where I work is moving to MS SQL Server from Lotus Notes. I have done a lot of coding in Lotus Notes, and have, I suppose, intermediate skills in basic SQL -- queries, insert, updates,...
5
by: Neil Zanella | last post by:
Hello, I am curious as to why the designers of C did not include support for Pascal-like nested procedures. I guess that it's because nested procedures in C would not buy you much other than...
45
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
4
by: dhruv19280 | last post by:
Can someone confirm that DB2 UDB v8.1.2 does NOT support internal procedures as supported by Oracle? For example, this would work in Oracle, but not in DB2. Am I correct? CREATE OR REPLACE...
1
by: Jon Haakon Ariansen | last post by:
Hi all, I don't know where to publish this question but hope this newsgroup is okey. I'm working in Visual Studio 2005 and are now creating a dataset file where I will do all the transactions...
1
by: jobs | last post by:
I have a growning list of stored procedures that accept a single string as a parameter. The list and parameters are maintained in a table with two columns. Some of the Stored procedures take...
3
by: Otto Carl Marte | last post by:
Hi, As I understand it, Declared Global Temporary Tables (DGTTs) have a scope that is session/connection based. Using the same connection, I have discovered that if I declare a DGTT in one...
3
by: codefragment | last post by:
Hi I have a chunky bit of sql that I will want to call from a number of places. It will return a few thousand rows. Whats the best way of structuring this? 1) I initially thought of using...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.