Procedure and Cursor - Simple example in PLSQL

Following is an simple example of Procedure and Cursors.

Here we fetch all the data in a cursor, loop through it and insert the data into the database.

Note: the following line is very important else the cursor will move to an infinite loop -> EXIT WHEN CURSOR_TB_DATA%NOTFOUND;

SchemaName is an argument to the procedure and is passed when calling the procedure.

Example ->

CREATE OR REPLACE
PROCEDURE FETCH_TABLES_ROWS(SchemaName IN VARCHAR2) AS
 
    CURSOR CURSOR_TB_DATA IS
    SELECT TABLE_NAME,
    TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT COUNT(*) C FROM '
    ||OWNER ||'.' ||TABLE_NAME)),'/ROWSET/ROW/C')) TABLE_ROWS
    FROM ALL_TABLES
    WHERE OWNER=SchemaName;  
   
    TABLE_DATA CURSOR_TB_DATA%ROWTYPE;
BEGIN    
    OPEN CURSOR_TB_DATA;
    LOOP
      FETCH CURSOR_TB_DATA INTO TABLE_DATA;
      EXIT WHEN CURSOR_TB_DATA%NOTFOUND;
      INSERT INTO GTB_FETCH_TABLE_ROWS VALUES (TABLE_DATA.TABLE_NAME,TABLE_DATA.TABLE_ROWS,SchemaName,SYSDATE);
    END LOOP;
    CLOSE CURSOR_TB_DATA;
    DBMS_OUTPUT.PUT_LINE('End of Procedure');
   
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No Data Found');
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error!');  
END;

Calling : The above procedure is called by executing the following statement->

EXECUTE FETCH_TABLES_ROWS('CIBC_SCRIPT_TEST');