Run/Export Oracle-Discoverer via batch file

[GaOx]

Hope this is the place for this:

Our legacy database was replaced with Oracle HR. The query program is Discoverer. With a huge network (US Air Force) and using a file of 70,000 records -- I find it very time consuming to run queries and export them to text for later loading into Access. I'd like to run the query(s) via a scheduler, which, in turn, runs a batch file. However, I can't find a way to pass the logon stuff, i.e., userID/password. The batch file runs until it gets to that point and quits. Admittedly, my "batch file speak" is weak -- but I've gleaned some stuff -- enough (as little as it is) to get Oracle to open. Just need to be able to logon, run the query and export the results.

Thanks for both your time and whatever good info comes of this query.

Respectfully,

Gary

 

[jimbopalmer]

Were it mine, I would use Business Objects to replace Access, Discoverer and the Unix Script, but that is an ideal world.

sqlplus apps/fnd -s @myquery.sql
unix2dos text.txt /shareddrive/text.txt

where myquery.sql is

spool text.txt

select Full_name||';'|| D_termination_date from per_people_v7 where d_termination_date is not null;

spool off I love everyone til they prove otherwise, sadly some prove otherwise SO quickly