RE: 30+ long db_link names and dbms_metadata.get_ddl
- From: "Dan Fink" <Dan.Fink@xxxxxxxxxx>
- To: Dave.Herring@xxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 26 May 2005 09:09:34 -0500
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@xxxxxxxxxxxxx
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@xxxxxxxxxx <mailto:dherri@xxxxxxxxxx> >
-------------------------------------
**********************************************************************
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
