Invoking oracle stored procedure from DataStage

Post by **admin** » Tue Jul 24, 2001 12:16 pm

Hi,
I am trying to call an Oracle stored procedure from DataStage using the ODBC stage which apparently supports this. However, I am unable to get it working. If someone has got this to work, can you please explain the steps in detail? I am trying to call a simple Oracle procedure which has just one output parameter of type NUMBER. If there are any other ways to get this to work (OCI stage or something else), please let me know.

Thanks in advance,
Derick

_________________________________________________________
For Rs. 20,00,000 worth of Aptech scholarships click below http://events.rediff.com/aptechsch/scholarship.htm

Post by **admin** » Tue Jul 24, 2001 8:46 pm

you may try to call store procedure in OCI directly, choose Updated Action as "User-defined SQL", then call your store procedure. call
owner.st_name(params)

xiong
> -----Original Message-----
> From: derick j JOSE [SMTP:datastage@rediffmail.com]
> Sent: Wednesday, 25 July 2001 00:16
> To: datastage-users@oliver.com
> Subject: Invoking oracle stored procedure from DataStage
>
> Hi,
> I am trying to call an Oracle stored procedure from DataStage using
> the ODBC stage which apparently supports this. However, I am unable
> to get it working. If someone has got this to work, can you please
> explain the steps in detail? I am trying to call a simple Oracle
> procedure which has just one output parameter of type NUMBER. If
> there are any other ways to get this to work (OCI stage or something
> else), please let me know.
>
> Thanks in advance,
> Derick
>
>
> _________________________________________________________
> For Rs. 20,00,000 worth of Aptech scholarships click below
> http://events.rediff.com/aptechsch/scholarship.htm
>
>

Post by **admin** » Tue Jul 24, 2001 10:49 pm

If you have an Ardent Before/After subroutine called ExecSQL (or similar),
you can use it as a before (or after) job subroutine. In the argument box put :

PassiveStageName; call ProcName(Param)

In the passive stage (odbc) you have to give the connection parameters (DSN
and so on).

I think it is also possible to use the command call ProcName(Param) in
the odbc stage, in a user-defined sql query, but perhaps you have to put
this command into braces {. Never tested it.

Hope it helps

Kasia

At 13:16 24/07/2001, you wrote:
>Hi,
> I am trying to call an Oracle stored procedure from DataStage using
> the ODBC stage which apparently supports this. However, I am unable to
> get it working. If someone has got this to work, can you please explain
> the steps in detail? I am trying to call a simple Oracle procedure which
> has just one output parameter of type NUMBER. If there are any other
> ways to get this to work (OCI stage or something else), please let me know.
>
>Thanks in advance,
>Derick
>
>
>_________________________________________________________
>For Rs. 20,00,000 worth of Aptech scholarships click below
>http://events.rediff.com/aptechsch/scholarship.htm

Post by **admin** » Wed Jul 25, 2001 1:46 am

Have you IMPORTED the stored procedure definition?

-----Original Message-----
From: derick j JOSE [mailto:datastage@rediffmail.com]
Sent: Tuesday, 24 July 2001 22:16
To: datastage-users@oliver.com
Subject: Invoking oracle stored procedure from DataStage

Hi,
I am trying to call an Oracle stored procedure from DataStage using the ODBC stage which apparently supports this. However, I am unable to get it working. If someone has got this to work, can you please explain the steps in detail? I am trying to call a simple Oracle procedure which has just one output parameter of type NUMBER. If there are any other ways to get this to work (OCI stage or something else), please let me know.

Thanks in advance,
Derick

_________________________________________________________
For Rs. 20,00,000 worth of Aptech scholarships click below http://events.rediff.com/aptechsch/scholarship.htm