Is there is anyway we can get C$_ table name and drop it, in case if there is any error?I need to drop it as part of error log mechanisam?
I want to do it through ERROR LOG ODI procedure..
In your IKM, you can create an extra step that loads some technical information (like the C$ table) in a special table.
To retrieve the C$ table, you must use some ODI API method.
select <%=odiRef.getTable("L", "COLL_NAME","W")%> from dual
select '<%=odiRef.getFrom()%>' from dual
In your special table, you will store name of Interface name, target table, C$, I$ table and whatever you want.
Then, with ODI variable, you will be able to retrieve what you want.
Then, in your package, create an error flow (with red arrow) that will execute and ODI procedure that deletes the tables.
Grab the C$ within the interface and pass it to a stored proc (execute immediate) to drop it, nice and simple - unless you want the C$ to persist after the interface?
Alternatively, just set all your steps to Warning and have the drop work table as one of the final steps.