Re: Bind variable as search pattern

Try:

 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;

"Tong Li" <tongli_at_unagi.cis.upenn.edu> wrote in message news:amqhnh$fc$1_at_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 - 20:33:38 CDT