Re: Dynamic SQL and Privileges

On Tue, 20 Oct 1998 14:12:02 GMT, 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 ?

The person executing the PL/SQL needs to have the role 'CREATE TABLE' granted directly to them. Roles are not enabled in PL/SQL. You probably have ther role resource that allows you to create a table from sql*plus. To test and see if any DDL will work from dynamic PL/SQL, from sql*plus try...

SQL> set role none;

Role set.

SQL> create table a ( n number );
create table a ( n number )
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> set role all;

Role set.

SQL> create table a ( n number );

Table created.

Hope this helps.

chris.

>
>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
Received on Tue Oct 20 1998 - 10:15:17 CDT