Hello Tom,
I have a plsql block that writes the records from a table into mulitple csv's. It is based on a condition(code) and if the csv has got more than 500,000 records then it will spool to another csv.
The question is my first CSV has got the output column headers printed correctly whereas the subsequent CSV's has got a comma in front of the first column header. I have enclosed the excerpt of the plsql block
PROCEDURE DUMP_CSV
AS
L_FILE UTL_FILE.FILE_TYPE DEFAULT NULL;
L_ROW_PER_FILE INTEGER DEFAULT 500000;
L_FILE_NAME VARCHAR2(100) DEFAULT 'CR4440_CONVERTED_{FILENO}.CSV';
L_FILE_DIR VARCHAR2(100) DEFAULT 'EXPDP_DIR';
L_FILE_NO INTEGER DEFAULT 0;
L_CUST VARCHAR2(30) DEFAULT 'CUST000';
L_FILENAME VARCHAR2(50);
L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
L_COLUMNVALUE VARCHAR2(4000);
L_STATUS INTEGER;
L_QUERY VARCHAR2(1000)
DEFAULT 'SELECT VOLUME, FOLIO, SURNAME_COMPANY_NAME,FIRST_NAME,MIDDLE_NAME,UNIT_NBR,STREET_NBR ,STREET_NAME ,CITY , STATE, POST_CODE,DEALING_NBR,MORTGAGEE_NAME,REGISTRATION_DATE FROM BULK_RESULT_DTL';
L_COLCNT NUMBER := 0;
L_SEPARATOR VARCHAR2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;
BEGIN
FOR I IN (SELECT CEIL(ROWNUM/500000) FILE_NO, CUSTOMER_CODE,VOLUME, FOLIO, SURNAME_COMPANY_NAME,FIRST_NAME,MIDDLE_NAME,UNIT_NBR,STREET_NBR ,STREET_NAME ,CITY , STATE, POST_CODE,DEALING_NBR,MORTGAGEE_NAME,REGISTRATION_DATE
FROM BULK_RESULT_DTL ORDER BY CUSTOMER_CODE)
LOOP
IF L_CUST!=I.CUSTOMER_CODE OR L_FILE_NO != I.FILE_NO
THEN
IF UTL_FILE.IS_OPEN(L_FILE)
THEN
UTL_FILE.FCLOSE(L_FILE);
END IF;
L_FILE_NO := I.FILE_NO;
L_CUST:=I.CUSTOMER_CODE;
L_FILENAME :=TO_CHAR(L_FILE_NO, 'FM099999')||'_'||L_CUST;
L_FILE := UTL_FILE.FOPEN
(
L_FILE_DIR
, REPLACE(L_FILE_NAME, '{FILENO}', L_FILENAME)
, 'W'
);
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''DD-MON-YYYY HH24:MI:SS''
';
DBMS_SQL.PARSE( L_THECURSOR, L_QUERY, DBMS_SQL.NATIVE );
DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );
FOR I IN 1 .. L_COLCNT LOOP
UTL_FILE.PUT( L_FILE, L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"'||L_SEPARATOR );
DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
L_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE( L_FILE );
/* IF ISEMPTY(L_FILE_DIR,V_FILE_NAME)THEN
DESC_COLUMN(L_FILE_DIR,V_FILE_NAME);
END IF;*/
END IF;
UTL_FILE.PUT_LINE(L_FILE, TRIM(I.VOLUME)|| ',' || TRIM(I.FOLIO)|| ',' || TRIM(I.SURNAME_COMPANY_NAME)|| ',' || TRIM(I.FIRST_NAME)|| ',' ||TRIM(I.MIDDLE_NAME)||','|| TRIM(I.UNIT_NBR)|| ',' || TRIM(I.STREET_NBR)||','|| TRIM(I.STREET_NAME)|| ',' ||TRIM(I.CITY)||','|| TRIM(I.STATE)|| ',' || TRIM(I.POST_CODE)|| ',' || TRIM(I.DEALING_NBR)|| ',' || TRIM(I.MORTGAGEE_NAME)|| ',' || TRIM(I.REGISTRATION_DATE));
--UTL_FILE.PUT_LINE(L_FILE, I.FOLIO_REF || ',' || I.CUSTOMER_CODE);
END LOOP;
IF UTL_FILE.IS_OPEN(L_FILE)
THEN
UTL_FILE.FCLOSE(L_FILE);
END IF;
END DUMP_CSV;OUTPUT of the first CSV:"VOLUME","FOLIO","SURNAME_COMPANY_NAME"
OUTPUT of the subsequent CSV'S:,"VOLUME","FOLIO","SURNAME_COMPANY_NAME"
Notice that l_separator starts as null, then after the first column we set it:
FOR I IN 1 .. L_COLCNT LOOP
UTL_FILE.PUT( L_FILE, L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"'||L_SEPARATOR );
DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
L_SEPARATOR := ','; <<<<=========
END LOOP;
So you need to reset it to null when you go to a new file.
Hope this helps.