Loop through all tables in database to fetch 2 rows per table-CodePudding

I have a database wherein there are multiple owners and some tables are empty.

Currently I want to sequentially execute the same fetch query against all non-empty tables of a specific owner.

I wrote the PL/SQL below, but got this error "ORA-06550: line 8, column 41: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list 06550. 00000 - "line %s, column %s:\n%s".

DECLARE
  CURSOR details IS 
    SELECT table_name 
    FROM all_tables 
    WHERE owner = 'emp' AND num_rows > 0;
  myvar all_tables.table_name%TYPE;
  rows_num NATURAL := 2;
  sql_stmt VARCHAR2(1000);
BEGIN
    OPEN details;
    LOOP
        FETCH details BULK COLLECT INTO myvar LIMIT rows_num;
    END LOOP;
    CLOSE details;
END;

How can I fix this?

edit: New code with SELECT included:

BEGIN
  OPEN details;
  LOOP
    FETCH details INTO my_var;
    EXIT WHEN myvar%NOTFOUND;
    sql_stmt := 'SELECT * FROM ' || my_var|| ' WHERE ROWNUM <= :rows_num';
    EXECUTE IMMEDIATE sql_stmt;
    DBMS_OUTPUT.PUT_LINE(sql_stmt);
  END LOOP;
  CLOSE table_cur;
END;

No more errors now but I couldn't see anything in the Dbms output.

CodePudding user response：

As P3CONSULTING said - declare TYPE of TABLE of CURSOR%ROWTYPE and then a variable that has type that newly declared TABLE OF type. Also please do not forget to add condition in loop to avoid infinite loop. Please try this code:

DECLARE
  CURSOR details IS 
    SELECT table_name 
    FROM all_tables 
    WHERE owner = 'emp' AND num_rows > 0;
  type t_var is table of details%rowtype;
  myvar t_var;
  rows NATURAL := 2;
BEGIN
    OPEN details;
    LOOP
        FETCH details BULK COLLECT INTO myvar LIMIT rows;
        EXIT WHEN myvar.count = 0;
    END LOOP;
    CLOSE details;
END;

CodePudding user response：

The variable into which you "BULK COLLECT" must be a "TABLE OF..." not a single ROWTYPE.