Using varchar in a control file for sqlldr

Question

I have a process that dynamically creates the CTL file through a sql query. And I have to have the file name inserted into the oracle table. I am able to get the filename to show on the query, but it keeps rejecting it. Do ctls only accept numbers? Because if I hard code it to a number it will work. If i hard code it to a varchar, it will reject it. How can i get the filename to be inserted while being loaded?

           '  LEAD_TIME,' || chr (10) ||
       '  DELIVERY_TIME,' || chr (10) ||
       '  DELIVERY_NO,' || chr (10) ||
       '  FREQUENCY,' || chr (10) ||
       '  FREQUENCY_UNIT,' || chr (10) ||
       '  MODE_TYPE,' || chr (10) ||
       '  AUDIT_LOG_ID "' || MAX(audit_log_id) || '",' || chr (10) ||
       '  STAGING_STATUS_ID "1",' || chr (10) ||
       '  FILENAME "&fname",' || chr (10) ||
       '  LINENO SEQUENCE(1)' || chr (10) ||
       ' )' || chr (10)
   from   audit_log
   where filename_orig = '&fname';

spool off

Here is the output from running sqlldr. It correctly shows the filename, but still rejects the entry.

       Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
VENDOR                              FIRST     *   ,       CHARACTER
COMM_CONTRACT                        NEXT     *   ,       CHARACTER
SERV_CONTRACT                        NEXT     *   ,       CHARACTER
ADDR_CHAIN                           NEXT     *   ,       CHARACTER
STORE                                NEXT     *   ,       CHARACTER
SITE_GROUP                           NEXT     *   ,       CHARACTER
START_DATE                           NEXT     *   ,       CHARACTER
END_DATE                             NEXT     *   ,       CHARACTER
ORDER_DAY                            NEXT     *   ,       CHARACTER
ORDER_TIME                           NEXT     *   ,       CHARACTER
LEAD_TIME                            NEXT     *   ,       CHARACTER
DELIVERY_TIME                        NEXT     *   ,       CHARACTER
DELIVERY_NO                          NEXT     *   ,       CHARACTER
FREQUENCY                            NEXT     *   ,       CHARACTER
FREQUENCY_UNIT                       NEXT     *   ,       CHARACTER
MODE_TYPE                            NEXT     *   ,       CHARACTER
AUDIT_LOG_ID                         NEXT     *   ,       CHARACTER
    SQL string for column : "26004459"
STAGING_STATUS_ID                    NEXT     *   ,       CHARACTER
    SQL string for column : "1"
FILENAME                             NEXT     *   ,       CHARACTER
    SQL string for column : "filenamexxx.csv"
LINENO                                                    SEQUENCE (1, 1)

value used for ROWS parameter changed from 64 to 51

Table DISA_STAGING_SUPP_SCHEDULE:
  0 Rows successfully loaded.
  6 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

What is the structure of the table you are inserting into (columns and data types)? What is the data being loaded, and what are the errors the six rows are being rejected with? — Alex Poole, May 12 '16 at 08:46

score 1 · Accepted Answer · answered May 12 '16 at 08:56

You should be seeing errors like ORA-00984: column not allowed here. The filenamexxx.csv is being seen as a column name rather than a value, because it isn't quoted; you can change it to be generated as:

FILENAME "'filenamexxx.csv'"

and to do that your query has escaped single quotes around the substitution variable:

       '  FILENAME "''&fname"'',' || chr (10) ||

or if you prefer, the alternative quoting mechanism:

       q'[  FILENAME "'&fname"',]' || chr (10) ||

Thank you so much. The first snippit fixed my issue. You saved me heartache. — Alkey29, May 12 '16 at 14:51

Using varchar in a control file for sqlldr

1 Answers1