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.
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) ||