RE: 30+ long db_link names and dbms_metadata.get

From: Dan Fink <Dan.Fink_at_hotsos.com>
Date: Thu, 26 May 2005 09:09:34 -0500
Message-ID: <30949390.1117116574790.JavaMail.root@hotsos01.hotsos.com>

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_at_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

<mailto:dherri_at_acxiom.com <mailto:dherri_at_acxiom.com> >

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 Received on Thu May 26 2005 - 10:14:30 CDT

RE: 30+ long db_link names and dbms_metadata.get_ddl