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;
