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.