Handling Parameter List

I got a question on handling parameter list (String with Comma Separated values from Java), nothing but table type parameter in Oracle.

Here is the question:

Hi,

How can I create an Oracle stored procedure which accepts a variable number of parameter values used to feed a IN clause?

This is what I am trying to achieve.

FUNCTION

EXECUTE_UPDATE ( value IN int)

RETURN int ISBEGIN [...other statements...]

Select * from table1 where id in ()

RETURN SQL%ROWCOUNT ;END;

We get the parameter_list from java

e.g., parameter_list = "’A’,’B’,’C’,’D’";

We need a solution which does not uses any temporary table for achieving this.

And here is my suggestion:

Hi,

Check the below piece of code. It may help you.

Table:

CREATE TABLE available_products (product VARCHAR2 (100));

Type:

CREATE OR REPLACE TYPE products_list AS TABLE OF VARCHAR2 (100);

Data:

INSERT INTO available_products VALUES ('A');

INSERT INTO available_products VALUES ('B');

INSERT INTO available_products VALUES ('C');

INSERT INTO available_products VALUES ('D');

Function:

CREATE OR REPLACE FUNCTION check_availability (products IN products_list)

RETURN NUMBER AS

  l_count NUMBER;

BEGIN

  SELECT COUNT (1)
    INTO l_count
    FROM available_products
   WHERE product IN (SELECT COLUMN_VALUE FROM TABLE (products));

  RETURN l_count;

END check_availability;

PL/SQL Block:

BEGIN

  DBMS_OUTPUT.put_line (
    'count:' || check_availability (products_list ('B', 'C'))
  );

END;

Output:

count:2