CREATE OR REPLACE package body Test as

procedure Test1 is
               
  
  cursor OracleDirectories is
    select *
      from all_directories;
      
      
    
  begin
    OutputToCSV (OracleDirectories);

  
  end;

procedure OutputToCSV (SelectStatement in sys_refcursor) is
               
    
  begin
    Use DBMS_SQL statement to write the columns of SelectStatement to a CSV file.

  
  end;


end Test;

CREATE OR REPLACE
  PACKAGE TEST
    IS
      PROCEDURE TEST1;
      PROCEDURE OUTPUT_TO_CSV(
                              P_REFCUR IN OUT SYS_REFCURSOR
                             );
END TEST;
/
CREATE OR REPLACE
  PACKAGE BODY TEST
    IS
      PROCEDURE OUTPUT_TO_CSV(
                              P_REFCUR IN OUT SYS_REFCURSOR
                             )
        IS
            V_DBMS_SQL_CUR NUMBER;
            V_COL_CNT      NUMBER;
            V_DESCTAB      DBMS_SQL.DESC_TAB;
            V_COLS         SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
        BEGIN
            V_DBMS_SQL_CUR := DBMS_SQL.TO_CURSOR_NUMBER(P_REFCUR);
            DBMS_SQL.DESCRIBE_COLUMNS(
                                      V_DBMS_SQL_CUR,
                                      V_COL_CNT,
                                      V_DESCTAB
                                     );
            V_COLS.EXTEND(V_COL_CNT);
            FOR V_I IN 1..V_COL_CNT LOOP
              DBMS_SQL.DEFINE_COLUMN(
                                     V_DBMS_SQL_CUR,
                                     V_I,
                                     V_COLS(V_I),
                                     4000
                                    );
            END LOOP;
            WHILE DBMS_SQL.FETCH_ROWS(V_DBMS_SQL_CUR) > 0 LOOP  
              FOR V_I IN 1..V_COL_CNT LOOP
                DBMS_SQL.COLUMN_VALUE(
                                      V_DBMS_SQL_CUR,
                                      V_I,
                                      V_COLS(V_I)
                                     );
                DBMS_OUTPUT.PUT_LINE(V_DESCTAB(V_I).COL_NAME || ' = ' || V_COLS(V_I));
             END LOOP;
           END LOOP;
           DBMS_SQL.CLOSE_CURSOR(V_DBMS_SQL_CUR);
      END;
      PROCEDURE TEST1
        IS
            V_REFCUR SYS_REFCURSOR;
        BEGIN
            OPEN V_REFCUR
              FOR SELECT  *
                    FROM  ALL_DIRECTORIES;
            OUTPUT_TO_CSV(
                          V_REFCUR
                         );
  END;
END;
/
SET SERVEROUTPUT ON
EXEC TEST.TEST1
OWNER = SYS
DIRECTORY_NAME = I_DRIVE
DIRECTORY_PATH = i:\
ORIGIN_CON_ID = 3
OWNER = SYS
DIRECTORY_NAME = TEMP
DIRECTORY_PATH = c:\temp
ORIGIN_CON_ID = 3

PL/SQL procedure successfully completed.

SQL>