无CREATE TYPE权限下PostgreSQL转Oracle数组返回函数方案咨询

Alright, let's tackle this problem head-on. Since you can't get CREATE TYPE permissions in your Oracle environment, we'll use Oracle's built-in collection types instead of creating custom ones—this avoids the permission issue entirely.

The Solution: Use SYS.ODCINUMBERLIST #

Oracle provides pre-defined collection types under the SYS schema, and SYS.ODCINUMBERLIST is perfect for returning a list of integers (it stores NUMBER values, which fully support integer use cases). No custom type creation required.

Here's the converted function matching your logic:

CREATE OR REPLACE FUNCTION get_array_of_integers(i_param IN NUMBER) 
RETURN SYS.ODCINUMBERLIST
IS
BEGIN
  CASE i_param
    WHEN 1 THEN
      RETURN SYS.ODCINUMBERLIST(1);
    WHEN 2 THEN
      RETURN SYS.ODCINUMBERLIST(1, 2, 3, 4);
    WHEN 3 THEN
      RETURN SYS.ODCINUMBERLIST(5, 6, 7); -- Fill in your omitted values here
    -- Add additional WHEN clauses for other parameter values as needed
    ELSE
      RETURN SYS.ODCINUMBERLIST(); -- Return empty collection, adjust if needed
  END CASE;
END;
/

How to Use the Function #

To retrieve the values (similar to using unnest() in PostgreSQL), you can use the TABLE() operator to unnest the collection into rows:

SELECT COLUMN_VALUE AS integer_value
FROM TABLE(get_array_of_integers(2));

This will return:

INTEGER_VALUE
-------------
1
2
3
4

Key Notes #

• If you need strict integer typing (instead of NUMBER), you can use SYS.ODCINTEGERLIST instead—both are pre-defined and require no special permissions.
• The function behaves exactly like your PostgreSQL version: it returns a collection of integers based on the input parameter, with no need for custom type setup.

内容的提问来源于stack exchange，提问作者user7571491