Oracle Error : ORA-02019 - Connect descriptor to remote database not found.

Does the same user own the dblink and the procedure?

I think I once had a similar problem.
Try to make the database link public or on the schema that owns the procedure.

If is not working try this:
Open the sqlnet.ora and look for statements like

SQLNET.AUTHENTICATION_SERVICES = (NONE)
sqlnet.crypto_seed = "some text"

and comment out these lines using a "#" sign.

hi there

From where did u create the DB link, did u create this from your PC.

Try creating the DB link from the server. Also remember to create it as public DB link and also the tnsnames.ora file in the server should have the connect descriptor used in the create statement.

hope this resolves ur problem

regards
annamalai

I created a public DBlink from the 8iDB to the 9iDB. I have also included the name of the connect descriptor in the tnsnames.ora file before i created the link.

The same user owns both the procedure, the trigger & the public dblink on the 8iDB.  But the user that owns the object on the 9iDB is different. It is a portal user. Can this be the problem. Must the table be owned by a user with the same name & privileges on the remote 9iDB.

hi

I was asking from where the DB link was created. Is it created from a PC or from the server. If created from PC, then drop the db link and create it from the server as public DB link.

The table need not be owned by the user with the same name, it can be any user. But privileges u have to check.

create public database link <link name> connect to <username> identified by <password> using '<tnsnames.ora connect string>';

remember to put the single quotes as i have mentioned.

regards
annamalai

I will illustrate the full scenario below:

********************************************************************************
The DBlink was created on the SCO unixware8 (Oracle 8i)  server & the entries on the server are as follows:

tnsnames.ora
=========
ASDB.BAPPLC.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = tcp)(HOST = 10.1.1.66)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = asdb.bapplc.com))
  )

/etc/hosts
======
10.1.1.66            dev.bapplc.com       dev

create dblink command
================
create public database link asdb.bapplc.com connect to portal identified by portal using 'asdb.bapplc.com';

sqlnet.ora
===========================
names.default_domain=world4
name.default_zone=world4
SQLNET.EXPIRE_TIME=1

listener.ora
============================
# LISTENER.ORA Configuration File:/u01/app/oracle/product/8.1.7/network/admin/listener.ora
# Generated by Oracle Net8 Assistant

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.10)(PORT = 1531))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = BANK)
      (ORACLE_HOME = /u01/app/oracle/product/8.1.5)
      (SID_NAME = BANK)
    )
  )
CONNECT_TIMEOUT_LISTENER=20
******************************************************************************
******************************************************************************
The entries on the RedHat Advanced server 2.1 (oracle 9i) are as follows

tnsnames.ora
==========================
ASDB.BAPPLC.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dev.bapplc.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = asdb.bapplc.com)
    )
  )

S0201001.WORLD4=(DESCRIPTION=
            (ADDRESS=
               (PROTOCOL=tcp)
               (HOST=S0201001)
               (PORT=1531)
            )
            (CONNECT_DATA=(SID=BANK)
            )
         )


/etc/hosts
=============
127.0.0.1       dev.bapplc.com  localhost.localdomain   localhost
10.1.1.10       S0201001        

create dblink command
================
no DBlink was created on this server.

sqlnet.ora
===========================
NAMES.DEFAULT_DOMAIN = bapplc.com
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, ONAMES, HOSTNAME)
DEFAULT_SDU_SIZE = 8761

listener.ora
============================
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = dev.bapplc.com)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/ias/infrastructure)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = asdb.bapplc.com)
      (ORACLE_HOME = /opt/oracle/ias/infrastructure)
       (SID_NAME = asdb)
    )
**************************************************************************

The 9iDB (on RedHat Linux) was installed along with Oracle10g AS installation, so we do not want to change its settings, but we're trying to plug it into the existing framework of SCO unixware8 (Oracle 8i) servers.

I will appreciate any assistance. Thank you.

A SELECT statement with the same link gives the resulting output :

select bal_code, bal_num from balact@asdb.balplc.com;

BAL_CODE    BAL_NUM
----------       ----------
       301     134308
       301     134309
       301     130046
       
The procedure that does the remote insert was created with the following syntax:

CREATE OR REPLACE  PROCEDURE INS$RT_PORTAL_BALACT  

      (bal_code number,bal_num number)
as

begin
    insert into portal_balact@asdb.balplc.com
    select a.BAL_CODE,a.BAL_NUM
    from bal_act a, branch_link b
    where a.bal_code = 301
    and to_char(a.bal_code) = b.bal_code;
    commit;
end;

SQL> exec ins$rt_portal_balact(301,134308);
BEGIN ins$rt_portal_balact(301,134308); END;

*
ERROR at line 1:
ORA-02019: connection description for remote database not found
ORA-02063: preceding line from ASDB.BALPLC.COM
ORA-06512: at "INS$RT_PORTAL_BALACT", line 7
ORA-06512: at line 1

1. Ho is the owner of this procedure?
2. Try to make a command line insert with same values like the procedure. Does it work?

I have solved the issue.

I eventually used packages & package bodies with calls to the dbms_reputil package. It goes thus;

create or replace package
as  
    procedure balact_rep_ins( .....,.....,.....,....);
end;

   
create or replace package body
as
 procedure balact_rep_ins
 (......,......,.....,)
 is  
  pflag number ;
  begin
    dbms_reputil.rep_begin;
    dbms_reputil.recursion_off;  
    insert into BALACT@ASDB.BALPLC.COM(........,........,,.......,)  
    values(.....,.....,.....,...,);
   
    exception
      when dup_val_on_index
      then delete BALACT@ASDB.BALPLC.COM;
      dbms_reputil.rep_end;
      when others then
      dbms_reputil.rep_end;
      raise;  
 end;

It worked perfectly. I guess there just a problem using database links in oracle stored procedures.

Thanks everyone.