I'm trying to pull data from a set of oracle tables (I've setup separate DTS
packages to pull data from each of oracle tables I work with) but what I need
to do is setup exclusive locks on those oracle tables whilst the DTS package
is runing. I was wondering whether this is possible using DTS?

DTS is only a UI layer in this case on top of the Oracle driver. Forget DTS
for a second how would you do this in Oracle? DTS is merely a client that
will go and get data from the Oracle client nothing else and as such if you
can lock the tables using the ORacle PLSQL then you can do this in DTS

That said from whom are you trying to lock the tables? Another DTS process?
If it is then you could write a row to an audit table when you start the
process and update it when you are finished. A new process would check that
table to see if the table they wanted was being accessed.





--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"EL" <EL (AT) discussions (DOT) microsoft.com> wrote

Thanks for your reply Allan, I should probably have been clearer in my first
post.

First of all, the reason for the locking of the relevant oracle tables is
that two separate processes would be performing various actions on these
tables (my dts process and an oracle process) which should not clash.

If I was to do the entire process from within Oracle then I would write a
set of commands in the following order:

1. place an exclusive lock on the relevant oracle table
2. perform a select of all records in the table (at this point a transfer to
SQL server where DTS sits occurs)
3. truncate of the oracle table
4. oracle command to close the lock

The above steps would all run within the same task / session within oracle.

My guess is that it's not possible to do the same thing using just a
Transform Data Task between SQL Server and Oracle. I therefore thought that I
could perform a test to see whether I could lock an oracle table from within
the DTS in the following order (I also have two database connections pointing
to the local Sql Server and the Oracle Server respectively):

1st task: place an exclusive lock on the oracle table (on the Oracle Side)
2nd task: Perform a wait command ('Waitfor delay '00:10:00') (on Sql Server
side)
3rd task: Close the exclusive lock (on the oracle side)

While this package was running, I tested whether I could also insert records
into the same Oracle table from a separate session. Annoyingly, this actually
worked and records were being inserted (whilst the DTS was performing Task
2). I'm assuming that, as each of the commands have now been placed in
separate tasks, each task is treated as a separate transaction?

Thanks

Eric


"Allan Mitchell" wrote: