Best practise for distributed transactions and oracle procedures.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ado (Adrian_donnelly_at_hotmail.com)
Date: 01/07/05 

Date: Fri, 7 Jan 2005 19:14:10 -0000

Hi All,
I would appreciate it if I could get some opinions on my proposed solution.
On a nightly basis I have to execute a batch job that would does the
following

1) Execute a Stored Procedure to return a recordset of IDs from a SQL2K DB
(Can be up to 750,000m rows) and insert these ids into a Oracle database.
2) Then call a oracle procedure that will validate these ids against its
tables, return any ids and exceptions message in a ref cursors where the ids
do not exist. This can be 100k + rows.
3) Insert these exceptions into a exception table in our SQL2K DB.
4) Execute another oracle procedure that transfroms the data into another
oracle table (a driver table) and return any exceptions.
5) Capture these exceptions and insert in a SQL DB.
6) Execute 15 different select statements against 15 different tables
filtering by the driver table created in step 4. Some of these select
statements can return upto 3m rows.

My solution is to create a DTS package and schedule it to run nightly. I am
using transfrom data task for step 1, (although I have read it might be
quicker to use linkedservers and write a distributed query to insert the ids
into the oracle table.) The only way I can think of running the Oracle
Procedure is by writing a ActiveX script using ADO to execute the Oracle
Procedure and returns the exception Ref Cursor to a recordset. I then loop
throught the recordset and insert the exceptions one at a time into our SQL
DB. This could potenially be very slow. Do I have any other options ?

When this ActiveX script completes I call another ActiveX script for step 4
to build the driver table. I am thinking of combining the two activeX
scripts into 1.
Finally I have set up 15 Transfrom Data tasks for each table I need to get
data from. The source is a SQL statement against the oracle tables filtered
by the driver table created in step 4.

So I really am just interested in peoples opinions on this. Is there a
better solution out there as I feel performance is going to be a problem
with potentiallly 10m+ rows going back and forward nightly. Any opinions
would be welcome. I hope the above makes sence.

Cheers
Adrian

Relevant Pages

  • Best practise for distributed transactions and oracle procedures.
    ... Execute a Stored Procedure to return a recordset of IDs from a SQL2K DB ... Then call a oracle procedure that will validate these ids against its ... Insert these exceptions into a exception table in our SQL2K DB. ...
    (microsoft.public.sqlserver.programming)
  • Re: Best practise for distributed transactions and oracle procedures.
    ... On a nightly basis I have to execute a batch job that would does ... > 4) Execute another oracle procedure that transfroms the data into another ... > oracle table and return any exceptions. ... > So I really am just interested in peoples opinions on this. ...
    (microsoft.public.sqlserver.dts)
  • Re: Best practise for distributed transactions and oracle procedures.
    ... On a nightly basis I have to execute a batch job that would does ... > 4) Execute another oracle procedure that transfroms the data into another ... > oracle table and return any exceptions. ... > So I really am just interested in peoples opinions on this. ...
    (microsoft.public.sqlserver.programming)
  • [NEWS] Multiple Vulnerabilities in Oracle Database (Trigger, Extproc, Wrapped Procedures, PL/SQL Inj
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... Multiple vulnerabilities were discovered in the Oracle database server. ... Oracle Trigger Abuse ... written in PL/SQL and execute with the privileges of the definer/owner. ...
    (Securiteam)
  • Re: Debug ORA-03113 on Oracle XE
    ... a laptop that runs the Oracle 10g Express ... The query returns no rows with the current data set but it's pretty ... If Oracle XE generates further info I don't know where I ... Usually, when you install Oracle, the UTL_FILE package is installed, EXECUTE is granted to PUBLIC, and a public synonym is created. ...
    (comp.databases.oracle.server)