Home » SQL & PL/SQL » SQL & PL/SQL » Exception while creating table using EXECUTE IMMEDIATE (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit)
Exception while creating table using EXECUTE IMMEDIATE [message #608367] Wed, 19 February 2014 04:00 Go to next message
Manoj.Gupta.91
Messages: 186
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I'm not able to create a table using execute immediate. I'm working on same schema (owner).

create or replace 
PROCEDURE prc_Create_Table
(
      ps_in_Table_Name     IN        VARCHAR2
)
AS

     TABLE_ALREADY_EXIST      EXCEPTION ;
     PRAGMA EXCEPTION_INIT( TABLE_ALREADY_EXIST, -955 ) ;

BEGIN

     EXECUTE IMMEDIATE( ' CREATE TABLE MANOJ ( A NUMBER(3) ) ' ) ;

     COMMIT ;
EXCEPTION
     WHEN TABLE_ALREADY_EXIST THEN
          DBMS_OUTPUT.PUT_LINE( 'ERROR 1.......!!' ) ;
          DBMS_OUTPUT.PUT_LINE( 'USER = ' || USER || ' ERROR = ' || SQLCODE || SQLERRM ) ;
          ROLLBACK ;
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE( 'ERROR 2.......!!' ) ;
          DBMS_OUTPUT.PUT_LINE( 'USER = ' || USER || ' ERROR = ' || SQLCODE || SQLERRM ) ;
          ROLLBACK ;
          RAISE ;
END prc_Create_Table ;



BEGIN
     prc_Create_Table( 'MANOJ' ) ;
END ;


ERROR :
Error starting at line 20 in command:
BEGIN
     prc_Create_Table( 'MANOJ' ) ;
END ;
Error report:
ORA-01031: insufficient privileges
ORA-06512: at "ETM.PRC_CREATE_TABLE", line 25
ORA-06512: at line 2
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.


Although if I execute below piece of code I can create table.

BEGIN
     prc_Create_Table( 'CREATE TABLE MANOJ ( A NUMBER(3) )' ) ;
END ;


Please help me to know what can be the issue.

Thanks & Regards
Manoj
Re: Exception while creating table using EXECUTE IMMEDIATE [message #608368 is a reply to message #608367] Wed, 19 February 2014 04:09 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
AskTom on roles and procedures
Re: Exception while creating table using EXECUTE IMMEDIATE [message #608370 is a reply to message #608367] Wed, 19 February 2014 04:22 Go to previous messageGo to next message
John Watson
Messages: 4093
Registered: January 2010
Location: Global Village
Senior Member
To test, connect as the owner, and try this:
set role none;
CREATE TABLE MANOJ ( A NUMBER(3) );
Re: Exception while creating table using EXECUTE IMMEDIATE [message #608372 is a reply to message #608370] Wed, 19 February 2014 04:28 Go to previous message
Manoj.Gupta.91
Messages: 186
Registered: March 2008
Location: Delhi
Senior Member
John Watson wrote on Wed, 19 February 2014 15:52
To test, connect as the owner, and try this:
set role none;
CREATE TABLE MANOJ ( A NUMBER(3) );



It works.
Previous Topic: get the data between the dates
Next Topic: Creating a table and referencing that table in same package.
Goto Forum:
  


Current Time: Mon Apr 21 10:20:09 CDT 2014

Total time taken to generate the page: 0.13586 seconds