Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    5

    Question Unanswered: Newbie PL/SQL Nested Cursor Problem

    Hi,
    I'm having problem with a nested cursor.
    I have a table - TMaster which contains two columns (table_name - holds list of master tables, column_code - a code which is a column in the corresponding master table)
    eg -
    Table_Name|Column_Code
    txnm |cash01
    debitm | dbc09

    I put a cursor on the TMaster table to get the master tables one by one -

    Cursor c1 is select table_name,column_code from tmaster;

    i then open the cursor select the value in to two variables vc1,vc2
    and do some of the processing as required.

    now i need to open a second cursor -

    wherein i require to put a cursor on the table - txnm and get distinct values of the column eg - cash01.

    so within the first cursor i put a

    declare
    cursor c2 is select distinct(vc2) from vc1;

    this gives me an error saying vc1 not found ?

    i wanted the cursor to be executing internally something like this -
    cursor c2 is select distinct(cash01) from txnm - for the first record of cursor c1 and so on....

    any way i can do this ? what am i doing wrong ? any help would be greatly appreciated.

    cheers,
    spike

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    How are you opening the first cursor?

    Your declare statement should look somehting like this hopefully:
    PHP Code:
    DECLARE
    vc1   varchar2(30);
    vc2   varchar2(30);
    cursor c1 is
      select table_name
    column_code
        from tmaster
    ;
    cursor c2 is
      select distinct vc2
        from vc1
    ;
    BEGIN
      open c1
    ;
        
    fetch c1 into vc1vc2;
          while 
    c1%found loop
             
    for v_data in c2 loop
              
    --(whatever you are doing)
             
    end loop;
        
    fetch c1 into vc1vc2;
       
    end loop;
      
    close c1;
    end;

    try it without the second cursor like this:
    PHP Code:
    DECLARE
    vc1   varchar2(30);
    vc2   varchar2(30);
    cursor c1 is
      select table_name
    column_code
        from tmaster
    ;

    BEGIN
      open c1
    ;
        
    fetch c1 into vc1vc2;
          while 
    c1%found loop

            select distinct vc2 from vc1
    ;


        
    fetch c1 into vc1vc2;
       
    end loop;
      
    close c1;
    end;


    I did this once and I used a REFCURSOR.
    I can supply the example if you want.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2003
    Posts
    5

    Re: Newbie PL/SQL Nested Cursor Problem

    Can you please send me the example of the refcursor please ?
    thanks,
    Spike

  4. #4
    Join Date
    May 2003
    Posts
    5

    Re: Newbie PL/SQL Nested Cursor Problem

    I need to have the second cursor as i have certain processing to be done on the the second cursor.
    eg. cursor c2 is select distinct(vc2) from vc1;
    where i will be doing processing on distinct values of (cash01) in the txnm table.

    cheers,
    Spike

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    well, both examples I provided used distinct on the column.

    anyways, here is the REFCURSOR example. I basically needed to select a list from a table depending on the column_name (crazy frickin legacy databases).

    so one cursor gets the column-names, and then the refcursor uses the column_name in the where clause.
    PHP Code:

    -- this cursor gets the column-names I needed
    cursor c1 is
      select unique
    (substr(column_name,1,9))
        
    from cols
          where table_name 
    'SWLNHDR'
            
    and column_name like 'IND_%';

    -- 
    then you specify your refcursor you will use with the variables below it
    TYPE ref_cur_1 IS REF CURSOR
    ;
    ref_cur        ref_cur_1;
    v_swlin_no        varchar2(6);

    -- 
    then in your BEGIN area call your refcursor
    OPEN ref_cur 
    FOR
    'SELECT a.swlin_no FROM swlnhdr a WHERE  '|| v_column ||' = ''X''';

    FETCH ref_cur into
    v_swlin_no
    ;

    WHILE 
    ref_cur%found LOOP 
    Notice you need to keep your REFCURSOR statement on one line and inside quotes until you use your variable, then use your variable outside the quotes.

    Your code would look something like:
    PHP Code:
    DECLARE
    vc1   varchar2(30);
    vc2   varchar2(30);
    cursor c1 is
      select table_name
    column_code
        from tmaster
    ;
    type ref_cur_1 is REF CURSOR;
      
    ref_cur ref_cur_1;
      
    vDISTINCT_DATA varchar2(30);

    BEGIN
      open c1
    ;
        
    fetch c1 into vc1vc2;
          while 
    c1%found loop
             
              OPEN ref_cur 
    FOR
              
    'select distinct '|| vc2 ||' from '|| vc1;
                 
    FETCH ref_cur into
                   vDISTINCT_DATA 
    ;
                   WHILE 
    ref_cur%found LOOP
                   
                 FETCH ref_cur into
                   vDISTINCT_DATA 
    ;
                    
    end loop;
                
    CLOSE ref_cur;

        
    fetch c1 into vc1vc2;
       
    end loop;
      
    close c1;
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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