dblink

[rjoshi2]

I have two database demo and cns. In cns I have a procedure cmtest. In this procedure I want execute a function, which belongs to a package in my demo db. How do I set up the dblink to do this? How to do I call the function? Any help would be appreciated.

Thank You,
rjoshi2

 

[SantaMufasa]

RJoshi,

Set up a database link to the package-owning schema on your "Demo" db, as I demonstrate here:

SQL> drop database link joshi;

Database link dropped.

SQL> create database link joshi
2 connect to dontdrop identified by dontdrop using 'fos';

Database link created.

SQL> col a heading "Number to Word translation" word_wrap
SQL> select dh_util.spell@joshi(12345678901.2345678) a from dual;

Number to Word translation
--------------------------------------------------------------------------------
Twelve Billion Three Hundred Forty-Five Million Six Hundred Seventy-Eight
Thousand Nine Hundred One and Two Million Three Hundred Forty-Five Thousand Six
Hundred Seventy-Eight / Ten Millionths


1 row selected.
[/code]

Also of note when creating database links are Turkbear's comments from thread759-731639:
"The tnsnames.ora on the database server is the one that needs the info about the linked database instance, not the one on your client. ( I won't reveal how many times that one has bitten me in the a__)

Also, the link name is controlled, to some extent, by the GLOBAL_NAMES parameter in the remote databases init file.
If it is set to TRUE, then the link name must be the same as the database name. Otherwise you can call the link anything you want."

Let me know if this is what you want and if it works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:30 (17Dec03) GMT, 11:30 (17Dec03) Mountain Time)

 

[rjoshi2]

question am I getting this error because I have not changed the tnsnames.ora on the database server:


CREATE PUBLIC DATABASE LINK "GMLINK" CONNECT
TO "DEMO" IDENTIFIED BY "sysadmin" USING 'DEMODB' ;

select REPORT_TYPE from report_picture@GMLINK;

select REPORT_TYPE from report_picture@GMLINK
*
ERROR at line 1:
ORA-02085: database link GMLINK.US.ORACLE.COM connects to DEMODB.US.ORACLE.COM

 

[SantaMufasa]

Joshi,

Among the things you need to check are:

1) From an O/S prompt on the machine that houses your CNS database, issue this command:
"sqlplus DEMO/sysadmin@DEMODB"

...if there are problems with this connection, then there will be problems with your DB link. Your tnsnames.ora on the CNS machine must have a host service, "DEMODB" for which the connection, above, is successful.

2) Once you have a clear connection to DEMODB, please issue this SELECT and ensure that the VALUE is "FALSE":

col p heading "Parameter" format a41
col v heading "Value" format a50 word wrap
select name p, value v from v$parameter
where name ='global_names';

If it is set to TRUE, then (as Turkbear says) "the link name must be the same as the database name."

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:20 (17Dec03) GMT, 13:20 (17Dec03) Mountain Time)

 

[Turkbear]

Hi,
To confirm what SantaMufasa said, that is the 'classic' error mesage when the GLOBAL_NAMES on the target is set to TRUE - Rename your link to

 DEMODB.US.ORACLE.COM

and it should work fine..If it were your tnsnames.ora at fault, you would not get the info about the target's name..

 

[rjoshi2]

Thank you for you help it was situation 2 so I just change the name of the dblink and it is working now.

- rjoshi2