From oracle-l-bounce@freelists.org Wed Jun 23 11:45:45 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5NGjKT30238 for ; Wed, 23 Jun 2004 11:45:30 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5NGjA630201 for ; Wed, 23 Jun 2004 11:45:20 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9376372CAE2; Wed, 23 Jun 2004 11:28:40 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00409-61; Wed, 23 Jun 2004 11:28:40 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D067372CA40; Wed, 23 Jun 2004 11:28:39 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 23 Jun 2004 11:27:11 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3015A72CA56 for ; Wed, 23 Jun 2004 11:27:11 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00647-10 for ; Wed, 23 Jun 2004 11:27:10 -0500 (EST) Received: from netmail6.thehartford.com (simmailgate.thehartford.com [162.136.191.90]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BE98D72CA40 for ; Wed, 23 Jun 2004 11:27:09 -0500 (EST) Received: from hlicorsimnti002.wwl.hartfordlife.com (hlicorsimnti002.hartfordlife.com [157.209.206.90]) by netmail6.thehartford.com (Switch-3.1.5/Switch-3.1.0) with ESMTP id i5NGmjHZ005306 for ; Wed, 23 Jun 2004 12:48:45 -0400 (EDT) Received: from HLICORSIMNTM015.hartfordlife.com (hlicorsimntm015.hartfordlife.com) by hlicorsimnti002.wwl.hartfordlife.com (Content Technologies SMTPRS 4.3.1) with ESMTP id for ; Wed, 23 Jun 2004 12:48:11 -0400 Subject: Error ORA-31603 while extracting DDL using dbms_metadata To: oracle-l@freelists.org X-Mailer: Lotus Notes Release 5.0.11 July 24, 2002 Message-ID: From: Prasada.Gunda@hartfordlife.com Date: Wed, 23 Jun 2004 12:41:36 -0400 X-MIMETrack: Serialize by Router on LIFESMTP3/HLIFE (Release 5.0.11 |July 24, 2002) at 06/23/2004 12:48:10 PM MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 3449 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Prasada.Gunda@hartfordlife.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org When I use dbms_metadata package to extract the index ddl of other schema(DISCLM) table using the stored procedure owned by another schema (UTIL), I am getting the following error. When I run the same code under anonymous block, it works fine. ORA-31603: object "IDX_BM_DD_ADTKY" of type INDEX not found in schema "DISCLM" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 628 ORA-06512: at "SYS.DBMS_METADATA", line 1221 ORA-06512: at line 1 DB is 9.2.0.5 and it is on hp-ux 11. I am aware of that the roles are disabled during the execution of pl/sql stored procedure unless the procedure is created under Invoker rights. This procedure created under Definer rights. I am not sure which system/object privilege I need to grant for this procedure to work. I tried using dbms_metadata.set_filter with SCHEMA_EXPR, BASE_OBJECT_SCHEMA, BASE_OBJECT_OWNER but no success. Any thoughts or inputs are much appreciated. Thanks for your help. Code snippet in the stored procedure. insert into index_control (owner, table_name, index_name, error_level, index_script) select owner_select_var, table_select_var, q_rec.index_name, 3, dbms_metadata.get_ddl('INDEX', q_rec.index_name, owner_select_var) from dual; UTIL schema has the following privileges: Sys Privs : drop user analyze any create user alter system alter session drop any index drop any table grant any role alter any index create any index select any table select any dictionary unlimited tablespace Tab privs : v_$session select dba_indexes select dba_ind_columns select dba_objects select dba_tables select dba_tab_columns select dba_tab_partitions select dba_ind_partitions select dbms_lock execute dbms_metadata execute Best Regards, Prasad ************************************************************************* PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. ************************************************************************* ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------