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?
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
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?
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"
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.