How to get comma separated values inside Oracle stored procedure

Question

I have to pass FlightDate, FlightNumbers as parameters to Oracle Stored procedure like below.

CREATE OR REPLACE PROCEDURE GetPaxDetails(
   FlyingDate IN PAX_DETAILS.FlightDate%TYPE, 
   FlightNumbers IN VARCHAR(300))
IS
BEGIN
    -- Assume that we've received **FlightNumbers** as '0620,0712,0154'
    -- Basically I am trying to select all passenger details (PAX_DETAILS) 
    -- for those flights passed in as a parameter(**FlightNumbers**) 
    -- for the specified dates
    -- In this procedure the query should be generated something like below:
    SELECT * 
      FROM PAX_DETAILS 
     WHERE TO_DATE(FlightDate,'DDMONYY') BETWEEN TO_DATE(FlyingDate,'DDMONYY') 
                                             AND TO_DATE(FlyingDate,'DDMONYY') + 1 
       AND FlightNo IN ('0620,0712,0154')

END

I am not understanding how to convert FlightNumbers parameter to something like AND FlightNo IN ('0620,0712,0154') inside the query.

have you tried : SELECT * FROM PAX_DETAILS WHERE TO_DATE(FlightDate,'DDMONYY') BETWEEN TO_DATE(FlyingDate,'DDMONYY') AND TO_DATE(FlyingDate,'DDMONYY') + 1 AND FlightNo IN (FlightNumbers) — Satya, Jul 23 '13 at 04:47
have you tried using dynamic sql ? : http://stackoverflow.com/questions/5007725/pl-sql-using-a-dynamic-query-inside-a-stored-procedure — planben, Jul 23 '13 at 05:07
You might find your answer her: [Sending an array of values to Oracle procedure to use in WHERE IN clause](http://stackoverflow.com/questions/13580245/sending-an-array-of-values-to-oracle-procedure-to-use-in-where-in-clause) — A.B.Cade, Jul 23 '13 at 06:47
@planben - why use dynamic SQL when it can be done in static SQL? — APC, Jul 23 '13 at 06:48
because he wants to receive a comma seperated FlightNumbers in a varchar parameter , so dynamic sql sounds like a good idea , don't you think ? — planben, Jul 23 '13 at 07:40
@planben, dynamic sql is never a good idea when static sql is possible, read [this Tom Kyte's post](http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:864237961410) — A.B.Cade, Jul 23 '13 at 08:29

Nick Krasnov · Accepted Answer · 2013-07-23T05:54:55.443

As one of the approaches, the regexp_substr() and regexp_count() regular expression functions can be used to split the source string into table rows and then you can easily include that result in your IN clause:

-- splitting a comma separated string
select regexp_substr(FlightNumbers, '[^,]+', 1, level) as num
  from dual
connect by level <= regexp_count(FlightNumbers, '[^,]+')

So, for instance, if you pass in FlightNumbers as '0620,0712,0154', the above query will produce the following result:

NUM
--------
0620
0712
0154

Having that at hand, your final query would be:

with numbers(num) as(
   select regexp_substr(FlightNumbers, '[^,]+', 1, level) as num
     from dual
  connect by level <= regexp_count(FlightNumbers , '[^,]+')
 )
SELECT *           -- do not forget INTO clause
  FROM PAX_DETAILS 
 WHERE TO_DATE(FlightDate,'DDMONYY') BETWEEN TO_DATE(FlyingDate,'DDMONYY') 
                                         AND TO_DATE(FlyingDate,'DDMONYY') + 1 
   AND FlightNo IN (select num
                      from numbers);

Good use of `regexp_count()`, new in 11g. – APC Jul 23 '13 at 06:47 — APC, Jul 23 '13 at 06:47
One up for new feature in 11g – Ashok kumar Jul 25 '13 at 13:38 — Ashok kumar, Jul 25 '13 at 13:38

How to get comma separated values inside Oracle stored procedure

1 Answers1

Linked