You can query a pipelined function using the TABLE function.
Here are some articles that should get you going:
I know that is because when I query a view the engine first brings all the rows of the view and then it filters the rows for the criteria supplied.
No - you don't 'know that' because it isn't true. Just check the explain plan for yourself. Oracle can still use any appropriate indexes so that only the rows needed are returned.
So my ultimate question is if it's somehow possible to select from the resultset that is returned from a function like this:
SELECT * FROM FUNCTION_1(12132323232).
No - you can't do it like that. You have to use the TABLE function to treat the function result set as a table:
'SELECT * FROM TABLE(FUNCTION_1(12132323232)).
-- type to match emp record
create or replace type emp_scalar_type as object
(EMPNO NUMBER(4) ,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
-- table of emp records
create or replace type emp_table_type as table of emp_scalar_type
-- pipelined function
create or replace function get_emp( p_deptno in number )
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
open emp_cv for select * from emp where deptno = p_deptno;
fetch emp_cv into l_rec;
exit when (emp_cv%notfound);
pipe row( emp_scalar_type( l_rec.empno, LOWER(l_rec.ename),
l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal, l_rec.comm, l_rec.deptno ) );
select * from table(get_emp(20))