oracle-databasefunctionstored-procedurestable-functions

Oracle - Return a result set via a SELECTable function without explicit object / table types


I'm trying to find a simple and easy-to-maintain way to return a result set via a function that can be referenced in a SELECT statement like this:

SELECT u.UserId, u.UserName, up.ProfileName
FROM GetUser(1) u
INNER JOIN user_profile up ON u.user_id = up.user_id; 

Here's what I have in Postgres:

CREATE OR REPLACE FUNCTION GetUser(
  pUserId INTEGER
)
RETURNS TABLE (UserId INTEGER, UserClass CHAR(2), UserName VARCHAR(100)) AS $$
BEGIN
  RETURN QUERY
  SELECT UserId, UserClass, UserName
  FROM Users 
  WHERE (UserId = pUserId OR pUserId IS NULL)
  ;
END;
$$ LANGUAGE 'plpgsql';

SELECT * FROM GetUser(1);

Here's an example of where I've gotten to in Oracle:

CREATE OR REPLACE TYPE appuser AS OBJECT (UserName VARCHAR(255)); -- user type
CREATE OR REPLACE TYPE appuser_table AS TABLE OF appuser; -- user table type

CREATE OR REPLACE FUNCTION GetUser (
  pUserId IN VARCHAR2 DEFAULT NULL
) RETURN appuser_table PIPELINED AS
BEGIN
  FOR v_Rec IN (
    SELECT UserName
    FROM Users
    WHERE (UserId = pUserId OR pUserId IS NULL)
  ) 
  LOOP
    PIPE ROW (appuser(v_Rec.UserName));
  END LOOP;

  RETURN;
END;

SELECT * FROM TABLE(GetUser(NULL));

It works, but it's cumbersome and requires multiple DDLs. In Postgres, I can do all this easily within the function:

RETURNS TABLE (ObjectId INTEGER, ObjectClass CHAR(2), ObjectName VARCHAR(100))

Is there a cleaner way to do this in Oracle?

Related posts
SELECT in Functions / SPs
DBMS_SQL.return_result
Pipelined Functions


Solution

  • Basically, the way you put it, all you need to create is a function - use Oracle's built-in type. Here's an example based on Scott's EMP table (as I don't have yours):

    SQL> select deptno, ename from emp where deptno = 10;
    
        DEPTNO ENAME
    ---------- ----------
            10 CLARK
            10 KING
            10 MILLER
    

    Function:

    SQL> create or replace function getuser (puserid in number default null)
      2    return sys.odcivarchar2list
      3  is
      4    retval sys.odcivarchar2list;
      5  begin
      6    select ename
      7      bulk collect into retval
      8      from emp
      9      where deptno = puserid or puserid is null;
     10
     11    return retval;
     12  end;
     13  /
    
    Function created.
    

    Testing:

    SQL> select * From table(getuser(10));
    
    COLUMN_VALUE
    ----------------------------------------------------------------------------
    CLARK
    KING
    MILLER
    
    SQL>
    

    If you'd want to get something "smarter" as a column name instead of COLUMN_VALUE, then you'd have to create your own type. Something like this:

    SQL> create or replace type t_tf_row as object (ename varchar2(20));
      2  /
    
    Type created.
    
    SQL> create or replace type t_tf_tab is table of t_tf_row;
      2  /
    
    Type created.
    
    SQL> create or replace function getuser (puserid in number default null)
      2    return t_tf_tab
      3  is
      4    retval t_tf_tab;
      5  begin
      6    select t_tf_row(ename)
      7      bulk collect into retval
      8      from emp
      9      where deptno = puserid or puserid is null;
     10    return retval;
     11  end;
     12  /
    
    Function created.
    
    SQL> select * from table(getuser(10));
    
    ENAME
    --------------------
    CLARK
    KING
    MILLER
    
    SQL>