I was hoping I could get some pointers on what I am doing wrong with converting an anonymous PL/SQL block to a Stored Procedure.
The block is below.
This block is working as-is.
1. For Loop to obtain a list of tables from all_tables
2. EXECUTE IMMEDIATE a SQL string that COUNTS the current rows for each of the tables.
3. The table_name and the count of rows in that table are used to update a stats table.
I know I can use the stats from the database, however, I do not have privileges to update the stats and retrieve the "num_rows" from all_tables.
I need a fresh count.
Even though the anonymous block runs with no issue, an error (R index is invalid) is thrown when the Stored procedure is compiled.
6/15 PL/SQL: SQL Statement ignored
7/24 PL/SQL: ORA-00942: table or view does not exist
10/9 PL/SQL: Statement ignored
10/54 PLS-00364: loop index variable 'R' use is invalid
12/9 PL/SQL: SQL Statement ignored
15/34 PL/SQL: ORA-00904: "R"."TABLE_NAME": invalid identifier
15/34 PLS-00364: loop index variable 'R' use is invalid
/* Start */
SET SERVEROUTPUT ON;
DECLARE
v_count integer;
BEGIN
FOR r in (SELECT owner, table_name
FROM sys.all_tables
WHERE owner = 'DBSNMP')
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || r.owner || '.' || r.table_name INTO v_count;
UPDATE test_schema.stats_table
set ref_record_cnt = v_count
WHERE ref_schema_name = r.owner
AND table_name = r.table_name;
commit;
end loop;
end;
/
/* End */
/* Start of Stored procedure Creation */
CREATE OR REPLACE PROCEDURE test_schema.sp_cnt_3
IS
v_count PLS_INTEGER := 0;
BEGIN
FOR r in (SELECT owner, table_name
FROM sys.dba_tables
WHERE owner = 'DBSNMP')
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || r.owner || '.' || r.table_name INTO v_count;
UPDATE test_schema.stats_table
set ref_record_cnt = v_count
WHERE ref_schema_name = r.owner
AND table_name = r.table_name;
commit;
end loop;
end sp_cnt_3;
/
/* End of Stored procedure. */