Thank you Thomas...
I have invoked SQL LOADER through UNIX like this...Is the way you have suggested?
PHP Code:
Control File is like this:
LOAD DATA
append
INTO TABLE ACXIOM_METADATA_A
FIELDS TERMINATED BY "\t"
TRAILING NULLCOLS
(
CLIENT_PRODUCT_CD "SUBSTRB(:CLIENT_PRODUCT_CD , 1, 30)"
,PARTNER_NAME "SUBSTRB(:PARTNER_NAME , 1, 30)"
,NO_CUSTOM_VALUE INTEGER
,CUSTOM_VALUE1_NAME "SUBSTRB(:CUSTOM_VALUE1_NAME , 1, 30)"
,CUSTOM_VALUE1_TYPE "SUBSTRB(:CUSTOM_VALUE1_TYPE , 1, 1)"
,CUSTOM_VALUE1_FORMAT "SUBSTRB(:CUSTOM_VALUE1_FORMAT , 1, 8)"
,CUSTOM_VALUE2_NAME "SUBSTRB(:CUSTOM_VALUE2_NAME , 1, 30)"
,CUSTOM_VALUE2_TYPE "SUBSTRB(:CUSTOM_VALUE2_TYPE , 1, 1)"
,CUSTOM_VALUE2_FORMAT "SUBSTRB(:CUSTOM_VALUE2_FORMAT , 1, 8)"
,CUSTOM_VALUE3_NAME "SUBSTRB(:CUSTOM_VALUE3_NAME , 1, 30)"
,CUSTOM_VALUE3_TYPE "SUBSTRB(:CUSTOM_VALUE3_TYPE , 1, 1)"
,CUSTOM_VALUE3_FORMAT "SUBSTRB(:CUSTOM_VALUE3_FORMAT , 1, 8)"
,CUSTOM_VALUE4_NAME "SUBSTRB(:CUSTOM_VALUE4_NAME , 1, 30)"
,CUSTOM_VALUE4_TYPE "SUBSTRB(:CUSTOM_VALUE4_TYPE , 1, 1)"
,CUSTOM_VALUE4_FORMAT "SUBSTRB(:CUSTOM_VALUE4_FORMAT , 1, 8)"
,CUSTOM_VALUE5_NAME "SUBSTRB(:CUSTOM_VALUE5_NAME , 1, 30)"
,CUSTOM_VALUE5_TYPE "SUBSTRB(:CUSTOM_VALUE5_TYPE , 1, 1)"
,CUSTOM_VALUE5_FORMAT "SUBSTRB(:CUSTOM_VALUE5_FORMAT , 1, 8)"
,CUSTOM_VALUE6_NAME "SUBSTRB(:CUSTOM_VALUE6_NAME , 1, 30)"
,CUSTOM_VALUE6_TYPE "SUBSTRB(:CUSTOM_VALUE6_TYPE , 1, 1)"
,CUSTOM_VALUE6_FORMAT "SUBSTRB(:CUSTOM_VALUE6_FORMAT , 1, 8)"
,CUSTOM_VALUE7_NAME "SUBSTRB(:CUSTOM_VALUE7_NAME , 1, 30)"
,CUSTOM_VALUE7_TYPE "SUBSTRB(:CUSTOM_VALUE7_TYPE , 1, 1)"
,CUSTOM_VALUE7_FORMAT "SUBSTRB(:CUSTOM_VALUE7_FORMAT , 1, 8)"
,CUSTOM_VALUE8_NAME "SUBSTRB(:CUSTOM_VALUE8_NAME , 1, 30)"
,CUSTOM_VALUE8_TYPE "SUBSTRB(:CUSTOM_VALUE8_TYPE , 1, 1)"
,CUSTOM_VALUE8_FORMAT "SUBSTRB(:CUSTOM_VALUE8_FORMAT , 1, 8)"
,CUSTOM_VALUE9_NAME "SUBSTRB(:CUSTOM_VALUE9_NAME , 1, 30)"
,CUSTOM_VALUE9_TYPE "SUBSTRB(:CUSTOM_VALUE9_TYPE , 1, 1)"
,CUSTOM_VALUE9_FORMAT "SUBSTRB(:CUSTOM_VALUE9_FORMAT , 1, 8)"
,CUSTOM_VALUE10_NAME "SUBSTRB(:CUSTOM_VALUE10_NAME , 1, 30)"
,CUSTOM_VALUE10_TYPE "SUBSTRB(:CUSTOM_VALUE10_TYPE , 1, 1)"
,CUSTOM_VALUE10_FORMAT "SUBSTRB(:CUSTOM_VALUE10_FORMAT , 1, 8)"
,CUSTOM_VALUE11_NAME "SUBSTRB(:CUSTOM_VALUE11_NAME , 1, 30)"
,CUSTOM_VALUE11_TYPE "SUBSTRB(:CUSTOM_VALUE11_TYPE , 1, 1)"
,CUSTOM_VALUE11_FORMAT "SUBSTRB(:CUSTOM_VALUE11_FORMAT , 1, 8)"
,CUSTOM_VALUE12_NAME "SUBSTRB(:CUSTOM_VALUE12_NAME , 1, 30)"
,CUSTOM_VALUE12_TYPE "SUBSTRB(:CUSTOM_VALUE12_TYPE , 1, 1)"
,CUSTOM_VALUE12_FORMAT "SUBSTRB(:CUSTOM_VALUE12_FORMAT , 1, 8)"
,CUSTOM_VALUE13_NAME "SUBSTRB(:CUSTOM_VALUE13_NAME , 1, 30)"
,CUSTOM_VALUE13_TYPE "SUBSTRB(:CUSTOM_VALUE13_TYPE , 1, 1)"
,CUSTOM_VALUE13_FORMAT "SUBSTRB(:CUSTOM_VALUE13_FORMAT , 1, 8)"
,CUSTOM_VALUE14_NAME "SUBSTRB(:CUSTOM_VALUE14_NAME , 1, 30)"
,CUSTOM_VALUE14_TYPE "SUBSTRB(:CUSTOM_VALUE14_TYPE , 1, 1)"
,CUSTOM_VALUE14_FORMAT "SUBSTRB(:CUSTOM_VALUE14_FORMAT , 1, 8)"
,CUSTOM_VALUE15_NAME "SUBSTRB(:CUSTOM_VALUE15_NAME , 1, 30)"
,CUSTOM_VALUE15_TYPE "SUBSTRB(:CUSTOM_VALUE15_TYPE , 1, 1)"
,CUSTOM_VALUE15_FORMAT "SUBSTRB(:CUSTOM_VALUE15_FORMAT , 1, 8)"
,CUSTOM_VALUE16_NAME "SUBSTRB(:CUSTOM_VALUE16_NAME , 1, 30)"
,CUSTOM_VALUE16_TYPE "SUBSTRB(:CUSTOM_VALUE16_TYPE , 1, 1)"
,CUSTOM_VALUE16_FORMAT "SUBSTRB(:CUSTOM_VALUE16_FORMAT , 1, 8)"
,CUSTOM_VALUE17_NAME "SUBSTRB(:CUSTOM_VALUE17_NAME , 1, 30)"
,CUSTOM_VALUE17_TYPE "SUBSTRB(:CUSTOM_VALUE17_TYPE , 1, 1)"
,CUSTOM_VALUE17_FORMAT "SUBSTRB(:CUSTOM_VALUE17_FORMAT , 1, 8)"
,CUSTOM_VALUE18_NAME "SUBSTRB(:CUSTOM_VALUE18_NAME , 1, 30)"
,CUSTOM_VALUE18_TYPE "SUBSTRB(:CUSTOM_VALUE18_TYPE , 1, 1)"
,CUSTOM_VALUE18_FORMAT "SUBSTRB(:CUSTOM_VALUE18_FORMAT , 1, 8)"
,CUSTOM_VALUE19_NAME "SUBSTRB(:CUSTOM_VALUE19_NAME , 1, 30)"
,CUSTOM_VALUE19_TYPE "SUBSTRB(:CUSTOM_VALUE19_TYPE , 1, 1)"
,CUSTOM_VALUE19_FORMAT "SUBSTRB(:CUSTOM_VALUE19_FORMAT , 1, 8)"
,CUSTOM_VALUE20_NAME "SUBSTRB(:CUSTOM_VALUE20_NAME , 1, 30)"
,CUSTOM_VALUE20_TYPE "SUBSTRB(:CUSTOM_VALUE20_TYPE , 1, 1)"
,CUSTOM_VALUE20_FORMAT "SUBSTRB(:CUSTOM_VALUE20_FORMAT , 1, 8)"
,CUSTOM_VALUE21_NAME "SUBSTRB(:CUSTOM_VALUE21_NAME , 1, 30)"
,CUSTOM_VALUE21_TYPE "SUBSTRB(:CUSTOM_VALUE21_TYPE , 1, 1)"
,CUSTOM_VALUE21_FORMAT "SUBSTRB(:CUSTOM_VALUE21_FORMAT , 1, 8)"
,CUSTOM_VALUE22_NAME "SUBSTRB(:CUSTOM_VALUE22_NAME , 1, 30)"
,CUSTOM_VALUE22_TYPE "SUBSTRB(:CUSTOM_VALUE22_TYPE , 1, 1)"
,CUSTOM_VALUE22_FORMAT "SUBSTRB(:CUSTOM_VALUE22_FORMAT , 1, 8)"
,CUSTOM_VALUE23_NAME "SUBSTRB(:CUSTOM_VALUE23_NAME , 1, 30)"
,CUSTOM_VALUE23_TYPE "SUBSTRB(:CUSTOM_VALUE23_TYPE , 1, 1)"
,CUSTOM_VALUE23_FORMAT "SUBSTRB(:CUSTOM_VALUE23_FORMAT , 1, 8)"
,CUSTOM_VALUE24_NAME "SUBSTRB(:CUSTOM_VALUE24_NAME , 1, 30)"
,CUSTOM_VALUE24_TYPE "SUBSTRB(:CUSTOM_VALUE24_TYPE , 1, 1)"
,CUSTOM_VALUE24_FORMAT "SUBSTRB(:CUSTOM_VALUE24_FORMAT , 1, 8)"
,CUSTOM_VALUE25_NAME "SUBSTRB(:CUSTOM_VALUE25_NAME , 1, 30)"
,CUSTOM_VALUE25_TYPE "SUBSTRB(:CUSTOM_VALUE25_TYPE , 1, 1)"
,CUSTOM_VALUE25_FORMAT "SUBSTRB(:CUSTOM_VALUE25_FORMAT , 1, 8)"
,RECORD_LOAD_DT "TO_DATE(:lRECORD_LOAD_DT, 'YYYYMMDD')"
,RECORD_LOAD_USER "SUBSTRB(:RECORD_LOAD_USER , 1, 32)"
)
05-24-2006
I wasn't aware that "\t" could be substituted for X'09' field terminators. Also SUBSTRB may or may not be suitable for your environment. Its purpose is to chop a string on a byte boundary rather than a character boundary. This is used sometimes when you have a character column that is specified as VARCHAR2, CHAR, etc of some size that can't contain Unicode characters using several bytes for a single character. It's better to define the table as NVARCHAR2, NCHAR, etc. The column will allow for multiple bytes worth of storage for any single or multiple byte sequences of characters.