PL/SQL Table Function - How to return an empty result set?

Question

I'm trying to create a PL/SQL table function. I don't mind if it is PIPELINED or not. I just want it to return a query-able result set.

And I want to start with an empty result set. (Because it is possible that the result set I intend to construct will be empty.)

Is it possible to create a PL/SQL table function that returns zero rows?

In the books I have, and tutorials I can find, I only see examples that must return at least one record.

Example of the problem:

CREATE OR REPLACE PACKAGE z_util AS
  TYPE t_row
    IS RECORD (
        CATEGORY VARCHAR2( 128 CHAR )
      , MEASURE NUMBER
    );
  TYPE t_tab
    IS TABLE OF t_row;
  FUNCTION f_test
    RETURN t_tab;
END z_util;
/

CREATE OR REPLACE PACKAGE BODY z_util AS
  FUNCTION f_test
    RETURN t_tab IS
      retval t_tab;
    BEGIN
      RETURN retval;
    END;
END z_util;
/

SELECT test.*
FROM TABLE ( z_util.f_test ) test;

Output:

Error starting at line : 24 in command -
SELECT test.*
FROM TABLE ( z_util.f_test ) test
Error at Command Line : 25 Column : 14
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:

Littlefoot · Accepted Answer · 2020-09-11 11:52:04Z

3

Something like this?

SQL> CREATE TYPE t_row AS OBJECT (id NUMBER, name VARCHAR2 (50));
  2  /

Type created.

SQL> CREATE TYPE t_tab IS TABLE OF t_row;
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION f_test
  2   RETURN t_tab
  3  AS
  4   retval  t_tab;
  5  BEGIN
  6   RETURN retval;
  7  END;
  8  /

Function created.

SQL> SELECT f_test FROM DUAL;

F_TEST(ID, NAME)
--------------------------------------------------------------------


SQL>

Saying that this is too simple and that it doesn't work while in package:

SQL> CREATE OR REPLACE PACKAGE pkg_test
  2  AS
  3     FUNCTION f_test
  4        RETURN t_tab;
  5  END;
  6  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg_test
  2  AS
  3     FUNCTION f_test
  4        RETURN t_tab
  5     AS
  6        retval  t_tab;
  7     BEGIN
  8        RETURN retval;
  9     END f_test;
 10  END pkg_test;
 11  /

Package body created.

SQL> select pkg_test.f_test from dual;

F_TEST(ID, NAME)
-------------------------------------------------------


SQL>

Works OK as well. Did you, by any chance, declare type within the package? If so, try to create it at SQL level.

edited Sep 11, 2020 at 11:52

answered Sep 11, 2020 at 11:36

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Ankit Bajpai Over a year ago

If you don't mind, How much experience do you have on oracle?

Leo Orientis Over a year ago

I want it to be that simple, but when I create this in a package, I get the result: SQL Error: ORA-00902: invalid datatype

Littlefoot Over a year ago

Leo: it works in package as well; I edited the answer and added another example - have a look. @Ankit: who? me or Leo?

Leo Orientis Over a year ago

But why doesn't it work when the type is declared within the package?

Littlefoot Over a year ago

@Ankit: select months_between(sysdate, date '1994-04-15') from dual

|

0xdb · Accepted Answer · 2020-09-11 19:08:21Z

See 6.4.6 Querying a Collection:

Note: In SQL contexts, you cannot use a function whose return type was declared in a package specification.

You cannot unnest the result of function, but the variable with the same data type:

create or replace package utils as
    type t_row is record (category varchar2 (8), measure number);
    type t_tab is table of t_row;
    function passon (t t_tab:=null) return t_tab;
end utils;
/
create or replace package body utils as 
    function passon (t t_tab:=null) return t_tab is
    begin 
        return t;
    end;
end utils;
/

Usage and outcomes:

var rc refcursor
declare 
    tab1 utils.t_tab := utils.passon (); -- empty
    tab2 utils.t_tab := utils.passon (utils.t_tab (utils.t_row ('category', 50)));
begin
    open :rc for 
        select * from table (tab1) union all
        select * from table (tab2); 
end;
/

CATEGORY    MEASURE
-------- ----------
category         50

Ahmed Mohamed · Accepted Answer · 2020-09-11 11:44:35Z

jsut use a return statment without any parameters. I have already one table function that splits a string based in token. I modified the code for you as an example answet for your quetion. If you pass the first string null, the function will return no rows, otherwise will return each token in separate row based on the second parameter offcurse.

// create needed types
    
    // row object
    CREATE OR REPLACE TYPE T_TOKEN_ROW AS OBJECT (
      id           NUMBER,
      token_text  VARCHAR2(50)
    );
    
    // table object
    CREATE OR REPLACE TYPE T_TOKEN_TAB IS TABLE OF t_token_row;


// create table function to toknize a string
// input   : P_string    : the string to be toknized
//           P_separator : a character to separate tokens 
// Outputs : each token in separate record with id field
    
    CREATE OR REPLACE FUNCTION PIPE_tokens (P_string varchar2,P_separator char) RETURN t_token_tab PIPELINED
    AS
           sLine            VARCHAR2(2000);
            nPos            INTEGER;
            nPosOld         INTEGER;
            nIndex          INTEGER;
            nLength         INTEGER;
            nCnt            INTEGER;
            sToken          VARCHAR2(200);
    BEGIN
      
            if (P_string is null ) then
                return ;
            
            else 
            sLine := P_string;
            IF (SUBSTR(sLine, LENGTH(sLine), 1) <> '|') THEN
                sLine := sLine || '|';
            END IF;
            nPos := 0;
            sToken := '';
            nLength := LENGTH(sLine);
            nCnt := 0;
            FOR nIndex IN 1..nLength LOOP
                 IF ((SUBSTR(sLine, nIndex, 1) = P_separator) OR (nIndex = nLength)) THEN
                        nPosOld := nPos;
                        nPos := nIndex;
                        nCnt := nCnt + 1;
                        sToken := SUBSTR(sLine, nPosOld + 1, nPos - nPosOld - 1);
                        PIPE ROW(t_token_row(nCnt,sToken));
                        --tTokenTab(nCnt) := sToken;
                 END IF;
            END LOOP;
            
            RETURN;
            
            end if;
    END;


// 2 Test query

    select * from table(PIPE_tokens(null,';')) ;
    select * from table(PIPE_tokens('5;2;3',';')) ;

Collectives™ on Stack Overflow

PL/SQL Table Function - How to return an empty result set?

3 Answers 3

11 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related