Query returns NULL – Oracle

oracleparameterquery

I've an Oracle query where I need to get some data on the basis of some records (strings). I am using IN clause and query is working fine in TOAD with hardcoded data. But when I run that query through code (Dapper) and pass those strings as Parameters, query always returns NULL/ Empty result.

Here is my query:

SELECT * FROM table WHERE id IN :lstIds

that's how I'm passing my data as parameter:

using (IDbConnection conn = new DBManager().connection)
        {
            conn.Open();

            lstData = conn.Query<ViewModelData>(sqlGetData, new
            {
                lstIds = lstIdsParams

            }).ToList();

                conn.Dispose();
        }

and this is my list of Ids, that I am passing as param

enter image description here

Even if I run this query with hardcoded data, it still does not work..

SELECT * FROM table WHERE id IN 'PL-0417-01301006-H01-D-00013#000013#18'

But if I run same query in TOAD with hardcoded data, it works fine and returns proper data. I don't know what I am doing wrong in code that is creating issue. Can anyone help me out here??

Best Answer

Your syntax is wrong you need parenthesis around the value(s)

Please remember not to post Images for data, queries and errors

CREATE TABLE table1 (
  
  id VARCHAR(50)
);

INSERT INTO table1
VALUES
  ('PL4418.01301006-H01-D-03013O322241');

INSERT INTO table1
VALUES
  ( '04l801301006-I401•0013ccO27I0');

  INSERT INTO table1
VALUES
  ( 'P104I8.013016-H0l-D0l3c60O22l1');

  INSERT INTO table1
VALUES
  ( 'P104I8013016-I40l-D013c0c0224V');

  INSERT INTO table1
VALUES
  ( 'P10418-013016-HO1-D-010X2141');

  INSERT INTO table1
VALUES
  ('PL-0418-013016-H01-D-0132241');

  INSERT INTO table1
VALUES
  ( 'PL.5410.01301006-H0l-D.0201300022'); 

SELECT * FROM table1 WHERE id IN ('P104I8013016-I40l-D013c0c0224V');

| ID                             |
| :----------------------------- |
| P104I8013016-I40l-D013c0c0224V |

db<>fiddle here

Related Question