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

Question

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

Answer 1

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>

Answer 2

That pipelined function with defined table types (either as SQL types or within a PL/SQL package) was the only way to achieve this until 19.6. Once you're on this version you can use a SQL Macros . For your particular requirement you could do a straight replacement like:

create or replace function getUser (pUserId  integer) 
return varchar2 sql_macro 
is
begin
  return q'{SELECT UserId, UserClass, UserName
  FROM Users 
  WHERE (UserId = pUserId OR pUserId IS NULL)}';
end getUser;
/

Or you can make it easier on the optimizer separating the query into the two possible forms - return everything or return just the row that matches the pUserId value:

create or replace function getUser (pUserId  integer) 
return varchar2 sql_macro 
is
begin
  if pUserId is null then 
    return q'{SELECT UserId, UserClass, UserName FROM Users}';
  else 
    return q'{SELECT UserId, UserClass, UserName FROM  Users WHERE UserId = pUserId}';
  end if;
end getUser;
/

I've put together a demo on LiveSQL.