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

Littlefoot · Accepted Answer · 2020-12-08T11:07:18.310

2

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>

edited Dec 08 '20 at 11:07

answered Nov 22 '20 at 14:39

Littlefoot

131,892
15
35
57

1

You are a saint and a scholar. This worked beautifully, thank you. – ravioli Nov 22 '20 at 15:21
1

@ravioli - this will work for the example you mocked up in the "Oracle" framework, where you are only outputting a single-column table. In your Postgre example your "objects" were used-defined, with three attributes. THAT you cannot implement in Oracle using the solution from this Answer. What you will need to do for this more general case is exactly what you already had (in Oracle). There is no shortcut - what you showed is indeed "the right way" in PL/SQL. – Nov 22 '20 at 15:24
Dangit! You're right. I was so excited it worked that I didn't look close enough at the `retval` definition. I should have provided a clearer example. I need to be able to return result sets with more than one field. Is it possible to reference multiple fields in the `BULK COLLECT INTO` statement? – ravioli Nov 22 '20 at 16:58
Littlefoot, is there a way to modify this function itself (not the `SELECT` statement) to have a different column name? So instead of `COLUMN_VALUE`, the function call would return a column named `emp_name` and I could keep it as a `SELECT *`? – ravioli Dec 08 '20 at 09:41
1

Yes, @ravioli. Create your own type. I edited the answer and added an example which shows *how* to do it. Have a look, please. – Littlefoot Dec 08 '20 at 11:07

score 1 · Answer 2 · answered Nov 22 '20 at 19:53

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.

Thanks Andrew, this is more along the lines of what I was hoping for. It looks like I have to stick with the `PIPELINE` method for now, though. — ravioli, Nov 23 '20 at 14:02

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

2 Answers2