dynamic sql in oci stage.

kollurianu · Post by **kollurianu** » Mon Nov 19, 2007 3:33 pm

Hi all ,

Is it possible in datastage to create a dynamic sql in oci stage .
Appreciate your inputs and help.

thanks to all

chulett · Post by **chulett** » Mon Nov 19, 2007 3:58 pm

Depends on your definition of 'dynamic'. What are you trying to accomplish?

chulett · Post by **chulett** » Mon Nov 19, 2007 4:26 pm

The closest you'll get to 'dynamic' (IMHO) is the Update Action of 'Load SQL from a file at run time'. Then the area where you would normally put your user-defined SQL holds the actual pathname of the file to load. Something to note, however, if you do that. While you can use job parameters for the filename itself, the actual SQL loaded from a file cannot contain any Job Parameters.

Your other option is to pass the SQL, or perhaps just a signficant portion of the WHERE clause, in as a Job Parameter and then bind that parameter into your generated SQL.

rleishman · Post by **rleishman** » Mon Nov 19, 2007 8:17 pm

An inferior solution (but possible) is use a user-defined SQL that calls a stored procedure, which in turn generates dynamic SQL in PL/SQL.

kollurianu · Post by **kollurianu** » Tue Nov 20, 2007 1:36 pm

Thank you all for your inputs.,