-
suing refcursor in a select statement
I am using a refcursor within a oracle function. The fucntion returns multiple rows(cursor), I woruld like to collect the values returned by the cursor into a select statement. I need to use a select to do the collection of the values returned because I use the reporting tool crystal report 8.0 . if there is any method where I can call a pl/sql block to collect the values returend by a function which can be called in crystal report I would like to know about it. My code goes as follows.
create or replace package util as
type cursorType is ref cursor;
end;
/
create or replace function cal_rowise_util(fdt in varchar2,
tdt in varchar2,
lno in prod_line_diemast_t.line_no%type)
return util.cursortype as l_cursor util.cursorType;
begin
open l_cursor for select (nvl(nvl(a.prob_qty_ok,0) * c.cycle_time_cutting,0)/3600)
/cal_avail_hrs(fdt,tdt,lno) util,
a.prod_date,
a.shift_no
from prod_hotinsp_dt a,
prod_hotinsp_hd b,
prod_line_diemast_t c
where a.line_no = b.line_no
and b.line_no = c.line_no
and a.shift_no = b.shift_no
and a.prod_date = b.prod_date
and a.die_no = c.die_no
and a.line_no = lno
and a.prod_date >= fdt
and a.prod_date <= decode(a.shift_no, 3, to_char(to_date(tdt) + 1,'DD-MON-YYYY'),
to_char(to_date(tdt),'DD-MON-YYYY'));
return l_cursor;
end;
/
and I execute the function like this,
variable c refcursor;
exec :c:= cal_rowise_util('02-JAN-1999','02-JAN-1999','PZS 900');
print c;
and I get a set of values like this
UTIL PROD_DATE SHIFT_NO
--------- --------- ---------
.14322917 02-JAN-99 1
.05983796 02-JAN-99 1
.12731481 02-JAN-99 2
.00954861 03-JAN-99 3
.06365741 02-JAN-99 2
These are a set of values I would like to display in a select in
a crystal report, since I have other fucntions to return other values too, to be called in a same select statement.
-
Assuming you have the latest version of Oracle
CREATE TYPE ty_emp AS object (eno NUMBER,ename VARCHAR2(30))
/
CREATE TYPE tab_emp AS TABLE OF ty_emp
/
CREATE OR REPLACE PACKAGE pk IS
t_emp tab_emp := tab_emp();
t_var VARCHAR2(10);
END pk;
/
CREATE OR REPLACE FUNCTION ret_type RETURN tab_emp IS
BEGIN
RETURN pk.t_emp;
END ret_type;
/
DECLARE
i PLS_INTEGER;
j PLS_INTEGER;
BEGIN
/* Insert into the table type in the package with some dummy values*/
pk.t_emp.DELETE; --For deleting the table array
--i := NVL (pk.t_emp.LAST, 0);
FOR j IN 1..10 LOOP
pk.t_emp.EXTEND;
pk.t_emp (j) := ty_emp (j, 'test'||j);
END LOOP;
FOR i IN pk.t_emp.first..pk.t_emp.last LOOP
--dbms_output.put_line(pk.t_emp(pk.t_emp.COUNT).ename);
dbms_output.put_line(pk.t_emp(i).ename);
END LOOP;
END;
/
select * from TABLE(cast(ret_type as tab_emp));
That should help you, also read the manuals how cast works.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|