[santhoshs79]

Hi all,
Here is a problem I am facing .I have to admit, I am not an expert in batch file scripting.

The database is oracle, and the table is something like this.

jobname date inputfile
________________________________
job1 mm-dd-yy input1
job2 mm-dd-yy input2
...... ............ ........


my batch file has to write a text file in the following format(jobfile.txt)

****************************************************

(jobname)

input file = (inputf filename)
......
......
....

******************************************************

The criteria is system date, i.e the bat file has to get the system date, and run a query, get the roes and ten write a text file, there might be any number of jobs for a particular date, and each job has one input file for a particular date.

I tried a clumsy method......The query result is first stored as a csv file, and then from this file I tried to write the required text file, but I am facing problems in the latter part. escpecially when I am reading the data from csv to write to the jobfile.txt.


here is the code

for /f "TOKENS=2* DELIMS=/ " %%A IN ('DATE/t') DO SET MM=%%A
for /f "TOKENS=3* DELIMS=/ " %%B IN ('DATE/t') DO SET DD=%%B
for /f "TOKENS=4* DELIMS=/ " %%C IN ('DATE/t') DO SET YYYY=%%C
set mydate=%MM%/%DD%/%YYYY%%

rem echo set feedback off >>%0.txt
echo set flush off >>%0.txt
echo set heading off >>%0.txt
echo set headsep off >>%0.txt
echo set timing off >>%0.txt
echo set pagesize 0 >>%0.txt
echo set recsep off >>%0.txt
echo set termout off >>%0.txt
echo set trimspool on >>%0.txt
echo set trimout on >>%0.txt
echo set verify off >>%0.txt
echo set wrap off >>%0.txt
echo set colsep ',' >>%0.txt
rem echo set space 0 >>%0.txt
echo set pause off >>%0.txt
echo spool select.txt >>%0.txt
echo select trip(jobname),trim(inputfile) from job_table;>>%0.txt
echo spool off >>%0.txt
echo set feed on >>%0.txt

echo connect user/pword@orcl_connect_string >%0.txt
sqlplus /nolog @%0.txt
echo exit 8; >>%0.txt

del %0.txt

REM I am facing problems here.....the only the last line is getting written,....

for /f "tokens=1-2 delims=," %%a in (..\select.txt) do (set job=%%a set input=%%b
echo %job% >> jobfile.txt
echo InputFile=%input% >> jobfile.txt
)

[HKOldGuy]

This simplest way is Using Oracle's UTL_FILE package and PL/SQL