{"id":692,"date":"2011-09-07T03:37:48","date_gmt":"2011-09-07T03:37:48","guid":{"rendered":"http:\/\/www.withdata.com\/ad\/?p=692"},"modified":"2011-09-07T03:37:48","modified_gmt":"2011-09-07T03:37:48","slug":"alternative-for-sqlloader","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/oracmd\/alternative-for-sqlloader.html","title":{"rendered":"Alternative for SQL*Loader"},"content":{"rendered":"

OraCmd is a command-line tools that can run sql command, import\/export data, even without Oracle client<\/strong>.<\/p>\n

OraCmd takes the pain out of manual importing\/exporting tasks, and avoids installing the Oracle client.<\/p>\n

 <\/p>\n

Convert SQL*Loader control file to OraCmd command<\/h3>\n

To use SQL*Loader you have to install Oracle client software. To avoid installing the large client, you can use OraCmd.<\/p>\n

OraCmd is not as flexible as SQL*Loader, but it is easy and powerful enough.<\/p>\n

A Short Example<\/h4>\n

This section contains a short example showing how SQL*Loader control file be converted to OraCmd command.<\/p>\n

The Data<\/strong><\/p>\n

The particular file used for this example contains handhelds infomation. It’s a delimited text file. The following example shows three records from that file. The lines wrap in this page, but in the file each name is on its own line:<\/p>\n

\t\"ACQ\",\"2007-09-04 15:47:47\",\"XV6700\",\"917-817-6829\",\"054-07696647\",\r\n\t\"HT638E602306\",\"166.240.214.11\",\"\",\"\"\r\n\r\n\t\"ACQ\",\"2007-09-04 15:50:17\",\"XV6700\",\"917-817-6904\",\"054-07696459\",\r\n\t\"HT638E602118\",\"166.240.214.12\",\"\",\"\"\r\n\r\n\t\"ACQ\",\"2007-09-04 15:50:26\",\"XV6700\",\"917-817-8778\",\"054-07696510\",\r\n\t\"HT638E602169\",\"166.240.214.13\",\"\",\"\"<\/pre>\n
As you can see, the data in the file is comma-delimited, and each field is enclosed within double quotes.<\/p>\n
We used the following SQL statement to create the table into which all this data will be loaded:<\/p>\n
\tcreate table HANDHELDS\r\n\t(\r\n\t  REGION_NAME    VARCHAR2(20),\r\n\t  ASSIGN_DATE    DATE,\r\n\t  MODEL          VARCHAR2(20),\r\n\t  PHONE_NUMBER   VARCHAR2(20),\r\n\t  ESN            VARCHAR2(20),\r\n\t  SERIAL         VARCHAR2(20),\r\n\t  IP_ADDRESS     VARCHAR2(20),\r\n\t  ASSIGN_TECH_ID VARCHAR2(20),\r\n\t  MOBILE_VERSION VARCHAR2(20)\r\n\t)<\/pre>\n
SQL*Loader control file<\/strong><\/p>\n
The following control file will be used to load the data.<\/p>\n
\tload data\r\n\tinfile 'E:\\testdata\\handhelds.csv'\r\n\treplace\r\n\tinto table HANDHELDS\r\n\tfields terminated by \",\"\r\n\toptionally enclosed by '\"'\r\n\t(\r\n\tREGION_NAME,\r\n\tASSIGN_DATE date 'yyyy-mm-dd hh24:mi:ss',\r\n\tMODEL,\r\n\tPHONE_NUMBER,\r\n\tESN,\r\n\tSERIAL,\r\n\tIP_ADDRESS,\r\n\tASSIGN_TECH_ID,\r\n\tMOBILE_VERSION\r\n\t)<\/pre>\n
OraCmd commmand<\/strong><\/p>\n
Convert the SQL*Loader control file to following OraCmd command:<\/p>\n
\tload table=handhelds datafile=\"E:\\testdata\\handhelds.csv\" delimiter=\",\" quote=\"chr(34)\" \r\n\tdateformat=\"YYYY-MM-DD HH24:MI:SS\" loadtype=replace<\/pre>\n
Or a batch file(replace the userid with your own database user info):<\/p>\n
\t\"C:\\Program files\\OraCmd\\OraCmd\" userid=hr\/hr@localhost:1521:xe task=load \r\n\ttable=handhelds datafile=\"E:\\testdata\\handhelds.csv\" delimiter=\",\" quote=\"chr(34)\" \r\n\tdateformat=\"YYYY-MM-DD HH24:MI:SS\" loadtype=replace quit=y \r\n\tlog=\"E:\\test\\load_handhelds.log\"<\/pre>\n
\n
The OraCmd parameter descriptions are as follows: <\/strong><\/p>\n
delimeter: delimiter char (\"|\",\",\",\"#\",\"TAB\",\"WHITESPACE\")\r\n\r\nquote: quote char (quote=\"'\" => single quotes, quote=\"chr(34)\" => double quotes)\r\n\r\nheader: y|n, data file contains field name at first line\r\n\r\nschema: schema name, use this prameter to load specific schema's data, if not set, use logon user's default schema\r\n\r\ntable: table name\r\n\r\ndatafile: data file name\r\n\r\nloadtype: append|replace|update\r\n\tappend: appends the new rows to the table.\r\n\treplace: all rows in the table are deleted and the new data is loaded.\r\n\tupdate: update the rows by the new data.\r\n\r\nupdatekey: key feild to update with, usually use primary key, only for loadtype=update.\r\n\r\ndateformat: date format (e.g. \"YYYY-MM-DD HH24:MI:SS\")\r\n\r\ntimestampformat: timestamp format (e.g. 'YYYY-MM-DD-HH24.MI.SS.FF6')\r\n\r\nnullif: specifies that the column should be loaded as NULL \r\n(\"BLANKS\" for zero length strings being loaded into numeric columns)\r\n\r\nbadfile: write the content that can not load to this badfile\r\n\r\nerrors:  maxinum error count<\/pre>\n<\/div>\n
\n
\n
OraCmd<\/a> is a command-line tools that can run sql command, import\/export data, even without Oracle client.<\/p>\n
30-day free trial \"Download<\/a><\/span>US$49.95 \"Buy <\/a>
\n<\/span><\/p>\n
 <\/p>\n
\u00a0<\/span><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
OraCmd is a command-line tools that can run sql command, import\/export data, even without Oracle client. OraCmd takes the pain out of manual importing\/exporting tasks, and avoids installing the Oracle client.   Convert SQL*Loader control file to OraCmd command To use SQL*Loader you have to install Oracle client software. To avoid installing the large client, […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[1325,72],"class_list":["post-692","post","type-post","status-publish","format-standard","hentry","category-oracmd","tag-oracle","tag-sqlloader"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/692","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=692"}],"version-history":[{"count":1,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/692\/revisions"}],"predecessor-version":[{"id":693,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/692\/revisions\/693"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=692"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=692"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=692"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}