From oracle-l-bounce@freelists.org Thu May 26 10:14:30 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4QFETba011964 for ; Thu, 26 May 2005 10:14:29 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j4QFETNi011960 for ; Thu, 26 May 2005 10:14:29 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3A1C51B321B; Thu, 26 May 2005 09:11:31 -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 30598-02; Thu, 26 May 2005 09:11:31 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BED841B2C3D; Thu, 26 May 2005 09:11:30 -0500 (EST) Date: Thu, 26 May 2005 09:09:34 -0500 From: "Dan Fink" To: Dave.Herring@acxiom.com, oracle-l@freelists.org Message-ID: <30949390.1117116574790.JavaMail.root@hotsos01.hotsos.com> In-Reply-To: <7ED53A68952D3B4C9540B4EFA5C76E367E6D7C@CWYMSX04.Corp.Acxiom.ne> Subject: RE: 30+ long db_link names and dbms_metadata.get_ddl x-scalix-Hops: 1 X-Client-IP: 4.37.64.164 Mime-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: 8bit X-archive-position: 20327 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Dan.Fink@hotsos.com Precedence: normal Reply-To: Dan.Fink@hotsos.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=none autolearn=ham version=2.63 Dave, DBMS_METADATA is rather buggy in 9i. You might have to write your own procedure to extract db_links. Most object names in the database are 30 characters, so the procedure was probably written with this in mind. At least that is the simplest explanation. Regards, Dan Fink ----- Original Message ----- From: Herring Dave - dherri Sent: Thu May 26 07:17:41 MDT 2005 To: oracle-l@freelists.org Subject: 30+ long db_link names and dbms_metadata.get_ddl Has anyone run into a problem trying to extract the definition of a database link, using DBMS_METADATA, where the link name is > 30 characters? Either I'm missing something or I've found another bug in 9.2.0.4. Here's an example: CREATE DATABASE LINK test_of_a_long_database_link_name USING 'sid1'; SET ECHO off FEEDBACK off HEADING off LINESIZE 2047 LONGCHUNKSIZE 2047 LONG 32767 PAGESIZE 0 SCAN off SET SQLBLANKLINES on TAB off COLUMN stmt WORD_WRAPPED EXECUTE dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM,'SQLTE RMINATOR',TRUE); SELECT dbms_metadata.get_ddl('DB_LINK','TEST_OF_A_LONG_DATABASE_LINK_NAME','DHE RRI') stmt FROM dual; .. and the output is: ORA-31600: invalid input value LONGNAME for parameter NAME in function SET_FILTER ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA_INT", line 1980 ORA-06512: at "SYS.DBMS_METADATA_INT", line 3665 ORA-06512: at "SYS.DBMS_METADATA", line 670 ORA-06512: at "SYS.DBMS_METADATA", line 571 ORA-06512: at "SYS.DBMS_METADATA", line 1221 ORA-06512: at line 1 If I change the database link name to ' test_of_a_long_database_link_n' it works fine. Any clues? Dave ------------------------------------- Dave Herring, DBA Acxiom Corporation 3333 Finley Downers Grove, IL 60515 wk: 630.944.4762 > ------------------------------------- ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l