Export PLSQL Variable into CSV file

Question

I have a PL/SQL block in which I declare and populate some variables. I just check the value using e.g. DBMS_OUTPUT.PUT_LINE('My Name= ' || V_NAME);

I have 42 variables like this and I need to export each variable into a CSV file.

SET SERVEROUTPUT ON;

DECLARE
    RECORD_NUM   VARCHAR2 (10) := 'ITEM0001';
    USER_ID      NUMBER (11);
    FIRST_NAME   VARCHAR2 (25);
    LAST_NAME    VARCHAR2 (25);
BEGIN
    SELECT ID, FIRSTNAME, LASTNAME
      INTO USER_ID, FIRST_NAME, LAST_NAME
      FROM MYSCHEM.EMPLOYEES
     WHERE HIRE_DATE > SYSDATE;

    DBMS_OUTPUT.PUT_LINE ('FIRSTNAME= ' || FIRSTNAME);
    DBMS_OUTPUT.PUT_LINE ('LASTNAME = ' || LAST_NAME);
    DBMS_OUTPUT.PUT_LINE ('USER_ID = ' || USER_ID);
END;
/

How can I export these variables to a CSV file?

Possible duplicate of [Can you help me write a procedure in Oracle to spool data from a table to a CSV file?](https://stackoverflow.com/questions/1028368/can-you-help-me-write-a-procedure-in-oracle-to-spool-data-from-a-table-to-a-csv) — kara, May 30 '18 at 13:19
Will this be a local (desktop) file or can it be on the server? What is the format? e.g. `James,Gordon,4321`? The link provided uses the [`UTL_FILE`](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/UTL_FILE.html) package to write to a file on the database server. — William Robertson, May 31 '18 at 14:34

score 0 · Answer 1 · answered Jun 01 '18 at 05:58

Here a minimal example how to use utl_file:

DECLARE
    outputfile   UTL_FILE.file_type;
BEGIN
    outputfile := UTL_FILE.FOPEN ('DIRECTORY', 'filename.csv', 'w');

    UTL_FILE.PUT_LINE (outputfile, 'One,Two,Three');
    UTL_FILE.PUT_LINE (outputfile, '1,2,3');
    UTL_FILE.PUT_LINE (outputfile, '1,2,3');

    UTL_FILE.FCLOSE (outputfile);
END;

Like William mentioned: this will create a file on you database-server.

If you want to create it on the client we need more detials, how your client accesses the db.

Export PLSQL Variable into CSV file

1 Answers1