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.procedu re1
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]

Re: Ora-942 in stored procedure

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.procedu re1
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]

Re: Ora-942 in stored procedure

dasingh@hotmail .com (DA Singh) wrote in message news:<78340f33. 0311100847.45d9 9306@posting.go ogle.com>...

Hi,
I have a stored procedure that tries to select from a table owned by
another schema -
create or replace procedure schema1.procedu re1
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]

Re: Ora-942 in stored procedure

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

[Frank]

Re: Ora-942 in stored procedure

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]

Re: Ora-942 in stored procedure

Frank <fbortel@home.n lwrote in message news:<borj78$o7 n$1@news3.tilbu 1.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]

Re: Ora-942 in stored procedure

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


dasingh@hotmail .com (DA Singh) wrote in message news:<78340f33. 0311100847.45d9 9306@posting.go ogle.com>...

Hi,
I have a stored procedure that tries to select from a table owned by
another schema -
create or replace procedure schema1.procedu re1
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