xml - ORA-00928: 在存储过程中传递 XML 时出现多个错误

Question

我正在使用XML如下方式将数据插入表中：

PROCEDURE insert_expt_excel(strxml  IN xmltype
                           ,poutmsg OUT NVARCHAR2)

 AS
  cnt NUMBER := 0;
BEGIN

  /* after update, delete query */
  FOR tmmapping IN (SELECT strxml.extract('excelData/R4GSTATE/text()').getstringval() AS r4gstate
                          ,strxml.extract('excelData/POLITICAL_STATE_NAME/text()').getstringval() AS political_state_name
                          ,strxml.extract('excelData/POLITICAL_STATE_CODE/text()').getstringval() AS political_state_code
                          ,strxml.extract('excelData/CMP/text()').getstringval() AS cmp
                          ,strxml.extract('excelData/SAP_ID/text()').getstringval() AS sap_id
                          ,strxml.extract('excelData/SITE_NAME/text()').getstringval() AS site_name
                          ,strxml.extract('excelData/RFCDATE/text()').getstringval() AS rfcdate
                          ,strxml.extract('excelData/RFS_DATE/text()').getstringval() AS rfs_date
                          ,strxml.extract('excelData/RFE1_DATE/text()').getstringval() AS rfe1_date
                          ,strxml.extract('excelData/SITE_DROP_DATE/text()').getstringval() AS site_drop_date
                          ,strxml.extract('excelData/INFRA_PROVIDER/text()').getstringval() AS infra_provider
                          ,strxml.extract('excelData/IP_COLO_SITEID/text()').getstringval() AS ip_colo_siteid
                          ,strxml.extract('excelData/VENDOR_CODE/text()').getstringval() AS vendor_code
                          ,strxml.extract('excelData/MW_INSTALLED/text()').getstringval() AS mw_installed
                          ,strxml.extract('excelData/DG_NONDG/text()').getstringval() AS dg_nondg
                          ,strxml.extract('excelData/EB_NONEB/text()').getstringval() AS eb_noneb
                          ,strxml.extract('excelData/TOWER_TYPE/text()').getstringval() AS tower_type
                          ,strxml.extract('excelData/ID_OD_COUNTCHANGE/text()').getstringval() AS id_od_countchange
                          ,strxml.extract('excelData/ID_OD_CHANGEDDATE/text()').getstringval() AS id_od_changeddate
                          ,strxml.extract('excelData/TENANCY_COUNTCHANGE/text()').getstringval() AS tenancy_countchange
                          ,strxml.extract('excelData/TENANCY_CHANGEDDATE/text()').getstringval() AS tenancy_changeddate
                      FROM TABLE(xmlsequence(strxml.extract('NewDataSet/excelData'))) strxml)
  LOOP
    INSERT INTO tbl_ipcolo_billing_mst
      (cmp, sap_id, id_od_countchange, id_od_changeddate, rrh_countchange,
       rrh_changeddate, tenancy_countchange, tenancy_changeddate, rfs_date,
       rfe1_date, infra_provider, ip_colo_siteid, site_name, r4gstate,
       mw_installed, dg_nondg, eb_noneb, tower_type, vendor_code, rfcdate,
       political_state_name, political_state_code, site_drop_date)
    VALUES
      (tmmapping.cmp, tmmapping.sap_id, tmmapping.id_od_countchange,
       tmmapping.id_od_changeddate, tmmapping.rrh_countchange,
       tmmapping.rrh_changeddate, tmmapping.tenancy_countchange,
       tmmapping.tenancy_changeddate, tmmapping.rfs_date, tmmapping.rfe1_date,
       tmmapping.infra_provider, tmmapping.ip_colo_siteid, tmmapping.site_name,
       tmmapping.r4gstate, tmmapping.mw_installed, tmmapping.dg_nondg,
       tmmapping.eb_noneb, tmmapping.tower_type, tmmapping.vendor_code,
       tmmapping.rfcdate, tmmapping.political_state_name,
       tmmapping.political_state_code, tmmapping.site_drop_date);
  END LOOP;

  cnt := SQL%ROWCOUNT;

  IF (cnt > 0)
  THEN
    BEGIN
      COMMIT;
      poutmsg := '1';
    END;
  ELSE
    BEGIN
      ROLLBACK;
      poutmsg := '0';

    END;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;

    poutmsg := '0';

END insert_expt_excel;

但我收到错误：

ORA-06550: line 28, column 24:
PL/SQL: ORA-00904: "STRXML"."EXTRACT": invalid identifier
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 81, column 2:
PLS-00364: loop index variable 'TMMAPPING' use is invalid
ORA-06550: line 81, column 12:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 31, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 85, column 1:
PLS-00201: identifier 'CNT' must be declared
ORA-06550: line 85, column 1:
PL/SQL: Statement ignored
ORA-06550: line 87, column 4:
PLS-00201: identifier 'CNT' must be declared
ORA-06550: line 87, column 1:
PL/SQL: Statement ignored

标签： xmloraclestored-procedures

Answer 1

我可以通过尝试完全按照发布的方式运行代码来重现您遇到的错误。

缺少的是该过程所属的包或它前面的“创建或替换”。删除插入语句（我没有表）后，此代码将编译。

create or replace PROCEDURE insert_expt_excel(strxml  IN xmltype
                           ,poutmsg OUT NVARCHAR2)

 AS
  cnt NUMBER := 0;
BEGIN

  /* after update, delete query */
  FOR tmmapping IN (SELECT strxml.extract('excelData/R4GSTATE/text()').getstringval() AS r4gstate
                          ,strxml.extract('excelData/POLITICAL_STATE_NAME/text()').getstringval() AS political_state_name
                          ,strxml.extract('excelData/POLITICAL_STATE_CODE/text()').getstringval() AS political_state_code
                          ,strxml.extract('excelData/CMP/text()').getstringval() AS cmp
                          ,strxml.extract('excelData/SAP_ID/text()').getstringval() AS sap_id
                          ,strxml.extract('excelData/SITE_NAME/text()').getstringval() AS site_name
                          ,strxml.extract('excelData/RFCDATE/text()').getstringval() AS rfcdate
                          ,strxml.extract('excelData/RFS_DATE/text()').getstringval() AS rfs_date
                          ,strxml.extract('excelData/RFE1_DATE/text()').getstringval() AS rfe1_date
                          ,strxml.extract('excelData/SITE_DROP_DATE/text()').getstringval() AS site_drop_date
                          ,strxml.extract('excelData/INFRA_PROVIDER/text()').getstringval() AS infra_provider
                          ,strxml.extract('excelData/IP_COLO_SITEID/text()').getstringval() AS ip_colo_siteid
                          ,strxml.extract('excelData/VENDOR_CODE/text()').getstringval() AS vendor_code
                          ,strxml.extract('excelData/MW_INSTALLED/text()').getstringval() AS mw_installed
                          ,strxml.extract('excelData/DG_NONDG/text()').getstringval() AS dg_nondg
                          ,strxml.extract('excelData/EB_NONEB/text()').getstringval() AS eb_noneb
                          ,strxml.extract('excelData/TOWER_TYPE/text()').getstringval() AS tower_type
                          ,strxml.extract('excelData/ID_OD_COUNTCHANGE/text()').getstringval() AS id_od_countchange
                          ,strxml.extract('excelData/ID_OD_CHANGEDDATE/text()').getstringval() AS id_od_changeddate
                          ,strxml.extract('excelData/TENANCY_COUNTCHANGE/text()').getstringval() AS tenancy_countchange
                          ,strxml.extract('excelData/TENANCY_CHANGEDDATE/text()').getstringval() AS tenancy_changeddate
                      FROM TABLE(xmlsequence(strxml.extract('NewDataSet/excelData'))) strxml)
  LOOP
    null;
  END LOOP;

  cnt := SQL%ROWCOUNT;

  IF (cnt > 0)
  THEN
    BEGIN
      COMMIT;
      poutmsg := '1';
    END;
  ELSE
    BEGIN
      ROLLBACK;
      poutmsg := '0';

    END;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;

    poutmsg := '0';

END insert_expt_excel;