Link to home
Start Free TrialLog in
Avatar of dbadm
dbadmFlag for Italy

asked on

Shell script that can run a oracle query

Hi,
OS Version: Red Hat Enterprise Linux 6.2
Oracle Version: 11.2.0.4

I need a shell script that will have a oracle sql file (select statement) inside it and produce a .csv format output in specific location (/log/output/myfile.csv).
I'd like also to remove the "sql query" display from the spooled csv file and separate the columns with a semicolon.


Thanks

Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

Then you'll need something like this:
#!/usr/bin/bash 
FILE="emp.csv" 
sqlplus -s scott/tiger@XE  <<EOF 
SET PAGESIZE 50000 
SET COLSEP ";" 
SET LINESIZE 200 
SET FEEDBACK OFF 
SPOOL $FILE 
SELECT * FROM EMP; 
SPOOL OFF EXIT EOF

Open in new window

Adapted from https://www.theunixschool.com/2012/12/shell-script-how-to-dump-oracle-table-into-csv-file.html
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Don't use COLSEP.  There are numerous notes on the web as to why.  It will not produce the results you probably want due to column widths and wrapping.

>>I'd like also to remove the "sql query" display from the spooled csv file

Use output redirection as suggested here:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:896428357362


If you wanted a true CSV (Comma Seperated Values), sqlplus can do that right out of the box:
set markup csv on
select 'hello','world' from dual;

Open in new window


How to use a semi-colon instead of a comma, you'll likely need to use string concatenation.
Oracle Version: 11.2.0.4 -> thus, "set markup csv on" will NOT work!
If he was on version 12.2 >, I'd be 100% on your side ;-)

Don't use COLSEP.  There are numerous notes on the web as to why. 
Could you provide some more information on this please, thanks :-)
Yep, forgot what version markup csv was introduced.


>>Could you provide some more information on this please

drop table bob purge;
create table bob(first_column varchar2(10), second_column varchar2(10));
insert into bob values('hello','world');
insert into bob values('a','b');
commit;

SET PAGESIZE 50000 
SET COLSEP ";" 
SET LINESIZE 200 
SET FEEDBACK OFF 
SELECT * FROM BOB; 

Open in new window


My output:
FIRST_COLU;SECOND_COL
----------;----------
hello     ;world
a         ;b

Open in new window


It will also cause issues if there happens to be a ';' in one of the columns.  No way to optionally enclose the data in double quotes.

No way to optionally enclose the data in double quotes.                 
You'd have to have the columns selected separately, each one "manually" enclosed with quotation marks -> depending on the columns, this could be a helluva work though :-(

https://stackoverflow.com/questions/50850503/how-to-include-double-quotes-around-string-result-in-sqlplus
>>this could be a helluva work though

Yes.  and as long as you are using concatenation, add the ';'.  Really no more work and gets around the 'colsep' issues.

And you use SQL to generate the SQL for the script.
or
a quick describe and some global search/replace in an editor that allows regex replacements like vim.

No matter how many columns you have, should only take a minute, or less, to generate the SQL to create the concatenated 'csv' select.
A small adjustment in line 9 to Alex's script

#!/usr/bin/bash
FILE="emp.csv"
sqlplus -s scott/tiger@XE  <<EOF
SET PAGESIZE 50000
SET COLSEP ";"
SET LINESIZE 200
SET FEEDBACK OFF
SPOOL $FILE
@your_sql_file
SPOOL OFF EXIT EOF

Open in new window

where your_sql_file holds the SQL to be executed.

Regards,
     Tomas Helgi
Avatar of dbadm

ASKER

I'd like to manage error handling while running sqlplus from shell script, below my script:

#!/bin/sh
LOGFILE=shell_log.log
FILE="test.csv"
sqlplus -s myuser/mypwd@tns  <<-EOF>> ${LOGFILE}
SET PAGESIZE 50000
SET COLSEP ";"
SET LINESIZE 400
SET echo off
SET FEEDBACK OFF
SPOOL $FILE
@test_csv.sql
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
SPOOL OFF
EXIT
EOF

sql_return_code=$?

if [ $sql_return_code != 0 ]
then
echo "The sql script failed. Please refer to the log shell_log.log for more information"
echo "Error code $sql_return_code"
exit 0;
fi

Open in new window


Do you think this error handling is correct?
ASKER CERTIFIED SOLUTION
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial