Code Snippet: PL/SQL Cursor inside Procedure or Anonymous Block

CREATE OR REPLACE PROCEDURE LOAD_DATA AS
/* replace the above line with DECLARE to have anonymous block */

  A_VAR VARCHAR2(30) := 'A Variable';

  CURSOR CUR_ST IS
  SELECT ST_ID, DESCRIPTION, ST_TYPE from SAMPLE_TABLE;
  /* SQL Query for defining cursor */

BEGIN

FOR CUR IN CUR_ST LOOP

  /* Looping... add main logic here */
  IF CUR.ST_TYPE = '1' THEN
    INSERT INTO OTHER_TABLE (OT_ID, DESCRIPTION)
    VALUES(CUR.ST_ID, CUR.DESCRIPTION);
  ELSE
    A_VAR := 'SKIPPING';
  END;

END LOOP;

COMMIT;

EXCEPTION
    /* Do error handling here */
    /* Remove the EXCEPTION block for exceptions
       to show up outside of procedure */
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found!');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error occurred!');
    ROLLBACK;

END LOAD_DATA;

DBMS_OUTPUT.PUT_LINE can be used to write to console. It works if server output is on:
set serveroutput on size 30000;

Leave a Reply

Your email address will not be published. Required fields are marked *