Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: like clause('%..%') in dbms_sql..

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

From: Reinhard Wahl <wahl_at_zv.fhg.de>
Date: Fri, 11 Dec 1998 12:21:46 +0100
Message-ID: <3671004A.F4D44964@zv.fhg.de>


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

Try this instead:

        stmt:='select col1 from '||tname||' where col2 like '':theoldname''';
or

        stmt:='select col1 from '||tname||' where col2 like ''%param_in%''';
without binding any variable
I hope this helps
Reinhard

> 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


E-Mail_at_work: mailto:wahl_at_zv.fhg.de
E-Mail_at_home: mailto:ReinhardWahl_at_online.de
----------------------------------------------------
Website http://www.online.de/homepages/ReinhardWahl

    Hiermit widerspreche ich der Nutzung oder    Uebermittlung meiner Daten fuer Werbezwecke    oder fuer die Markt- oder Meinungsforschung   gemaess Par. 28 Abs. 3 Bundesdatenschutzgesetz.



Please do not use my E-Mail address for advertising!
Received on Fri Dec 11 1998 - 05:21:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US