We are writing a table to CSV for import into another database, using SQLcl. Our table named “user_requests” has a column named “remark”. So we tried:
set loadformat csv;
unload user_requests;
We got the expected csv file, USER_REQUESTS_DATA_TABLE.csv with all the data in the table formatted correctly. But the column header on the first line of the file has "FIRST_NAME", "LAST_NAME","","STATUS"… where it should have "FIRST_NAME", "LAST_NAME","REMARK","STATUS"… We tried several other tables and got the expected results - the problem is only with this table.
So we tried:
spool USER_REQUESTS_DATA_TABLE.csv
SELECT /*csv*/user_request_id, user_request_date, first_name, last_name, remark, status, status_remark, version_id, ols_label, ctrl_label, rel_label, coi_label, rel_to_label, net_rel_label, orgunit_label, user_dn, poc_id, approved_by_user_id
FROM user_requests;
spool off;
Same result. The column name is missing in the header. Oddly, running that in SQL Developer works as expected.
We tried changing the column reference in the SELECT to:
remark as “REMARK”
But that didn't work. So we changed to:
remark as remark_text
That works - but of course the header now says “REMARK_TEXT”. We'll live with that - we can change the column name in the table to which we're importing. But we are wondering if this is a bug in SQLcl - maybe having to do with processing of reserved words.