Get data from Oracle

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


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.

Final remark

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

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.