ORA-02019: connection description for remote database not found

[mortonsa]

Hi Everyone,

I am getting the following error message:

ORA-02019: connection description for remote database not found
ORA-02063: preceding line from ECGM

I am working in a database called ECSM. I set up a link called ECGM. I have been writing stored procedures to take data from ECSM and update tables in ECGM. I have over 100 procedures so far that are doing this without a hitch. But, I have encountered this error now for one procedure that I have written. Here is my code:

CREATE OR REPLACE PROCEDURE ERWIN.CONVERTCONTATTPHONE AS
ERROR_LOGIDY NUMBER;
ERROR_NUM INT;
v_ErrorCode NUMBER;
v_ErrorText VARCHAR2(200);

cursor c_phone is
select c.contactattorneyidy, c.phonenumber, c.phoneext,c.phonetypepid, c.primaryphoneind, a.attorneycontactidy
from tcontattphone c, tattorneycontact@ecgm a
where c.contactattorneyidy = a.attorneycontactidy
and c.primaryphoneind = 'Y';

begin
for i in c_phone loop
update TATTORNEYCONTACT@ecgm set
PHONENUMBER1 = i.phonenumber,
PHONEEXT1 = i.phoneext,
PHONETYPE1 = i.phonetypepid
where ATTORNEYCONTACTIDY = i.CONTACTATTORNEYIDY;
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
v_ErrorCode := SQLCODE;
v_ErrorText := substr(sqlerrm, 1, 200);
ROLLBACK;
SELECT SEQ_ERROR_LOG.NextVal INTO ERROR_NUM FROM dual;
SELECT SEQ_TERROR_LOG.NextVal INTO ERROR_LOGIDY FROM dual;
INSERT INTO TERROR_LOG (ERROR_LOGIDY, ERROR_ID, TEXT, CODE, OBJECT_NAME, RECORDDATE, USERNAME)
VALUES (ERROR_LOGIDY, ERROR_NUM, v_ErrorText, v_ErrorCode, 'CONVERTCONTATTPHONE', SYSDATE,'CONVERSION');
COMMIT;
END;

All of my procedures are written this way (aliases have not been a problem). I have tried pulling the select statement out of the cursor as follows...

select c.contactattorneyidy, c.phonenumber, c.phoneext,c.phonetypepid, c.primaryphoneind, a.attorneycontactidy
from tcontattphone c, tattorneycontact@ecgm a
where c.contactattorneyidy = a.attorneycontactidy
and c.primaryphoneind = 'Y';

and I get results returned to me. I can't figure it out! If anybody can offer any suggestions I would be greatly appreciative!

Thanks!
Stephany

 

[karluk]

I get the extremely strong impression that the code you have written is forcing Oracle to try to execute sql on ECGM which is in turn doing a remote call to ECGM. This won't work because the db link to ECGM is defined on ECSM, not ECGM.

Please note that your ORA-02019 is accompanied by an ORA-02063, indicating that the error actually happened on ECGM.

It appears that the statement that is really causing the problem is your update. It looks as if this statement is being executed on ECGM, and hence the @ECGM reference in the update is unnecessary.

If my analysis is correct, you can fix your error by removing the @ECGM from your update statement.

 

[mortonsa]

Hi Karluk,
Thanks for responding. I tried rewritting my code to follow your suggestion...

for i in c_phone loop
update TATTORNEYCONTACT set
PHONENUMBER1 = i.phonenumber,
PHONEEXT1 = i.phoneext,
PHONETYPE1 = i.phonetypepid
where ATTORNEYCONTACTIDY = i.CONTACTATTORNEYIDY;
end loop;

and when I did this I got the following error:
PLS-00201:identifier 'TATTORNEYCONTACT' must be declared.

The table tAttorneyContact only exists on ECGM so if I don't put the link after the table name, it will fail. I tried another approach and rewrote the code as follows...

cursor c_phone is
select contactattorneyidy, phonenumber,phoneext, phonetypepid, primaryphoneind
from tcontattphone
where primaryphoneind = 'Y';

begin
for i in c_phone loop
update tattorneycontact@ecgm set
phonenumber1 = REPLACECHARS(i.phonenumber),
phoneext1 = i.phoneext,
phonetype1 = i.phonetypepid
where attorneycontactidy = i.contactattorneyidy;
end loop;

And still I got the original error message (ORA-02019 followed by ORA-02063). Did I rewrite this correctly? Can you think of any other reason this might be happening?

Thanks in advance,
Steph

 

[karluk]

The obvious suggestion is that the db link is private and hence not visible to the owner of the procedure, Erwin in your case. However, you indicate that you have done this numerous times, so that seems unlikely to be the problem.

 

[mortonsa]

I just queried all_db_links and this was the outcome...

OWNER = PUBLIC
DB_LINK = ECGM
USERNAME = ECC
HOST = ECGM
CREATED = 04/04/2002

So, yes you are correct that it is a public DB link. I just don't understand how all of the other procedures which are written the same way don't have this problem.

Here is a sample of one of the other procedures that is working correctly. Do you see any differences that I am missing?

cursor c_email is
select ce.contactemployeridy, ce.emailaddress, ec.employercontactidy
from tcontempemail ce, temployercontact@ecgm ec
where ce.contactemployeridy = ec.employercontactidy;

begin
for i in c_email loop
update temployercontact@ecgm
set emailaddress1 = i.emailaddress
where employercontactidy = i.contactemployeridy;
commit;
end loop;

 

[mortonsa]

Has anybody else out there encountered this problem?