RE: Preventing select on table

Ok, so now you have learnt the lesson that not to define a primary key based on any attributes.

is it ok for ur customer if the values r updated at night ? if so, u can add a column to the table, say "new_customer_id", and then at night run thru a huge update to replace all the keys in referenced tables. if this is an acceptable soln, i would further suggest writing a procedure which uses dynamic sql to read the oracle data dictionary and updating all the tables using this key. also, u can use bulk binds to get ur updates done faster.

and the hard way which u r saying is really not that hard. u would just need a wknd and get all ur updates run.

hope this helps !

-----Original Message-----
Sent: Monday, February 17, 2003 5:39 AM
To: Multiple recipients of list ORACLE-L

> We have a database application that uses a primary key field value of
> CUSTOMER in several tables, and as a foreign key in others. Everything
> works fine, but the customer has come up with a new requirement - they
> wish to be able to rename these primary keys values on the fly.
>
> I'm looking for a simple way to do this, the problem is simply issuing a
> load of updates would be a risky business because what could happen is ...
>
> 1) Part of the application reads a CUSTOMER value using a SELECT
> statement and stores the value in a variable.
> 2) I do the big update renaming CUSTOMER values everywhere they are
> used & commit.
> 3) The part of the application that still has the old values stored in
> a variable and then writes the old value to a new record and the database
> ends up with a mix of old and new values.
>
> One way to prevent this would be to lock all tables I want to update in 2)
> before doing anything. This will only work if the lock prevents all SELECT
> statements from working until I am done.
>
> From answers given on this list, it seems that SELECTs cannot be prevented
> in this way. We are also not using any referential integrity constraints
> so the update in 3) would work fine.
>
> Can anyone think of a simple solution, or do I have to do this the hard
> way (rewrite whole thing to use a sequence number as the primary key, and
> have Customer as an updatable bit of text).
>
> -----Original Message-----
> From: John Dunn
> Sent: 17 February 2003 08:58
> To: Jamie Pearce
> Subject:
>
> << Message: Re: Lock table and disallow select >> << Message:
> Re:Lock table and disallow select >> << Message: RE: Lock table and
> disallow select >> << Message: Re:Lock table and disallow select >> <<
> Message: Re: Lock table and disallow select >> << Message: RE: Lock table
> and disallow select >> << Message: RE: Lock table and disallow select >>
> << Message: Re: Lock table and disallow select >> << Message: Re: Lock
> table and disallow select >> << Message: RE: Lock table and disallow
> select >>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Dunn
  INET: john.dunn_at_sefas.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <netmadcap_at_netzero.com
  INET: netmadcap_at_netzero.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).