Re: dynamic SQL question

Looks like a permission problem. You have to grant execute privs on this package to yourself or to public.

Roger Snowden
Sr. Systems Engineering Specialist
Oracle Corporation
rsnowden_at_NOTus.oracle.com <-- to reply, remove the obvious

steve_young_at_my-dejanews.com wrote:
>
> I have the following procedure built to use the dynamic SQL:
>
> CREATE OR REPLACE PROCEDURE exec(string IN varchar2) AS
> cursor_name INTEGER;
> ret INTEGER;
> BEGIN
> cursor_name:= DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.NATIVE);
> ret := DBMS_SQL.EXECUTE(cursor_name);
> DBMS_SQL.CLOSE_CURSOR(cursor_name);
> END;
> .
> /
>
> After I compile it, I got an error when I run it:
> SQL> execute exec('create table t(f integer)');
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "USER_NAME.EXEC", line 6
> ORA-06512: at line 1
>
> I have the dbmssql.sql and prvtsql.plb compiled on SYS already. Anyone can
> tell me what happen? Thanks!!
>
> Steve
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Jan 12 1999 - 00:00:00 CST