Re: Stored procedures and dynamic SQL
From: Vikram Goel <vgoel_at_pts.mot.com>
Date: 1996/02/19
Message-ID: <4gaav5$isg_at_lserv1.paging.mot.com>#1/1
Date: 1996/02/19
Message-ID: <4gaav5$isg_at_lserv1.paging.mot.com>#1/1
Peter,
Try the following:
SQL> show user
user is "SYSTEM"
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;
dbms_sql.parse(cid,'create table '||string||' '||arg,
dbms_sql.v7);
val := dbms_sql.execute(cid);
dbms_output.put_line('table created '||string||arg);
END;
SQL> execute exec_create_table_sp('VG_TEST','(cola number(10) not null, colb varchar2(5))');
PL/SQL procedure successfully completed.
SQL> desc vg_test
Name Null? Type
------------------------------- -------- ----
COLA NOT NULL NUMBER(10)
COLB VARCHAR2(5)
Note, The person who creates the procedure, must have resource privilege in the database. Any other
user must have execute privilege from the owner of the procedure to execute . The table will
always be created in the schema of the procedure owner.
<<< This is also the issue with the privilege message in your part2.
Hope this helps.
Vikram
-- Vikram Goel Motorola email: vgoel_at_pts.mot.com Sr. Oracle DBA - Consultant Aerotek Inc. My email: vgoel_at_emi.net Motorola Info: Mail Stop 39, Room S1014 1500 Gateway Blvd, Boynton Beach, FL 33426 In article <31204C88.3EFE_at_tpg.tpg.oz.au>, Peter Derrick <pld_at_tpg.tpg.oz.au> writes:Received on Mon Feb 19 1996 - 00:00:00 CET
>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:
> */
>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.
> *
>
>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
>
>*/