Re: Newbie PL/SQL script question?mlml

This is probably because SYSTEM gets its access to V$DATAFILE via the DBA role, as opposed to a direct grant. since you're creating a stored procedure, the user creating it must have direct access to all objects required, not access through roles.

I believe if you try this same procedure logged in as SYS instead of SYSTEM it will work.

Michel Lee wrote:
>
> This problem has been bugging me for 1 whole day!
> Please can u help me with this trivial question.
>
> If I run the below script (i am running as SYSTEM), i get this message:
>
> Warning: Procedure created with compilation errors.
>
> __________________________________
> CREATE PROCEDURE myspace AS
> CURSOR datafile2 IS
> SELECT name, bytes
> FROM v$datafile;
> BEGIN
> DBMS_OUTPUT.PUT_LINE('-----------------------------------');
> END myspace;
> /
> __________________________________
>
> If I run this script, it runs with NO 'Warning' messages
>
> __________________________________
> CREATE PROCEDURE myspace AS
> CURSOR datafile2 IS
> SELECT 1 from dual;
> BEGIN
> DBMS_OUTPUT.PUT_LINE('-----------------------------------');
> END myspace;
> /
> __________________________________
>
> Whats the diff?
> Obviously I am trying to get the 1st to run.
> I tried just run the select portion by itself in the commandline,
> and it works.
> Where can i get a more detailed 'Warning' message?
>
> cheers
> Mike
>
> --
> /-----------------------------------------------/ \--\--\ \-- \-- \--
> / www.ncf.carleton.ca/~ae299 /---\ \ \ \\ \ \
> / ae299_at_freenet.carleton.ca /-----\ \ \\-- \-- \--
> /-----------------------------------------------/
  Received on Thu Feb 26 1998 - 00:00:00 CST