Help - Search - Members - Calendar
Full Version: CREATE TABLE fails inside PROC
Oracle DBA Forums > Oracle > Oracle Forum
ulab
Hi All,

i tried creating table inside named PL/SQL block , that resulted in insufficient privs error but when i tried same outside the PROC it is working fine... any reasons??

CODE
SQL> exec create_ct_tabs;
CREATING TAB EXEMPTIONS_T_CT
CREATE TABLE EXEMPTIONS_T_CT AS SELECT
OBJ_ID0,PERCENT,EXEMPT_END_T,CERTIFICATE_NUM,TYPE,REC_ID,EXEMPT_START_T,OPERATIO
N$,COMMIT_TIMESTAMP$ FROM STG.EXEMPTIONS_T_CT@DSP
ERROR RESULTED WHILE CRETAING TABLE EXEMPTIONS_T_CT ERROR:ORA-01031:
insufficient privileges
FAILED NUMBER:1

PL/SQL procedure successfully completed.

SQL> CREATE TABLE EXEMPTIONS_T_CT AS SELECT OBJ_ID0,PERCENT,EXEMPT_END_T,CERTIFICATE_NUM,TYPE,REC_ID,EXEMPT_START_T,
  2  OPERATION$,COMMIT_TIMESTAMP$ FROM STG.EXEMPTIONS_T_CT@DSP;

Table created.


Many thanks
ulab
Thanks Burleson ...

but i was trying to use dynamic sql only.....

execute immediate v_create_tab;

above line resulted in error...
burleson
DDL is not a good idea in PL/SQL.

Table are persistent, that's a bad design to create tables frequently


If you need temporary storage use the WITH clause.

READ!

https://www.google.com/search?&q=ddl+wi...t+privileges%22
SteveC
DDL is supported in PL/SQL, a classic example is index maintenance for bulk loads in data warehousing (drop index, do the bulk load, rebuild index).
Another example is using a staging table created for a daily load from a flat file, the table is named based on the day's date.

The issue is invoker versus definer rights. Who is running the procedure, and what permissions does that user have? Your error is quite common, where in SQL you can do whatever, but within PL/SQL, all of a sudden you have insufficient privileges.

Grant execute on the procedure to the user invoking the procedure.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.