Hi,
Is there any way to handle dynamic queries in a oracle stage? For example, I have a server job say Job1 whose source oracle stage has a query to fetch some number of records. Now, I will run a unix script to execute my jobs starting with Job1 and rest all jobs follow this. Now, is it possible to form the query in the Job1's oracle stage based on some parameters from unix script?
I have tried copying the same job with a different name and tried to use job parameters to form the query and couldn't succeed.
Could anybody clarify the approach to be followed in terms of DataStage for this scenario? (Probably this scenario could be represented in a different way when we think this design in DataStage)
Thanks in advance,
Srinivas.
Dynamic query in oracle stage
Moderators: chulett, rschirm, roy
-
dlvsrinivas
- Participant
- Posts: 18
- Joined: Mon Jul 07, 2003 12:56 am
- Location: India
Srinivas
Send the whole SQL statement in as a parameter. You would always have to have to same number of columns and of the same type but you could have them all be of type varchar.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Send the whole SQL statement in as a parameter. You would always have to have to same number of columns and of the same type but you could have them all be of type varchar.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
dlvsrinivas
I have no experience with oracle stages, but I would be very surprised if it wasn't possible to form the query using job parameters, as this is a very common in all kind of stages that I use. The main difference is between active stages (e.g. transformer stage, aggregator stage)and passive stages (e.g. odbc stage, sequential file stage), 'cause in passive stages you would have to surround a job parameter with hashes, e.g. #PARAMNAME# (probably hard to find out as no expression editor is available from the shortcut menu in passive stages). Sometimes you would even have to surround this with single or double quotes.
As for the derivation of values used as parameters for jobs, I usually perform this within a controlling job. Evoking UNIX scripts within a controlling job (using DSExecute for example) that help to derive values is a legal practice, afaik.
Hope this helps!
Stephan
I have no experience with oracle stages, but I would be very surprised if it wasn't possible to form the query using job parameters, as this is a very common in all kind of stages that I use. The main difference is between active stages (e.g. transformer stage, aggregator stage)and passive stages (e.g. odbc stage, sequential file stage), 'cause in passive stages you would have to surround a job parameter with hashes, e.g. #PARAMNAME# (probably hard to find out as no expression editor is available from the shortcut menu in passive stages). Sometimes you would even have to surround this with single or double quotes.
As for the derivation of values used as parameters for jobs, I usually perform this within a controlling job. Evoking UNIX scripts within a controlling job (using DSExecute for example) that help to derive values is a legal practice, afaik.
Hope this helps!
Stephan
-
vmcburney
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
You have two options. You can pass in all or part of the statement as job parameters or you can rebuild the full SQL statement and output it to a Unix text file which the stage will open and execute.
The Oracle OCI stage has three options for creating SQL:
- Generated SQL will automatically create the select statement. You can add to the where clause.
- User defined SQL lets you define the entire SQL statement.
- User defined SQL File will open a sql script in your server file system and execute that SQL. With this option you can generate a sql statement from your calling Unix script and then run the job.
Job parameters is the easiest way to create dynamic SQL. You set your job parameters from the command line when you run DSJob:
dsjob -run -param updatedate=currentupdatedate
In the Oracle OCI stage you add a where clause that uses this job parameter updatedate.
WHERE clause:
UpdateDate > #updatedate#
Vincent McBurney
Data Integration Services
www.intramatix.com
The Oracle OCI stage has three options for creating SQL:
- Generated SQL will automatically create the select statement. You can add to the where clause.
- User defined SQL lets you define the entire SQL statement.
- User defined SQL File will open a sql script in your server file system and execute that SQL. With this option you can generate a sql statement from your calling Unix script and then run the job.
Job parameters is the easiest way to create dynamic SQL. You set your job parameters from the command line when you run DSJob:
dsjob -run -param updatedate=currentupdatedate
In the Oracle OCI stage you add a where clause that uses this job parameter updatedate.
WHERE clause:
UpdateDate > #updatedate#
Vincent McBurney
Data Integration Services
www.intramatix.com