1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

DBLINK works in SQL but not in PLSQL

Discussion in 'SQL PL/SQL' started by vpatil1983, May 15, 2014.

  1. vpatil1983

    vpatil1983 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    75
    Hi All,

    I have 2 databases db1 with 2 users (user1 (DBA) and user2) and db2 with one user user3.
    In user1 table tab_a is created.user 1 created user2 and granted select on user1.tab_1.
    User2 has DEFAULT ALL Role.


    Now in db2 (user3), have created dblink dev.com as:

    Code (SQL):
    CREATE DATABASE link "DEV.COM"
    CONNECT TO user2
    IDENTIFIED BY "user1234"
    USING 'DEV.COM';
    When I connect to user3 and try to access db1's user1s table tab_a.

    Code (SQL):
    SELECT COUNT(*) FROM [email]tab1@DEV.COM[/email];

    I GET error  "PLS00201-Identifier TAB_A must be declared ".
    Since there were no synonyms granted to user2 .I have created on log on trigger in user2 and altering the schema to point to user1.

    Then I ran below query which was successfull.

    Code (SQL):
    "select count(*) from [email]tab_a@DEV.COM[/email]"
    It works.

    If the same query is executed using plsql :

    Code (SQL):
    DECLARE
    v_count NUMBER;
    BEGIN
     SELECT COUNT(*)
     INTO v_count
     FROM [email]tab1@DEV.COM[/email];
    END;

    I am getting error  "PLS00201-Identifier TAB_A must be declared ".
    If I prefix table with schema name , it works.

    Code (SQL):
    DECLARE
    v_count NUMBER;
    BEGIN
     SELECT COUNT(*)
     INTO v_count
     FROM [email]user1.tab1@DEV.COM[/email];
    END;
    I doubt its because of synoyms.However I cant use this prefix solution as an alternate because I will have to make changes at so many places.
    Please advise any other alternate.

    Thanks in Advance,
    Vijay
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It is because of synonyms since when you prefix the table name with the owner you get access.