shellscript.query Oracle table..populate in a text file

Shell Programming and Scripting
1

Hi Guys,
I'm new to this forum as well as to UNIX shell scripting.
I'm looking for a shellscript to query an Oracle database table and populate the result set of the query in a text file.
Could you someone help me out with a sample code?

Thanks,
Bhagat

2

Hi Bhagat,
U can use the following query to make connection to a oracle database and spool the reults
to a text file:--

sqlplus -s username@databse_string/password << EOF
whenever sqlerror exit sql.sqlcode;
whenever oserror exit FAILURE

set define off
set head off
set feedback off
set echo off
set pagesize 0
set pages 0
set linesize 200
set timing off

spool appropriate path/dummy.txt
UR QUERY GOES HERE
spool off

EOF

If u want to make only manipulations to databse without spooling the results to a any fimle,
dont put it with in SPOOL command.

Hope it works for u......

3

here is the script to query and put output in a file

X=`sqlplus -s user/pwd@host<<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select * from table where rownum<5;
EXIT;
eof`

echo $X>testing.dat

and check this url where the same question was answered.this

4

Thanks both of you!!!
That was very helpful!!
Appreciate your guidance

Regards,
Bhagat

5

Hi Guys,
I'm looking for a slightly modified script this time...
ie.Query Oracle database table and populate the result set of the query in a text file.The column values should be seperat
I also want the count of records returned by the Oracle query.
Could you someone help me out with a sample code?

Thanks,
Bhagat

6

Hi Bhagat
There is small change and i guess will be pretty simple.Give path and filename in spool command where it will store the output.In select statement give column names you want to be in the output and here '|' is used as a delimiter you can use any char you want to.

sqlplus -s user/pwd@host<<eof
set heading off;
set linesize 1000;
spool /dirpath/filename;
select col1||'|'||col2||'|'||col3.......... from table where condition;
spool off;
EXIT;
eof

and counting for no of rows you can chack with a command to spooled file
x=`wc -l /path/filename | awk '{print $1}'`
echo "no of records \c $x"

7

sqlplus -s user/pwd@host<<eof>urfile
set serveroutput on feedback off linesize 1000 pagesize 0;
select * from table where rownum<5;
EXIT;
eof

8

Hi, guys and girls!
I tried the script, but it outputs SQL I typed:
"SQL> select TRANSACTION_ID||'|'||STORE_ID||'|'||EMPLOYEE_ID from TRANSACTIONS;
1|1|1
2|2|2
3|3|3"
I just need the results without the statement, like this:
"1|1|1
2|2|2
3|3|3"
Thanks.
:smiley: