Hi,
I am a bit stuck to take the rows coming from a link, insert them and select the numbers generated by the stored proc. I know I can do this with the help of a stored proc as a tranform.
However, the issue is having a stored proc stage in the job would mean that every time the job is invoked it will login to the database regardless of data being pass through the link or not. which is what we are trying to avoid here.
using enterprise or the connector stage we can do a pl sql or close or open command but I cant have input and output link together in the same stage.
Please shed some lights if you know another way of invoking the stored proc by passing the rows and capturing the values returned by stored proc.
Thanks in advance.
Alternate way to insert and read data from Oracle db
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 15, 2006 12:23 pm
Re: Alternate way to insert and read data from Oracle db
You are trying to avoid the unavoidable. Every database stage in the job will connect, regardless of whether or not data passes (or will pass) down the link.ahmedwaseem2000 wrote:However, the issue is having a stored proc stage in the job would mean that every time the job is invoked it will login to the database regardless of data being pass through the link or not. which is what we are trying to avoid here.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 15, 2006 12:23 pm
Re: Alternate way to insert and read data from Oracle db
Perhaps, to add more to my description, it is an ISD job and In the connector stage there is an option to retain the session permanently. So, is there a similar way to deal with stored proc stage?
-
- Premium Member
- Posts: 145
- Joined: Fri Sep 15, 2006 12:23 pm
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Have you tried using the Oracle connector with the read mode of PL/SQL? This could then be used as a sparse lookup and you could include a condition on the lookup to only fire if all values are present.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn