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 "...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_n ame||',
''1234567890'',''9732581406''));'
from user_tab_columns where column_name = 'SSN'
/

[Bob Hairgrove]

On Fri, 30 Jan 2004 22:28:59 -0700, "Shawn" <sp*******@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
No**********@Home.com