SQL
>
create table test
(
name varchar2
(
4
),
text varchar2
(
9
));
SQL
>
create view test_view
as
select text from test where name
=
userenv
(
'client_info'
);
SQL
>
insert into test values
(
'zxx'
,
'boy'
);
SQL
>
insert into test values
(
'taw'
,
'gril'
);
SQL
>
commit
;
SQL
>
exec dbms_application_info
.
set_client_info
(
'zxx'
);
SQL
>
select
*
from test_view
;
TEXT
---------
boy
--
a
create or replace type emp_type
as object (
empno number(4),
ename varchar2(10)
);
/
create or replace type emp_type_table as table of emp_type;
/
create or replace function emp_of_dept(p_deptno in number)
return emp_type_table
pipelined as
l_emp_type emp_type;
begin
for l_myrow in (select empno, ename from emp
where deptno = p_deptno) loop
l_emp_type := emp_type(l_myrow.empno, l_myrow.ename);
pipe row (l_emp_type);
end loop;
return;
end emp_of_dept;
/
select * from table(emp_of_dept(10));
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER
select * from table(emp_of_dept(30));
EMPNO ENAME
---------- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES
Tom - what do you think about this?
Followup:
I think it is alot more code then:
create or replace
procedure foo( p_deptno in number, p_result_set out sys_refcursor )
as
begin
open p_result_set for
select empno, ename from emp where deptno = p_deptno;
end;
and having the client (which is doing Oracle stuff anyway) issue
begin foo( :n, :m ); end;
It'll be less performant as well.
Neat "trick" but not best practice.
create or replace function f_test(p_num in number)
return sys_refcursor
is
Result sys_refcursor;
begin
open Result for
select * from t1 t where t1.c1=p_num;
return(Result);
exception
when others then
raise;
end;
select f_test(1) from dual;