Re: Dynamic SQL-Command in PL/SQL

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 25 Feb 2005 06:43:39 -0800
Message-ID: <1109342619.404512.232440@z14g2000cwz.googlegroups.com>

COAST wrote:
> It's not what I want.
>
> If myVar is null, I want ALL records otherwise I want only those
records matching to myVar.
>
> Peter

SQL> create table foo (test number);

Table created.

SQL> insert into foo values (1);

1 row created.

SQL> insert into foo values (2);

1 row created.

SQL> insert into foo values (2);

1 row created.

SQL> insert into foo values (1);

1 row created.

SQL> set serverout on
SQL> ed
Wrote file afiedt.buf

  1 declare
  2 test_ number := null;
  3 begin
  4 for rec_ in (select test from foo where to_char(test) like nvl(to_char(test_), '%')) loop

  5        dbms_output.put_line(rec_.test);
  6     end loop;

  7* end;
SQL> /
1
2
2
1

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1 declare
  2 test_ number := 1;
  3 begin
  4 for rec_ in (select test from foo where to_char(test) like nvl(to_char(test_), '%')) loop

  5        dbms_output.put_line(rec_.test);
  6     end loop;

  7* end;
SQL> /
1
1

PL/SQL procedure successfully completed.

Regards
/Rauf Received on Fri Feb 25 2005 - 08:43:39 CST