Re: Dynamic SQL and Privileges

When using dbms_sql, you CANNOT have gotten privileges from roles, you must have the privilege EXPLICITLY granted to your userid(schema).

joe
http://www.oracle-dba.com

PS: my guess is you have connect, resource and/or dba role, have the create table privilege granted to your userid.

In article <70i5ni$ir5$1_at_nnrp1.dejanews.com>,   lrako_at_hotmail.com wrote:
> Dear all ,
>
> I've written a procedure to copy the contents of a table into another one.
> I'm using the package DBMS_SQL to execute a
>
> 'CREATE TABLE START_TableName AS (SELECT * FROM TableName)'
>
> At runtime, I receive 'ORA-01031: insufficient privileges'.
> But if I just type the DDL from SQL Plus, my table is sucessfully created.
>
> Does anyone have an explanation ?
> How Can I solve this ?
>
> Many thanks in advance.
>
> Lalaina
>
> PS: FIY find the source code of the proc.
>
> CREATE OR REPLACE PROCEDURE COPY_TABLE (ivTableName IN VARCHAR2 ,
> ovErrorMsg OUT VARCHAR2)
> IS
>
> vSQL VARCHAR2(2048) ;
> iCursor INTEGER ;
> nRowCount NUMBER ;
>
> BEGIN
>
> iCursor := DBMS_SQL.OPEN_CURSOR ;
>
> vSQL := NULL ;
> vSQL := CONCAT(vSQL, 'CREATE TABLE ') ;
> vSQL := CONCAT(vSQL, 'START_' ||ivTableName) ;
> vSQL := CONCAT(vSQL, ' AS ( SELECT * FROM ' ||ivTableName) ;
> vSQL := CONCAT(vSQL, ' )') ;
>
> DBMS_OUTPUT.PUT_LINE(vSQL) ; DBMS_SQL.PARSE(iCursor, vSQL, DBMS_SQL.NATIVE) ;
> nRowCount := DBMS_SQL.EXECUTE(iCursor) ; DBMS_SQL.CLOSE_CURSOR(iCursor) ;
> ovErrorMsg := 'Table ' ||ivTableName || ' has been successfully copied into '
> ; ovErrorMsg := CONCAT(ovErrorMsg, 'START_' ||ivTableName) ;
>
> EXCEPTION
> WHEN OTHERS THEN
> ovErrorMsg := SQLERRM ;
> END COPY_TABLE ;
> /
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Oct 21 1998 - 08:23:49 CDT