Dump table containing CLOB to csv using PL/SQL proc

Question

I have assembled a procedure to dump a query containing CLOB columns to a csv file. It seems to be working fine until I encounter a query containing dates.

ORA-00932: inconsistent datatypes: expected CLOB got DATE

Is there a way to dynamically convert those dates to some default string format to be able to use the procedure as it is now. Or how can I refactor it if necessary?

create or replace
PROCEDURE export_query_csv(
      p_query     IN VARCHAR2,
      p_filename  IN VARCHAR2)
IS      
    l_separator   VARCHAR2 (10 CHAR) := ';';
    l_dir         VARCHAR2 (128 CHAR)  := 'MY_DIR';
    l_output      utl_file.file_type;
    l_theCursor   INTEGER DEFAULT dbms_sql.open_cursor;
    l_columnValue CLOB;
    l_status      INTEGER;
    l_colCnt      NUMBER DEFAULT 0;
    l_cnt         NUMBER DEFAULT 0;
    l_descTbl     dbms_sql.desc_tab; 

    l_substrVal VARCHAR2(4000) ;
    l_offset NUMBER :=1;
    l_amount NUMBER := 3000;
    l_clobLen NUMBER :=0;
  BEGIN
    EXECUTE IMMEDIATE 'alter session set nls_date_format = ''dd-mon-yyyy hh24:mi:ss''';

    l_output := utl_file.fopen(l_dir, p_filename, 'wb');

    dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);

    FOR i IN 1 .. 1000
    LOOP
      BEGIN
        dbms_sql.define_column(l_theCursor, i, l_columnValue);
        l_colCnt := i;
      EXCEPTION
      WHEN OTHERS THEN
        IF ( SQLCODE = -1007 ) THEN
          EXIT;
        ELSE
          RAISE;
        END IF;
      END;
    END LOOP;

    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); 

    FOR i IN 1 .. l_colCnt 
    LOOP
      utl_file.put_raw(l_output,utl_raw.cast_to_raw('"'));
      utl_file.put_raw(l_output,utl_raw.cast_to_raw(l_descTbl(i).col_name));
      utl_file.put_raw(l_output,utl_raw.cast_to_raw('"'));
      IF i < l_colCnt THEN
        utl_file.put_raw(l_output,utl_raw.cast_to_raw(l_separator));
      END IF;
    END LOOP;

    utl_file.put_raw(l_output,utl_raw.cast_to_raw(chr(13) || chr(10)));

    l_status := dbms_sql.execute(l_theCursor);
    LOOP
      EXIT WHEN (dbms_sql.fetch_rows(l_theCursor) <= 0);
      FOR i IN 1 .. l_colCnt
      LOOP
        dbms_sql.column_value(l_theCursor, i, l_columnValue);

        l_clobLen := dbms_lob.getlength(l_columnValue);
        WHILE l_offset <= l_clobLen
        LOOP
          l_substrVal := dbms_lob.substr(l_columnValue,l_amount,l_offset);
          utl_file.put_raw(l_output,utl_raw.cast_to_raw('"'));
          utl_file.put_raw(l_output,utl_raw.cast_to_raw(l_substrVal));
          utl_file.put_raw(l_output,utl_raw.cast_to_raw('"'));
          l_offset:=l_offset+l_amount;
        END LOOP;
        l_offset := 1;

        IF i < l_colCnt THEN
          utl_file.put_raw(l_output,utl_raw.cast_to_raw(l_separator));
        END IF;
      END LOOP;
      utl_file.put_raw(l_output,utl_raw.cast_to_raw(chr(13) || chr(10)));
      l_cnt := l_cnt + 1;
    END LOOP;

    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose(l_output);

END;

Answer 1

Found it myself, following this pattern:

-- Define columns
FOR i IN 1 .. colcnt LOOP
    IF desctab(i).col_type = 2 THEN
       DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
    ELSIF desctab(i).col_type = 12 THEN
        DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
......
     ELSE
        DBMS_SQL.DEFINE_COLUMN(curid, i, namevar);
     END IF;
END LOOP;