为什么我会收到 PLS - 00382？

Question

这是我的对象 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))

这是表 def:

CREATE OR REPLACE TYPE FALCON.contacttbl AS TABLE OF contacts

这是我的管道函数

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;

当我在此处调用表函数时收到错误：

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：表达式类型错误

由于我不知道 xmltypedefs_spec.conttbl 是如何定义的，因此我从管道函数中删除了输入参数，并让它动态生成假数据：

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;

子查询现在执行时没有错误：

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( ) )

这告诉我可能有xmltypedefs_spec.conttbl 有问题，可能是在 SQL 语句中使用集合类型？没有把握。如果您将 xmltypedefs_spec.pidtyp 更改为使用 falcon.contacttbl 而不是 xmltypedefs_spec.conttbl，会怎样？似乎您有一种包类型和一种对象类型在做同样的事情？

Answer 2

xmltypedefs_spec 定义与 XML 元素相对应的记录类型。这些记录类型用于分解和构建 XML。最初，XML 不使用重复元素，但现在必须使用。我试图获取 xmltypedefs_spec.pidtyp 表并使用管道函数从关联表返回“行”数据。我想以这种方式发送数组记录行来构建 xml。