How To Declare A Number Variable Where I Can Save Th Count Of Table In My Loop

I work wirh oracle Database. I have a plsql code where i run a query in a loop for multiple tables. so, table name is a variable in my code. I would like to have another variable (

Solution 1:

There are three things wrong with your dynamic SQL.

1. EXECUTE IMMEDIATE is not a function: the proper syntax is execute immediate '<<query>>' into <<variable>>.
2. An INSERT statement takes a VALUES clause or a SELECT but not both. SELECT would be very wrong in this case. Also note that it's VALUES not VALUE.
3. COLUMN_NAME is a string literal in the dynamic SQL so it needs to be in quotes. But because the SQL statement is itself a string, quotes in dynamic strings need to be escaped so it should be `'''||column_name||'''.

So the corrected version will look something like this

declareCursor C_TABLE  isselecttrim(table_name) as table_name
      from all_tables
     where table_name in ('T1', 'T2', 'T3');

  V_ROWNUM number;

beginfor m in C_TABLE
      loop 
          for i in ( select column_name 
                      from (
                          select c.column_name
                             from all_tab_columns c
                            where c.table_name = m.table_name 
                              and c.owner ='owner1' 
                            )  
                    )       
     loop
         execute immediate 'select count(*) from '|| m.table_name into  V_ROWNUM;
         execute immediate 'insert into MY_table values ( '''|| i.column_name ||''', '|| V_ROWNUM ||')';

        end loop;
    end loop;
end;
/

Dynamic SQL is hard because it turns compilation errors into runtime errors. It is good practice to write the statements first as static SQL. Once you have got the basic syntax right you can convert it into dynamic SQL.

Solution 2:

you can't assign the result of execute immediate to a variable. it is not a function.

but you can do it by using the into_clause e.g.

execute immediate 'select count(*) from '|| m.table_name into V_ROWNUM ;