I'm looking to use SQL Developer's SQLcl (v.23.1) to download some Oracle 19c tables to csv files.
I tried using UNLOAD with the following formatting:
set loadformat delimited delimiter <d> enclosures off column_names off row_terminator <L>
Using the UNLOAD command though has drawbacks such as not being able to specify the output file name or to limit the rows being downloaded.
I therefore tried using SPOOL since it lets me specify the output file name and limit the rows I want to download with the following formatting:
set sqlformat delimited <d>
This, however, has its own drawbacks such as not being able to turn off enclosing quotes or to specify a row terminator (as far as I can tell). Is there a way to get SPOOL to work without enclosing quotes and with a row terminator? Something like this:
set sqlformat delimited <d> enclosures off row_terminator <L>
I appreciate any assistance you can provide. Thanks.
Here's a trick that can be used as a workaround...but no guarantees this will continue to work in future.
SQL> conn scott/tiger@pdb21a
Connected.
SQL> create or replace
2 view v_emp as
3 select * from emp
4* where deptno = 10;
View V_EMP created.
SQL> create table all_Objects
2* as select 'SCOTT' owner, 'V_EMP' object_name, 'TABLE' object_type from dual;
Table ALL_OBJECTS created.
SQL> unload v_emp
format csv
column_names on
delimiter ,
enclosures ""
encoding UTF8
row_terminator default
** UNLOAD Start ** at 2024.01.17-13.47.26
Export Separate Files to C:\tmp
DATA TABLE V_EMP
File Name: C:\tmp\V_EMP_DATA_TABLE.csv
Number of Rows Exported: 3
** UNLOAD End ** at 2024.01.17-13.47.26
SQL> drop view v_emp;
View V_EMP dropped.
SQL> drop table all_objects;
Table ALL_OBJECTS dropped.
If you want full control over the writing of files, search the download section in Resource for "oracle-ascii-unload.pc" for a Pro*c version.
Plus more coverage on options here