How to pass varchar with single quotes to Stored Proc in Oracle

Question

I have stored procedure called like this:

call packagename.StoredProc('''A'',''B''')

'A', 'B' part is then meant to be used as parameter in procedure's code in the IN clause.

When I run that code with in clause as paramvalue IN ('A', 'B') it returns rows as expected.

But when call to stored proc is executed no rows which makes me think those quotes must be wrong but I can't get it right apparently. Can anyone see why is this not working? Thanks

Code from proc:

SELECT COUNT(*) 
            INTO v_PendingCheckCount
            FROM FUND_CHANGE_REQUEST
            WHERE STATUS IN ('PENDING_CHK', 'PEND_2ND_CHK')
            AND PROVIDER IN (pProviderList);

Proc call:

CALL PackageName.ProcedureName('''A'',''B''')

pProviderList is parameter passed to procedure.

Are you passing pProviderList as VARCHAR ? Something like- CREATE PROCEDURE ProcedureName(pProviderList VARCHAR). — Keyur Panchal, Jun 28 '17 at 09:40
I think your problem is that the string you pass into the procedure is not being parsed as a comma-delimited list - it's nothing to do with the single quotes. You could prove this by using DBMS_OUTPUT to print the parameter value - pretty sure you'll see the single quotes. — Christian Palmer, Jun 28 '17 at 09:47
Probable duplicate of [this](https://stackoverflow.com/q/34699223/1509264), [this](https://stackoverflow.com/q/44778342/1509264), [this](https://stackoverflow.com/q/35231757/1509264) and [this](https://stackoverflow.com/q/35648999/1509264) — MT0, Jun 28 '17 at 09:52
Obviously a _single string_ that happens to contain SQL syntax is not the same thing as an actual list of separate values. It would be completely disastrous if it worked that way. I am puzzled that anyone would find this puzzling. — William Robertson, Jun 28 '17 at 10:47
@William, I am junior you see. :) And was given the code already written and task to find out why it is not working. — JanT, Jun 28 '17 at 18:10
Fair enough, we were all beginners once. I'm just wondering if there is any computer language that behaves that way. — William Robertson, Jun 28 '17 at 18:34

MT0 · Accepted Answer · 2017-06-28T10:02:42.473

Pass a collection not a string and use MEMBER OF rather than IN:

CREATE OR REPLACE TYPE characterlist IS TABLE OF CHAR(1);
/

CREATE PACKAGE your_package AS
  PROCEDURE countPending(
    pProviderList IN  characterlist
    pCount        OUT INTEGER
  )
  AS
  BEGIN
    SELECT COUNT(*) 
    INTO pCount
    FROM FUND_CHANGE_REQUEST
    WHERE STATUS IN ('PENDING_CHK', 'PEND_2ND_CHK')
    AND PROVIDER  MEMBER OF pProviderList;
  END;
END;
/

Then you can call it as:

DECLARE
  vCount INTEGER;
BEGIN
  your_package.countPending(
    characterlist( 'A', 'B' ),
    vCount
  );
  DBMS_OUTPUT.PUT_LINE( vCount );
END;
/

Can anyone see why is this not working?

You are passing a single string and not a list of values - so the IN condition is testing to see whether the PROVIDER column exactly matches your entire input string and not, as you are assuming, each element of your quoted delimited list.

 WHERE 'A' IN ( q'['A', 'B']' )

Will never match since neither 'A' does not equal q'['A', 'B']' (or '''A'', ''B''') and the count will always be zero.

 WHERE 'A' IN ( 'A', 'B' )

Will match but there are two terms in the expression list of the IN condition.

+1 Thanks for your answer. We got it working replacing IN clause with INSTR which seems to be the simplest. So it went like this: INSTR(pProviderList, PROVIDER, 1, 1) > 0. I would imagine your solution would work much faster on bigger data, so I'm accepting your answer. And also thank for explaining why it didn't work with IN. — JanT, Jun 28 '17 at 18:03

Nick Krasnov · Answer 2 · 2017-06-28T11:01:43.717

No, you cannot (well, you can, but it'll be considered as one string literal, not a list of values) use a pure string of comma separated values in the IN clause of the query. What you can(should) do in this case (as one of the options) is to turn your comma separated string in a collection and use it accordingly. Here is an example:

Note: Here I have used built-in apex_application_global vc_arr2 collection. You might decide to create your own. It's up to you. You might need to be granted execute privilege on apex_util package if you choose to use it.

Just a demonstration

set serveroutput on;
declare
   -- your source string
   l_str varchar2(100) := 'X, B, C';
   -- collection  the source string will be turned into  
   l_str_tab apex_application_global.vc_arr2;
   l_result number;
 begin
   -- convert the comma separated string of values
   -- into a collection 

   l_str_tab := apex_util.string_to_table(l_str, ',');

   -- Querying the collection using TABLE operator
   -- and passing result into the IN clause of the main query 

   select count(*)
     into l_result
     from dual
    where dummy in (select column_value
                      from table(l_str_tab));

    dbms_output.put_line(to_char(l_result));                  
 end;

Result:

1
PL/SQL procedure successfully completed.

If `apex_util.string_to_table` is a collection (and not a `VARRAY`) then you can simplify it (as per [my answer](https://stackoverflow.com/a/44799087/1509264)) to get rid of the table collection expression and instead use `WHERE dummy MEMBER OF l_str_tab` — MT0, Jun 28 '17 at 10:05
There are plenty of non-apex ways of splitting a delimited string - there is an entire [SO documentation page](https://stackoverflow.com/documentation/oracle/1968/splitting-delimited-strings) on it including [this PL/SQL function](https://stackoverflow.com/documentation/oracle/1968/splitting-delimited-strings/6447/splitting-strings-using-a-pl-sql-function). — MT0, Jun 28 '17 at 10:08
+1 Thanks for your answer. I found your solution little harder to read compared to MT0's and also likely coworkers wouldn't know apex_util and I like to keep thing as simple as possible. — JanT, Jun 28 '17 at 18:06

How to pass varchar with single quotes to Stored Proc in Oracle

2 Answers2

Linked