Re: like clause('%..%') in dbms_sql..

umagri_at_my-dejanews.com schrieb:
>
> hi,
> i am trying to locate a string(that i pass in as a parameter in the procedure)
> ie; select col1 from sometable where col2 like '%param_in%';
> i know there are many records, but my procedure is getting me nowhere.
> what am i doing wrong?
> is something wrong with the way iam doing the bind?
> thanks in advance,
> --uma
>
> create or replace procedure someproc(param_in varchar2) as
>
> --variables and cursor declaration
>
> begin
> tablecur:=dbms_sql.open_cursor;
> open c1;
> loop
> fetch c1 into tname;
> exit when c1%notfound;
> begin
> stmt:='select col1 from '||tname||' where col2 like :theoldname';
> dbms_sql.parse(tablecur, stmt, dbms_sql.v7);
> dbms_sql.bind_variable(tablecur, ':theoldname', '%param_in%');
> retval:=dbms_sql.execute(tablecur);
> .
> .
> .
> end loop;
> close c1;
> end;
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Hi,

try:

        stmt:='select col1 from '||tname||' where col2 like ' || chr(39) || chr(37) || param_in || chr(37) || chr(39);

        dbms_sql.parse(tablecur, stmt, dbms_sql.v7);
        retval:=dbms_sql.execute(tablecur);

didn't test may reach your needs.

HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Fri Dec 11 1998 - 05:43:08 CST