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?
Oracle Bulk Load - manual mode and ctl file generation
Moderators: chulett, rschirm, roy
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).
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).
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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 .
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 .
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:
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).
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)
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).