Re: Checking for deleted rows

It sounds like you want to construct a statement which (loosely) says:

select {list of keys on workstation}
MINUS
select list of keys on server

to produce an output of the list of keys you need to delete.

The viability of this depends on the version of Oracle you have got, and the tools to send the query and receive the result.

E.g. trivial example for a 2-column key and a handful of rows

(
select 99 server_col_name1,32 server_col_name2 from dual union all
select 46,91 from dual
union all
select 15,75 from dual
union all
select 39,66 from dual
)
MINUS
select server_col_name1, server_col_name2 from server_table
;

will do it for all versions of Oracle, but the length of the list may cause the query text to be too long.

With option 8 you may be able to define and populate an object type (see my website about PL/SQL and single variables being treated as a list of value) with all the local keys, then execute:

select * from THE (select cast (object bits) from dual) MINUS
select server_col_name1, server_col_name2 from server_table
;

The final option is to send a file of keys to the server, use sqlload to dump it into a table then do:

select server_col_name1, server_col_name2 from temp_table
MINUS
select server_col_name1, server_col_name2 from server_table
;

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Simon Place wrote in message
<937385403.11031.0.nnrp-03.d4f09432_at_news.demon.co.uk>...
>Thanks for your reply but it doesn't quite help. What I didn't make clear
is
>that the local client copy isn't part of an Oracle database but is just a
>copy of the data in a bespoke format but one from which I can delete data.
I
>can also pass a list of primary keys to an Oracle query and then use the
>results to delete data.
>
>Simon
>
>
>
>
Received on Wed Sep 15 1999 - 03:56:49 CDT