Dématérialisation - Services Numériques - nouvelles technologies

CREATE OR REPLACE directory TMP AS ‘/home/db/user1′;

CREATE OR REPLACE PROCEDURE QUERY_TO_CSV AS
BEGIN
DECLARE
f_file_id UTL_FILE.FILE_TYPE;
v_file_location VARCHAR2(256) := ‘TMP’;
v_line VARCHAR2(4000);
BEGIN
DBMS_OUTPUT.put_line(v_file_location);
f_file_id := UTL_FILE.FOPEN(v_file_location,‘export.csv’,‘w’);
UTL_FILE.PUT_LINE(f_file_id,‘FIELD_1,FIELD_2,FIELD_3,FIELD_4,FIELD_5′);
FOR fields IN
(
SELECT
   FIELD_1,
   FIELD_2,
   FIELD_3,
   FIELD_4,
   FIELD_5
FROM
   MA_TABLE
)LOOP
UTL_FILE.PUT_LINE(f_file_id,fields.FIELD_1||‘,’||fields.FIELD_2||‘,’||fields.FIELD_3||‘,’||fields.FIELD_4||‘,’||fields.FIELD_5);
UTL_FILE.FCLOSE (f_file_id);
UTL_FILE.FCLOSE_ALL;
END LOOP;
END;
END QUERY_TO_CSV;