Return table on function + object types + Oracle

Sometimes we need to function on oracle, return of table because propably you need to send dinamic parameters of query, you can resolve the problem with materialized view or view but both your parameters is define, then when you want change the params you can replace or create again. but with objetc types not is necessary.

I suggest you, to read to concept object types. because i only share the example but not concept.

Step 1. i create my real or exist table and insert. for this example.

CREATE TABLE my_real_table
  (
     username VARCHAR2(25),
     age      NUMBER
  ); 

INSERT ALL
INTO my_real_table (username, age) VALUES ('User1', 25)
INTO my_real_table (username, age) VALUES ('User2', 25)
INTO my_real_table (username, age) VALUES ('User3', 33)
INTO my_real_table (username, age) VALUES ('User4', 30)
INTO my_real_table (username, age) VALUES ('User5', 40)
SELECT *
FROM   dual; 

Step 2.

Create a object of your table.

CREATE OR replace TYPE obj_test AS object (
  username VARCHAR2(25),
  age      NUMBER ); 

Step 3.

Create a type of your object.

CREATE OR replace TYPE t_test
  AS TABLE OF OBJ_TEST; 

Step 4. Create of function with parameters and return the table depends on parameters.

CREATE OR replace FUNCTION Fu_test (v_age IN NUMBER)
RETURN T_TEST
IS
  return_value T_TEST;
BEGIN
    SELECT Obj_test(username, age)
    bulk   collect INTO return_value
    FROM   (SELECT username,
                   age
            FROM   my_real_table
            WHERE  age > = v_age);

    RETURN return_value;
END; 

Step 4. you can send diferent parameters the function and return table. 

SELECT *
FROM   TABLE(Fu_test(30)); 

That it is.