table2csv

table2csv

#https://oracle-base.com/articles/9i/generating-csv-files

#https://pandazen.wordpress.com/2008/02/01/export-oracle-query-result-to-csv/

#https://oracle-base.com/dba/script?category=miscellaneous&file=csv.sql

#MEMO:  if the text col has \n \r, the function will break.

Here the simple procedure to do it,
CREATE OR REPLACE function DUMP_CSV(P_QUERY     IN VARCHAR2,
                                    P_SEPARATOR IN VARCHAR2 DEFAULT ',',
                                    P_DIR       IN VARCHAR2,
                                    P_FILENAME  IN VARCHAR2) RETURN NUMBER IS
  L_OUTPUT      UTL_FILE.FILE_TYPE;
  L_THECURSOR   INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
  L_COLUMNVALUE VARCHAR2(2000);
  L_STATUS      INTEGER;
  L_COLCNT      NUMBER DEFAULT 0;
  L_SEPARATOR   VARCHAR2(10) DEFAULT '';
  L_CNT         NUMBER DEFAULT 0;
BEGIN
  L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'w');
  DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);
  FOR I IN 1 .. 255 LOOP
    BEGIN
      DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 2000);
      L_COLCNT := I;
    EXCEPTION
      WHEN OTHERS THEN
        IF (SQLCODE = -1007) THEN
          EXIT;
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;
  DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, 1, L_COLUMNVALUE, 2000);
  L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
  LOOP
    EXIT WHEN(DBMS_SQL.FETCH_ROWS(L_THECURSOR) <= 0);
    L_SEPARATOR := '';
    FOR I IN 1 .. L_COLCNT LOOP
      DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);
      UTL_FILE.PUT(L_OUTPUT, L_SEPARATOR || L_COLUMNVALUE);
      L_SEPARATOR := P_SEPARATOR;
    END LOOP;
    UTL_FILE.NEW_LINE(L_OUTPUT);
    L_CNT := L_CNT + 1;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
  UTL_FILE.FCLOSE(L_OUTPUT);
  RETURN L_CNT;
END DUMP_CSV;
/

To test it, you can simply run
SELECT dump_csv (‘SELECT * FROM EMPLOYEES’, ‘,’, ‘WORK_DIR’, ‘test.csv’) FROM DUAL;
– P_QUERY            = ‘SELECT * FROM EMPLOYEES’
– P_SEPARATOR  = ‘,’
– P_DIR                   = ‘WORK_DIR’
– P_FILENAME     = ‘test.csv’
WORK_DIR is oracle directory, that create through this script,
CREATE OR REPLACE DIRECTORY WORK_DIR AS ‘C:\csv’;

Custom Solution (PL/SQL)

Define a directory object which points to an existing filesystem directory on the server. We must grant the necessary access privilege on the directory object to the user who will perform the extract.

CONNECT / AS SYSDBA
CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS 'c:\oracle\extract';
GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO SCOTT;
GRANT EXECUTE ON UTL_FILE TO SCOTT;
Next we create the extract procedure.

CONNECT scott/tiger

CREATE OR REPLACE PROCEDURE EMP_CSV AS
  CURSOR c_data IS
    SELECT empno,
           ename,
           job,
           mgr,
           TO_CHAR(hiredate,'DD-MON-YYYY') AS hiredate,
           sal,
           comm,
           deptno
    FROM   emp
    ORDER BY ename;
    
  v_file  UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN(location     => 'EXTRACT_DIR',
                           filename     => 'emp_csv.txt',
                           open_mode    => 'w',
                           max_linesize => 32767);
  FOR cur_rec IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file,
                      cur_rec.empno    || ',' ||
                      cur_rec.ename    || ',' ||
                      cur_rec.job      || ',' ||
                      cur_rec.mgr      || ',' ||
                      cur_rec.hiredate || ',' ||
                      cur_rec.empno    || ',' ||
                      cur_rec.sal      || ',' ||
                      cur_rec.comm     || ',' ||
                      cur_rec.deptno);
  END LOOP;
  UTL_FILE.FCLOSE(v_file);
  
EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE;
END;
/
We are now able to perform the extract as follows.

EXEC EMP_CSV;


SELECT dump_csv ('select * from secumain', ',', 'EXTRACT_DIR', 'test.csv') FROM DUAL;



============================================HSDB data
select * from jydb.secumain;

select * from jydb.CT_SystemConst t
where lb='201' 
--and t.ivalue = 2 
order by t.ivalue asc;

SELECT sid, serial# FROM v$session;

select id, companycode, infopubldate, infosource, bulletintype, enddate, accountingstandards, mark, basiceps, dilutedeps, basicepscut, dilutedepscut, eps, roebyreport, roe, roecut, wroe, wroecut, operatingreenue, npfromparentcompanyowners, netprofitcut, profitatisa, retainedprofit, netoperatecashflow, netoperatecashflowps, cashequialentincrease, cashequialents, totalassets, sewithoutmi, netassetisa, napsbyreport, naps, napsadjusted, capitalreserefund, totalshares, totalrecompense, feeforaccountantoffice, updatetime, jsid, operatingprofit, totalprofit, fairvaluechangeincome, nonoperatingincome, nonoperatingexpense, incometaxcost, uncertainedinvestmentlosses, minorityprofit, netprofit, netinvestcashflow, netfinancecashflow, exchanratechangeeffect, endperiodcashequivalent, tradingassets, interestreceivables, dividendreceivables, accountreceivables, otherreceivable, inventories, totalcurrentassets, holdforsaleassets, holdtomaturityinvestments, investmentproperty, longtermequityinvest, intangibleassets, totalnoncurrentassets, shorttermloan, tradingliability, salariespayable, dividendpayable, taxspayable, interestpayable, otherpayable, totalliability, paidincapital, surplusreservefund, minorityinterests, totalshareholderequity, totalliabilityandequity, totalcurrentliability, financialexpense, investincome, totalnoncurrentliability, noncurrentliabilityin1year, nonrecurringprofitloss  from jydb.LC_MainDataNew t

where t.id ='8314262209000';

--CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS 'f:\work';
GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO strategy;
GRANT EXECUTE ON UTL_FILE TO strategy;

--GRANT EXECUTE ON session TO strategy;

grant debug connect session to strategy; 
grant debug any procedure to strategy;


SELECT sid, serial# FROM v$session;