Re: Native Dynamic SQL in Oracle8i

I am very curious why you ever need to do this. Maybe you should provide more details as to why this is necessary. That said, you can do something like this perhaps?

SQL> select * from test2;

C1 C2
-- ----------

a           1
b           2
c           3
e           1
K           6
P           0
G           0
a           4
a           5
a           6
a           7

C1         C2
-- ----------
a           6
a           7
G           1
G           1

15 rows selected.

SQL> declare
  2 str1 varchar2(100);
  3 val1 varchar2(10):='G';
  4 begin
  5 str1 := 'execute immediate ''update test2 set c2=0 where c1=:val1''';
  6 val1 :='G';
  7 str1 := 'begin '||str1||' using '||''''||val1||''''||'; end;';   8 dbms_output.put_line(str1);
  9 execute immediate str1;
 10 end;
 11 /
begin execute immediate 'update test2 set c2=0 where c1=:val1' using 'G'; end;

PL/SQL procedure successfully completed.

SQL> select * from test2;

C1 C2
-- ----------

a           1
b           2
c           3
e           1
K           6
P           0
G           0
a           4
a           5
a           6
a           7

C1         C2
-- ----------
a           6
a           7
G           0
G           0

15 rows selected.

ryandocena_at_yahoo.com (Ryan Docena) wrote in message news:<829a27e7.0208211631.37dc14ca_at_posting.google.com>...
> Since Oracle8i's native dynamic sql is in this form:
>
> EXECUTE IMMEDIATE <sql-stmt>
> USING <var-to-bind-1> [...,<var-to-bind-n>]
>
> I've made even the 'USING' clause of the above statement dynamic
> because it isn't known till runtime.
>
> Now that all my dynamic sql (whole EXECUTE IMMEDIATE <sql-stmt>...
> USING <>) is in a variable, how can I invoke it to execute?
>
> Thanks.
Received on Thu Aug 22 2002 - 13:00:01 CDT