dump_semesters.sql

DECLARE
  FNAM  VARCHAR2(50);
  F1    UTL_FILE.FILE_TYPE;
  LOUT  VARCHAR2(255);
  SEML  ROVTERM%ROWTYPE;
  SEMS  VARCHAR2(50);

  CURSOR curSem IS
    SELECT * FROM rovterm WHERE
      rovterm_start_date > TO_DATE('01-MAY-2013', 'DD-MON-YYYY') AND
      rovterm_start_date < TO_DATE('01-JAN-2999', 'DD-MON-YYYY')
      ORDER BY rovterm_start_date;
BEGIN
  DBMS_OUTPUT.ENABLE;

  -- Sets the file name
  FNAM := 'semesters_'||TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')||'.txt';
  DBMS_OUTPUT.put_line(FNAM);

  -- Opens the filehandle F1
  F1 := UTL_FILE.fopen('DAXOUT', FNAM, 'W');

  -- Open the cursor and loop through
  OPEN curSem;
  LOOP
    -- Read one line from the cursor into SEML
    FETCH curSem INTO SEML;

    EXIT WHEN curSem%NOTFOUND;

    -- Put a handful of column into SEMS
    SEMS := SEML.ROVTERM_CODE||','||SEML.ROVTERM_DESC||','||TO_CHAR(SEML.ROVTERM_START_DATE, 'YYYYMMDD');
    DBMS_OUTPUT.put_line(SEMS);

    -- Write out SEMS
    UTL_FILE.put_line(F1, SEMS, TRUE);
  END LOOP;
  CLOSE curSem; -- Close the cursor

  -- Closes the filehandle F1
  UTL_FILE.fclose(F1);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
    RAISE;
END;