How to replace nested table with Oracle SQL function or stored procedure

Question

In Oracle, if I have the following two tables

CREATE TABLE Test_Persons_A (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons_A
    (PersonID,LastName,FirstName)
    values(11,'LN_A1','FN_A1');

INSERT INTO Test_Persons_A
    (PersonID,LastName,FirstName)
    values(12,'LN_A2','FN_A2');

CREATE TABLE Test_Persons_B (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons_B
    (PersonID,LastName,FirstName)
    values(21,'LN_B1','FN_B1');

INSERT INTO Test_Persons_B
    (PersonID,LastName,FirstName)
    values(22,'LN_B2','FN_B2');

commit;

and I can do a query of a union as below:

with
UNIONED as
(    
    --block A to replaced by calling a function or stored proc---
    select PersonID as PID, LastName as LN, FirstName as FN
        from Test_Persons_A tp
        where tp.LASTNAME like '%1%'

    union

    --block B to replaced by calling a function or stored proc---
    select PersonID as PID, LastName as LN, FirstName as FN
        from Test_Persons_B tp
        where tp.LASTNAME like '%2%'
)

select * from UNIONED;

My question is how can i achieve something like the following:

with
UNIONED as
(    
    --block A replaced---
    myFuncOrStoredProc('Test_Persons_A', '%1%');

    union

    --block B replaced---
    myFuncOrStoredProc('Test_Persons_B', '%2%');
)

select * from UNIONED;

There has been a lot of post on 'how to return a table (or multiple rows of data) from a function or stored proc'. But because of the numerous answers, I feel more confused.

Pipeline table function (https://stackoverflow.com/a/2830722/2041023)
Bulk Collect (https://stackoverflow.com/a/2832735/2041023)
Cursor (https://stackoverflow.com/a/46998148/2041023)

For such a small and simple (and I assume quite common) scenario and purpose, what could be the cleanest way to do it? (in particular, I prefer to avoid having to specify the type of each column for the returned table.)

score 1 · Answer 1 · answered Jul 12 '18 at 23:26

1

Oracle Setup:

CREATE TABLE Test_Persons_A ( PersonID, LastName, FirstName ) AS
  SELECT 11, 'LN_A1', 'FN_A1' FROM DUAL UNION ALL
  SELECT 12, 'LN_A2', 'FN_A2' FROM DUAL;

CREATE TABLE Test_Persons_B ( PersonID, LastName, FirstName ) AS
 SELECT 21, 'LN_B1', 'FN_B1' FROM DUAL UNION ALL
 SELECT 22, 'LN_B2', 'FN_B2' FROM DUAL;

CREATE TYPE test_obj IS OBJECT(
  PersonID INT,
  FirstName VARCHAR2(255),
  LastName VARCHAR2(255)
);
/

CREATE TYPE test_tbl IS TABLE OF test_obj;
/

CREATE OR REPLACE FUNCTION test_fn(
  table_name VARCHAR2,
  lastNameFilter VARCHAR2
) RETURN test_tbl
IS
  v_tbl test_tbl;
BEGIN
  IF UPPER( table_name ) = 'TEST_PERSONS_A' THEN
    SELECT TEST_OBJ( PersonID, LastName, FirstName )
    BULK COLLECT INTO v_tbl
    FROM   test_persons_a
    WHERE  LastName LIKE lastNameFilter;
  ELSIF UPPER( table_name ) = 'TEST_PERSONS_B' THEN
    SELECT TEST_OBJ( PersonID, LastName, FirstName )
    BULK COLLECT INTO v_tbl
    FROM   test_persons_b
    WHERE  LastName LIKE lastNameFilter;
  END IF;
  RETURN v_tbl;
END;
/

Query 1 Use MULTSET UNION [ALL|DISTINCT] in a single table collection expression:

SELECT *
FROM   TABLE(
         test_fn( 'TEST_PERSONS_A', '%1%' )
         MULTISET UNION ALL
         test_fn( 'TEST_PERSONS_B', '%2%' )
       );

Query 2 Use UNION [ALL] and multiple table collection expressions:

SELECT * FROM TABLE( test_fn( 'TEST_PERSONS_A', '%1%' ) )
UNION ALL
SELECT * FROM TABLE( test_fn( 'TEST_PERSONS_B', '%2%' ) );

Output for both queries:

  PERSONID FIRSTNAME LASTNAME
---------- --------- --------
        11 LN_A1     FN_A1
        22 LN_B2     FN_B2

answered Jul 12 '18 at 23:26

MT0

143,790
11
59
117

thanks a lot MT0. so there is no way I can get around having to define a table for each of the columns even if I just want to return table of the same structure of certain table? (e.g. if I want to return select * from Test_Persons) – Stochastika Jul 13 '18 at 12:41
1

@Stochastika You can return a cursor with the same structure as a table without defining an object to hold the data but you cannot union two cursors. You can union two collections but if your collection contains objects with multiple columns then you need to have defined an object with those columns. Maybe I'm not understanding the point you are trying to make but you can't use a union and not know the columns. – MT0 Jul 13 '18 at 13:04
@MTO, I just noticed, the main body of the logic the select within the function in your answer: 'SELECT TEST_OBJ( PersonID, LastName, FirstName ) BULK COLLECT INTO v_tbl FROM test_persons_b WHERE LastName LIKE lastNameFilter;', problem here is that I still have to do copy and paste then? if so, is the only choice to avoid that to use Dynamic SQL, if I want the table name to be a 'variable'? – Stochastika Jul 13 '18 at 14:20
@Stochastika You can use dynamic SQL if you want - I chose to whitelist the valid table names and use static SQL as it is less of vulnerability than dynamic SQL. – MT0 Jul 13 '18 at 14:48
That's one thing that has puzzled me for a while: is there no other way to use a variable for a table in code block similar to yours above at all? especially if, in this case, we know table TEST_PERSONS_A and TEST_PERSONS_B has exactly the same structure? – Stochastika Jul 13 '18 at 15:25
@Stochastika Yes, use `EXECUTE IMMEDIATE` and dynamic SQL. – MT0 Jul 13 '18 at 15:32

How to replace nested table with Oracle SQL function or stored procedure

1 Answers1