Re: Bind variable as search pattern

From: Karsten Farell <kfarrell_at_medimpact.com>
Date: Tue, 24 Sep 2002 21:50:27 GMT
Message-ID: <D65k9.1234$dX5.28637338@newssvr14.news.prodigy.com>

I don't think you need to use a bind variable. Try something like this:

---

create or replace procedure test5
(p1 IN varchar2)
IS

   v_counter number;
   v_prop prop%rowtype;
   CURSOR block_cursor (p1cur in varchar2) IS    select * from my_table where upper(location) like '%p1cur%';

begin

v_counter:=0;

  open block_cursor (p1);
  loop
  fetch block_cursor into v_prop;
  exit when block_cursor%rowcount>5 or

           block_cursor%notfound;
  end loop;

close block_cursor;
end test5;

---

Basically, I only added a cursor parameter and passed the procedure parameter to it when I opened it. You could also shorten the procedure to something like:

---

create or replace procedure test5 (

   p1 in varchar2
) is

   cursor block_cursor (p1cur in varchar2) is      select * from my_table where upper(location) like '%p1cur%'; begin

   for v_prop in block_cursor loop
     ...[do whatever with v_prop.column]...    end loop;
end test5;

---

As always, make sure you test the above (since I take no responsibility for typos).

Tong Li wrote:
> Hello There:
>
> I am trying use bind variable as search pattern in cursor,
> but I couldn't get result set.
> I think there is something wrong with the like statement.
> Any hint, please?
> Thanks in advance.
>
> Tina
>
>
> Below is my code.
> ----------------------------------------
> create or replace procedure test5
> (p1 IN varchar2)
> IS
> v_counter number;
> v_prop prop%rowtype;
> CURSOR block_cursor IS
> select * from my_table where upper(location) like '%:p1%';
>
> begin
>
> v_counter:=0;
>
> open block_cursor;
> loop
> fetch block_cursor into v_prop;
> exit when block_cursor%rowcount>5 or
> block_cursor%notfound;
> end loop;
>
> close block_cursor;
> end test5;
>
Received on Tue Sep 24 2002 - 16:50:27 CDT