BULK Loading

[DWResource]

I get following error while trying to use BULK Loading.


Traget & Source DB : Oracle 9i
Client 9i

No PK/FK indexes on target table.

I have defined the qualifier in front of the source as well as target table.

Just loading data from source to target and BULK loading option FAILS.


If I change from "BULK" to "NORMAL", it works fine.

Any clues ??

Error I get from log is :


*****START LOAD SESSION*****

Load Start Time: Tue 27.09.2005 13:32:06

Target tables:

TEMP1


READER_1_1_1> RR_4010 SQ instance [SQ_IN_DSP_HISTORICAL_FORECAST_ARC] SQL Query [SELECT prod1.IN_DSP_HISTORICAL_FORECAST_ARC.FVERSION_MONTH, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.FVERSION_YEAR, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.FVERSION_TYPE, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.PLANNING_ITEM, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.FORECAST_MONTH, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.FORECAST_YEAR, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.FORECAST_QUANTITY, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.AREA, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.SDST_CODE, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.SOFF_CODE, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.CUSTOMER_NUMBER, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.NON_REV_UNITS, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.ORDER_INVOICE_LT, prod1.IN_DSP_HISTORICAL_FORECAST_ARC.LOAD_DATE FROM prod1.IN_DSP_HISTORICAL_FORECAST_ARC]
WRITER_1_1_1> WRT_8167 Start loading table [TEMP1] at: Tue 27.09.2005 13:32:06
WRITER_1_1_1> Tue 27.09.2005 13:32:06
WRITER_1_1_1> WRT_8229 Database errors occurred:
ORA-24329: invalid character set identifier

Database driver error...
Function Name : Prepare DPL
SQL Stmt : INSERT INTO test1.TEMP1(FVERSION_MONTH,FVERSION_YEAR,FVERSION_TYPE,PLANNING_ITEM,FORECAST_MONTH,FORECAST_YEAR,FORECAST_QUANTITY,AREA,SDST_CODE,SOFF_CODE,CUSTOMER_NUMBER,NON_REV_UNITS,ORDER_INVOICE_LT,LOAD_DATE) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Oracle Fatal Error
Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO test1.TEMP1(FVERSION_MONTH,FVERSION_YEAR,FVERSION_TYPE,PLANNING_ITEM,FORECAST_MONTH,FORECAST_YEAR,FORECAST_QUANTITY,AREA,SDST_CODE,SOFF_CODE,CUSTOMER_NUMBER,NON_REV_UNITS,ORDER_INVOICE_LT,LOAD_DATE) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Oracle Fatal Error
WRITER_1_1_1> Tue 27.09.2005 13:32:06
WRITER_1_1_1> WRT_8076 Writer run terminated. [Commit Error]
WRITER_1_1_1> WRT_8333 Rolling back all the targets due to fatal session error.
WRITER_1_1_1> WRT_8325 Final rollback executed for the target [TEMP1] at end of load
WRITER_1_1_1> Tue 27.09.2005 13:32:06
WRITER_1_1_1> WRT_8081 Writer run terminated. [Error in loading data to target table [TEMP1]]
WRITER_1_1_1> WRT_8168 End loading table [TEMP1] at: Tue 27.09.2005 13:32:06
WRITER_1_1_1> WRT_8035 Load complete time: Tue 27.09.2005 13:32:06

LOAD SUMMARY
============

WRT_8036 Target: TEMP1 (Instance Name: [TEMP1])
WRT_8044 No data loaded for this target



WRITER_1_1_1> WRT_8043 *****END LOAD SESSION*****
MASTER> PETL_24020

***** RUN INFO FOR TGT LOAD ORDER GROUP [1], SRC PIPELINE [1] *****
MASTER> PETL_24023 Thread [READER_1_1_1] created for the read stage of partition point [SQ_IN_DSP_HISTORICAL_FORECAST_ARC] has completed. The total run time was insufficient for any meaningful statistics.
MASTER> PETL_24025 Thread [WRITER_1_1_1] created for the write stage of partition point(s) [TEMP1] has completed. The total run time was insufficient for any meaningful statistics.
MASTER> PETL_24021 ***** END RUN INFO *****

MASTER> PETL_24005 Starting post-session tasks.
MASTER> PETL_24028 Post-session task completed with failure.
MAPPING> TM_6020 Session [S_BL_Test1] completed at [Tue 27.09.2005 13:32:06]
MAPPING> TM_6018 Session [S_BL_Test1] run completed with [0] row transformation errors.
MAPPING> TM_6022

SESSION LOAD SUMMARY
================================================

MAPPING> TM_6252 Source Load Summary.
MAPPING> CMN_1740 Table: [SQ_IN_DSP_HISTORICAL_FORECAST_ARC] (Instance Name: [SQ_IN_DSP_HISTORICAL_FORECAST_ARC])
Output Rows [3318], Affected Rows [3318], Applied Rows [3318], Rejected Rows [0]
MAPPING> TM_6253 Target Load Summary.
MAPPING> CMN_1740 Table: [TEMP1] (Instance Name: [TEMP1])
Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
MAPPING> TM_6023
===================================================

 

[blom0344]

Bulk load should evoke the native ORACLE SQL-loader.
Does this work like it should when you use the loader in ORACLE itself?
(sqlldr or sqlload utility)

Ties Blom
Information analyst

 

[DWResource]

Hi Sorry Ties Blom, got busy with other stuff.

Yes you are correct, it should be a straight oracle load.

But since Informatica provides nice GUI functionality so got spoiled now....

By the way,

I found out that ORACLE CLIENT Installed on Informatica SERVER is 8.1.7 and not 9i, so I am wondering that may be the reason as I see lot of other posts in Informatica KB as well where people mentioned having issues with BULK Loading if the ORACLE CLIENT ON INFORMATICA SERVER is 8.1.7.

And when they upgraded to ORACLE 9i it worked fine.

ANOTHER THING WHICH May be useful for people is that "NLS_LANG" variable in your registry under ORACLE Folder should be set to the same as the Character set of your database.


Which you can find out by running the query:

select * from v$nls_parameters.

If anybody has got the BULK Load working on ORACLE 8.1.7 Client on Informatica Server with 9i DB, pls let me know.

Thanks.