Re: Stored procedures and dynamic SQL

Peter Derrick wrote:
>
> 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);

Try 'begin '||string||'('''||arg||'''); end;'

a method you might think about using to access parameters would be to create a package that employs pl/sql tables.

create or replace package p as

   type vtab is table of varchar2(2000) index by binary_integer;    arg vtab;
end;

This is a simple implementation. obviously lots of cool functionality could be added.

>
> 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
>
> */

You need to explicitly grant CREATE TABLE privilege to the user that will execute the procedure.

-- 
_________________________________________________
Robert C. Nix
Oracle Tools Support
rnix_at_us.oracle.com

The thoughts, opinions, remarks, statements, ...
expressed here are my own and do not necessarily
represent those of Oracle Corporation.