Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Unanswered: How to create nested select in PL/SQL

    Hi all,

    I need to create a cursor that has a nested table in PL/SQL. I have 2 tables (A and B) where A.ref = B.id. I need to declare a cursor that will give me the following kinda output:
    Code:
    A.id       A.ref        A.col     BNested
                                          B.id         B.col
    ------------------------------------------
    1          1             xyz    |  1            abc
                                       |  1            ghc
                                       |  1            jkh
                                       -----------------
    2          3             fys     |  3            asdf
                                       |  3             qwerqw
                                       --------------------
    I think it should be something like:
    Code:
    	cursor mycursor is
    		select
    			A.id,
    			A.ref,
    			A.col,
    			(
    				select B.id, B.col from B where A.ref = B.id				
    ) BNested
    		from
    			A;
    But when I run this, it gives me something about returning more than one value.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    What you need is CURSOR.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Thanks RBARAER.
    That did the trick.

    Now I would like to bind the columns into local variables like I usually do. Not sure how to proceed though?

    Code:
    declare
    cursor mycursor is
       select
       A.id,
       A.ref,
       A.col,
       cursor(
          select B.id, B.col from B where A.ref = B.id				
       ) BNested
       from
       A;
    
    l_id number;
    l_ref number;
    l_col varchar2(1024);
    l_cursor ?????
    begin
        open mycursor;
        fetch mycursor into l_id, l_ref, l_col, l_cursor;
        close mycursor;
    end;
    What do I need to do to fetch the value into a local variable?

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    This should work :
    l_cursor SYS_REFCURSOR;
    Code:
    rbaraer@Ora10g> DECLARE
        rcMainCursor SYS_REFCURSOR;
        l_cursor SYS_REFCURSOR;
      2    3    4      iTmp NUMBER;
      5  BEGIN
      6      OPEN rcMainCursor FOR
      7          SELECT CURSOR(SELECT 1 FROM DUAL UNION SELECT 2 FROM DUAL)
      8          FROM DUAL;
      9
     10      LOOP
     11          FETCH rcMainCursor INTO l_cursor;
     12          EXIT WHEN rcMainCursor%NOTFOUND;
     13          LOOP
     14              FETCH l_cursor INTO iTmp;
     15              EXIT WHEN l_cursor%NOTFOUND;
     16              DBMS_OUTPUT.PUT_LINE('Result : '||iTmp);
     17          END LOOP;
     18          CLOSE l_cursor;
     19      END LOOP;
     20
     21      CLOSE rcMainCursor;
     22  END;
     23  /
    Result : 1
    Result : 2
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g>
    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You can also use a cursor loop

    Code:
     declare
    cursor mycursor is
       select
       A.id,
       A.ref,
       A.col,
       from
       A;
    
    cursor other(x_ref in varchar2) is
         select B.id, B.col from B where x_ref = B.id;
    begin
        for pnt in mycursor loop
         -- reference the value by using the cursor columns appended with pnt. for example
           for bpnt in other(pnt.ref) loop
               dbms_output.put_line(pnt.id||bpnt.col);
           end loop;
        end loop;
    end;
    Last edited by beilstwh; 01-22-07 at 17:08.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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