Reputation: 587
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;
Upvotes: 1
Views: 411
Reputation: 587
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;
Upvotes: 2