SQLLDR: column with default value not filled

Question

I have a table in Oracle 11g I'm trying to load with SQLLDR. This table has a column that should contain the creation date that stores when this row was created in the database:

...
ORACLE_ROW_CREATE TIMESTAMP(6) DEFAULT SYSTIMESTAMP
...

My control file looks like this:

INTO TABLE rrdbo.IBXR_STAGE_0000
WHEN (12-26) <> 'IBXRBASELINE   ' AND (30-47) <> BLANKS
   (
   CLIENT_GROUP_ID POSITION(1:10)
   , PORTAL_ID POSITION(11:20)
   , ACCOUNT_BRANCH_ID POSITION(21:23)
   , ACCOUNT_BASE_ID POSITION(24:29)
   , RELATIONSHIP_TYPE_CD POSITION(30)
   , CLIENT_ID POSITION(31:40)
   , CLIENT_SEGMENT_ID POSITION(41:45) DECIMAL EXTERNAL
   , TOA_IND POSITION(46)
   , LINK_TYPE_CD POSITION(47)
   , LST_UPD_TMST POSITION(48:67) TIMESTAMP "YYYYMMDDHH24MISSFF6"
   , LST_UPD_PRCS_ID POSITION(68:77)
   , LST_UPD_ID POSITION(78:87)
   , MULTI_COMPANY_NUM CONSTANT "0000"
   )

After the load completes the ORACLE_ROW_CREATE column has no values. How do I provide this value without setting DIRECT=FALSE?

Alex Poole Alex Poole · Accepted Answer · 2014-08-29T20:31:17

You could use SYSDATE:

   , ORACLE_ROW_CREATE SYSDATE

But you'd lose millisecond precision. So more usefully in your case, youe can use EXPRESSION to pick up the column default:

   , ORACLE_ROW_CREATE EXPRESSION "DEFAULT"

The documentation explicitly says:

In both conventional path mode and direct path mode, the EXPRESSION parameter can be used to load the default value into column_name

... so it won't be ignored as it normally would be for a direct-path load. (Although I've never actually used this, so I hope the docs are right...)

SQLLDR: column with default value not filled

1 Answers