Re: How to do a subquery

On 24 Mar 2002 10:29:39 -0800, stevenmgarcia_at_hotmail.com (Steve) wrote:

>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

Actually, you *should* bind those values unless you definitely want your server to become cpu-bound, so execute immediate v_sql using etc. Other than that you can escape a single quote by an extra single quote.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Mar 24 2002 - 13:54:13 CST