{"id":189,"date":"2010-06-18T16:17:31","date_gmt":"2010-06-18T15:17:31","guid":{"rendered":"http:\/\/www.nax.cz\/2010\/06\/18\/oracle-sqlldr-field-in-data-file-exceeds-maximum-length\/"},"modified":"2010-06-18T16:17:31","modified_gmt":"2010-06-18T15:17:31","slug":"oracle-sqlldr-field-in-data-file-exceeds-maximum-length","status":"publish","type":"post","link":"https:\/\/nax.cz\/?p=189","title":{"rendered":"Oracle sqlldr: Field in data file exceeds maximum length"},"content":{"rendered":"<p>I tried to include comments field into my migration scripts to a new DB and what was my surprise when I received error &#8222;Field in data file exceeds maximum length&#8220; from sqlldr utility. BTW that utility is very handy &#8211; it loads CSV file and load it into specified table. All is driven by control *.ctl file.<\/p>\n<p>I found <a href=\"http:\/\/dbaspot.com\/forums\/oracle-tools\/147217-sqlldr-field-data-file-exceeds-maximum-length.html\">this solution<\/a> for this specific problem. The key is that by default each field is expected to be just 255 char long. In this case both DB had definition VARCHAR2(4000) for comments (in Oracle you can find out by sql command <strong>desc tablename<\/strong> and about 200 records have this field > 250.<\/p>\n<p>The solution is slightly change the CTL file:<\/p>\n<pre>\ncat 100618.ctl\nload data\n infile '..\/csv\/100618.csv'\n append\n into table Emloyee\n fields terminated by \",\" optionally enclosed by \"'\"\n ( ID,NAME,COMMENTS CHAR(4000),BIRTH_DATE )\n<\/pre>\n<p>The script itself to load data based on this CTL file is easy:<\/p>\n<pre>\nbash-3.00$ cat autosqlldr.sh \n#!\/bin\/bash\nCTLFILE=${1?Ctl file not specified!}\nexport ORACLE_SID=MYDB\nexport ORACLE_HOME=\/usr\/local\/ora1020\nexport PATH=$PATH:$ORACLE_HOME\/bin\ncd \/space\/loader\nsqlldr username\/secretpass control=$CTLFILE\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I tried to include comments field into my migration scripts to a new DB and what was my surprise when I received error &#8222;Field in data file exceeds maximum length&#8220; from sqlldr utility. BTW that utility is very handy &#8211; it loads CSV file and load it into specified table. All is driven by control [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[50,65,79],"class_list":["post-189","post","type-post","status-publish","format-standard","hentry","category-database","tag-csv","tag-oracle","tag-sqlldr"],"_links":{"self":[{"href":"https:\/\/nax.cz\/index.php?rest_route=\/wp\/v2\/posts\/189","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nax.cz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nax.cz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nax.cz\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nax.cz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=189"}],"version-history":[{"count":0,"href":"https:\/\/nax.cz\/index.php?rest_route=\/wp\/v2\/posts\/189\/revisions"}],"wp:attachment":[{"href":"https:\/\/nax.cz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=189"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nax.cz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=189"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nax.cz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=189"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}