Re: How to do a subquery

I just wanted to make sure nobody laughs at me if I use dynamic SQL becuase there is an easier solution. :-)

Actually based on your post, I just learned about "Native Dynamic SQL." Neat stuff. I would have used the methods available in the DBMS_SQL package, but it looks easy without it. Would I do

v_sql varchar2(300);
declare cursor c is
select table_name from user_tab_columns where column_name='DOMAIN'; begin
for r in c loop

    v_sql = 'update ' || r.table_name || ' set domain='new_value' where domain='old_value';

    execute immediate v_sql
end loop;
end;

(this has 10 lines. :-)). This calls into question on how to escape single quotes (') in PL/SQL. I guess I could bind those values, but how do you escape the single quote characters in the string?

Anyway, is this what you were thinking of?

Thanks

"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message news:<3C9CECC1.7C593E0B_at_exesolutions.com>...
> I can't imagine anything easier than native dynamic SQL.
>
> It can be done in a loop with only about a dozen lines of code.
>
> You want easier than that?
>
> Daniel Morgan
>
>
>
> Steve wrote:
>
> > I have a need to write a query that updates every table in a schema
> > that contains the column name "domain" (a VARCHAR2).
> >
> > From the data dictionary I know that about 35 tables (out of 70) in my
> > schema have the column name "domain". So I want to update all rows
> > where the "domain" field equals some value.
> >
> > I know I can do this using the dynamic sql package, but is there an
> > easier subquery that I can do? I tried
> >
> > SQL> update (select table_name from user_tab_columns where
> > column_name='DOMAIN') set domain='new_value' where domain='old_value'
> >
> > but that doesn't compile. I think the subquery is illegally formed.
> > I would like to avoid using the dynamic sql package if I can. Any
> > takers?
Received on Sun Mar 24 2002 - 12:29:39 CST