Routine to read the value from oracle

shrey3a · Post by **shrey3a** » Fri Jun 03, 2005 10:49 am

Hi,

I want to read the value from oracle thro' Datastage routine i.e. I have oracle table in which there is only one row for each object type. As per my design I want to read the value thro' a routine and then pass it to sequencer job as an return value.

Regards,

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jun 03, 2005 2:27 pm

Look for DSExecute.

shrey3a · Post by **shrey3a** » Fri Jun 03, 2005 3:32 pm

Hi ,

I tried looking for code/routine which can be executed from DS enviorment to fetch the value from oracle table but was not able to find.

Can anybody help on this..

Thanks in advance...

Regards

Sainath.Srinivasan wrote:Look for DSExecute.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 03, 2005 5:50 pm

It can be done in a number of ways.

BCI functions mimic the ODBC API (search for BCI) but you need a licensed ODBC driver.

The routine can run a job that fetches the data from Oracle into, say, a text file then reads the value from there.

Why do you believe you need a routine? Why not pre-load a hashed file containing just one row with that value. The key for the hashed file is a constant, and is accessible in any job that needs the value by performing a reference lookup using that constant as the reference key expression. Way more efficient, and you remain within DataStage's graphical paradigm.

This approach naturally extends to storing a finite set of specific returned values.