Re: How to do a subquery

To escape single quotes (') in PL/SQL, you have to double the quotes like this : ''new_value''.

Another solution without PL/SQL, you can use the generic sql in your oracle session, for example :
SQL> spool upd.sql
SQL> select 'update ' || table_name || ' set domain = ''new_value'' where domain =''old_value'' ; '

           from user_tab_columns where column_name='DOMAIN' ;

SQL> spool off
SQL>@upd.sql
SQL> commit ; -- if you want
SQL> exit

regards,

T.A

"Steve" <stevenmgarcia_at_hotmail.com> a écrit dans le message news: 4c049a8c.0203241029.1e0c1c32_at_posting.google.com...
> 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 Mon Apr 01 2002 - 03:42:16 CST