Re: Dynamic SQL and Privileges

From: Sandy <Sandeep.digra_at_in.bosch.com>
Date: Mon, 23 Nov 1998 13:47:04 +0530
Message-ID: <36591A00.879C1430@in.bosch.com>

Hi,

        You need to have the create table privilege granted to you explicitly, not through roles, as roles are disabled during

         stored procedures.
         So contact your system administrator, and ask him to give you a create
user privilege explicitly .
          i.e login as dba,
             give sql command,    GRANT CREATE TABLE TO <YOUR_USERNAME>

Regards
Sandeep

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
Received on Mon Nov 23 1998 - 02:17:04 CST