I have hundreds of SQL on a sequential file (all of them have same output columns). I am wondering how I can process them with a server job.
If I have a server job with the SQL statement and output filename as job parameters, is there a way to read the parameters from the sequential file and pass them to this job?
If I have to process the SQL in a routine activity, does anybody have an existing routine to connect to Oracle database? I am using DS version 7.5 with windows environment. The Oracle version is Oracle i9. In this case, I still need a way to read the parameters from the sequential file and pass them to the routine activity.
Yes it is possible. Create a server job where it has oracle plug-in as your source and the output text file. In your Oracle stage use user-defined sql and the output have a parameter defined.
Create a basic code to read your SQL statements from sequential file and pass it on the server job. loop the job so that it can execute all the sql's in your sequential file.
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Thanks for your reply. I have the server job with oracle plug-in as my source and the output text file ready. In my Oracle stage I used user-defined sql with parameters defined (#SQL_statement# and #Output_Filename#, and so on), and the job is working fine.
The bottleneck is how to pass the parameters. Because these are used as job parameters (in the job properties), I can not read them out and pass them to a stage in the same server job. Instead, I need to read them out in a server job and pass them to another server job through a job sequence (Am I right here?). The only way I know to pass parameters in a routine job is to scan the filenames in a folder and pass them to another server job. Can you give me some guidance to read my SQL statements from sequential file and pass it on the server job?
The more controlled way is to create a separate Job Control job which reads your sequential file to get the SQL statements and loops inside to call your server job.
For this you need to write BASIC codes.
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
I've never tried it, but could it be done with a Stored Procedure stage?
Pass the SQL Statement to the SP as a parameter, use dynamic SQL to execute it in a stored procedure, returning a cursor of known column names/types.
Other alternative to pass the parameter from the file. Use Execute command activity to read the file using 'head' as the command and -#StartLoop_Activity.$Counter# | tail -1 as the parameter to it.
and pass the outpu of this activity to the subsequent job through Command output. Place these arrangement in a loop through Start loop Activity. Loop activity should itrate for total number of recors in the file.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
I have hundreds of SQL on a sequential file (all of them have same output columns). I am wondering how I can process them with a server job.
