Re: Coding question - piping rows from a function

FUNCTION read_claims( p_in_date IN DATE )

    RETURN claim_tab
    PIPELINED
IS

  CURSOR cr_cursor( p_in_date DATE )
  IS
    SELECT a.field1,

           b.field1
      FROM clm_hdr a,
           clm_hdr b
     WHERE b.srv_dt_from >= p_in_date
       AND a.sys_clm_hdr_id = b.sys_clmhdr_id;

  v_out      claim_obj := claim_obj( NULL,NULL );

BEGIN   FOR c1 IN cr_cursor ( p_in_date )
  LOOP     v_out.field1 := c1.field1;
    v_out.field2 := c1.field2;

    PIPE ROW ( v_out );

  END LOOP;   RETURN; END; PROCEDURE main
IS

  CURSOR cr_cursor( p_in_date DATE )
  IS
    SELECT *
      FROM TABLE( read_claims( p_in_date ) );

BEGIN   v_date := SYSDATE-365;

  FOR c1 IN cr_cursor( v_date )
  LOOP     ... process ...

  END LOOP; END; The tables in the function's cursor have the following row counts:

     clm_hdr - 35,520,410 rows
     clm_dtl - 78,871,356 rows

I got a similar query to "work" with another table but without the passing of parameters. Could I be causing a cartesian join with the code for these two tables or just have the parameters giving me trouble? Received on Tue Mar 20 2007 - 16:39:55 CDT