DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: suing refcursor in a select statement

  1. #1
    Join Date
    Apr 2003
    Posts
    29

    Question 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.

  2. #2
    Join Date
    Dec 2000
    Posts
    138
    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
  •  


Click Here to Expand Forum to Full Width