Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Passing value from a PL/SQL variable to a SQL*Plus variable
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;
-- Matt Brennan SQL*Tools Specialist GERS Retail Systems 9725-C Scranton Road San Diego, California 92121 1-800-854-2263 mbrennan_at_gers.com (Original email address is spam-blocked.)Received on Tue Jun 23 1998 - 00:00:00 CDT