{"id":941,"date":"2005-09-01T12:00:00","date_gmt":"2005-09-01T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=941"},"modified":"2005-09-01T12:00:00","modified_gmt":"2005-09-01T12:00:00","slug":"nice-sqlldr-option-for-external-tables","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2005\/09\/01\/nice-sqlldr-option-for-external-tables\/","title":{"rendered":"Nice SQLLDR option for external tables"},"content":{"rendered":"<p>(Trying this one again to see if it appears on Orablogs)<\/p>\n<p>SQLLDR has the following command line option EXTERNAL_TABLE = GENERATE_ONLY which, when you give it an old-fashioned SQLLDR control file, will spit out the External Table equivalent in the log file.<\/p>\n<p>e.g.Control File<\/p>\n<pre>LOAD DATA<br\/>INFILE 'test.txt'<br\/>TRUNCATE<br\/>INTO TABLE doug_test<br\/>FIELDS TERMINATED BY \"\"<br\/>(pk,<br\/>test_value CHAR \"TO_NUMBER(:test_value, '999,990.90','NLS_NUMERIC_CHARACTERS = '',.''')\")<\/pre>\n<p>Command to runsqlldr<\/p>\n<pre>scott\/tiger control=test.ctl external_table=generate_only<\/pre>\n<p>Contents of log file (some extraneous text stripped out)<\/p>\n<pre>CREATE DIRECTORY statements needed for files<br\/>------------------------------------------------------------------------<br\/>CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'C:\\'<br\/><p><\/p><br\/>CREATE TABLE statement for external table:<br\/>------------------------------------------------------------------------<br\/>CREATE TABLE \"SYS_SQLLDR_X_EXT_DOUG_TEST<br\/>\"(\"PK\" NUMBER,\"TEST_VALUE\" VARCHAR(255))<br\/>ORGANIZATION external<br\/>(TYPE oracle_loader<br\/>DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000<br\/>ACCESS PARAMETERS<br\/>(RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252<br\/>BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.bad'<br\/>LOGFILE 'test.log_xt'<br\/>READSIZE 1048576<br\/>FIELDS TERMINATED BY \"\" LDRTRIM<br\/>REJECT ROWS WITH ALL NULL FIELDS<br\/>(\"PK\" CHAR(255)TERMINATED BY \"\",\"<br\/>TEST_VALUE\" CHAR(255)TERMINATED BY \"\"))<br\/>location('test.txt'))<br\/>REJECT LIMIT UNLIMITED<br\/><p><\/p><br\/>INSERT statements used to load internal tables:<br\/>------------------------------------------------------------------------<br\/>INSERT \/*+ append *\/<br\/>INTO DOUG_TEST(PK,TEST_VALUE)<br\/>SELECT\"PK\",TO_NUMBER(\"TEST_VALUE\", '999,990.90','<br\/>NLS_NUMERIC_CHARACTERS = '',.''')<br\/>FROM \"SYS_SQLLDR_X_EXT_DOUG_TEST\"<br\/><p><\/p><br\/>statements to cleanup objects created by previous statements:<br\/>------------------------------------------------------------------------<br\/>DROP TABLE \"SYS_SQLLDR_X_EXT_DOUG_TEST\"<br\/>DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000<\/pre>\n<p>This parameter can also be set to EXECUTE, and Oracle will attempt to run the commands shown above, rather than just generating them in the logfile, but my interest was in saving some typing but being able to review and modify the code.This is all in the documentation, as usual, but I&#8217;d never noticed it and think it&#8217;s a useful little feature!<\/p>\n<p>Cheers,<\/p>\n<p>Doug<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Trying this one again to see if it appears on Orablogs) SQLLDR has the following command line option EXTERNAL_TABLE = GENERATE_ONLY which, when you give it an old-fashioned SQLLDR control file, will spit out the External Table equivalent in the log file. e.g.Control File LOAD DATAINFILE &#8216;test.txt&#8217;TRUNCATEINTO TABLE doug_testFIELDS TERMINATED BY &#8220;&#8221;(pk,test_value CHAR &#8220;TO_NUMBER(:test_value, &#8216;999,990.90&#8217;,&#8217;NLS_NUMERIC_CHARACTERS&hellip; <a class=\"more-link\" href=\"http:\/\/orcldoug.com\/blog\/2005\/09\/01\/nice-sqlldr-option-for-external-tables\/\">Continue reading <span class=\"screen-reader-text\">Nice SQLLDR option for external tables<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-941","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":957,"url":"http:\/\/orcldoug.com\/blog\/2005\/08\/10\/a-shortcut-for-oracle_home\/","url_meta":{"origin":941,"position":0},"title":"A shortcut for ORACLE_HOME","date":"August 10, 2005","format":false,"excerpt":"I've enjoyed quite a few of the tips in other people's Oracle-related blogs (see the links in the right hand pane) so thought I'd chuck in a few as well. This one's an old one, so many of you will know about it already, but for those that don't ...In\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1320,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/23\/parallel-query-and-11g-part-2\/","url_meta":{"origin":941,"position":1},"title":"Parallel Query and 11g &#8211; Part 2","date":"September 23, 2007","format":false,"excerpt":"Now, that's weird. A little surprising might be more accurate and maybe I'm missing something.During the various tests with and without parallel hints and different parallel_io_cap_enabled settings, I expected the runs that didn't use parallelism to show up \"db file scattered read\" events in the trace files. For example, here's\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1488,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/22\/diagnosing-locking-problems-using-ashlogminer-part-7\/","url_meta":{"origin":941,"position":2},"title":"Diagnosing Locking Problems using ASH\/LogMiner \u2013 Part 7","date":"April 22, 2009","format":false,"excerpt":"Picking up from the end of the last example, I immediately generated a log file dump as follows. (You'll need to look back at Part 5 to see that the ROWID, log file name etc. match up or just trust me that these steps came from a continuation of the\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1484,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/17\/diagnosing-locking-problems-using-ash-part-5\/","url_meta":{"origin":941,"position":3},"title":"Diagnosing Locking Problems using ASH \u2013 Part 5","date":"April 17, 2009","format":false,"excerpt":"... and so it continues.Coincidentally, the subject of tracking down locking problems after they occurred cropped up on the Oak Table mailing list just after the last post. Several suggestions were offered but I think the nearest and most detailed suggestion was that proposed by Kyle Hailey, ex-Oracle and now\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1019,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/14\/alter-table-move-and-table-stats-part-ii\/","url_meta":{"origin":941,"position":4},"title":"alter table &#8230; move and table stats (part II)","date":"July 14, 2006","format":false,"excerpt":"Following up on another comment from Howard, I took the initrans change off the alter table ... move so that it was the most basic variation and then ran it on 8i, 9i and 10g. I've trimmed lots of the output this time, but I have the log files if\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":895,"url":"http:\/\/orcldoug.com\/blog\/2005\/12\/07\/oracle-enterprise-manager-10gr2\/","url_meta":{"origin":941,"position":5},"title":"Oracle Enterprise Manager 10gR2","date":"December 7, 2005","format":false,"excerpt":"First of all, let me be clear that I'm not a fan of GUI tools. That's not based on a misguided macho opinion that somehow you need to be able to struggle with a command line interface to prove that you're a good DBA but because :- I'm a contractor\u2026","rel":"","context":"With 9 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/941","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=941"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/941\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=941"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=941"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=941"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}