Problem with nested table function parameters

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Mon, 13 Nov 2006 23:17:50 GMT
Message-ID: <y276h.14180$B31.8455@newssvr27.news.prodigy.net>

I'm (still) trying to completely figure out table functions. I have them working okay alone but when I tried to nest two for the sake of trying to get it to work I get "wrong number or type of argument" error.

I'm probably overlooking something very simple but for the life of me cannot figure this out.

Below if the code used. Any help will be greatly appreciated. The statement where I receive the error is at the bottom of the code.

Thanks

---

CREATE OR REPLACE TYPE hmp_load_mbrhlth_obj AS OBJECT (

   person_id NUMBER(38),

   mbr_key   VARCHAR2(50),
   hmp_disease_code  CHAR(2),
   srv_dt_from   DATE,

   elig_code CHAR(1),
   mbr_sys_key VARCHAR2(50),
   data_source CHAR(1),
   sys_date DATE
);
/

CREATE OR REPLACE TYPE hmp_load_mbrhlth_tab AS TABLE OF hmp_load_mbrhlth_obj;
/

---

CREATE OR REPLACE TYPE min_visits_obj AS OBJECT (

   person_id NUMBER(38),
   srv_dt_from DATE
);
/

CREATE OR REPLACE TYPE min_visits_tab AS TABLE OF min_visits_obj; /

---

  FUNCTION load_mbrhlth ( p_rec IN min_visits_obj )

      RETURN hmp_load_mbrhlth_tab
      PIPELINED;

  FUNCTION load_min_visits
      RETURN min_visits_tab
      PIPELINED;

=========================================================

  FUNCTION load_mbrhlth ( p_rec IN min_visits_obj )
      RETURN hmp_load_mbrhlth_tab
      PIPELINED

  IS

    CURSOR cr_load_mbrhlth
    IS

      SELECT DISTINCT mbr_key,
                      hmp_disease_code,
                      elig_code,
                      mbr_sys_key,
                      data_source,
                      sys_date
                 FROM hmp_hold_mbrhlth
                WHERE person_id = p_rec.person_id
                  AND srv_dt_from = p_rec.srv_dt_from;

    v_out          hmp_load_mbrhlth_obj := hmp_load_mbrhlth_obj(

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL );     v_in cr_load_mbrhlth%ROWTYPE;

  BEGIN     OPEN cr_load_mbrhlth;

    LOOP

      FETCH cr_load_mbrhlth INTO v_in;
      EXIT WHEN cr_load_mbrhlth%NOTFOUND;

      v_out.person_id        := p_rec.person_id;
      v_out.mbr_key          := v_in.mbr_key;
      v_out.hmp_disease_code := v_in.hmp_disease_code;
      v_out.srv_dt_from      := p_rec.srv_dt_from;
      v_out.elig_code        := v_in.elig_code;
      v_out.mbr_sys_key      := v_in.mbr_sys_key;
      v_out.data_source      := v_in.data_source;
      v_out.sys_date         := v_in.sys_date;

      PIPE ROW ( v_out );

    END LOOP;     CLOSE cr_load_mbrhlth;

    RETURN;   EXCEPTION
    WHEN OTHERS THEN

      global.sql_error('hmp_load.load_mbrhlth',SQLCODE);
      RAISE global.e_unhandled_exception;

  END load_mbrhlth;

• -------------------------------------------------------------------------

  ---

  FUNCTION load_min_visits

      RETURN min_visits_tab
      PIPELINED

  IS

    CURSOR cr_load_min_visits
    IS

      SELECT person_id,
             MIN(srv_dt_from)     AS srv_dt_from
        FROM hmp_hold_mbrhlth
       GROUP BY person_id;

    v_out          min_visits_obj := min_visits_obj( NULL,NULL );

    v_in           cr_load_min_visits%ROWTYPE;

  BEGIN     OPEN cr_load_min_visits;

    LOOP

      FETCH cr_load_min_visits INTO v_in;
      EXIT WHEN cr_load_min_visits%NOTFOUND;

      v_out.person_id   := v_in.person_id;
      v_out.srv_dt_from := v_in.srv_dt_from;

      PIPE ROW ( v_out );

    END LOOP;     CLOSE cr_load_min_visits;

    RETURN;   EXCEPTION
    WHEN OTHERS THEN

      global.sql_error('hmp_load.load_min_visits',SQLCODE);
      RAISE global.e_unhandled_exception;

  END load_min_visits;

---

  PROCEDURE insert_to_load_mbrhlth
  IS

  BEGIN     INSERT /*+ APPEND NOLOGGING PARALLEL(hmp_load_mbrhlth) */ INTO hmp_load_mbrhlth

      SELECT DISTINCT *


==>        FROM TABLE( load_mbrhlth

                    ( CURSOR( SELECT *
                                FROM TABLE( load_min_visits() ) )));

    COMMIT;   EXCEPTION
    WHEN OTHERS THEN

      global.sql_error('hmp_load.insert_to_load_mbrhlth',SQLCODE);
      RAISE global.e_unhandled_exception;

  END insert_to_load_mbrhlth;

==> PLS-00306: wrong number or type of arguments in call to 'LOAD_MBRHLTH' Received on Mon Nov 13 2006 - 17:17:50 CST