I have a script where I'm trying to read the all_tab_columns table to find
all the columns and the tables in which they reside so I can dynamically
have a SQL update statement that does the updating.
For example, I want to update zip codes in all the tables where zip code
resides from an existing zip code to a new zip code, so I hit
all_tab_columns and I find all the columns that have ZIP in the name and
the tables they live in and then for each row I find, I want to then run an
update statement for the table and column.
My problem is getting the values from all_tab_columns into the update
statement. It doesn't like my PL/SQL variables in an update statement in
place of the table name, so I tried bind variables because it sounds like
bind variables are from the SQL*Plus environment and I can get the PL/SQL
variable value into the bind variable, and then I was going to try to get
the bind variable's value into a SQL*Plus substitution variable (the ones
with the ampersand) and run the update, but I can't get that far. Maybe I
don't need bind variables at all - I don't know, but it seemed like a good
try for lack of something else.
Can anyone help? Thanks in advance!
Here's what I've done so far..., but I keep getting prompted for
s_table_name and s_column_name, which is what I don't want - I want values
for those passed in from PL/SQL or the bind variables which are populated
from PL/SQL.
variable b_table_name char (30)
variable b_column_name char (30)
define s_table_name = ":b_table_name"
define s_column_name = ":b_column_name"
begin
declare
temp_table_name char(30);
temp_column_name char(30);
temp_oldcode char(10);
temp_newcode char(10);
temp_data_length number;
cursor c1 is
select a.table_name, a.column_name, a.data_length, c.oldcode, c.newcode
from all_tab_columns a, db_conv_tp c, user_objects o
where c.convtype = 'ZCM'
and o.object_name = a.table_name
and o.object_type = 'TABLE'
and a.column_name like '%ZIP%';
begin
open c1;
loop
fetch c1 into temp_table_name, temp_column_name,
temp_data_length, temp_oldcode, temp_newcode;
:b_table_name := temp_table_name;
:b_column_name := temp_column_name;
exit when c1%notfound;
update &&s_table_name
set &&s_column_name = substr(temp_newcode,1,temp_data_length)
where &&s_column_name = temp_oldcode;
commit;
end loop;
close c1;
end;
