Ora-942 in stored procedure

[DA Singh]

Hi,
I have a stored procedure that tries to select from a table owned by
another schema -
create or replace procedure schema1.procedure1
as
insert into table2
select col1, col2
from schema2.table1;
end;
/

And I get the Ora-942 error - Table or view does not exist.
I can select from the table from the SQL prompt, when logged in as
schema1.
Are there some permissions that need to be set?
The Oracle version is 9.2.0.3.0
Thanks,
D A Singh

[Frank]

DA Singh wrote:

Hi,
I have a stored procedure that tries to select from a table owned by
another schema -
create or replace procedure schema1.procedure1
as
insert into table2
select col1, col2
from schema2.table1;
end;
/

And I get the Ora-942 error - Table or view does not exist.
I can select from the table from the SQL prompt, when logged in as
schema1.
Are there some permissions that need to be set?
The Oracle version is 9.2.0.3.0
Thanks,
D A Singh

As always - grant select on table1 to <owner of the procedure>
roles are _not_ active in PL/SQL, they are in SQL.

--
Regards, Frank van Bortel

[Mark D Powell]

da*****@hotmail.com (DA Singh) wrote in message news:<78**************************@posting.google. com>...

Hi,
I have a stored procedure that tries to select from a table owned by
another schema -
create or replace procedure schema1.procedure1
as
insert into table2
select col1, col2
from schema2.table1;
end;
/

And I get the Ora-942 error - Table or view does not exist.
I can select from the table from the SQL prompt, when logged in as
schema1.
Are there some permissions that need to be set?
The Oracle version is 9.2.0.3.0
Thanks,
D A Singh

It is fairly clearly stated in the manuals that for a procedure owned
by A to reference objects owned by B that user A must have a direct
grant on the object and not inherit his or her privileges through a
role.

HTH -- Mark D Powell --

[DA Singh]

Thank you Frank and Mark.
Our DBA didn't know this.
regards,
D A Singh

[Frank]

DA Singh wrote:

Thank you Frank and Mark.
Our DBA didn't know this.
regards,
D A Singh

Your programmer(s) should, as they ought to be able to
explain the DBA what privileges to grant to which user.
--
Regards, Frank van Bortel

[DA Singh]

Frank <fb*****@home.nl> wrote in message news:<bo**********@news3.tilbu1.nb.home.nl>...

DA Singh wrote:

Thank you Frank and Mark.
Our DBA didn't know this.
regards,
D A Singh

Your programmer(s) should, as they ought to be able to
explain the DBA what privileges to grant to which user.

They did. The end users don't use sqlplus.

[Jeff Lambert]

sounds like a case of case. Make sure you access your tables between
double-quotes, such as

insert into "table2" select col1, col2 from schema2."table1";

try renaming all your tables to upper case and you'll be able to
access them without the quotes.

it's a shame oracle never came out with a different error-code.

Jeff
da*****@hotmail.com (DA Singh) wrote in message news:<78**************************@posting.google. com>...

Hi,
I have a stored procedure that tries to select from a table owned by
another schema -
create or replace procedure schema1.procedure1
as
insert into table2
select col1, col2
from schema2.table1;
end;
/

And I get the Ora-942 error - Table or view does not exist.
I can select from the table from the SQL prompt, when logged in as
schema1.
Are there some permissions that need to be set?
The Oracle version is 9.2.0.3.0
Thanks,
D A Singh