Hello!
I have the usual job stream structure
Source Stage -> Transformer stages -> Target Stage
I am getting some correct arguments - Arg1, Arg2, only during the Transformer stages phase of a Job.
My goal is to issue a custom SQL query:
Select Owner.FunctionName(Arg1, Arg2) from Dual
to get a single-row result from a FunctionName and then to be able to use it in downstream Stages.
That's why this query should be issued during the Transformer stages phase and I cant do that during the Source Stage for example.
So my question is - how it is possible to make this query and with which stage?
May be it is possible to make a custom SQL query in Transformer Stage?
Make custom SQL query during a Job flow
Moderators: chulett, rschirm, roy
-
Sreenivasulu
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Hi Sreeni! Thanks for Your reply.
Pls let me clearify some details.
Did you mean that Stored Procedure Stage can be used also for calling a DB function?
I was not sure about this and previously tried to specify my funtion inside the SP stage properties and have got some errors.
But I'll try one more time to use SP Stage for a function.
Ofcourse, the workable solution is to make a custom SQL with a DB function in the Source stage, but the problem is that the data itself - resides in one DB and should be transformed to be
ready to become an arguments, and the function - is in another DB.
Pls let me clearify some details.
Did you mean that Stored Procedure Stage can be used also for calling a DB function?
I was not sure about this and previously tried to specify my funtion inside the SP stage properties and have got some errors.
But I'll try one more time to use SP Stage for a function.
Ofcourse, the workable solution is to make a custom SQL with a DB function in the Source stage, but the problem is that the data itself - resides in one DB and should be transformed to be
ready to become an arguments, and the function - is in another DB.
Finally got it working!
I have imported a Stored Procedure Definition through the:
Import - > Table Definitions - > Stored Procedure Definitions
On the General Tab, I set
Data Source = my DB TNS record value (which is in server's tnsnames.ora)
Username / Password - it is obvious )
DataBase Name = SERVICE_NAME = xxx (from TNS record)
On the Parameters Tab
Arg1 - Type = Input
Arg2 - Type = Input
Arg3 - Type = Output
All parameters were named as they are inside the DB function, but may be will work with other names, i hadn't check.
Also Maps to correct and existing columns were made here.
On the Syntax Tab
Procedure Name - Picked up the procedure definition, which was imported earlier.
I have dechecked "Generate Procedure Call" option,
Procedure Call Syntax for my case appeared to be the following:
"BEGIN :3 := GET_ENC_VAL(:1, :2); END;"
Procedure type is set to "Transform".
After all, I've got the correct result, seems that SP Stage could be used to call DB Functions.
Thanks!
I have imported a Stored Procedure Definition through the:
Import - > Table Definitions - > Stored Procedure Definitions
On the General Tab, I set
Data Source = my DB TNS record value (which is in server's tnsnames.ora)
Username / Password - it is obvious )
DataBase Name = SERVICE_NAME = xxx (from TNS record)
On the Parameters Tab
Arg1 - Type = Input
Arg2 - Type = Input
Arg3 - Type = Output
All parameters were named as they are inside the DB function, but may be will work with other names, i hadn't check.
Also Maps to correct and existing columns were made here.
On the Syntax Tab
Procedure Name - Picked up the procedure definition, which was imported earlier.
I have dechecked "Generate Procedure Call" option,
Procedure Call Syntax for my case appeared to be the following:
"BEGIN :3 := GET_ENC_VAL(:1, :2); END;"
Procedure type is set to "Transform".
After all, I've got the correct result, seems that SP Stage could be used to call DB Functions.
Thanks!