Escribir en fichero con UTL FILE Oracle 10g XE

Ora.ini

Añadir en el ora.ini

UTL_FILE_DIR=* en el ora ini 

Con sys

Creamos el directorio de trabajo, y le damos permisos a nuestro usuario

create or replace directory dir_temp as 'c:\temp';
grant read, write on directory dir_temp to DST;
grant execute on UTL_FILE to public;

Con usuario normal

SELECT function FROM JOB ORDER BY function;;
DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
 SELECT function FROM JOB ORDER BY function;
BEGIN
-- OPEN FILE FOR WRITING
JOBSFILE := UTL_FILE.FOPEN('DIR_TEMP','JOBS.TXT','W');
-- TAKE ONE RECORD FROM CURSOR
FOR REC IN JOBSCUR
LOOP
  UTL_FILE.PUT_LINE(JOBSFILE, REC.function);  -- WRITE INTO FILE
END LOOP; 
UTL_FILE.FCLOSE(JOBSFILE);  -- CLOSE FILE
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE( 'ERROR -->' ||  SQLERRM);
END;

Referencias

• Using UTL_FILE in Oracle10g XE

--Daniel Simao 09:24 2 mar 2012 (UTC)