asked on

compile error of pl/sql body

Hello expert friends!
this is Driving me crazy. May be very simple for you guys/gals.
I can execute this command in toad (oracle) it gives me one row:
SELECT *
FROM DBA_USERS
WHERE USERNAME = 'ALAD';--vUserId;

but when I use the above statement in pl sql it gives me compile error table or view does not exist.
see my attached script please.
Thank youso much.
1MypckBody.sql
1Mypckspec.sql

slightwv (䄆 Netminder)

When running queries inside a stored procedure ALL grants need to be granted explicitly and not through roles.

Issue the following as SYSTEM or other privileged account and try again:
grant select on DBA_USERS to SIMDATA;

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsone

Privilege on ALL_USERS is granted to PUBLIC. It seems like you should be able to use that without a problem. Assuming that the data you are looking for is present in that view. At least it works in 12.2.0.1.

The people that commented so far I know have been using Oracle for a long time. Has privileges granted to PUBLIC always worked in PL/SQL? I didn't think they did. But, the more I think about it, I'm not sure I ever tried it before.

Anil Lad

ASKER

Thank you Mark. Your solution worked! I didn't even think about that.. Thank you netminder and johnsone for your insight and input.

You guys rock!
cheers