How to use a variable from a cursor in the select statement of another cursor in pl/sql

Question

I want to run a query, get the results and then iterate through the results of that query with another select statement using the values of the first statement in my 2nd statement (cursor).

I have 40 users in my db. All the users have the same db schema structure. I want to get the username via :

SELECT  distinct username 
   from all_users

then use the user name to run a query like this:

Select lastname, firstname, email, email2 from username.member.

My results set will return multiple rows so I need a row type as well.

I have tried many different pl/sql combinations:

DECLARE
   CURSOR client_cur IS
   SELECT  distinct username 
   from all_users 
   where length(username) = 3;
   -- client cursor 
   CURSOR emails_cur (cli all_users.username%TYPE) IS
   SELECT id, name 
     FROM cli.org;
BEGIN
   FOR client IN client_cur LOOP
      dbms_output.put_line('Client is '|| client.username);
      FOR email_rec in client_cur(client.username) LOOP
         dbms_output.put_line('Org id is ' ||email_rec.id || ' org nam ' || email_rec.name);
      END LOOP;
  END LOOP;
END;
/

and

DECLARE
  CURSOR c1 IS
    SELECT  distinct username from all_users where length(username) = 3;
    client c1%rowtype;
   cursor c2 is Select id, name, allow_digest_flg from c1.username.org;
 digest c2%rowtype;
-- declare record variable that represents a row fetched from the employees table
--   employee_rec c1%ROWTYPE; 
 BEGIN
-- open the explicit cursor and use it to fetch data into employee_rec
    OPEN c1;
  loop
     FETCH c1 INTO client; 
   open c2; 
   loop
    fetch c2 into digest;
      DBMS_OUTPUT.PUT_LINE('digest is : ' || c2.id || ' and name is ' || c2.name || ' flg is ' || c2.allow_digest_flg );
   end loop;
  end loop;
 END;
/

AND MANY VARIATIONS OF THESE.

Can someone help me. THANKS

I've never used cursor expressions, so I'm not sure if they help, but maybe look here: download.oracle.com/docs/cd/B10501_01/server.920/a96540/… Or have nested loops: java2s.com/Code/Oracle/Cursor/Cursorwithinacursor.htm — FrustratedWithFormsDesigner
– FrustratedWithFormsDesigner, Commented Nov 1, 2010 at 21:09
If the 40 tables with the same structure all need to be read together by a single query then why isn't there just ONE table with an additional username column? — Tony Andrews
– Tony Andrews, Commented Nov 2, 2010 at 9:59

Alex Poole · Accepted Answer · 2010-11-03 16:42:34Z

7

You need to use dynamic SQL to achieve this; something like:

DECLARE
    TYPE cur_type IS REF CURSOR;

    CURSOR client_cur IS
        SELECT DISTING username
        FROM all_users
        WHERE length(username) = 3;

    emails_cur cur_type;
    l_cur_string VARCHAR2(128);
    l_email_id <type>;
    l_name <type>;
BEGIN
    FOR client IN client_cur LOOP
        dbms_output.put_line('Client is '|| client.username);
        l_cur_string := 'SELECT id, name FROM '
            || client.username || '.org';
        OPEN emails_cur FOR l_cur_string;
        LOOP
            FETCH emails_cur INTO l_email_id, l_name;
            EXIT WHEN emails_cur%NOTFOUND;
            dbms_output.put_line('Org id is ' || l_email_id
                || ' org name ' || l_name);
        END LOOP;
        CLOSE emails_cur;
    END LOOP;
END;
/

Edited to correct two errors, and to add links to 10g documentation for OPEN-FOR and an example. Edited to make the inner cursor query a string variable.

edited Nov 3, 2010 at 16:42

answered Nov 1, 2010 at 22:15

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

VT_programmerWanneBe Over a year ago

Alex thank you so much..this code is very close to what I think I need.

VT_programmerWanneBe Over a year ago

Alex thank you so much..this code is very close to what I think I need. However when I ran it I got an error, that I really havent been able to solve. I am sure it is a syntax issue, just dont know where to find the correct syntax for the concept you are showing me. I fixed one typo. FETCH emails INTO l_email_id, l_name; error: OPEN CURSOR emails FOR 'SELECT id, name FROM ' * ERROR at line 11: ORA-06550: line 11, column 21: PLS-00103: Encountered the symbol "EMAILS" when expecting one of the following: . ( % ; for The symbol "." was substituted for "EMAILS" to continue.

Alex Poole Over a year ago

@VT: yes, made (at least) two mistakes changing an example to fit your original data, the incorrect variable name you spotted and the cause of your second error - the CURSOR keyword should not have been there for the OPEN ... FOR syntax. I've updated the answer.

VT_programmerWanneBe Over a year ago

Thanks again for your time. I got this error: OPEN emails FOR 'SELECT id, name FROM ' * ERROR at line 11: ORA-06550: line 11, column 14: PLS-00201: identifier 'EMAILS' must be declared ORA-06550: line 11, column 9: PL/SQL: Statement ignored ORA-06550: line 14, column 19: PLS-00201: identifier 'EMAILS' must be declared I tried declaring CURSOR emails; got an error and declaring CURSOR emails row%TYPE; and got an error.. any other clues or thoughts you have would be greatly appreciated.

Alex Poole Over a year ago

@zygimantus - you can declare a variable and select into it instead of using a cursor loop.

|

Justin Cave · Accepted Answer · 2010-11-01 21:28:39Z

You can certainly do something like

SQL> ed
Wrote file afiedt.buf

  1  begin
  2    for d in (select * from dept)
  3    loop
  4      for e in (select * from emp where deptno=d.deptno)
  5      loop
  6        dbms_output.put_line( 'Employee ' || e.ename ||
  7                              ' in department ' || d.dname );
  8      end loop;
  9    end loop;
 10* end;
SQL> /
Employee CLARK in department ACCOUNTING
Employee KING in department ACCOUNTING
Employee MILLER in department ACCOUNTING
Employee smith in department RESEARCH
Employee JONES in department RESEARCH
Employee SCOTT in department RESEARCH
Employee ADAMS in department RESEARCH
Employee FORD in department RESEARCH
Employee ALLEN in department SALES
Employee WARD in department SALES
Employee MARTIN in department SALES
Employee BLAKE in department SALES
Employee TURNER in department SALES
Employee JAMES in department SALES

PL/SQL procedure successfully completed.

Or something equivalent using explicit cursors.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor dept_cur
  3        is select *
  4             from dept;
  5    d dept_cur%rowtype;
  6    cursor emp_cur( p_deptno IN dept.deptno%type )
  7        is select *
  8             from emp
  9            where deptno = p_deptno;
 10    e emp_cur%rowtype;
 11  begin
 12    open dept_cur;
 13    loop
 14      fetch dept_cur into d;
 15      exit when dept_cur%notfound;
 16      open emp_cur( d.deptno );
 17      loop
 18        fetch emp_cur into e;
 19        exit when emp_cur%notfound;
 20        dbms_output.put_line( 'Employee ' || e.ename ||
 21                              ' in department ' || d.dname );
 22      end loop;
 23      close emp_cur;
 24    end loop;
 25    close dept_cur;
 26* end;
 27  /
Employee CLARK in department ACCOUNTING
Employee KING in department ACCOUNTING
Employee MILLER in department ACCOUNTING
Employee smith in department RESEARCH
Employee JONES in department RESEARCH
Employee SCOTT in department RESEARCH
Employee ADAMS in department RESEARCH
Employee FORD in department RESEARCH
Employee ALLEN in department SALES
Employee WARD in department SALES
Employee MARTIN in department SALES
Employee BLAKE in department SALES
Employee TURNER in department SALES
Employee JAMES in department SALES

PL/SQL procedure successfully completed.

However, if you find yourself using nested cursor FOR loops, it is almost always more efficient to let the database join the two results for you. After all, relational databases are really, really good at joining. I'm guessing here at what your tables look like and how they relate based on the code you posted but something along the lines of

FOR x IN (SELECT *
            FROM all_users,
                 org
           WHERE length(all_users.username) = 3
             AND all_users.username = org.username )
LOOP
  <<do something>>
END LOOP;

Thanks, 'all_users' is the sysdba view/table of my Oracle DB. I want to get the name of the users then append that to access each (users') own schema cause all the users/clients have the same schema structure. so I want <username>.<tablename>.<fieldname> victor.member.address1
@JustinCave: I was almost about to complicate my code with Ref cursors and then suddenly realized that the two queries can be joined and the result set can be produced using the select itself after reading your post. Thanks for the enlightenment. ;)

René Nyffenegger · Accepted Answer · 2013-04-29 11:06:19Z

Use alter session set current_schema = <username>, in your case as an execute immediate.

See Oracle's documentation for further information.

In your case, that would probably boil down to (untested)

DECLARE

   CURSOR client_cur IS
     SELECT  distinct username 
       from all_users 
      where length(username) = 3;

   -- client cursor 
   CURSOR emails_cur IS
   SELECT id, name 
     FROM org;

BEGIN

   FOR client IN client_cur LOOP

   -- ****
      execute immediate 
     'alter session set current_schema = ' || client.username;
   -- ****

      FOR email_rec in client_cur LOOP

         dbms_output.put_line(
             'Org id is ' || email_rec.id || 
             ' org nam '  || email_rec.name);

      END LOOP;

  END LOOP;
END;
/

Collectives™ on Stack Overflow

How to use a variable from a cursor in the select statement of another cursor in pl/sql

3 Answers 3

10 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related