Alternate way to insert and read data from Oracle db

Dedicated to DataStage and DataStage TX editions featuring IBM<sup>®</sup> Service-Oriented Architectures.

Moderators: chulett, rschirm

Post Reply
ahmedwaseem2000
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 15, 2006 12:23 pm

Alternate way to insert and read data from Oracle db

Post by ahmedwaseem2000 »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Re: Alternate way to insert and read data from Oracle db

Post by chulett »

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.
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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ahmedwaseem2000
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 15, 2006 12:23 pm

Re: Alternate way to insert and read data from Oracle db

Post by ahmedwaseem2000 »

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?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Can you not make the job 'always on'?

(and off we go to a more appropriate forum)
-craig

"You can never have too many knives" -- Logan Nine Fingers
ahmedwaseem2000
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 15, 2006 12:23 pm

Post by ahmedwaseem2000 »

Yes, Job is always on through the web service. all other connector stages maintain a persistent session while the stored proc gets disconnected as soon as the request is sent out.
vmcburney
Participant
Posts: 3593
Joined: Thu Jan 23, 2003 5:25 pm
Location: Australia, Melbourne
Contact:

Post by vmcburney »

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.
Post Reply