dynamic SQL question

From: <steve_young_at_my-dejanews.com>
Date: 1999/01/07
Message-ID: <772upe$8ar$1@nnrp1.dejanews.com>#1/1

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 Thu Jan 07 1999 - 00:00:00 CST