Re: Oracle Stored Procedure Permissions Problem

Hello Aaron,

comments embedded.

Guy Noir wrote:
> Hello all. I am new to Oracle stored procedures. I have an Oracle
> account on the university server. I can create and drop tables all day
> long, but I cannot seem to create any kind of stored procedure. I have
> tried very simple select procedures, etc and always get:
>
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
> I'm working on a semester project and I have to create stored
> procedures as a result. Any ideas? Do I truely have insufficent privs?

If you don't believe Oracle's error messages, you'll be having a hard time with your project.

SQL*PLUS normally repeats the offending line with a star where it believes the error occured:

hb_at_DEMO10G>create procedure dummy

   2 is
   3 begin
   4 null;
   5 end;
   6 /
create procedure dummy
*
ERROR at line 1:
ORA-01031: insufficient privileges

Read error messages carefully and include them to the letter if possible, when asking for help.

>
> I have tried the "set role none;" and the commands I was attempting in
> the procedure, as suggested in many other posts, which worked fine.
>
> I'm using SQL*PLUS...is there a way I can tell what privs I have been
> given?
> Advice?
> Thanks Much
> -Aaron
>

Since you seem new to Oracle, you might be interested that there is an overwhelming set of documentation at tahiti.oracle.com (choose the version most appropriate).

You require a free registration, but they don't spam and those 5 minutes are well invested.

The SQL Reference, PL/SQL User Guide and Reference and the Application Developer's Guide will be your best friends, and if you want to do it right, the Concepts Guide so you understand what Oracle does 'behind the scenes', what transactions are and how they work in Oracle.

And if you can get a copy of Beginning Oracle Programming at your library, this will speed up things as well.

HTH
Holger Received on Tue May 03 2005 - 02:51:13 CDT