Opret fil og skriv til denne

        CREATE OR REPLACE PROCEDURE test1 IS

          file_handle UTL_FILE.FILE_TYPE; -- file handle of OS flat file
          col1  NUMBER;                  -- C1 retrieved from testtab table
          retrieved_buffer VARCHAR2(100); -- Line retrieved from flat file

      BEGIN
          -- Open file to write into and obtain its file_handle.
          file_handle :=
            UTL_FILE.FOPEN(\'/u05/home/output/mydir\',\'myfile.txt\',\'W\');

          -- Write a line of text out to the file.
          UTL_FILE.PUT_LINE(file_handle, \'this is line 1 as a test\');

          -- Select the c1 from the testtab table where empno = 7900.
          SELECT c1 INTO col1 FROM testtab
              WHERE c2 = 25;

          -- Using PUTF write text with the col1 argument out to the file.
          UTL_FILE.PUTF (file_handle,
              \'This is the c1 %s when the c2 is %s.\\n\',
              col1,\'25\');

          -- Close the file.
          UTL_FILE.FCLOSE(file_handle);

          -- Open the same file to read from.
          file_handle :=
              UTL_FILE.FOPEN(\'/u05/home/output/mydir\',\'myfile.txt\',\'R\');

          -- Read a line from the file.
          UTL_FILE.GET_LINE (file_handle, retrieved_buffer);

          -- Print fetched line out to the SQL*Plus prompt.
          DBMS_OUTPUT.PUT_LINE(retrieved_buffer);

          -- CLose the file.
          UTL_FILE.FCLOSE(file_handle);

        EXCEPTION

          WHEN NO_DATA_FOUND THEN
              DBMS_OUTPUT.PUT_LINE(\'no_data_found\');
              UTL_FILE.FCLOSE(file_handle);
          WHEN UTL_FILE.INVALID_PATH THEN
              DBMS_OUTPUT.PUT_LINE(\'UTL_FILE.INVALID_PATH\');
              UTL_FILE.FCLOSE(file_handle);
          WHEN UTL_FILE.READ_ERROR THEN
              DBMS_OUTPUT.PUT_LINE(\' UTL_FILE.READ_ERROR\');
              UTL_FILE.FCLOSE(file_handle);
          WHEN UTL_FILE.WRITE_ERROR THEN
              DBMS_OUTPUT.PUT_LINE(\'UTL_FILE.WRITE_ERROR\');
              UTL_FILE.FCLOSE(file_handle);
          WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE(\'other stuff\');
              UTL_FILE.FCLOSE(file_handle);
        END;
        /

Husk at sætte parametren UTL_FILE_DIR i init.ora filen:


        UTL_FILE_DIR = /u05/home/output/mydir, /output

            or

        UTL_FILE_DIR = *