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!

DB Link Not working in PLSQL but Works in SQL

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 for user1's table.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]tab1@DEV.COM[/email]"
    If the same query is executed using plsql :

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

    I am getting error  "PLS00201-Identifier TAB_A must be declared ".
    Please advise.

    Thanks,
    Vijay
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It seems that the working query is using a table named TAB1 and the failing statement is using TAB_A. Which table name is correct?