Re: updating all tables with a column column

From: Bob Hairgrove <wouldnt_you_like_at_to_know.com>
Date: Sat, 31 Jan 2004 11:01:50 +0100
Message-ID: <p7vm10h3fi6dm1bs3roo6jhj19blrt9ssk_at_4ax.com>


On Fri, 30 Jan 2004 22:28:59 -0700, "Shawn" <spedwards_at_qwest.net> wrote:

>Hi,
>
>I am currently using the below script to generate another sql script to
>scramble all columns in our development databases.
>The problem I'm having are the larger tables are erroring with "...rollback
>segment...". So, it was suggested that I create
>a dynamic PL/SQL script to commit after every 1000 rows. They problem I'm
>having (not being a PL/SQL export for one),
>but apparently you can't use the table name as a variable. So, I'm having
>problems getting it started so I can update all
>tables with this common column. I'd appreciated any pointers.
>
>Thanks!
>
>
>set pages 0
>spo scramble_ssns.sql
>select 'update '||table_name||' set '||column_name||' = TRANSLATE
>(''915482376'',''123456789'',TRANSLATE('||column_name||',
>''1234567890'',''9732581406''));'
>from user_tab_columns where column_name = 'SSN'
>/
>

Check out EXECUTE IMMEDIATE in the documentation.

--
Bob Hairgrove
NoSpamPlease_at_Home.com
Received on Sat Jan 31 2004 - 11:01:50 CET

Original text of this message