andrewb

dump_semesters.sql

Jun 27th, 2014
618
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 1.26 KB | None | 0 0
  1. DECLARE
  2.   FNAM  VARCHAR2(50);
  3.   F1    UTL_FILE.FILE_TYPE;
  4.   LOUT  VARCHAR2(255);
  5.   SEML  ROVTERM%ROWTYPE;
  6.   SEMS  VARCHAR2(50);
  7.  
  8.   CURSOR curSem IS
  9.     SELECT * FROM rovterm WHERE
  10.       rovterm_start_date > TO_DATE('01-MAY-2013', 'DD-MON-YYYY') AND
  11.       rovterm_start_date < TO_DATE('01-JAN-2999', 'DD-MON-YYYY')
  12.       ORDER BY rovterm_start_date;
  13. BEGIN
  14.   DBMS_OUTPUT.ENABLE;
  15.  
  16.   -- Sets the file name
  17.   FNAM := 'semesters_'||TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')||'.txt';
  18.   DBMS_OUTPUT.put_line(FNAM);
  19.  
  20.   -- Opens the filehandle F1
  21.   F1 := UTL_FILE.fopen('DAXOUT', FNAM, 'W');
  22.  
  23.   -- Open the cursor and loop through
  24.   OPEN curSem;
  25.   LOOP
  26.     -- Read one line from the cursor into SEML
  27.     FETCH curSem INTO SEML;
  28.    
  29.     EXIT WHEN curSem%NOTFOUND;
  30.    
  31.     -- Put a handful of column into SEMS
  32.     SEMS := SEML.ROVTERM_CODE||','||SEML.ROVTERM_DESC||','||TO_CHAR(SEML.ROVTERM_START_DATE, 'YYYYMMDD');
  33.     DBMS_OUTPUT.put_line(SEMS);
  34.    
  35.     -- Write out SEMS
  36.     UTL_FILE.put_line(F1, SEMS, TRUE);
  37.   END LOOP;
  38.   CLOSE curSem; -- Close the cursor
  39.  
  40.   -- Closes the filehandle F1
  41.   UTL_FILE.fclose(F1);
  42. EXCEPTION
  43.   WHEN OTHERS THEN
  44.     DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
  45.     RAISE;
  46. END;
Add Comment
Please, Sign In to add comment