Facing error Cannot access rows from a non-nested table item when using varrays in plsql stored proc-CodePudding

I am a beginner in plsql and I am trying to write a procedure which counts the no of records present in a table and puts the result in another table. Now there are multiple tables I want to do this for so I put those in a varray but I am facing the above mentioned error

Create Or Replace Procedure count
AS
type arr IS VARRAY(5) OF VARCHAR2(30);
tables arr :=arr('tb1','tb2','tb3','tb4','tb5');
cnt NUMBER;
BEGIN
   FOR i in 1 .. tables.COUNT
   LOOP
    select Count(*) INTO cnt from tables(i);
    insert into tb6 VALUES(tables(i),cnt);
    END LOOP
END;

CodePudding user response：

You'll need dynamic SQL for that.

Here's how: a few sample tables (based on Scott's emp table), and tb6 which will contain the result:

SQL> create table tb6 (val varchar2(10), cnt number);

Table created.

SQL> create table tb1 as select * from emp where deptno = 10;

Table created.

SQL> create table tb2 as select * from emp where deptno = 20;

Table created.

SQL> create table tb3 as select * from emp where deptno = 30;

Table created.

Which result do I expect?

SQL> select deptno, count(*) from emp group by deptno order by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        20          5
        30          6

Procedure:

SQL> create or replace procedure p_count as
  2    type   arr is varray(5) of varchar2(30);
  3    tables arr := arr('tb1', 'tb2', 'tb3');
  4    cnt    number;
  5  begin
  6     for i in 1 .. tables.count loop
  7       execute immediate 'select count(*) from ' || tables(i) into cnt;
  8       insert into tb6 values(tables(i), cnt);
  9     end loop;
 10  end;
 11  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec p_count;

PL/SQL procedure successfully completed.

SQL> select * from tb6;

VAL               CNT
---------- ----------
tb1                 3
tb2                 5
tb3                 6

SQL>

As you can see, tb6 contents matches expected values.