Stored Procedure with OCI8 Stage.

[admin]

--Next_1068287825---0-202.54.124.179-1972
Content-type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hey Gurus,=0A=0AIm using a Store Procedure (Update Action:Truncate and Inse=
rt only) in OCI8 Stage. But my Stored Procedure will run 13hrs atleast. Can=
anybody give better alternate solution to use Stored Procedures in a job b=
y using OCI8 only (no ODBC, Ofcourse It is possible with ODBC).=0A=0A(DataS=
tage 5.2 (Cilent/Server) in Windows 2000.=0A=0AThanks & Regards,=0AVenkat.
--Next_1068287825---0-202.54.124.179-1972--

[admin]

The DataStage server has the Oracle client installed,
obviously. Therefore, you have the ability to execute
sqlplus from the command line. Logic further extends
that you should be able to write a SQL script
containing your stored procedure call, or your
anonymous block stored procedure logic.

By using either a command stage in a sequencer, or a
BATCH style job with a DSExecute API call to execute a
system level command, you should be able to do what
you want.

-Ken



--- Venkat wrote:
> Hey Gurus,
>
> Im using a Store Procedure (Update Action:Truncate
> and Insert only) in OCI8 Stage. But my Stored
> Procedure will run 13hrs atleast. Can anybody give
> better alternate solution to use Stored Procedures
> in a job by using OCI8 only (no ODBC, Ofcourse It is
> possible with ODBC).
>
> (DataStage 5.2 (Cilent/Server) in Windows 2000.
>
> Thanks & Regards,
> Venkat.


__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

[admin]

Well, the obvious question to ask is where is the inefficiency?
How long does the stored procedure take if DataStage is not in the picture?
Can the stored procedure be written more efficiently?
Do you, in fact, need the stored procedure at all? What's wrong with having DataStage (through the OCI stage) insert rows directly into the Oracle table?

----- Original Message -----
From: "Venkat"
Date: 8 Nov 2003 10:37:05 -0000
To: datastage-users@oliver.com
Subject: Stored Procedure with OCI8 Stage.

> Hey Gurus,
>
> Im using a Store Procedure (Update Action:Truncate and Insert only) in OCI8 Stage. But my Stored Procedure will run 13hrs atleast. Can anybody give better alternate solution to use Stored Procedures in a job by using OCI8 only (no ODBC, Ofcourse It is possible with ODBC).
>
> (DataStage 5.2 (Cilent/Server) in Windows 2000.
>
> Thanks & Regards,
Venkat.