Issuewith OracleEnterpriseStage-fast Oracleloader technology

[CGI_SUREN]

I am using Oracle Enterprise stage to load into Oracle tables.
I am using the Write Method 'Load' option which uses fast Oracle loader technology.
When i set the execution mode of the Oracle Enterprise stage to 'Parallel', i am encountering the following error:
Oracle_MetricBalanceTable: Indexes on table 'ODS.BALANCE_ACTIVITY' preclude direct parallel loading unless an index option is included. Add an index option or drop indexes and rerun step.

When i changed the execution mode of the Oracle Enterprise stage to 'Sequential', i am encountering the following error:
Oracle_MetricBalanceTable,0: The call to sqlldr failed; the return code = 32,512;
please see the loader logfile: /dsdata/Scratch/Files/ora.680136.208331.0.log for details.

The contents of the above file are as follows:

OPTIONS(DIRECT=TRUE, PARALLEL=FALSE, SKIP_INDEX_MAINTENANCE=YES)

LOAD DATA INFILE 'ora.475292.268054.fifo.0' "FIX 331"
APPEND INTO TABLE ODS.BALANCE_ACTIVITY
(
BALANCE_ACTIVITY_ID POSITION(1:7) DECIMAL(12,0) ,
BALANCE_REF_ID POSITION(8:14) DECIMAL(12,0) ,
BALANCE_DTM POSITION(15:33) DATE "YYYY-MM-DD HH24:MI:SS" ,
BALANCE_PROC_NM POSITION(34:85) VARCHAR(50) ,
BALANCE_STATUS POSITION(86:112) VARCHAR(25) ,
FIRST_BALANCE_VAL POSITION(113:121) DECIMAL(17,2) ,
SECOND_BALANCE_VAL POSITION(122:130) DECIMAL(17,2) ,
REF_NM POSITION(131:182) VARCHAR(50) NULLIF (133:182) = BLANKS,
REF_ID POSITION(183:189) DECIMAL(12,0) NULLIF (183:189) = X'00000000000000',
CREAT_DT POSITION(190:208) DATE "YYYY-MM-DD HH24:MI:SS" ,
CREAT_USER POSITION(209:260) VARCHAR(50) ,
LAST_UPDT_DT POSITION(261:279) DATE "YYYY-MM-DD HH24:MI:SS" NULLIF (261:279) = BLANKS,
LAST_UPDT_USER POSITION(280:331) VARCHAR(50) NULLIF (282:331) = BLANKS

)


Thanks in Advance

[Vinodanand]

Hi,

Looks like its an Oracle error, and the following is the explanation for the same

ora 32512 :

// *Cause: trying to dump an invalid type name
// *Action: specify a known type

Can you try to load the text file directly using sqlloader without datastage to see if the issue persists.

Regards,
Vinod

[ray.wurlod]

Should VARCHAR be VARCHAR2 ?

[CGI_SUREN]

Should VARCHAR be VARCHAR2 ?

Ray,

The contents in the above file are generated by the DataStage, i have no control over that file.

The datatype specified inside OracleEnterpriseStage columns (metadata) is specified as Varchar.

Thanks in Advance.

[ray.wurlod]

OK, is there anything interesting in /dsdata/Scratch/Files/ora.680136.208331.0.log ?

[CGI_SUREN]

OK, is there anything interesting in /dsdata/Scratch/Files/ora.680136.208331.0.log ?

The contents of the file '/dsdata/Scratch/Files/ora.680136.208331.0.log ' are as specifed below:


OPTIONS(DIRECT=TRUE, PARALLEL=FALSE, SKIP_INDEX_MAINTENANCE=YES)

LOAD DATA INFILE 'ora.475292.268054.fifo.0' "FIX 331"
APPEND INTO TABLE ODS.BALANCE_ACTIVITY
(
BALANCE_ACTIVITY_ID POSITION(1:7) DECIMAL(12,0) ,
BALANCE_REF_ID POSITION(8:14) DECIMAL(12,0) ,
BALANCE_DTM POSITION(15:33) DATE "YYYY-MM-DD HH24:MI:SS" ,
BALANCE_PROC_NM POSITION(34:85) VARCHAR(50) ,
BALANCE_STATUS POSITION(86:112) VARCHAR(25) ,
FIRST_BALANCE_VAL POSITION(113:121) DECIMAL(17,2) ,
SECOND_BALANCE_VAL POSITION(122:130) DECIMAL(17,2) ,
REF_NM POSITION(131:182) VARCHAR(50) NULLIF (133:182) = BLANKS,
REF_ID POSITION(183:189) DECIMAL(12,0) NULLIF (183:189) = X'00000000000000',
CREAT_DT POSITION(190:208) DATE "YYYY-MM-DD HH24:MI:SS" ,
CREAT_USER POSITION(209:260) VARCHAR(50) ,
LAST_UPDT_DT POSITION(261:279) DATE "YYYY-MM-DD HH24:MI:SS" NULLIF (261:279) = BLANKS,
LAST_UPDT_USER POSITION(280:331) VARCHAR(50) NULLIF (282:331) = BLANKS

)

[CGI_SUREN]

OK, is there anything interesting in /dsdata/Scratch/Files/ora.680136.208331.0.log ?

The contents of the file '/dsdata/Scratch/Files/ora.680136.208331.0.log ' are as specifed below:


OPTIONS(DIRECT=TRUE, PARALLEL=FALSE, SKIP_INDEX_MAINTENANCE=YES)

LOAD DATA INFILE 'ora.475292.268054.fifo.0' "FIX 331"
APPEND INTO TABLE ODS.BALANCE_ACTIVITY
(
BALANCE_ACTIVITY_ID POSITION(1:7) DECIMAL(12,0) ,
BALANCE_REF_ID POSITION(8:14) DECIMAL(12,0) ,
BALANCE_DTM POSITION(15:33) DATE "YYYY-MM-DD HH24:MI:SS" ,
BALANCE_PROC_NM POSITION(34:85) VARCHAR(50) ,
BALANCE_STATUS POSITION(86:112) VARCHAR(25) ,
FIRST_BALANCE_VAL POSITION(113:121) DECIMAL(17,2) ,
SECOND_BALANCE_VAL POSITION(122:130) DECIMAL(17,2) ,
REF_NM POSITION(131:182) VARCHAR(50) NULLIF (133:182) = BLANKS,
REF_ID POSITION(183:189) DECIMAL(12,0) NULLIF (183:189) = X'00000000000000',
CREAT_DT POSITION(190:208) DATE "YYYY-MM-DD HH24:MI:SS" ,
CREAT_USER POSITION(209:260) VARCHAR(50) ,
LAST_UPDT_DT POSITION(261:279) DATE "YYYY-MM-DD HH24:MI:SS" NULLIF (261:279) = BLANKS,
LAST_UPDT_USER POSITION(280:331) VARCHAR(50) NULLIF (282:331) = BLANKS

)

[ray.wurlod]

Nothing more? There's usually a summary at the end of what occurred. Is there a "bad" file in the same location? Are you certain you haven't posted the CTL file?