FW: Can't create an object through a Stored Procedure ?
Date: Fri, 8 Aug 2014 17:11:07 +0800
Message-ID: <0BDF2A25A09ADD40908745EEFC0A0FB60220117A_at_HKJUMXMB103B.zone1.scb.net>
I think we can ignore this email.
My account has been granted CREATE privileges through a Role, not directly.
Hemant K Chitale
From: Chitale, Hemant K
Sent: Friday, August 08, 2014 5:06 PM
To: ORACLE-L
Subject: Can't create an object through a Stored Procedure ?
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-lReceived on Fri Aug 08 2014 - 11:11:07 CEST