6 Replies Latest reply: Nov 22, 2012 12:59 PM by Sayan Malakshinov. RSS

    Simple query with like return wrong result

    975681
      Hi,

      I run simple query with like.
      If I use parameter I get wrong results.
      If I use query without parameter results are ok.

      My script:

      ALTER SESSION SET NLS_SORT=BINARY_CI;
      ALTER SESSION SET NLS_COMP=LINGUISTIC;

      -- drop table abcd;
      create table abcd (col1 varchar2(10));

      INSERT INTO ABCD VALUES ('122222');
      insert into abcd values ('111222');


      SELECT * FROM ABCD WHERE COL1 LIKE :1; -- wrong result with value 12%
      /*
      COL1
      ----------
      122222
      *111222*
      */

      select * from abcd where col1 like '12%'; -- result ok
      /*
      COL1
      ----------
      122222
      */

      I use Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      and query run in Oracle SQL Developer 3.1.07.
        • 1. Re: Simple query with like return wrong result
          908002
          Sorry! Just about to correct.... Not sure what i am thinking...


          SELECT * FROM ABCD WHERE COL1 LIKE &1||'%';

          OR


          SELECT * FROM ABCD WHERE COL1 LIKE '&1'; I want to use this in my earlier case, just used : instead of &. here the inputshould include with %

          Edited by: Kiran on Nov 22, 2012 1:43 AM

          Edited by: Kiran on Nov 22, 2012 1:47 AM
          • 2. Re: Simple query with like return wrong result
            Manik
            Interesting solution Kiran,it should be:
            SELECT * FROM ABCD WHERE COL1 LIKE &1||'%';
            Cheers,
            Manik.
            • 3. Re: Simple query with like return wrong result
              AlbertoFaenza
              Hi,

              welcome to the forum.

              When you put some code please enclose it between two lines starting with {noformat}
              {noformat}
              i.e.:
              {noformat}
              {noformat}
              SELECT ...
              {noformat}
              {noformat}
              
              You should specify exactly how you run your code.
              
              If I run this statement in SQL Plus:
              SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

              Session altered.

              SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

              Session altered.

              SQL>
              SQL> -- drop table abcd;
              SQL> create table abcd (col1 varchar2(10));

              Table created.

              SQL>
              SQL> INSERT INTO ABCD VALUES ('122222');

              1 row created.

              SQL> insert into abcd values ('111222');

              1 row created.

              SQL>
              SQL> SELECT * FROM ABCD WHERE COL1 LIKE :1;
              SP2-0552: Bind variable "1" not declared.
              SQL>
              I got this error. So I wonder how you set value 12%
              
              Please specify exactly how you run your test as we cannot reproduce your problem.
              
              Regards.
              Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
              • 4. Re: Simple query with like return wrong result
                975681
                I set value in this code:
                VARIABLE A1 VARCHAR2(10);
                EXEC :A1 := '12%';
                SELECT * FROM ABCD WHERE COL1 LIKE :A1;
                or by "Run Statement" then sql developer show window to set value.
                • 5. Re: Simple query with like return wrong result
                  AlbertoFaenza
                  972678 wrote:
                  I set value in this code:
                  VARIABLE A1 VARCHAR2(10);
                  EXEC :A1 := '12%';
                  SELECT * FROM ABCD WHERE COL1 LIKE :A1;
                  or by "Run Statement" then sql developer show window to set value.
                  And are you still having problem?

                  I made the same test:
                  SQL> 
                  SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
                  
                  Session altered.
                  
                  SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
                  
                  Session altered.
                  
                  SQL> 
                  SQL> --drop table abcd;
                  SQL> create table abcd (col1 varchar2(10));
                  
                  Table created.
                  
                  SQL> 
                  SQL> INSERT INTO ABCD VALUES ('122222');
                  
                  1 row created.
                  
                  SQL> insert into abcd values ('111222');
                  
                  1 row created.
                  
                  SQL> 
                  SQL> VARIABLE A1 VARCHAR2(10);
                  SQL> EXEC :A1 := '12%';
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> SELECT * FROM ABCD WHERE COL1 LIKE :A1;
                  
                  COL1
                  ----------
                  122222
                  
                  SQL> 
                  and as you see I got only one row which is correct.

                  Regards.
                  Al
                  • 6. Re: Simple query with like return wrong result
                    Sayan Malakshinov.
                    I think it is just a bug, and it is fixed in 11.2.0.3:

                    11.2.0.1:
                    DB11.2.0.1>> select * from v$version where rownum=1;
                    
                    BANNER
                    --------------------------------------------------------------------------------
                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                    
                    1 row selected.
                    
                    DB11.2.0.1>> ALTER SESSION SET NLS_SORT=BINARY_CI;
                    
                    Session altered.
                    
                    DB11.2.0.1>> ALTER SESSION SET NLS_COMP=LINGUISTIC;
                    
                    Session altered.
                    
                    DB11.2.0.1>> create table abcd (col1 varchar2(10));
                    
                    Table created.
                    
                    DB11.2.0.1>> INSERT INTO ABCD VALUES ('122222');
                    
                    1 row created.
                    
                    DB11.2.0.1>> insert into abcd values ('111222');
                    
                    1 row created.
                    
                    DB11.2.0.1>> VARIABLE A1 VARCHAR2(10);
                    DB11.2.0.1>> EXEC :A1 := '12%';
                    
                    PL/SQL procedure successfully completed.
                    
                    DB11.2.0.1>> SELECT * FROM ABCD WHERE COL1 LIKE :A1;
                    
                    COL1
                    ----------
                    122222
                    111222
                    
                    2 rows selected.
                    11.2.0.3:
                    DB11.2.0.3>> select * from v$version where rownum=1;
                    
                    BANNER
                    --------------------------------------------------------------------------------
                    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                    
                    1 row selected.
                    
                    DB11.2.0.3>> ALTER SESSION SET NLS_SORT=BINARY_CI;
                    
                    Session altered.
                    
                    DB11.2.0.3>> ALTER SESSION SET NLS_COMP=LINGUISTIC;
                    
                    Session altered.
                    
                    DB11.2.0.3>> create table abcd (col1 varchar2(10));
                    
                    Table created.
                    
                    DB11.2.0.3>> INSERT INTO ABCD VALUES ('122222');
                    
                    1 row created.
                    
                    DB11.2.0.3>> insert into abcd values ('111222');
                    
                    1 row created.
                    
                    DB11.2.0.3>> VARIABLE A1 VARCHAR2(10);
                    DB11.2.0.3>> EXEC :A1 := '12%';
                    
                    PL/SQL procedure successfully completed.
                    
                    DB11.2.0.3>> SELECT * FROM ABCD WHERE COL1 LIKE :A1;
                    
                    COL1
                    ----------
                    122222
                    
                    1 row selected.
                    Regards,
                    Sayan Malakshinov
                    http://orasql.org