Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 4B21C19601BE for ; Fri, 8 Aug 2014 11:18:25 +0200 (CEST) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Fri, 8 Aug 2014 11:18:25 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 130B82C62D; Fri, 8 Aug 2014 05:18:24 -0400 (EDT) Authentication-Results: turing.freelists.org; dkim=fail (verification failed; insecure key) header.i=@gmail.com; dkim-adsp=none (insecure policy) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id k0dD-QdZMGjp; Fri, 8 Aug 2014 05:18:23 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 48C032C5D3; Fri, 8 Aug 2014 05:17:43 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 08 Aug 2014 05:17:02 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 23ED52C3D7 for ; Fri, 8 Aug 2014 05:17:02 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id c81QYfeC3zaw for ; Fri, 8 Aug 2014 05:17:02 -0400 (EDT) Received: from mail-oa0-f42.google.com (mail-oa0-f42.google.com [209.85.219.42]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E91E42C1C5 for ; Fri, 8 Aug 2014 05:16:38 -0400 (EDT) Received: by mail-oa0-f42.google.com with SMTP id n16so3890575oag.29 for ; Fri, 08 Aug 2014 02:16:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=zBIZKkAfSdkwNAO4w2c/f1UkqBwK48MAxNoxmEkH4ew=; b=iX+rartxd8e9HWaqpaNyHHNY1iJWVv0AGrS8QVynW5uQd8MHJllaBzokyV5EWz5rge d4pGvDB36Jxd+nRGsim94I6oj2O1MlL5c/KBxXKf7aurREhhuFi6IRC8OlJmLLaJvMK5 6ds47nJDese9m8P7tP8DpuUZNOaQFi821OHESqeii9Oz4Y6I0+Vzf27ubHWijHGaUmKH Ps55rHr52i1NKRzHmhiuB6cjL5QBwcbS2zCXQ0flhmu9wavRJHdAOI14ma6+f8Hjby9G qP9YpUFYYXm+mjAfE+MshNrwCh3lj1hj75r4Y5KvCLjO/ItdGa5uPDl+fTqfgz/fcGd4 DNpg== MIME-Version: 1.0 X-Received: by 10.182.236.162 with SMTP id uv2mr29319141obc.12.1407489397765; Fri, 08 Aug 2014 02:16:37 -0700 (PDT) Received: by 10.76.152.39 with HTTP; Fri, 8 Aug 2014 02:16:37 -0700 (PDT) In-Reply-To: <0BDF2A25A09ADD40908745EEFC0A0FB60220116C@HKJUMXMB103B.zone1.scb.net> References: <0BDF2A25A09ADD40908745EEFC0A0FB60220116C@HKJUMXMB103B.zone1.scb.net> Date: Fri, 8 Aug 2014 14:46:37 +0530 Message-ID: Subject: Re: Can't create an object through a Stored Procedure ? From: vijay sehgal To: Hemant-K.Chitale@sc.com Cc: ORACLE-L Content-Type: multipart/alternative; boundary=001a11c2eb52943dae05001aa9c5 X-archive-position: 55861 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: vijaysehgal21@gmail.com Precedence: normal Reply-To: vijaysehgal21@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --001a11c2eb52943dae05001aa9c5 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hemant, 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 wrote: > This has me stumped / stymied. I can DROP objects using a Stored > Procedure but I can=E2=80=99t 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 copi= es > 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.htm= l > . > --001a11c2eb52943dae05001aa9c5 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hemant,

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

Oracle Database 11g Ent= erprise Edition Release 11.2.0.4.0 - 64bit Production On Solaris Box.
SQL> desc t3;
ERROR:
ORA-04043: object t3 does not exist

SQL>=C2=A0 exec execute_ddl('create table t3(col1 number)');
PL/SQL procedure successfully completed.

SQL> desc t3;
= =C2=A0Name=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 Null?=C2=A0=C2=A0=C2=A0 Type
=C2=A0----------------------------------------- -------- -------------
= =C2=A0COL1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NUMBER
<= br>SQL>=C2=A0 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, Chi= tale, Hemant K <Hemant-K.Chitale@sc.com> wrote:

This has me stum= ped / stymied.=C2=A0 I can DROP objects using a Stored Procedure but I can<= /font>=E2=80=99t CREATE them ?!

SQL>select object_name, obje= ct_type from user_objects

=C2=A0 2=C2= =A0 where object_name like 'HKC%'

=C2=A0 3=C2= =A0 order by object_type, object_name

=C2=A0 4=C2= =A0 /

OBJECT_NAME= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 OBJECT_TYPE

------------= ------------------ -------------------

HKC_SOURCE_1= _NDX_1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 INDEX

HKC_SEQ_1=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SEQUENCE

HKC_GTT_1=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_GTT_2=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_SOURCE_1= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_SOURCE_2= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_TARGET_1= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_TEST_100= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_TEST_X= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_VW=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 VIEW=

10 rows sele= cted.

SQL>creat= e or replace procedure execute_ddl(ddl_in varchar2)

=C2=A0 2=C2= =A0 authid definer

=C2=A0 3=C2= =A0 as

=C2=A0 4=C2= =A0 begin

=C2=A0 5=C2= =A0 execute immediate ddl_in;

=C2=A0 6=C2= =A0 end;

=C2=A0 7=C2= =A0 /

Procedure cr= eated.

SQL>execu= te execute_ddl('create table hkc_test_y (id_column number)');

BEGIN execut= e_ddl('create table hkc_test_y (id_column number)'); END;

*

ERROR at lin= e 1:

ORA-01031: i= nsufficient privileges

ORA-06512: a= t "GLELD1_O.EXECUTE_DDL", line 5

ORA-06512: a= t line 1


SQL>execu= te execute_ddl('drop table hkc_test_x');

PL/SQL proce= dure successfully completed.

SQL>execu= te execute_ddl('create sequence hkc_seq_2 start with 1');

BEGIN execut= e_ddl('create sequence hkc_seq_2 start with 1'); END;=

*

ERROR at lin= e 1:

ORA-01031: i= nsufficient privileges

ORA-06512: a= t "GLELD1_O.EXECUTE_DDL", line 5

ORA-06512: a= t line 1


SQL>execu= te execute_ddl('drop sequence hkc_seq_1');

PL/SQL proce= dure successfully completed.

SQL>execu= te execute_ddl('create view hkc_vw_2 as select * from dual');

BEGIN execut= e_ddl('create view hkc_vw_2 as select * from dual'); END;

*

ERROR at lin= e 1:

ORA-01031: i= nsufficient privileges

ORA-06512: a= t "GLELD1_O.EXECUTE_DDL", line 5

ORA-06512: a= t line 1


SQL>execu= te execute_ddl('drop view hkc_vw');

PL/SQL proce= dure successfully completed.

SQL>selec= t object_name, object_type from user_objects

=C2=A0 2=C2= =A0=C2=A0 where object_name like 'HKC%'

=C2=A0 3=C2= =A0=C2=A0 order by object_type, object_name

=C2=A0 4=C2= =A0=C2=A0 /

OBJECT_NAME= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 OBJECT_TYPE

------------= ------------------ -------------------

HKC_SOURCE_1= _NDX_1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 INDEX

HKC_GTT_1=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_GTT_2=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_SOURCE_1= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_SOURCE_2= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_TARGET_1= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

HKC_TEST_100= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TABLE

7 rows selec= ted.

SQL>

What am I missin= g here ?

11.2.0.3<= /span>

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.

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