SET ECHO ON
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET AUTOP OFF

CREATE OR REPLACE PROCEDURE executeDDL(ppstatement IN varchar2) AS
cursor_name INTEGER;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, ppstatement, DBMS_SQL.native);
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Closing cursor, err code = ' || sqlcode || ', err = ' || sqlerrm);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
DBMS_OUTPUT.PUT_LINE('cursor closed');
END;
/



hmm..... everything works fine now, but I found a new problem: When I passed the ALTER TABLE statement to the executeDDL procedure, it works great. but it fails when I trying to execute the CREATE TABLE STATEMENT using the procedure.

here is the output:

SQL> exec executeDDL('create table test3 (aaa varchar2 (50))');
Closing cursor, err code = -1031, err = ORA-01031: insufficient privileges
cursor closed

PL/SQL procedure successfully completed.
SQL>

I don't understand why it says "insufficient privilege" since I am login as a dba and the procedure was ran by me. If I copy and paste the create statement to the command line, then it works fine,

SQL>
SQL> create table test3 (aaa varchar2 (50));

Table created.

SQL>


This is weird. does anyone know what's happening?