Oracle 使用XMLTABLE PLS-00302

当我尝试使用XMLTYPE和XMLTABLLE从XML块中进行选择时，我得到了PLS-00302。

这工作，我得到了所需的输出...

SELECT xt.*
  FROM (SELECT xmltype (
'

Organization
Organization Title
Account
Account Title
01
Pooled
1234
My Account
')
                  xml_data
          FROM DUAL) x,
       XMLTABLE ('/TABLE/TR'
                 PASSING x.xml_data
                 COLUMNS "td1" VARCHAR2 (100) PATH 'TD[1]',
                         "td2" VARCHAR2 (100) PATH 'TD[2]',
                         "td3" VARCHAR2 (100) PATH 'TD[3]',
                         "td4" VARCHAR2 (100) PATH 'TD[4]') xt;
复制

输出看起来像这样...
td1 td2 td3 td4
组织机构名称账户名称
01池1234我的帐户

但是，当我尝试将该SQL放入PL/SQL时，出现302错误...

DECLARE
   lv1   VARCHAR2 (100);
   lv2   VARCHAR2 (100);
   lv3   VARCHAR2 (100);
   lv4   VARCHAR2 (100);
   lvc   CLOB
      := '

Organization
Organization Title
Account
Account Title
01
Pooled
1234
My Account
';
BEGIN
   FOR myrec
      IN (SELECT xt.*
            FROM (SELECT xmltype (lvc) xml_data FROM DUAL) x,
                 XMLTABLE ('/TABLE/TR'
                           PASSING x.xml_data
                           COLUMNS "td1" VARCHAR2 (100) PATH 'TD[1]',
                                   "td2" VARCHAR2 (100) PATH 'TD[2]',
                                   "td3" VARCHAR2 (100) PATH 'TD[3]',
                                   "td4" VARCHAR2 (100) PATH 'TD[4]') xt)
   LOOP
      DBMS_OUTPUT.put_line (
            'MYREC-->'
         || myrec.td1
         || ':'
         || myrec.td2
         || ':'
         || myrec.td3
         || ':'
         || myrec.td4
         );
   END LOOP;
END;
复制

当我注释对列的引用时，循环完成没有错误...

--         || myrec.td1
--         || ':'
--         || myrec.td2
--         || ':'
--         || myrec.td3
--         || ':'
--         || myrec.td4
复制

如何在游标for循环中引用这些列？

谢谢!

仔细检查该SQL的输出 :-)

SQL> SELECT xt.*
  2    FROM (SELECT xmltype (
  3  '
  4  
  5  
  6  
Organization
Organization Title
Account
Account Title
01
Pooled
1234
My Account
')
  7                    xml_data
  8            FROM DUAL) x,
  9         XMLTABLE ('/TABLE/TR'
 10                   PASSING x.xml_data
 11                   COLUMNS "td1" VARCHAR2 (100) PATH 'TD[1]',
 12                           "td2" VARCHAR2 (100) PATH 'TD[2]',
 13                           "td3" VARCHAR2 (100) PATH 'TD[3]',
 14                           "td4" VARCHAR2 (100) PATH 'TD[4]') xt;

td1
----------------------------------------------------------------------------------------------------
td2
----------------------------------------------------------------------------------------------------
td3
----------------------------------------------------------------------------------------------------
td4
----------------------------------------------------------------------------------------------------
Organization
Organization Title
Account
Account Title

01
Pooled
1234
My Account
复制

参见列不是 “TD1” 等，它们是 “td1”。它们是 * 小写 *。

因此，您需要在plsql代码中引用它们作为引用的标识符，即myrec。“td1”

或者你可以将你的SQL更改为:

列TD1 VARCHAR2 (100) 路径 'TD[1]'，