Why am I getting PLS - 00382?

Question

Here is my object def:

CREATE OR REPLACE TYPE FALCON.contacts AS OBJECT (phone           VARCHAR2(50)
     ,phoneusage      VARCHAR2(25)
     ,phonetype       VARCHAR2(25)
     ,email           VARCHAR2(150)
     ,phoneext        VARCHAR2(25)
     ,anytext         VARCHAR2(250))

Here is the table def:

CREATE OR REPLACE TYPE FALCON.contacttbl AS TABLE OF contacts

Here is my pipelined function

FUNCTION get_pcontacts(p_conttbl IN xmltypedefs_spec.conttbl)
RETURN falcon.contacttbl
PIPELINED
IS
  l_contact falcon.contacts;
BEGIN
   FOR n IN 1 .. p_conttbl.count
   LOOP
      PIPE ROW(**falcon.contacts**(p_conttbl(n).phone, p_conttbl(n).phoneusage,              p_conttbl(n).phonetype, p_conttbl(n).email, p_conttbl(n).phoneext, p_conttbl(n).anytext));
   END LOOP;
   RETURN;
END get_pcontacts;

I am getting the error when I call the table function here:

FUNCTION get_pidxml(p_pidrec xmltypedefs_spec.pidtyp) 
RETURN CLOB
IS
  l_tmprec                 CLOB;
  l_pxml                   xmltype;
  l_bxml                   xmltype;
  l_pcontacts              xmltypedefs_spec.conttbl := p_pidrec.personalcont;
  l_bcontacts              xmltypedefs_spec.conttbl := p_pidrec.businesscont;

BEGIN

--      l_pxml := get_contacts(p_pidrec, 'p');
--      l_bxml := get_contacts(p_pidrec, 'b');

SELECT xmlelement("pid"
                    ,xmlforest(p_pidrec.setid AS "setID"
                              ,p_pidrec.patidexternal AS "patientIDExternal"
                              ,p_pidrec.patientid AS "patientID"
                              ,p_pidrec.patintasgnauth AS "patientIDInterAssignAuthority"
                              ,p_pidrec.patinttypecd AS "patientIDInternalIDTypeCode"
                              ,p_pidrec.patidalternate1 AS "patientIDAlernate1"
                              ,p_pidrec.patlastname AS "patientLastName"
                              ,p_pidrec.patfirstname AS "patientFirstName"
                              ,p_pidrec.patmiddleinit AS "patientMiddleInitial"
                              ,p_pidrec.patsuffix AS "patientSuffix"
                              ,p_pidrec.patprefix AS "patientPrefix"
                              ,p_pidrec.degree AS "degree"
                              ,p_pidrec.familyname AS "familyName"
                              ,p_pidrec.givenname AS "givenName"
                              ,p_pidrec.mothermaidname AS "mothersMaidenName"
                              ,p_pidrec.dob AS "dateOfBirth"
                              ,p_pidrec.adminsex AS "administrativeSex"
                              ,p_pidrec.patientalias AS "patientAlias"
                              ,p_pidrec.race AS "race"
                              ,p_pidrec.racetext AS "raceText"
                              ,p_pidrec.pataddr1 AS "patientAddress1"
                              ,p_pidrec.pataddr2 AS "patientAddress2"
                              ,p_pidrec.patcity AS "patientCity"
                              ,p_pidrec.patstate AS "patientState"
                              ,p_pidrec.patzip AS "patientZip"
                              ,p_pidrec.countrycode AS "countryCode"
                              ,p_pidrec.addresstype AS "addressType"
                              ,p_pidrec.othgeodesig AS "otherGeographicDesignation"
                              ,p_pidrec.county AS "county"

                              ,(SELECT xmlagg(xmlelement("contactInfo",
                                            xmlforest(phone AS "phoneNumber",
                                                      phoneusage AS "telecomUseCode",
                                                      phonetype AS "telecomequiptype",
                                                      email AS "email",
                                                      phoneext AS "phonenumberextension",
                                                      anytext AS "anytext")))
                               FROM TABLE(**get_pcontacts(l_pcontacts**))) AS "personalContact"

Answer 1

http://pls-00382.ora-code.com/

PLS-00382: expression is of wrong type

Since I don't know how xmltypedefs_spec.conttbl is defined, I removed the input parameter from the pipelined function and just had it generate fake data on the fly:

CREATE OR REPLACE FUNCTION get_contacts
    RETURN contacttbl PIPELINED
IS
  -- converts some structure to pipe of contacts
BEGIN
   FOR n IN 1 .. 5 LOOP
      PIPE ROW( 
         contact( 
            '877-867-5309', 
            'Work',
            'Cell', 
            '[email protected]', 
            n, 
            'WTF?'
         )
      );
   END LOOP;
   RETURN;
END get_contacts;

The subquery now executes without error:

SELECT 
    xmlagg(
        xmlelement("contactInfo",
            xmlforest(
                phone AS "phoneNumber",
                phoneusage AS "telecomUseCode",
                phonetype AS "telecomequiptype",
                email AS "email",
                phoneext AS "phonenumberextension",
                anytext AS "anytext"
            )
        )
    )
FROM 
    TABLE( get_contacts( ) )

This tells me there is probably something wrong with xmltypedefs_spec.conttbl, perhaps in using a collection type within an SQL statement? Not sure. What if you changed xmltypedefs_spec.pidtyp to use the falcon.contacttbl instead of xmltypedefs_spec.conttbl. Seems like you've got one package type and one object type that are doing the same thing?

Answer 2

xmltypedefs_spec defines record types that correspond to XML elements. These record types are used to shred and build XML. Originally, the XML did not use repeating elements, but now must. I am attempting to take a table of xmltypedefs_spec.pidtyp and use the pipelined function to return 'rows' of data from an associative table. It is in this fashion that I want to send rows of array records to build xml.