Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink
DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544333] Tue, 21 February 2012 05:16 Go to next message
julb
Messages: 17
Registered: November 2010
Junior Member
Hi,

I have user U1 with dblink DBL1 (private dblink, not public).
When I logged in with U1 and try to execute the following statement :
 SELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME)
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';


I get ORA-31603 error:
ORA-31603: object "DBL1" of type DB_LINK not found in schema "U1"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

DBL1 script is as follows:
CREATE DATABASE LINK DBL1
  CONNECT TO U1 IDENTIFIED BY XX USING 'TNS1';


when I select from user_objects with the following query , it is there.
SELECT *
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';


DBMS_METADATA.GET_DDL works for every other objects (tables for example) in this schema except for dblinks.
What can cause this behavior?

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544346 is a reply to message #544333] Tue, 21 February 2012 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
SQL> select object_name
  2  FROM SYS.USER_OBJECTS
  3  WHERE OBJECT_TYPE = 'DATABASE LINK'
  4  AND OBJECT_NAME   = 'MYLINK.WORLD'
  5  /
OBJECT_NAME
------------------------------
MYLINK.WORLD

1 row selected.

SQL> SELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME)
  2  FROM SYS.USER_OBJECTS
  3  WHERE OBJECT_TYPE = 'DATABASE LINK'
  4  AND OBJECT_NAME   = 'MYLINK.WORLD'
  5  /
SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME)
--------------------------------------------------------------------------------

  CREATE DATABASE LINK "MYLINK.WORLD"
   CONNECT TO "MICHEL" IDENTIFIED BY VALUES '050F0983FD639837FEF3377178AFB39CC4'
   USING 'MIKA.WORLD'

It works for me.

Regards
Michel

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544367 is a reply to message #544346] Tue, 21 February 2012 07:01 Go to previous messageGo to next message
julb
Messages: 17
Registered: November 2010
Junior Member
I know that this script suppose to work.
The question is why it doesn't work?

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544368 is a reply to message #544367] Tue, 21 February 2012 07:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why don't you post your case as I posted mine?

Regards
Michel

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544379 is a reply to message #544368] Tue, 21 February 2012 07:51 Go to previous messageGo to next message
julb
Messages: 17
Registered: November 2010
Junior Member
Is it better?


SQL> SELECT object_name, SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME)
  2  FROM SYS.USER_OBJECTS
  3  WHERE OBJECT_TYPE = 'DATABASE LINK'
  4  AND OBJECT_NAME   = 'DBL1';
ERROR:
ORA-31603: object "DBL1" of type DB_LINK not found in schema "U1"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1



no rows selected

SQL> 
SQL> SELECT object_name
  2  FROM SYS.USER_OBJECTS
  3  WHERE OBJECT_TYPE = 'DATABASE LINK'
  4  AND OBJECT_NAME   = 'DBL1';

OBJECT_NAME
--------------------------------------------------------------------------------

DBL1

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544386 is a reply to message #544379] Tue, 21 February 2012 08:06 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
and what user are you connected as?

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544434 is a reply to message #544386] Tue, 21 February 2012 12:37 Go to previous messageGo to next message
julb
Messages: 17
Registered: November 2010
Junior Member
I'm connected with U1 - the same user dblink belongs to.

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544436 is a reply to message #544434] Tue, 21 February 2012 12:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
SQL> SELECT object_name
  2  FROM SYS.USER_OBJECTS
  3  WHERE OBJECT_TYPE = 'DATABASE LINK'
  4  AND OBJECT_NAME   = 'DBL1';

OBJECT_NAME
--------------------------------------------------------------------------------

DBL1

>I'm connected with U1 - the same user dblink belongs to.
No, not really!

above reveals that the DB_LINK is owned by SYS; not by U1

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544437 is a reply to message #544436] Tue, 21 February 2012 12:48 Go to previous messageGo to next message
julb
Messages: 17
Registered: November 2010
Junior Member
It doesnt matter if you select from user_objects or sys.user_objects.
It will always return objects owned by current user. In my case it is U1.

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544438 is a reply to message #544437] Tue, 21 February 2012 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>It doesnt matter if you select from user_objects or sys.user_objects.
REALLY?

SQL> connect user1/user1
Connected.
SQL> desc t1
 Name									 Null?	  Type
 ----------------------------------------------------------------------- -------- -------------------------------------------------
 D1										  DATE

SQL> select count(*) from sys.user_object where object_name = 'T1';
select count(*) from sys.user_object where object_name = 'T1'
                         *
ERROR at line 1:
ORA-00942: table or view does not exist


>It will always return objects owned by current user. In my case it is U1.
post proof like above

Report message to a moderator

Re: DBMS_METADATA.GET_DDL Fails with ORA-31603 on dblink [message #544439 is a reply to message #544438] Tue, 21 February 2012 12:56 Go to previous message
julb
Messages: 17
Registered: November 2010
Junior Member
Yes, REALLY...
You are selecting from USER_OBJECT instead of USER_OBJECTS...

Report message to a moderator

Previous Topic: Trigger for updating two accounts after money transfer*2 Merged)
Next Topic: oracle not found - error - very critical - need help(4 Merged)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 30 04:04:14 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.