can not insert through dblink in procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: can not insert through dblink in procedure

  1. #1
    Join Date
    Sep 2005
    Posts
    21

    Exclamation 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
    Share on Google+

  2. #2
    Join Date
    Sep 2005
    Posts
    21

    Exclamation

    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
    Share on Google+

  3. #3
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    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
    Able was I ere I saw Elba
    Share on Google+

  4. #4
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    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.
    Share on Google+

  5. #5
    Join Date
    Sep 2005
    Posts
    21
    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
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width