Re: Can't create an object through a Stored Procedure ?

I am able to use your code and create the table below are details.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production On Solaris Box.

SQL> desc t3;
ERROR:
ORA-04043: object t3 does not exist

SQL> exec execute_ddl('create table t3(col1 number)');

PL/SQL procedure successfully completed.

SQL> desc t3;

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 COL1                                               NUMBER

SQL> exec execute_ddl('drop table t3 purge');

PL/SQL procedure successfully completed.

SQL> desc t3;
ERROR:
ORA-04043: object t3 does not exist

Regards,
Vijay Sehgal.

On Fri, Aug 8, 2014 at 2:36 PM, Chitale, Hemant K <Hemant-K.Chitale_at_sc.com> wrote:

> This has me stumped / stymied. I can DROP objects using a Stored
> Procedure but I can’t CREATE them ?!
>
> SQL>select object_name, object_type from user_objects
>
> 2 where object_name like 'HKC%'
>
> 3 order by object_type, object_name
>
> 4 /
>
> OBJECT_NAME OBJECT_TYPE
>
> ------------------------------ -------------------
>
> HKC_SOURCE_1_NDX_1 INDEX
>
> HKC_SEQ_1 SEQUENCE
>
> HKC_GTT_1 TABLE
>
> HKC_GTT_2 TABLE
>
> HKC_SOURCE_1 TABLE
>
> HKC_SOURCE_2 TABLE
>
> HKC_TARGET_1 TABLE
>
> HKC_TEST_100 TABLE
>
> HKC_TEST_X TABLE
>
> HKC_VW VIEW
>
> 10 rows selected.
>
> SQL>create or replace procedure execute_ddl(ddl_in varchar2)
>
> 2 authid definer
>
> 3 as
>
> 4 begin
>
> 5 execute immediate ddl_in;
>
> 6 end;
>
> 7 /
>
> Procedure created.
>
> SQL>execute execute_ddl('create table hkc_test_y (id_column number)');
>
> BEGIN execute_ddl('create table hkc_test_y (id_column number)'); END;
>
> *
>
> ERROR at line 1:
>
> ORA-01031: insufficient privileges
>
> ORA-06512: at "GLELD1_O.EXECUTE_DDL", line 5
>
> ORA-06512: at line 1
>
> SQL>execute execute_ddl('drop table hkc_test_x');
>
> PL/SQL procedure successfully completed.
>
> SQL>execute execute_ddl('create sequence hkc_seq_2 start with 1');
>
> BEGIN execute_ddl('create sequence hkc_seq_2 start with 1'); END;
>
> *
>
> ERROR at line 1:
>
> ORA-01031: insufficient privileges
>
> ORA-06512: at "GLELD1_O.EXECUTE_DDL", line 5
>
> ORA-06512: at line 1
>
> SQL>execute execute_ddl('drop sequence hkc_seq_1');
>
> PL/SQL procedure successfully completed.
>
> SQL>execute execute_ddl('create view hkc_vw_2 as select * from dual');
>
> BEGIN execute_ddl('create view hkc_vw_2 as select * from dual'); END;
>
> *
>
> ERROR at line 1:
>
> ORA-01031: insufficient privileges
>
> ORA-06512: at "GLELD1_O.EXECUTE_DDL", line 5
>
> ORA-06512: at line 1
>
> SQL>execute execute_ddl('drop view hkc_vw');
>
> PL/SQL procedure successfully completed.
>
> SQL>select object_name, object_type from user_objects
>
> 2 where object_name like 'HKC%'
>
> 3 order by object_type, object_name
>
> 4 /
>
> OBJECT_NAME OBJECT_TYPE
>
> ------------------------------ -------------------
>
> HKC_SOURCE_1_NDX_1 INDEX
>
> HKC_GTT_1 TABLE
>
> HKC_GTT_2 TABLE
>
> HKC_SOURCE_1 TABLE
>
> HKC_SOURCE_2 TABLE
>
> HKC_TARGET_1 TABLE
>
> HKC_TEST_100 TABLE
>
> 7 rows selected.
>
> SQL>
>
> What am I missing here ?
>
> 11.2.0.3
>
> Hemant K Chitale
>
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all copies
> and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered Bank
> and their subsidiaries at https://www.sc.com/en/incorporation-details.html
> .
>

--
http://www.freelists.org/webpage/oracle-l