Hi All
I have a 12c database and wondered if I can get some help on data that needs to be prodcued to a csv file
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
A test table and a couple of input statements produced....
create table test (field_1 varchar2(255), field_2 varchar2(255));
Insert into PRAMS.TEST (FIELD_1, FIELD_2) Values ('test 1', 'This is a test of the input data, and there are brackets (bracket) and " within the text files and all sorts of other chars');
Insert into PRAMS.TEST (FIELD_1, FIELD_2) Values ('test 2', 'This is a test of the input data, and '||CHR(13)||CHR(10)||' there are carriage returns, (brackets) and " double quotes');
COMMIT;
I have a test.sql script which prodeuces the .csv file based on the query in test1.sql
test.sql
--------
set colsep ,
set headsep off
set pagesize 50000
set trimspool on
set linesize 1000
set verify off
set termout off
set numwidth 12
set underline off
spool C:\test.csv
@C:\test1.sql;
spool off
exit;
test1.sql
---------
column field_1 HEADING "field 1" Format a250
column FIELD_2 heading "FIELD 2" Format a250
SELECT field_1, FIELD_2 FROM TEST;
exit;
expected output
How would I take account of commas, carriage returns, double quotes etc when extracting the data from the database so that it doesnt see the comma as another field and ensures the double quotes, carriage returns are dealt with so that the csv file produces two columns worth of data in each instance of the select statement. I would be looking at doing something in the select statement of field_2 to ensure commas, quotes, carriage returns are dealt with
Regards