can not insert through dblink in procedure

I have created one procedure which is selecting,inserting and updating data to another database on same server using dblink. it select and update data successfully but when it is going to insert using dblink it gives following error.

ORA-02019: connection description FOR remote DATABASE NOT FOUND
ORA-02063: preceding line FROM PAS

But when i executed same insert statement on sql prompt it is exeuted successfully.

insert into tab1@dblink values('blah','blah','blah');

I have dropped all my dblink and recreate it again i have given priveleges externally means insert any table,select any table,update any table to username.

but still i have recieved same error. Please guide me.

Thanks in advance
dhaval

1. select * from global_name
PAS and RATEPAS

I have created two database on same server.
named pas and ratepas.

I can not send procedure's code. but actual logic of procedure is selecting a record from temporary table and check whether it is a valid record on the basis of time_stamp and it is inserting a record to base table and update time_stamp to other two tables. and it is giving error only when

SELECT * FROM dba_db_links;

OWNER
----------------------
DB_LINK
----------------------
USERNAME
----------------------
HOST
----------------------
CREATED
---------
PUBLIC
PAS
PASUSER

OWNER
----------------------
DB_LINK
----------------------
USERNAME
----------------------
HOST
----------------------
CREATED
---------
pas125
13-OCT-05

It is possible.

1.Create (private) synonym in your schema for the db_link.
2.Make sure u have appropriate right on object(s) in db_link database.
3.Make sure u have appropriate right on object(s) in db_link database.!!!

SAP insert into a remote Oracle db. I then run update,select procedures on that Oracle db from my db using a job(run procedure) to capture and release SAP orders back to the db_link databse. It is complicated but the most problems I got was the appropraite rights on the other Oracle DB_link dababase.

good luck ;)

inserting thru dblink inside a procedure works for me, 8i & 9i.

looks like your local db (with the db link defined) is RATEPAS, remote is PAS?

The error you're getting is the same error you'd get if you specify a non-existent db link; it's interesting that you get the message that the error came from database PAS. Doesn't look like a privs issue on remote (PAS), you'd get a different message (like table doesn't exist).

There's nothing complicated about the insert part of your proc? It just inserts data across a link?

Should have nothing to do with your problem, but are you closing the db links at the end of your proc? Just curious.

I have dropped tables,dblinks,procedures and revoke all privilleges from my user and create all again and test it.Now it is inserting successfully. It is fixed by trial and error. God knows what is real problem?

Thanx for your help and views

Dhaval