There exist many ways to get data from Oracle. After all, this is the core functionality of Oracle: getting data in and out. A nice possibility is to retrieve data by means of a small script. Such script is straightforward:
set echo off set pagesize 0 set feedback off set linesize 50 set termout off spool c:/onzin.txt select '"'||job_id||'";"'||job_id||'";' ||min_salary||';'||max_salary from jobs; spool off
The two most important lines are: “spool ” which designates a file that will receive data and the SQL that reads like “select etc”. Such script can be executed from SQLplus by @ “filename” where “filename” is the file that holds the script. In SQLplus, this looks like
When I go to the directory c:\, I see the file “onzin.txt”, that contains the data
"AD_PRES";"AD_PRES";20000;40000 "AD_VP";"AD_VP";15000;30000 "AD_ASST";"AD_ASST";3000;6000 "FI_MGR";"FI_MGR";8200;16000
I like this approach: you only need SQLplus as client application, which is standard in most circumstances. Moreover, it is fast: it depends on the network, but it is possible to retrieve millions of records within reasonable amount of time. Finally, this approach is repeatable since the script can be stored in a file.
It is possible to execute this approach in a scheduled environment. If we have a script (say a.bat) that looks like:
connect scott/bunvgeni@INTERNET @C:\a.sql quit
This script can be executed by a scheduler with subsequent command “sqlplus /nolog @a.bat”. Without human interference, SQLplus is started and within SQLplus, a script is executed that retrieves data.