"User Defined SQL Query File" in a ORAOCI8 Plug_In

eoyylo · Post by **eoyylo** » Mon Mar 01, 2004 12:59 pm

Hi,
i'd like to use an oraoci8 plug_in with the option "User Defined SQL Query File" because i have a very complicated query.
i set the options... but:
where can i specify the name and the directory of file with the query?

tanks in advance

Mario Loreti

ogmios · Post by **ogmios** » Mon Mar 01, 2004 1:01 pm

eoyylo wrote:Hi,
i'd like to use an oraoci8 plug_in with the option "User Defined SQL Query File" because i have a very complicated query.
i set the options... but:
where can i specify the name and the directory of file with the query?

tanks in advance

Mario Loreti

:D, No such thing. You enter the query in the stage itself.

Ogmios

chulett · Post by **chulett** » Mon Mar 01, 2004 1:15 pm

Sure there is! And yes, when you specify SQL Query File in the stage what you input is the full path to the file - in the same place that you would put any Custom SQL. You can also right-click on the stage and specify 'Grid Style', then where you put it is a little more obvious.

crouse · Post by **crouse** » Mon Mar 01, 2004 1:17 pm

I believe you type the entire path (directory and filename) in the space provided (where you would normally type the sql)

Remember, the file will need to be on the DataStage server (not the client).

ogmios · Post by **ogmios** » Mon Mar 01, 2004 1:29 pm

Never used it before, can't seem to find it in the Oracle Stage on my version. Under the SQL tab I only get "Generated", "User-defined", "Before", "After", and 2 DDL tabs (or I may need a new pair of glasses

).

Anyway I guess it would make moving jobs to production harder.

Ogmios

chulett · Post by **chulett** » Mon Mar 01, 2004 1:37 pm

It's always been there as far as I remember... back to 3.5 anyways.

And no, not harder if you do it right - just one more thing to parameterize.

vmcburney · Post by **vmcburney** » Mon Mar 01, 2004 5:01 pm

I've seen it used, it's handy if you want a DBA team to be responsible for your complex SQL queries. They own and maintain and optimise the SQL scripts and save them to a directory on the DataStage server, they don't need to do anything with with DataStage, they just have to make sure the column select list doesn't change. They can fix up statements without requiring a recompile of the DataStage job.

For simple queries I would avoid them, why have code sitting outside of DataStage? Just another thing to get lost or forgotten during delivery.

chulett · Post by **chulett** » Mon Mar 01, 2004 5:45 pm

vmcburney wrote:For simple queries I would avoid them, why have code sitting outside of DataStage? Just another thing to get lost or forgotten during delivery.

I wholeheartedly agree! I've used it (once!) in the past for dynamically generated SQL - one job generates the SQL statement and writes it to a file and the next runs using that SQL File. Not something to be doing on a regular basis unless you've got no other way to manage it. IMHO, of course.

crouse · Post by **crouse** » Mon Mar 01, 2004 5:58 pm

As a final (maybe) note, job parameters are not allowed in User-defined SQL Query files...

DSXchange

"User Defined SQL Query File" in a ORAOCI8 Plug_In

"User Defined SQL Query File" in a ORAOCI8 Plug_In

Re: "User Defined SQL Query File" in a ORAOCI8 Plu