DSXchange

Hi all ,

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

thanks to all

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

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.

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.

Thank you all for your inputs.,