updating all tables with a column column

[Shawn]

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 "...rollbac k
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.s ql
select 'update '||table_name|| ' set '||column_name| |' = TRANSLATE
(''915482376'', ''123456789'',T RANSLATE('||col umn_name||',
''1234567890'', ''9732581406'') );'
from user_tab_column s where column_name = 'SSN'
/

[Bob Hairgrove]

Re: updating all tables with a column column

On Fri, 30 Jan 2004 22:28:59 -0700, "Shawn" <spedwards@qwes t.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 "...rollbac k
>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.s ql
>select 'update '||table_name|| ' set '||column_name| |' = TRANSLATE
>(''915482376'' ,''123456789'', TRANSLATE('||co lumn_name||',
>''1234567890'' ,''9732581406'' ));'
>from user_tab_column s where column_name = 'SSN'
>/
>

Check out EXECUTE IMMEDIATE in the documentation.


--
Bob Hairgrove
NoSpamPlease@Ho me.com