I still have to figure out the internals of Informatica BULK target load type. How exactly it works, how it is different from its Normal load, and more importantly how its implementation is different from database to database. Today I have encountered with some issue with bulk load type enabled and ROLLBACKing the transaction (in Informatica) using Transaction Control transformation.
My idea is to issue ROLLBACK after inserting few records into the target. So, I have picked SCOTT.EMP table (in Oracle) as source with ORDER BY EMPNO.
As I wanted to issue ROLLBACK in the half i.e. after loading about 7 records out of 14, I created a Transaction Control Transformation with “IIF(EMPNO = 7782, TC_ROLLBACK_BEFORE)”
This failed with below ORA- error.

Rollback statistics:
WRT_8162 ===================================================
WRT_8330 Rolled back [6] inserted, [0] deleted, [0] updated rows for the target [EMP]

CMN_1022 [
ORA-24795: Illegal ROLLBACK attempt made

Database driver error...
Function Name : Commit
Database driver error...
Function Name : Rollback]

However, this worked well with Normal target load type. If Informatica’s bulk load type is like Oracle’s INSERT with  APPEND hint, direct transaction in Oracle should also fail, but that would not be the case. So, this makes me feel that Informatica has some special handling of BULK load type without completely throwing it to database side.

karteek@orcl10gr2>insert into /*+ APPEND */ emp select * from scott.emp;
14 rows created.
karteek@orcl10gr2>rollback;
Rollback complete.
karteek@orcl10gr2>

• APPEND hint makes the INSERT statement to write the data directly into the data blocks above the high water mark, and more over this supports only INSERT with SELECT clause, but not with VALUES clause where in we pass values for each record.
• Chances are there that Informatica is using an approach similar to BULK COLLECT INTO in Oracle. But, this might not be exactly the same approach, because BULK COLLECT is possible only in PL/SQL.

I think there are few questions that are left unanswered yet.

Update (03/08/2010): (per Srinivas comment below)

I am trying to load the below comma seperated file into a table using direct path enabled in sqlldr.
C:\Users\Karteek\test>type num.dat

1,3
2,4
3,7

SQL>desc tnum;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B NUMBER

SQL> select index_name,status from user_indexes where table_name = 'TNUM';
INDEX_NAME STATUS
------------------------------ --------
TNUM_PK VALID
SQL> select * from tnum;

no rows selected

SQL> $sqlldr karteek control=num.ctl direct=true
Password:
SQL*Loader: Release 10.2.0.3.0 - Production on Mon Mar 8 11:03:47 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Load completed - logical record count 3.

SQL> select * from tnum;

A B
---------- ----------
1 3
2 4
3 7

SQL> select index_name,status from user_indexes where table_name = 'TNUM';
INDEX_NAME STATUS
------------------------------ --------
TNUM_PK VALID

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Users\Karteek\test>

Happy Holidays!!
Karteek