-- see if this works
CREATE OR REPLACE PACKAGE pak IS
TYPE pkd_cursor IS REF CURSOR;
END pak;
/
CREATE TYPE list_object AS OBJECT (
list_value VARCHAR2 (35)
);
CREATE TYPE list_table AS TABLE OF list_object;
CREATE OR REPLACE FUNCTION fun_emp (pvar list_table)
RETURN pak.pkd_cursor AS
pkddetails pak.pkd_cursor;
mempid VARCHAR2 (1000);
BEGIN
mempid :=
'select * from emp where ENAME IN ( select list_value '
||' from table (cast(:pvar as list_table)))';
OPEN pkddetails FOR mempid using pvar;
RETURN pkddetails;
END fun_emp;
/
DECLARE
v_rec list_table :=
list_table (list_object ('SMITH'), list_object ('SCOTT'));
v_cur pak.pkd_cursor;
memp emp%ROWTYPE;
x integer;
BEGIN
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;
/
--*******************************************************
Notice a couple of things.
1) the use of a cursor parameter i.e.:pvar
2) it is (cast(:pvar as list_table)) not (cast(:pvar) as list_table)
and most of all you need to use a "TABLE OF OBJECTs" as your collection in
order to use the TABLE function.
CREATE TYPE list_object AS OBJECT (
list_value VARCHAR2 (35)
);
CREATE TYPE list_table AS TABLE OF list_object;
not sure if this will all work in 8.1.7 but it should.,
As a final note, you are returning a ref cursor. I would have simply
returned the Object Table. Maybe your assignment required you to use a
ref_cursor?
regards
Michael
On Fri, Oct 24, 2008 at 2:42 AM, somy <[EMAIL PROTECTED]> wrote:
>
> 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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
