| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Newbie PL/SQL script question?mlml
A copy of this was sent to ae299_at_FreeNet.Carleton.CA (Michel Lee) (if that email address didn't require changing) On 27 Feb 1998 00:32:32 GMT, you 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?
roles are never enabled during the execution/compilation of a procedure.
Try this:
SQL> set role none;
SQL> desc v$datafile
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You need to "grant select on v_$datafile to system" when logged in as SYS to create this procedure (v$datafile is a synonym that points to the view v_$datafile and you cannot grant on synonyms, you have to do it on the view)
>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?
>
SQL> show error procedure PROCEDURE_NAME;
will show you the errors or issueing a select against the table USER_ERRORS.
>cheers
>Mike
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Feb 27 1998 - 00:00:00 CST
 |
 |