Run Test.sql from unix shell export the report to .CSV file

February 5, 2012 at 18:09:41
Specs: Windows XP
Currently I am running a sql query in toad for Oracle 9.1 to export a set of repots as .csv files. Now I am expected to automate this process though a scheduled job with unix script. I am very new to shell scripting as well as scheduling. I know that I have to create a .bat file which consists of the shell script to take the input (Test.sql) file and export as test.csv file.
Can someone help me out on the script to run the sql file and export the .csv file from the application server for example if the input file is there in C:\samplefolder\Test.sql folder

Thanks in advance,

See More: Run Test.sql from unix shell export the report to .CSV file

Report •

February 6, 2012 at 06:33:11
First, is this a Unix or a Window's question? Drive C: generally means Windows.

Anyway, take a look at this link:

Report •

February 7, 2012 at 17:12:08
Hi got the code.. here it is

Batch file code:
set SQL_DIR=C:\oracle\product\10.2.0\client_1\BIN
set LMS_SCHEMA=@@@
set LMS_SID=@@@
set DB_DIR=C:\bea\user_projects\domains\mydomain\IT_Report_automated\database


echo Initializing the delta tables
%SQL_DIR%\sqlplus %DB_VAR% @%DB_DIR%\test.sql
echo Done

Test. Sql

Spool on
set heading off
Spool C:\bea\user_projects\domains\mydomain\IT_Report_automated\data\output\test.xls


select * from ------- (your SQL query)

Spool off

Report •

February 7, 2012 at 17:47:44

How to send the above generated .XLS through email as an attachement.

Please let me know.

Report •
Related Solutions

Ask Question