0

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.

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"
  • Thanks! (adding -S front helped)... Thanks for pointing to CSV portion as well! – Joe Feb 19 '18 at 19:25
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.