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!

ORA-00904 and PLS-00364 issue reg

Discussion in 'SQL PL/SQL' started by laxman, Feb 24, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    i have this following table

    SQL> select * from cur1;

    TID TNAME
    ---------- --------------------
    1 sonarcheck
    3 sonardev
    4 sonarbuild

    SQL> select * from cur2;

    PID PNAME
    ---------- --------------------
    1 sonarcheck
    2 sonartest
    5 sonarprod
    when i am executing the following block i am facing some error

    SQL> declare
    cursor test1 is select tid from cur1;
    cursor test2 is select pid from cur2 where pid<>test1_rec.tid;
    begin
    for test1_rec in test1 loop
    for test2_rec in test2 loop
    dbms_output.put_line(test2_rec.pid);
    end loop;
    end loop;
    end; 2 3 4 5 6 7 8 9 10
    11 /
    cursor test2 is select pid from cur2 where pid<>test1_rec.tid;
    *
    ERROR at line 3:
    ORA-06550: line 3, column 49:
    PL/SQL: ORA-00904: "TEST1_REC"."TID": invalid identifier
    ORA-06550: line 3, column 17:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 7, column 41:
    PLS-00364: loop index variable 'TEST2_REC' use is invalid
    ORA-06550: line 7, column 20:
    PL/SQL: Statement ignored

    Kindly need ur suggestion

    Thanks n regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You should be writing this instead:

    Code (SQL):
    DECLARE
    cursor test1 IS SELECT tid FROM cur1;
    cursor test2(vtid IN NUMBER) IS SELECT pid FROM cur2 WHERE pid<>vtid;
    BEGIN
    FOR test1_rec IN test1 loop
    FOR test2_rec IN test2(test1_rec.tid) loop
    dbms_output.put_line(test2_rec.pid);
    END loop;
    END loop;
    END;
    /
     
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thanks a lot sir ,the logic is working but i just want only the output which satisfy the condition i.e

    SQL> select * from cur1;

    TID TNAME
    ---------- --------------------
    1 sonarcheck
    3 sonardev
    4 sonarbuild

    SQL> select * from cur2;

    PID PNAME
    ---------- --------------------
    1 sonarcheck
    2 sonartest
    5 sonarprod

    SQL> declare
    cursor test1 is select tid from cur1;
    cursor test2(vtid in number) is select pid from cur2 where pid<>vtid;
    begin
    for test1_rec in test1 loop
    for test2_rec in test2(test1_rec.tid) loop
    dbms_output.put_line(test2_rec.pid);
    end loop;
    end loop;
    end; 2 3 4 5 6 7 8 9 10
    11 /
    2
    5
    1
    2 ---- HERE I SUPPOSE TO GET ONLY 2 & 5 AS OUTPUT BUT IT IS COMING AS SHOWN HERE.
    5
    1
    2
    5

    PL/SQL procedure successfully completed.

    REGARDS
    Laxman
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Explain your 'logic'; look at the available values in both tables. Let's walk through this code with the given values:

    Code (SQL):
    SELECT pid FROM cur2 WHERE pid<>vtid;
    When vtid = 1 then the query returns 2 and 5 because both are not equal to 1.
    When vtid = 3 the query returns all values (1,2,5) because all are not equal to 3.
    When vtid = 4 the query again returns all values (1.2.5) because all are not equal to 4.

    Thus the query is working correctly. What you want, apparently, is the output from the following query:

    Code (SQL):
    SELECT pid FROM cur2 WHERE pid NOT IN (SELECT tid FROM cur1);
    The above query will return only 2 and 5 since they are the only values not found in the list (1,3,4). The query you've coded compares all pid values in cur2 to the ONE tid value passed from the outer cursor, thus the comparisons are made on a single value from cur1, not the entire set of values in the table.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No .. You are selectin the records One by One.

    When first curcor select TId = 1 , then Second cursor becomes select pid from cur2 where pid<>1
    It gives Result 2 & 5
    When first curcor select TId = 3 , then Second cursor becomes select pid from cur2 where pid<>3
    It gives Result 1, 2 & 5
    When first curcor select TId = 4 , then Second cursor becomes select pid from cur2 where pid<>4
    It gives Result 1, 2 & 5

    If you want to get your desired input then the cursor could be ,

    Code (SQL):

    cursor test1 IS
    SELECT pid
      FROM cur2
     WHERE pid NOT IN (SELECT tid  
                         FROM cur1)
     
    Or you can use Not exists clause also.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Ooooops
    Zargon, I am late. . .
     
  7. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    ya now its look fine with this approach,thanks a lot sir.nested cursor logic was not the correct approach to achieve this.

    Regards
    Laxman