Oracle stored procedure local collection of numbers for use in SQL statement-CodePudding

I'm trying to query a table or collection of NUMBER and use that collection in a WHERE IN statement in a separate query. I feel like it should be simple but I cannot get it to work. I'm very new to PL/SQL and have spent days trying to figure out a solution, any help is greatly appreciated.

When I use SYS.ODCINUMBERLIST I get an error at INTO V_INVENTORY_ITEMS:

PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER

If I use the commented out V_INVENTORY_ITEMS nested_typ to store the collection, I get

95/21 PLS-00642: local collection types not allowed in SQL statements

It also seems that I cannot use any IF logic inside of the WHERE IN (...) clause.

This is a much simplified version of what I'm trying to do:

CREATE OR REPLACE PROCEDURE GET_ITEMS(
P_CR OUT SYS_REFCURSOR,
IN_ITEM_TYPE VARCHAR2
)

IS

V_INVENTORY_ITEMS SYS.ODCINUMBERLIST;

--TYPE nested_typ IS TABLE OF NUMBER;
--V_INVENTORY_ITEMS   nested_typ;
BEGIN

IF IN_ITEM_TYPE  = 'TYPE1'
    SELECT ITEM_ID 
    INTO V_INVENTORY_ITEMS 
    FROM ITEM_MASTER
    WHERE CATEGORY = 'CAT1' OR CATEGORY = 'CAT2';
ELSE
    SELECT ITEM_ID 
    INTO V_INVENTORY_ITEMS 
    FROM ITEM_MASTER
    WHERE CATEGORY = 'CAT3' OR CATEGORY = 'CAT4';
END IF;

OPEN P_CR FOR
    SELECT * FROM ORDER_LINES
    WHERE ITEM_ID IN (V_INVENTORY_ITEMS )
END GET_ITEMS;

CodePudding user response：

Either use a table collection expression in a sub-query:

CREATE OR REPLACE PROCEDURE GET_ITEMS(
  P_CR         OUT SYS_REFCURSOR,
  IN_ITEM_TYPE     VARCHAR2
)
IS
  V_INVENTORY_ITEMS SYS.ODCINUMBERLIST;
BEGIN
  IF IN_ITEM_TYPE  = 'TYPE1'
    SELECT ITEM_ID 
    INTO V_INVENTORY_ITEMS 
    FROM ITEM_MASTER
    WHERE CATEGORY = 'CAT1' OR CATEGORY = 'CAT2';
  ELSE
    SELECT ITEM_ID 
    INTO V_INVENTORY_ITEMS 
    FROM ITEM_MASTER
    WHERE CATEGORY = 'CAT3' OR CATEGORY = 'CAT4';
  END IF;

  OPEN P_CR FOR
    SELECT * FROM ORDER_LINES
    WHERE ITEM_ID IN (SELECT column_value FROM TABLE(V_INVENTORY_ITEMS))
END GET_ITEMS;
/

Or, do not use a collection:

CREATE OR REPLACE PROCEDURE GET_ITEMS(
  P_CR         OUT SYS_REFCURSOR,
  IN_ITEM_TYPE     VARCHAR2
)
IS
BEGIN
  IF IN_ITEM_TYPE  = 'TYPE1'
    OPEN P_CR FOR
      SELECT * FROM ORDER_LINES
      WHERE ITEM_ID IN (
        SELECT ITEM_ID 
        FROM ITEM_MASTER
        WHERE CATEGORY = 'CAT1' OR CATEGORY = 'CAT2'
      );
  ELSE
    OPEN P_CR FOR
      SELECT * FROM ORDER_LINES
      WHERE ITEM_ID IN (
        SELECT ITEM_ID 
        FROM ITEM_MASTER
        WHERE CATEGORY = 'CAT3' OR CATEGORY = 'CAT4'
      );
  END IF;
END GET_ITEMS;
/

CodePudding user response：

You can bypass specifically testing for IN_ITEM_TYPE altogether and reduce to a single statement;

create or replace procedure get_items (p_cr         out sys_refcursor
                                     , in_item_type in  varchar2
                                     )
is
begin 
    open p_cr  for
         select ol.*
           from order_lines ol 
          where ol.item_id  in
                (select im.item_id
                   from item_master im
                  where im.type = in_item_type 
                    and (   (im.type = 'TYPE1' and im.category in ('CAT1','CAT2'))  
                         or (im.type = 'TYPE2' and im.category in ('CAT3','CAT4'))
                        )
                );
end get_items;