Oracle "IN clause" from parameter

Question

I'm very unfamiliar with Oracle and am just trying to get this to work. I've looked here and here but have no idea how to make it all work. Here's what I need. I need a proc that will accept a comma-delimited string as a parameter that I can use in the "IN clause" of the query and return a cursor result. Is ther a way to do this in one, all-inclusive proc? If not, what are the different steps I need to take? Again, I apologize for my lack of knowledge of Oracle, this is just trying to get something to work real quick.

Thanks

Here's the proc (p_Scope would be the comma-delimited input):

create or replace PROCEDURE CU_SELECTION_ID
(
p_Scope IN varchar2,
p_ResultSet OUT SYS_REFCURSOR
)
is
BEGIN
OPEN p_ResultSet FOR
select
b.addr1,
b.addr2,
b.city,
b.country_code,
a.customer_no,
b.des1,
a.entity,
b.main_phone_no,
b.phone_area_code,
b.status,
b.wb_site_url,
b.zip
from
ar_customer a,
ct_addr b
where b.main_loc_flag = 'Y' and
a.customer_no = b.customer_no and
a.entity = b.cust_entity and
b.stk_loc_no = '3' and 
b.customer_no in (p_Scope);
END;

Towland

Have you looked into Dynamic SQL? download.oracle.com/docs/cd/A97630_01/appdev.920/a96590/… – Renfred

Please show what you have tried already. – Highsmith

These are preexisting oracle procs that need the changes. If it can be done without major structure changes, that would be preferable at this point. To answer your question, no I haven't looked into dynamic sql...i wouldn't know where to begin with that either :). – Towland

@CosCallis I've tried variations on the two links in the question...mostly trying to figure out if the in table(cast(array as type_array)) kind of thing would work. I will add the proc to my question, but it's back to the basic proc at this point. – Towland

@George Dynamically I'd still have to loop through the comma-delimited parameter to build up the string. This might be viable, but I am so out of my element here that it's not even funny. I've barely ever touched Oracle. – Towland

possible duplicate of Oracle stored procedure with parameters for IN clause Take a look at that question and see if the answers there fit. – Nananne

Answer 1

I believe there is a 'better way', but I'm not sure what it is right now...

This should work for you though:

replace:

b.customer_no in (p_Scope);

with

instr(p_Scope, ','||b.customer_no||',' ) > 0

This will search p_Scope and return a value of > 0 if b.customer_no appears in the list.

Make sure that the first and last character in the list is a comma (',')

(also, as a new comer to Oracle I found Tech Republic to be a very helpful quick resource.)

Highsmith

There probably is a more "elegant" way to do this, but this is excellent! Works perfect and gets me passed my hump. Thanks so much! – Towland

Won't there be issues with this if two customer numbers happen to end with the same digits (eg 12345 and 2345)? You should bracket b.customer_no with a comma on both sides. – Leshia

@Tim, good point...will edit in a solution for that. @Towland take note of this matter and update – Highsmith

This is still by far simplest solution. Other solutions include pipelined functions or associative arrays - much more work + I question If any perfomance benefits from using such things. Worth mentioning about this answer - you need to pass a string like ",32,33,". So commas at beginning & the end of string. – Bounteous

Answer 2

Assuming declaration

create or replace type cvarchar2 as table of varchar2(4000);

the query

select * from some_table t where some_column in 
('FOO','BAR')

gives same result as

select * from some_table t where some_column in 
(select column_value from table(cvarchar2('FOO','BAR')))

You can use second one and pass PLSQL collection into table function. I do it this way from Java where ...table(?) perfectly works. Explain plan seems not too bad in comparison with traditional IN clause.

Solutions based on text search with delimiters may be performance killer.

Sidra

This is the recommended approach to this problem, yet it has been ignored. – Hitherward

Interesting article about details and pitfalls of such a solution: pontis.biz/resources/articles/collection_card.php – Kaenel

Answer 3

You can use it this way:

 SELECT * FROM MATABLE 
 WHERE MT_ID 
    IN (SELECT REGEXP_SUBSTR(MYPARAM,'[^,]+', 1, LEVEL) 
        FROM DUAL 
        CONNECT BY REGEXP_SUBSTR(MYPARAM, '[^,]+', 1, LEVEL) IS NOT NULL))

MYPARAM- '368134,181956'

Plasticine

How about adding a link to the oracle docs for that function? – Intermigration

The performance of this query is terrible. The top answer is orders of magnitude faster: https://mcmap.net/q/667330/-oracle-quot-in-clause-quot-from-parameter – Oratory

Answer 4

For the record, here's another ugly way to do it.

   PROCEDURE getreport (
      p_affiliates           IN       varchar2,
      p_StartDate            IN       date,
      p_EndDate              IN       date,
      p_ReturnValue         OUT       sys_refcursor
   ) IS
   BEGIN

        DECLARE
           sql_stmt          VARCHAR2(4000);
        BEGIN

          sql_stmt := 'SELECT
            FIRSTNAME,
            LASTNAME,
            ADDRESSLINE,
            SUITE,
            CITY,
            STATE,
            ZIP
        FROM 
            ORDERHEADER head
            INNER JOIN ORDERDETAIL detail 
                on head.ORDERTRACKINGLOGID = detail.ORDERTRACKINGLOGID
            INNER JOIN ORDERTRACKINGDETAIL trackdetail
                on detail.ORDERDETAILID = trackdetail.ORDERDETAILID 
                    AND head.ORDERHEADERID = trackdetail.ORDERHEADERID
            INNER JOIN AFFILIATE aff
                on trackdetail.AFFILIATEID = aff.AFFILIATEID
        WHERE
            aff.AFFILIATEID IN
            (
                select 
                    AFFILIATEID
                from 
                    AFFILIATE
                where
                    AFFILIATEID IN (' || p_affiliates || ')
            )
            AND
            head.CALENDAR_DATE >= TO_DATE( :p_StartDate )
            AND
            head.CALENDAR_DATE <= TO_DATE( :p_EndDate )    
        ORDER BY AFFILIATEID,
                AFFILIATENAME    
    ';

        OPEN p_ReturnValue for sql_stmt USING p_StartDate, p_EndDate;

        END;

    END getreport;

Tepee