pl/sql loop through the table and pass the each entry of the row to a stored procedure

Question

I have a table with three columns (table_name, column_name, data_type), I am trying to pass values of these three columns as parameter to a stored procedure in a loop.

Now, I have a developed very basic code to check if the looping works fine.

DECLARE
 
  cursor c1 is 
    select table_name, column_name, data_type from table_list;

BEGIN
  FOR i IN c1 LOOP
    DBMS_OUTPUT.PUT_LINE(i.table_name||' ' ||i.column_name ||''||i.data_type);
   
    
  END LOOP;
END;

Update on the issue: loop is working fine, but when I call the procedure as shown in below code it gives me an error

DECLARE
 
  cursor c1 is select table_name, column_name,data_type from table_list;

BEGIN
  FOR i IN c1 LOOP

    DBMS_OUTPUT.PUT_LINE(i.table_name||' ' ||i.column_name ||' '||i.data_type);
    UPD_PII_DATA(i.table_name,i.column_name,i.data_type);
    DBMS_OUTPUT.PUT_LINE(i.table_name|| ' '|| ' and column '|| i.column_name|| ' completed test');

  END LOOP;
END;

Error at line 1 ORA-00942: table or view does not exist ORA-06512: at "MP_ODI.UPD_PII_DATA", line 13 ORA-06512: at line 9 ORA-06512: at line 9

could please help me on this error

Littlefoot Littlefoot · Accepted Answer · 2021-08-11T10:12:52

The way you put it, you'd just call the procedure and pass values retrieved by the cursor. With a little bit less typing:

BEGIN
   FOR i IN (SELECT table_name, column_name, data_type FROM table_list)
   LOOP
      DBMS_OUTPUT.PUT_LINE (i.table_name || ' ' || i.column_name || '' || i.data_type);

      -- call the procedure, pass values
      my_procedure (i.table_name, i.column_name, i.data_type);
   END LOOP;
END;

pl/sql loop through the table and pass the each entry of the row to a stored procedure

1 Answers