Query returns NULL - Oracle

Question

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

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

Here is my query I think that your strings list is converted to one string literal, i.e. you obtain SELECT * FROM table WHERE id IN ('v1, v2, v3') instead of SELECT * FROM table WHERE id IN ('v1', 'v2', 'v3')... — Akina
– Akina, Commented Sep 14, 2020 at 6:05
You must parse CSV which is transferred into the query to separate rows one value per row. I don't know does Oracle have some special construction for this purposes. Anycase you may convert your CSV to JSON array (on the client, or in the query using proper REPLACE) and use JSON_TABLE() for parsing. — Akina
– Akina, Commented Sep 14, 2020 at 6:21
but even if I pass hardcoded values, it still does not work.. Please check updated question.. — Naila Akbar
– Naila Akbar, Commented Sep 14, 2020 at 6:53

nbk · Accepted Answer · 2020-09-14 14:18:33Z

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

Stack Exchange Network

Query returns NULL - Oracle

1 Answer 1

Your Answer

Hot Network Questions

Query returns NULL - Oracle

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions