{"id":349,"date":"2012-10-12T20:02:47","date_gmt":"2012-10-12T20:02:47","guid":{"rendered":"http:\/\/62.131.51.129\/wordpress\/?p=349"},"modified":"2012-10-12T20:02:47","modified_gmt":"2012-10-12T20:02:47","slug":"get-data-from-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=349","title":{"rendered":"Get data from Oracle"},"content":{"rendered":"

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:<\/p>\n

\nset echo off   \nset pagesize 0 \nset feedback off\nset linesize 50\nset termout off\n\nspool c:\/onzin.txt\n\nselect '\"'||job_id||'\";\"'||job_id||'\";'\n||min_salary||';'||max_salary from jobs;\n\nspool off<\/pre>\n
The two most important lines are: \u201cspool \u201d which designates a file that will receive data and the SQL that reads like \u201cselect etc\u201d. Such script can be executed from SQLplus by @ \u201cfilename\u201d where \u201cfilename\u201d is the file that holds the script. In SQLplus, this looks like\"\"<\/a>
\n When I go to the directory c:\\, I see the file \u201conzin.txt\u201d, that contains the data<\/p>\n
\n\"AD_PRES\";\"AD_PRES\";20000;40000                   \n\"AD_VP\";\"AD_VP\";15000;30000                       \n\"AD_ASST\";\"AD_ASST\";3000;6000                     \n\"FI_MGR\";\"FI_MGR\";8200;16000                      \n<\/pre>\n
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.<\/p>\n
Final remark<\/p>\n
It is possible to execute this approach in a scheduled environment. If we have a script (say a.bat) that looks like: <\/p>\n
\nconnect scott\/bunvgeni@INTERNET\n@C:\\a.sql\nquit\n<\/pre>\n
This script can be executed by a scheduler with subsequent command \u201csqlplus \/nolog @a.bat\u201d. Without human interference, SQLplus is started and within SQLplus, a script is executed that retrieves data. <\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-349","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/349","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=349"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/349\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=349"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=349"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=349"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}