How to connect from local computer (mac) to remote Oracle DB and execute script and save results to CSV?

Question

I can connect to remote Oracle DB using this signature:
sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'

Now want to execute a script and save results to local CSV file - how to do that?
I already have a script that is doing a work but need a terminal (bash) command to connect (remotely) to a DB and then call a script (all in 1 command).

Guessing something like this (but did not work):
sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))' @sql_script

Thanks.

Answer 1

SQL*Plus starting with version 12.2 supports printing the output in CSV format.

You can add the below in your script:

set markup csv on

Then execute your script called myscript.sql as:

sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))' @myscript.sql

For example:

[oracle@o71 ~]$ cat myscript.sql
set markup csv on
set feedback off echo off termout off
spool my.csv
select username, account_status, created from dba_users where username like 'SYS%';
spool off
exit

[oracle@o71 ~]$ sqlplus -S 'bp/bp@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o71.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SIMPLE)))' @myscript.sql
[oracle@o71 ~]$ cat my.csv

"USERNAME","ACCOUNT_STATUS","CREATED"
"SYS","OPEN","10-JAN-18"
"SYSTEM","OPEN","10-JAN-18"
"SYS$UMF","EXPIRED & LOCKED","10-JAN-18"
"SYSBACKUP","EXPIRED & LOCKED","10-JAN-18"
"SYSRAC","EXPIRED & LOCKED","10-JAN-18"
"SYSKM","EXPIRED & LOCKED","10-JAN-18"
"SYSDG","EXPIRED & LOCKED","10-JAN-18"

Answer 2

In addition to Balazs‘ answer you might want to try the newer SQL shell sqlcl available on the Oracle sqldeveloper website.

With this tool you have a lot of output options including CSV and Shell history.