Customized SQL in Oracle_Enterprise stage

lindatgeorge · Post by **lindatgeorge** » Fri Jul 03, 2009 8:10 am

Hi,

I need to truncate and load the Oracle table in a parallel job using Oracle_Enterprise stage. While loading I need to pass customized SQL also.But I could not find any options for the same in Oracle enterprise stage.

Could you please help me on this.

Thanks in advance...

Linda

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jul 03, 2009 8:13 am

Do it in two steps - one truncate and custom SQL.

Btw, what you expect to do as 'custom' ?

chulett · Post by **chulett** » Fri Jul 03, 2009 8:14 am

The answer will also depend on what kind of 'load' you are referring to.

priyadarshikunal · Post by **priyadarshikunal** » Fri Jul 03, 2009 8:31 am

Load mode allows you to truncate the table before loading but restricts from using customized sql.

Upsert mode allows to write customized SQL but restricts for using truncate option like load.

So its better if you truncate the table in before job subroutine and use customized query in OE stage using upsert mode.

However it depend on kind of load and also the requirements.

lindatgeorge · Post by **lindatgeorge** » Fri Jul 03, 2009 8:32 am

I need to load the tables daily. But before loading the data, I need to truncate the data, so that only one day's data will be present there.
Also while inserting data I am getting value from one oracle source table and few parameters. So I need to refer the parameters also in the insert statement. That is what I meant by customized SQL.

Please let me know if you need any more info.

thanks...

priyadarshikunal · Post by **priyadarshikunal** » Fri Jul 03, 2009 8:36 am

Can't you use those parameters inside the job itself?

Provide more information and you may get a better logic to implement the same.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jul 03, 2009 8:38 am

Design the job to perform reference activity such that the load is only 'load with truncate'.