Bind variable as search pattern

From: Tong Li <tongli_at_unagi.cis.upenn.edu>
Date: 24 Sep 2002 20:25:21 GMT
Message-ID: <amqhnh$fc$1@netnews.upenn.edu>

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 - 15:25:21 CDT