Oracle Bulk Load - manual mode and ctl file generation

evee1 · Post by **evee1** » Thu Sep 01, 2011 11:40 pm

For loading dta into the database I'm using Oracle Connector in the Bulk Mode with Manual option.

The stage generated the control file, but it creates the fixed width field definition of the file, for example LOC_IDNT POSITION(128:139) VARCHAR(10). Unfortunatley my source file is variable length and comma delimited.

Is there a way to make it generate control file with the TERMINATED BY clause?

evee1 · Post by **evee1** » Thu Sep 01, 2011 11:59 pm

I think I have misunderstood the functionality of the Manul mode.

I thought that I can pass the control and data files and Oracle connector will load them, but it looks like it generates both control and data files base on the field definition and data passed in from the incoming stage.

So my question is sort of irrelevant (I think).

chulett · Post by **chulett** » Fri Sep 02, 2011 7:33 am

Looks that way. Manual mode means you will arrange the actual load, all it does is create what you'd need to do so.

chulett · Post by **chulett** » Fri Sep 02, 2011 8:15 am

FYI - what I've done, back in the day, when I needed something like this was to post-process the .ctl file created so that it ended up with the options that I needed. If there's no options to control that still, then perhaps using something like sed would do the trick. Did for me.

evee1 · Post by **evee1** » Sat Sep 03, 2011 4:03 am

When I realised how it is supposed to work, I managed to generate the file ctl file that matches my source file, with the exception of some control characters being inserted into varchar fields. Not sure why it's doing this, but it is quite consistent and happend also when I changed the order of the fields. At the same time it generates other types of fields (decimal, date) correctly. Because of these control characters it's failing the load.
Ah, I'm testing it with only one line in the source file, as before with many line in the source file, the generated data file contained only one long (incomplete) line

.

ray.wurlod · Post by **ray.wurlod** » Sat Sep 03, 2011 5:20 pm

Can you post an actual data example, along with "some control characters" being identified?

evee1 · Post by **evee1** » Sun Sep 04, 2011 4:41 pm

Here are the details of my test load.

Source file:
20110524,291,10999,1.89,1.89,0

Peek_197,0:
DAY_DT:2011-05-24 00:00:00 LOC_IDNT:291 ITEM_IDNT:10999 BASE_QTY: 000000000001.8900 ACT_QTY: 000000000001.8900 IND:0 REC_LOAD_DT:2010-07-04 00:00:00 PROCESS_KEY: 0000012345

Control file:

Code: Select all

OPTIONS(READSIZE=20971520, DIRECT=TRUE, MULTITHREADING=TRUE, DATE_CACHE=10000)
LOAD DATA
INFILE '/app/biw/prd/data/inbound/out.txt'
"FIX 218"
APPEND
PRESERVE BLANKS
INTO TABLE CML_FUT_SLS_DLY_FCST_ILD_STG
(DAY_DT POSITION(1:64) TIMESTAMP 'YYYY-MM-DD HH24:MI:SS',
LOC_IDNT POSITION(65:76) VARCHAR(10),
ITEM_IDNT POSITION(77:103) VARCHAR(25),
BASE_QTY POSITION(104:121) CHAR,
ACT_QTY POSITION(122:139) CHAR,
IND POSITION(140:142) VARCHAR(1),
REC_LOAD_DT POSITION(143:206) TIMESTAMP 'YYYY-MM-DD HH24:MI:SS',
PROCESS_KEY POSITION(207:218) CHAR)

When I vi the file /app/biw/prd/data/inbound/out.txt, this is what I'm getting:
2011-05-24 00:00:00^C291^E10999 000000000001.8900 000000000001.8900^A02010-07-04 00:00:00 0000012345.

Also vi complains that "Last line is not complete", and in a case of processing a test source file with 10 lines it just concatenates them all:
2011-05-25 00:00:00 000000000291.0000 000000010999.0000^D2.14^D2.14^A02010-07-04 00:00:00 0000012345.2011-05-26 00:00:00 000000000291.0000 000000010999.0000^D2.87^D2.87^A02010-07-04 00:00:00 0000012345.2011-05-27 00:00:00 000000000291.0000 000000010999.0000^D3.13^D3.13^A02010-07-04 00:00:00 0000012345.2011-05-28 00:00:00 000000000291.0000 000000010999.0000^D1.92^D1.92^A02010-07-04 00:00:00 0000012345.2011-05-29 00:00:00 000000000291.0000 000000010999.0000^D1.22^D1.22^A02010-07-04 00:00:00 0000012345.2011-05-30 00:00:00 000000000291.0000 000000010999.0000^D2.55^D2.55^A02010-07-04 00:00:00 0000012345.2011-05-31 00:00:00 000000000291.0000 000000010999.0000^D1.90^D1.90^A02010-07-04 00:00:00 0000012345.2011-06-01 00:00:00 000000000291.0000 000000010999.0000^D2.15^D2.15^A02010-07-04 00:00:00 0000012345.2011-06-02 00:00:00 000000000291.0000 000000010999.0000^D2.51^D3.07^A12010-07-04 00:00:00 0000012345.2011-05-24 00:00:00 000000000291.0000 000000010999.0000^D1.89^D1.89^A02010-07-04 00:00:00 0000012345.

I haven't try to load sqllds from the command line yet, but I don't think the file should contain any ^C, ^E or ^A characters.

The Datastage gives me the following error:
ora_RDS_TargetTable,0: The OCI function OCIDirPathFinish returned status -2: OCI_INVALID_HANDLE. (CC_OraLoad::finishLoad, file CC_OraLoad.cpp, line 2,185)

If I change the Oracle Connector to use non manual mode, the job loads the data correctly (no warnings).