Re: creating a stored procedure in a s.p.

this message posted and emailed.

In article <387CB5E5.3AC9F7F0_at_acm.org>,   Edgar Weippl <weippl_at_acm.org> wrote:
> hi,
>
> following code does not work.
> calling the stored procedure dag (call dag()) should
> create or replace another stored procedure name mytest.
>
> CREATE OR REPLACE PROCEDURE dag IS
> BEGIN
> EXECUTE IMMEDIATE 'CREATE OR REPLACE PROCEDURE mytest IS BEGIN INSERT
> INTO dag_table values (1, 43); END;/';
> END;
> /
>
> i log in as scott/tiger into sqlplus paste the procedure and start it.
> it quits with error ORA-01031: insufficient privileges.
>
> creating the procedure dag, however, worked perfectly.
>
> in case this does not work, is there another way to create a stored
> procedure from one a string (or set of string)?
>
> thanx for your help,
> edgar.
> ps. please send a cc: by mail.
> --
> Edgar Weippl mailto:weippl_at_acm.org
> http://www.acm.org/~weippl
> PGP Fingerprint: F5FC 25AA 3AA1 7242 8F66 DD55 AFFE 0E46 F71E 571F
>

This is an example of the problem of ROLES and PL/SQL. Likely your ability to create procedures is GRANTed to scott via a ROLE. Those privileges don't count in PL/SQL PROCEDURES. Get the privilege GRANTed directly to the scott account and it will work.

HTH
 Ed

--
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support 440-498-3700 magic_at_interfacefamily.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 13 2000 - 13:02:52 CST