Dynamic SQL and Privileges

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)

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 - 09:12:02 CDT