Re: Stored procedures and dynamic SQL

PL/SQL can only perform Data Manipulation not Data Definition. Therefore CREATE, DROP, and TRUNCATE statements are not allowed. Only SELECT, FETCH, OPEN, etc. are allowd in PL/SQL. This applies to both dynamic SQL and stored procedures.

Hope this helps...

        -Zang

In article <31204C88.3EFE_at_tpg.tpg.oz.au>, pld_at_tpg.tpg.oz.au says...
>
>Dear PL/SQL experts
>
>We are currently developing packages for our application and have
>struck the following problems with procedures and dynamic SQL. Any
>help on the following would be much appreciated. Replies either by
>e-mail or to the newsgroup.
>
>Thanks in advance
>
>Peter Derrick
>
>/*
> * PROBLEM 1: Dynamic SQL, executing a stored procedure with arg.
> *
> * OBJECTIVE:
> * What I want is a stored procedure that takes 2 VARCHAR2 args.
> * The first arg is a name of a stored procedure, the second arg
> * is a parameter that is to be passed to that stored procedure
> * when called.
> *
> * Here is the Stored procedure.
> */
>CREATE OR REPLACE PROCEDURE exec_create_table_sp(string IN VARCHAR2,
>

      arg
>IN VARCHAR2) AS
> cid INTEGER;
> val integer;
>BEGIN
> cid := dbms_sql.open_cursor;
>
> /* Here is the problem!! */
> dbms_sql.parse(cid, ?FORMAT?? , dbms_sql.v7);
>
> val := dbms_sql.execute(cid);
> dbms_sql.close_cursor(cid);
>EXCEPTION
> WHEN OTHERS THEN
> dbms_sql.close_cursor(cid);
> raise;
>END exec_create_table_sp;
>
>
>/*
> * Call format: exec exec_create_table_sp('sp_name', 'arg');
> *
> * I have tried using the exec command and using bind but
> * to no avail.
> *
> * When I make the call, I get a SQL syntax error. HELP??
> */
>
>/* ****************************************************
> * PROBLEM 2: creating a table within a sp by passing it a table name.
> *
> * Problem here is that although we can create the table
> * outside of this call, it gives us an error when try
> * to create it within this sp. I believe the actual error
> * is not the 'heart' of the problem and only a side effect.
> */
>CREATE OR REPLACE PROCEDURE exec_create_table_sp(tname IN VARCHAR2) AS
> cid INTEGER;
> val integer;
>BEGIN
> cid := dbms_sql.open_cursor;
>
> dbms_sql.parse(cid, 'CREATE TABLE ' || tname ||
> ' (myname varchar2(50) NOT NULL)'
> , dbms_sql.v7);
>
> val := dbms_sql.execute(cid);
> dbms_sql.close_cursor(cid);
>EXCEPTION
> WHEN OTHERS THEN
> dbms_sql.close_cursor(cid);
> raise;
>END exec_create_table_sp;
>
>/*
>
>Call as: exec create_table('blob');
>
>result: ORA-01031: insufficient privileges
> ORA-06512: at "TST.EXEC_CREATE_TABLE_SP", line 14
> ORA-06512: at line 1
>
>*/
Received on Wed Feb 21 1996 - 00:00:00 CET