Re: Bind variable as search pattern

Both of you are making the same mistake - by embedding a variable name in a literal string, the compiler simply takes the characters of the variable name and makes it part of the literal string. At runtime, column values will be compared against '%:p1%' or '%p1cur%' (that is, the column value must actually contain the characters 'p1cur' in order to match the pattern).

You can use bind or local variables with LIKE, but the variable's value must include any wildcard characters (e.g. :p1 = '%book%') or else you should concatenate the wildcards (e.g. LIKE '%' || :p1 || '%').

Martin Doherty

Karsten Farell wrote:

> 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;
> >