Nice SQLLDR option for external tables

(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 DATA
INFILE 'test.txt'
TRUNCATE
INTO TABLE doug_test
FIELDS TERMINATED BY ""
(pk,
test_value CHAR "TO_NUMBER(:test_value, '999,990.90','NLS_NUMERIC_CHARACTERS = '',.''')")

Command to runsqlldr

scott/tiger control=test.ctl external_table=generate_only

Contents of log file (some extraneous text stripped out)

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'C:\'

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DOUG_TEST
"("PK" NUMBER,"TEST_VALUE" VARCHAR(255))
ORGANIZATION external
(TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.bad'
LOGFILE 'test.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "" LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
("PK" CHAR(255)TERMINATED BY "","
TEST_VALUE" CHAR(255)TERMINATED BY ""))
location('test.txt'))
REJECT LIMIT UNLIMITED

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */
INTO DOUG_TEST(PK,TEST_VALUE)
SELECT"PK",TO_NUMBER("TEST_VALUE", '999,990.90','
NLS_NUMERIC_CHARACTERS = '',.''')
FROM "SYS_SQLLDR_X_EXT_DOUG_TEST"

statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DOUG_TEST"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

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'd never noticed it and think it's a useful little feature!

Cheers,

Doug