Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Passing value from a PL/SQL variable to a SQL*Plus variable

Re: Passing value from a PL/SQL variable to a SQL*Plus variable

From: Kevin P. Fleming <kfleming_at_access-laserpress.com>
Date: 1998/06/24
Message-ID: <35904ACA.C1AD05AD@access-laserpress.com>#1/1

You're going to have to build a dynamic SQL statement and execute that to do your updates, instead of trying to get SQL*Plus to do it. Look into the DBMS_SQL package, it will do what you want.

Matt Brennan wrote:
>
> 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;
> end;
> /
> undefine s_table_name
> undefine s_column_name
> --
> 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 Wed Jun 24 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US