cursor result set in pipelined function

From: g3000 <carlton_gregory_at_yahoo.com>
Date: 6 Oct 2005 09:20:52 -0700
Message-ID: <1128615652.391015.298050@o13g2000cwo.googlegroups.com>

9.2.0.6 win2k I have:
CREATE OR REPLACE TYPE pending_issues_type AS OBJECT (

ME_SEQ                                             NUMBER,
PERS_SEQ                                           NUMBER,
NAME                                               VARCHAR2(4000),
SSN                                                VARCHAR2(9),
RATE                                               VARCHAR2(10),
START_DATE                                         DATE,
TYPE_ID                                            VARCHAR2(1),
TYPE_DESC                                          VARCHAR2(30),
PROVIDER                                           VARCHAR2(4000),
FINALIZED_DATE                                     DATE,
LOG_DTG                                            DATE,
SSN_LAST_4                                         VARCHAR2(4),
SSN_LAST_2                                         VARCHAR2(9),
FMPC_ID                                            VARCHAR2(2),
SORT_FMPC_ID                                       VARCHAR2(2),
STATUS_GROUP                                       VARCHAR2(1),
DEPT_NAME                                          VARCHAR2(20),
CMD_NAME                                           VARCHAR2(100),
WC_NAME                                            VARCHAR2(20),
DIV_NAME                                           VARCHAR2(20),
PAYGRADE_CLASS                                     VARCHAR2(4),
CMD_DOD_UIC                                        VARCHAR2(8),
CMD_ASSIGNMENT                                     VARCHAR2(32),
VISITOR_IND                                        VARCHAR2(1) )

/

CREATE OR REPLACE TYPE pending_issues_set_type AS TABLE OF pending_issues_type;
/
SHOW ERROR @me_lib.pks

SHOW ERROR @me_lib.pkb

SHOW ERROR CREATE OR REPLACE VIEW ME_PENDING_ISSUES_VW (     ME_SEQ,
    PERS_SEQ,
    NAME,
    SSN,
    RATE,
    START_DATE,
    TYPE_ID,
    TYPE_DESC,
    PROVIDER,
    FINALIZED_DATE,

    LOG_DTG,
    SSN_LAST_4,
    SSN_LAST_2,

    FMPC_ID,
    SORT_FMPC_ID,
    STATUS_GROUP,
    DEPT_NAME,
    CMD_NAME,
    WC_NAME,
    DIV_NAME,
    PAYGRADE_CLASS,
    CMD_DOD_UIC,
    CMD_ASSIGNMENT,
    VISITOR_IND )
AS
 SELECT * FROM TABLE ( me_lib.get_pending_issues() );

The function get_pending_issues looks like: FUNCTION get_pending_issues RETURN pending_issues_set_type PIPELINED
IS
  cursor c_issues is

         SELECT         mp.me_seq,
                        mp.pers_seq,
                        hs_lib.get_pers_name(mp.pers_seq),
                        mp.me_ssn,
                        mp.me_rate,
                        mp.me_start_date,
                        mp.me_type_id,
                        mt.type_desc,
                        sams_system.getprovidername(mp.prov_seq),
                        mp.finalized_date,
                        ml.log_dtg,
                        rhv.ssn_last_4,
                        rhv.ssn_last_2,
                        rhv.fmpc_id,
                        rhv.sort_fmpc_id,
                        rhv.status_group,
                        rhv.dept_name,
                        rhv.cmd_name,
                        rhv.wc_name,
                        rhv.div_name,
                        rhv.paygrade_class,
                        rhv.cmd_dod_uic,
                        rhv.cmd_assignment,
                        rhv.visitor_ind
                   FROM me_report_header_vw rhv,
                        me_pers             mp,
                        me_prov_log         ml,
                        me_type             mt
                  WHERE mp.finalized_date IS NULL
                    AND mp.me_type_id      != 'R'
                    AND mp.log_seq         = ml.log_seq
                    AND mp.me_type_id      = mt.type_id
                    AND mp.me_seq          = rhv.me_seq
                UNION
                 SELECT mp.me_seq,
                        mp.pers_seq,
                        hs_lib.get_pers_name(mp.pers_seq),
                        mp.me_ssn,
                        mp.me_rate,
                        mm.mer_date,
                        decode(mp.me_type_id, 'S','Z','R'),
                        decode(mp.me_type_id, 'S','MER(via
SOAPP)','MER'),
                        sams_system.getprovidername(mp.prov_seq),
                        mm.finalized_date,
                        ml.log_dtg,
                        rhv.ssn_last_4,
                        rhv.ssn_last_2,
                        rhv.fmpc_id,
                        rhv.sort_fmpc_id,
                        rhv.status_group,
                        rhv.dept_name,
                        rhv.cmd_name,
                        rhv.wc_name,
                        rhv.div_name,
                        rhv.paygrade_class,
                        rhv.cmd_dod_uic,
                        rhv.cmd_assignment,
                        rhv.visitor_ind
                   FROM me_report_header_vw rhv,
                        me_pers             mp,
                        me_prov_log         ml,
                        me_type             mt,
                        me_mer              mm
    -- line 981   WHERE mm.finalized_date IS NULL
                    AND mp.log_seq         = ml.log_seq
                    AND mp.me_type_id      = mt.type_id
                    AND mp.me_seq          = rhv.me_seq;
BEGIN
      FOR x IN c_issues LOOP

             PIPE ROW( x );
      END LOOP;
             RETURN;

END get_pending_issues;

but when I try to compile the package I get

SQL> @me_lib.pkb
DOC>***********************************************************************
DOC>* ME_LIB.PKB

*

DOC>* As of 06 Oct 2005 @ 09:16.
*

DOC>*---------------------------------------------------------------------*

DOC>*
*

DOC>**********************************************************************/

Creating Package Body 'ME_LIB'
DOC>* ME_LIB package body

DOC>*************************************************************************

Warning: Package Body created with compilation errors.

SQL> show error
Errors for PACKAGE BODY ME_LIB:

LINE/COL ERROR

---

---

981/14 PL/SQL: Statement ignored
981/24 PLS-00382: expression is of wrong type

I even tried without defining a explicit cursor in did the for x in ( select .... ) loop but got the same error

any ideas? Received on Thu Oct 06 2005 - 11:20:52 CDT