By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,196 Members | 973 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,196 IT Pros & Developers. It's quick & easy.

updating all tables with a column column

P: n/a
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'
/
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.