CODEWITHSUNDEEP
oracle-databaseplsql
mspart1
mspart1

Reputation: 1

pl/sql block code issue with multiple records return

can't make this work... have a multiple tables names which i need to pass into select. Each select will return multiple records. Resultset should be printed to the user on a screen . 

SQL> set serveroutput on;
SQL> DECLARE
    vs_statement VARCHAR2 (1000);
    my_var1      VARCHAR2(100);
    my_var2      VARCHAR2(100);
    CURSOR c1 IS
      SELECT table_name
      FROM   all_tables
      WHERE  table_name LIKE Upper('redit_1%');
BEGIN
    FOR table_rec IN c1 LOOP
        vs_statement :=
        'select a.userinfo, a.userstatus into my_var1, my_var12 from '
        || table_rec.table_name
        || ' A, FILES b  where A.objectid = B.id order by 1';

        EXECUTE IMMEDIATE vs_statement INTO my_var1,
                                            my_var2;

        dbms_output.Put_line(my_var1
                             ||'     '
                             || my_var2);
    END LOOP;
END;
/

Upvotes: 0

Views: 122

Answers (2)

APC
APC

Reputation: 146239

"Each select will return multiple records...Resultset should be printed to the user on a screen"

Open a ref cursor for each generated statement. Loop round that and display the values.

DECLARE
    rc sys_refcursor;
    vs_statement VARCHAR2 (1000);
    my_var1      VARCHAR2(100);
    my_var2      VARCHAR2(100);
    CURSOR c1 IS
      SELECT table_name
      FROM   all_tables
      WHERE  table_name LIKE Upper('redit_1%');
BEGIN
    << tab_loop >>
    FOR table_rec IN c1 LOOP
        vs_statement :=
        'select a.userinfo, a.userstatus from '
        || table_rec.table_name
        || ' A, FILES b  where A.objectid = B.id order by 1';

        open rc for vs_statement;
        dbms_output.put_line('records for '||table_rec.table_name);
        << rec_loop >>
        loop
            fetch rc into my_var1,my_var2;
            exit when rc%notfound;
            dbms_output.Put_line(my_var1
                             ||'     '
                             || my_var2);
        end loop rec_loop;
        close rc;

    END LOOP tab_loop;
END;
/

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142723

This line should look like the following (i.e. remove INTO clause):

vs_statement :=
    'select a.userinfo, a.userstatus from '
    || table_rec.table_name
    || ' A, FILES b  where A.objectid = B.id order by 1';

Also, make sure that this SELECT returns only 1 row, otherwise you'll end up with the TOO-MANY-ROWS error.

Other than that, I guess it should work.

[EDIT, regarding fear of TOO-MANY-ROWS]

Huh, I'd create a whole new PL/SQL BEGIN-END block, containing a loop which would do the job, displaying all rows returned by SELECT statement.

As I don't have your tables, I used HR's. Have a look:

SQL> DECLARE
  2      vs_statement VARCHAR2 (1000);
  3      my_var1      VARCHAR2(100);
  4      my_var2      VARCHAR2(100);
  5      CURSOR c1 IS
  6        SELECT table_name
  7        FROM   all_tables
  8        WHERE  table_name LIKE Upper('%departments%');
  9  BEGIN
 10      FOR table_rec IN c1 LOOP
 11          vs_statement := 'begin for cur_r in ( '
 12                          || Chr(10)
 13                          || 'select distinct a.department_id, a.department_name from '
 14                          || Chr(10)
 15                          || table_rec.table_name
 16                          ||
 17                              ' A, employees b  where A.department_id = B.department_id order by 1) loop'
 18                          || Chr(10)
 19                          || ' dbms_output.put_line(cur_r.department_name); '
 20                          || Chr(10)
 21                          || ' end loop; end;';
 22
 23          EXECUTE IMMEDIATE vs_statement; -- into my_var1, my_var2;
 24      END LOOP;
 25  END;
 26  /
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Accounting

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions