How can I use a collection within an Oracle SQL statement

Question

I want to write an Oracle function that collects some data in multiple steps into a collection variable and use that collection data within a SELECT query like in this very simplified example:

CREATE OR REPLACE FUNCTION TESTFUNC01 RETURN VARCHAR2 AS 
  -- INT_LIST is declared globally as "TYPE INT_LIST IS TABLE OF INTEGER"
  MyList INT_LIST := INT_LIST();
  MyName VARCHAR2(512);
BEGIN
  MyList.Extend(3);
  MyList(0) := 1;
  MyList(1) := 2;
  MyList(2) := 3;

  SELECT Name INTO MyName
  FROM Item WHERE ItemId NOT IN MyList;
  RETURN MyName;
END TESTFUNC01;

Unfortunately the part "NOT IN MyList" is no valid SQL. Is there a way to achieve this?

Solution 1

What you're looking for is the table function:

CREATE OR REPLACE FUNCTION TESTFUNC01 RETURN VARCHAR2 AS 
  -- INT_LIST is declared globally as "TYPE INT_LIST IS TABLE OF INTEGER"
  MyList INT_LIST := INT_LIST();
  MyName VARCHAR2(512);
BEGIN
  MyList.Extend(3);
  MyList(1) := 1;
  MyList(2) := 2;
  MyList(3) := 3;

  SELECT Name INTO MyName
  FROM Item WHERE ItemId NOT IN (select * from table(MyList));
  RETURN MyName;
END TESTFUNC01;

Solution 2

You can do it like this:

CREATE OR REPLACE FUNCTION TESTFUNC01 RETURN VARCHAR2 AS 
  -- INT_LIST is declared globally as "TYPE INT_LIST IS TABLE OF INTEGER"
  MyList INT_LIST := INT_LIST();
  MyName VARCHAR2(512);
BEGIN
  MyList.Extend(3);
  MyList(1) := 1;
  MyList(2) := 2;
  MyList(3) := 3;

  SELECT Name INTO MyName
  FROM Item WHERE ItemId NOT IN (SELECT COLUMN_VALUE FROM TABLE(MyList));
  RETURN MyName;
END TESTFUNC01;

Note that I've also changed the list indices. The start with 1 (not 0).

Solution 3

If your using oracle 10 you could use the collections extensions:

CREATE OR REPLACE FUNCTION TESTFUNC01 RETURN VARCHAR2 AS 
  -- INT_LIST is declared globally as "TYPE INT_LIST IS TABLE OF INTEGER"
  MyList INT_LIST := INT_LIST();
  MyName VARCHAR2(512);
BEGIN
  MyList.Extend(3);
  MyList(1) := 1;
  MyList(2) := 2;
  MyList(3) := 3;

  SELECT Name INTO MyName
  FROM Item WHERE ItemId MEMBER OF MyList;
  RETURN MyName;
END TESTFUNC01;

for more details see this post

Solution 4

-- INT_LIST is declared globally as "TYPE INT_LIST IS TABLE OF INTEGER"

That looks like a PL/SQL declaration. SELECT statements use the SQL engine. This means you need to declare your TYPE in SQL.

CREATE TYPE INT_LIST AS TABLE OF NUMBER(38,0);
/

Then you can use it in a SELECT statement:

SELECT Name INTO MyName
FROM Item WHERE ItemId NOT IN (select * from table(MyList));

---

Of course, you need to make sure that your query returns only one row, or that your program handles the TOO_MANY_ROWS exception.

View more solutions

Comments

[Sid]

That's what I was looking for. Thanks much.