Avatar of dbadm
dbadm
Flag 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

Shell ScriptingLinuxOracle Database

Avatar of undefined
Last Comment
Alex [***Alex140181***]

8/22/2022 - Mon
Alex [***Alex140181***]

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
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.
Alex [***Alex140181***]

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 :-)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

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.
Alex [***Alex140181***]


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
slightwv (䄆 Netminder)

>>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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tomas Helgi Johannsson

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
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
Alex [***Alex140181***]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question