1 Reply Latest reply on Mar 13, 2017 8:32 PM by rp0428

    Passing a comma delimited string from SSRS to Oracle

    4de95b52-4762-45dc-adbd-e538bee5a99e

      I am new to Oracle, its looks to be a completly different world for SQL savy. Have a peculiar problem passing the comma delimited string('F5672898,F5672899,F5672100') from SSRS to Oracle.

      I have a table X with a column "case_id" of type char(34 byte) , and values are just 8 alphanumeric characters (F5672898                          )rest of the characters are stored as spaces.

      I am converting the comma delimited string into a column and passing to in statement to find them in X table

       

      select regexp_substr(:Test,'[^,]+', 1, level) from dual connect by regexp_substr(:Test, '[^,]+', 1, level) is not null

       

      When I pass 'F5672898,F5672899,F5672100' to Test variable above I would get a column as below without any spaces.

      F5672898

      F5672899

      F5672100

       

      Above values are stored as case_id(F5672898                          ) with spaces in X table. I am searching the column returned by above returned regular expression as shown below which doesn't return me any results. Below expression return results if I pass values with spaces.

       

      select * from X where case_id in (select regexp_substr(:Test,'[^,]+', 1, level) from dual connect by regexp_substr(:Test, '[^,]+', 1, level) is not null)

       

      I tried trimming the case_id to avoid spaces, it gives me results but takes very long time to load. Could some one help me  to suffix spaces to the returned values of the regexp or any other approach to resolve the issue.