Thanks Micheal , appreciate your help.
I tried out what you said, but it is not working,
i am using oracle 8i, and below is the working example(works with
scott schema).
Please guide me if am going wrong some where
===================================================
Create or replace PACKAGE Pak
IS
TYPE pkd_cursor IS REF CURSOR;
end pak;
/
Create or replace type t_rec is table of varchar2(20);
===========================================================================================
Create or replace PACKAGE Pak
CREATE OR REPLACE FUNCTION fun_emp (pvar t_rec)
RETURN pak.pkd_cursor
AS
pkddetails pak.pkd_cursor;
mempid VARCHAR2 (1000);
BEGIN
--mempid := 'select * from emp where ENAME IN ( select * from
table (pvar))';
--mempid := 'select * from emp where ENAME IN ( select * from
THE( cast(pvar) as t_rec ))';
-- mempid := 'select * from emp where ENAME IN ( select * from the
(pvar))';
mempid := 'select * from emp where ENAME IN ( select * from table
(cast(pvar) as t_rec))';
OPEN pkddetails FOR mempid;
RETURN pkddetails;
END fun_emp;
=======calling the function below==================================
declare
v_rec t_rec:=t_rec();
v_cur pak.pkd_cursor;
Memp emp%rowtype;
begin
v_rec.extend;
v_rec(1):='SMITH';
v_rec.extend;
v_rec(2):='ALLEN';
v_cur:=fun_emp(v_rec);
Loop
fetch v_cur into Memp;
exit when v_cur%notfound;
dbms_output.put_line('Testing'||Memp.ename);
end Loop;
end;
/
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
