ORA-00928: Getting multiple errors while passing XML in stored procedure

Question

I am inserting data into table using XML like below:

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;

But I am getting error as:

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

I don't see the error mentioned in the title in your question. — Rene, Sep 30 '19 at 06:24
@Rene: there are multiple errors coming so I described that in my post — Nad, Sep 30 '19 at 06:25
Describe what you want to do, the steps taken and the errors that you get. The piece of code you provide can't possibly be all. — Rene, Sep 30 '19 at 06:36
@Rene: I want to insert my record in the table using `xml`. I have written the stored procedure for the same. But due to above mentioned error i am unable to do it. I have updated it with proper errors now — Nad, Sep 30 '19 at 06:37
A good approach is to try to minimize the example while preserving the error. Could you observe the same behavior with two EXTRACTs instead of 21? Then use the saved space to post data to make the example reproducible. You may also try to switch to a `INSERT ... SELECT` instead of using INSERT is a `cursor loop`. — Marmite Bomber, Sep 30 '19 at 06:40
@MarmiteBomber" `ORA-06550: line 9, column 24: PL/SQL: ORA-00904: "STRXML"."EXTRACT": invalid identifier` and `PLS-00364: loop index variable 'TMMAPPING' use is invalid` are still coming with 2 extracts. — Nad, Sep 30 '19 at 06:42

score 1 · Answer 1 · answered Sep 30 '19 at 07:00

I can reproduce the errors you get by trying to run your code exactly as posted.

Missing is either the package the procedure is part of or a "create or replace" in front of it. With the insert statement removed (I don't have the table) this code compiles.

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;

but I want to insert the xml data in my table.. if u want i can post the desc of table — Nad, Sep 30 '19 at 07:10
i also added the insert statement and it got compiled.. is that OK ? — Nad, Sep 30 '19 at 07:16

score 1 · Accepted Answer · answered Sep 30 '19 at 08:56

Use XMLTABLE:

Procedure:

CREATE PROCEDURE insert_expt_excel(
  strxml  IN xmltype,
  poutmsg OUT NVARCHAR2
)
AS
BEGIN
  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
  )
  SELECT cmp,
         sap_id,
         id_od_countchange,
         id_od_changeddate,
         NULL AS rrh_countchange,
         NULL AS 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
  FROM   XMLTABLE(
           '//NewDataSet/excelData'
           PASSING strxml
           COLUMNS cmp                  VARCHAR2(4000) PATH './CMP',
                   sap_id               VARCHAR2(4000) PATH './SAP_ID',
                   id_od_countchange    VARCHAR2(4000) PATH './ID_OD_COUNTCHANGE',
                   id_od_changeddate    DATE           PATH './ID_OD_CHANGEDDATE',
                   tenancy_countchange  VARCHAR2(4000) PATH './TENANCY_COUNTCHANGE',
                   tenancy_changeddate  DATE           PATH './TENANCY_CHANGEDDATE',
                   rfs_date             DATE           PATH './RFS_DATE',
                   rfe1_date            DATE           PATH './RFE1_DATE',
                   infra_provider       VARCHAR2(4000) PATH './INFRA_PROVIDER',
                   ip_colo_siteid       VARCHAR2(4000) PATH './IP_COLO_SITEID',
                   site_name            VARCHAR2(4000) PATH './SITE_NAME',
                   r4gstate             VARCHAR2(4000) PATH './R4GSTATE',
                   mw_installed         VARCHAR2(4000) PATH './MW_INSTALLED',
                   dg_nondg             VARCHAR2(4000) PATH './DG_NONDG',
                   eb_noneb             VARCHAR2(4000) PATH './EB_NONEB',
                   tower_type           VARCHAR2(4000) PATH './TOWER_TYPE',
                   vendor_code          VARCHAR2(4000) PATH './VENDOR_CODE',
                   rfcdate              DATE           PATH './RFCDATE',
                   political_state_name VARCHAR2(4000) PATH './POLITICAL_STATE_NAME',
                   political_state_code VARCHAR2(4000) PATH './POLITICAL_STATE_CODE',
                   site_drop_date       DATE           PATH './SITE_DROP_DATE'
         );

  IF ( SQL%ROWCOUNT > 0 ) THEN
    poutmsg := '1';
  ELSE
    poutmsg := '0';
  END IF;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    poutmsg := '0';
END insert_expt_excel;
/

You will need to update it to the appropriate data types. rrh_countchange and rrh_changeddate are not in your SELECT statement.

Some other points:

Don't use OTHERS to catch all exceptions; instead catch specific expected exceptions and if an unexpected exception occurs then you are able to see that it has occurred and debug the issue rather than just silently hiding it.
Don't use COMMIT in the stored procedure; instead use it in the calling block so you can chain multiple stored procedures and COMMIT/ROLLBACK them all as a single transaction.

Call it:

DECLARE
  success NUMBER(1,0);
BEGIN
  insert_expt_excel( XMLType( '<NewDataSet>
  <excelData>
    <CMP>abc</CMP>
    <SAP_ID>def</SAP_ID>
    <ID_OD_COUNTCHANGE>ghi</ID_OD_COUNTCHANGE>
    <ID_OD_CHANGEDDATE>2019-09-30</ID_OD_CHANGEDDATE>
  </excelData>
</NewDataSet>' ), success );
  IF success = 1 THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
END;
/

Output:

SELECT * FROM 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
:-- | :----- | :---------------- | :---------------- | :-------------- | :-------------- | :------------------ | :------------------ | :------- | :-------- | :------------- | :------------- | :-------- | :------- | :----------- | :------- | :------- | :--------- | :---------- | :------ | :------------------- | :------------------- | :-------------
abc | def    | ghi               | 30-SEP-19         | null            | null            | null                | null                | null     | null      | null           | null           | null      | null     | null         | null     | null     | null       | null        | null    | null                 | null                 | null

db<>fiddle here

`EXTRACT` would work but it is [deprecated in Oracle 12c](https://docs.oracle.com/database/121/SQLRF/functions068.htm#SQLRF00640) so you should be using `XMLQUERY` or `XMLTABLE` instead as these are going to continue being supported in future versions. — MT0, Sep 30 '19 at 09:20
I added two more field like below `rrh_countchange VARCHAR2(4000) PATH './RRH_COUNTCHANGE', rrh_changeddate DATE PATH './RRH_CHANGEDDATE'` but got error while compiling it after `ALTER` as `ORA-00922: missing or invalid option 00922. 00000 - "missing or invalid option"` — Nad, Sep 30 '19 at 10:23

score 0 · Answer 3 · answered Sep 30 '19 at 07:23

Alternative way without using a FOR LOOP, which can lead to suboptimal performance as it is row by row approach would be to use XMLTable - see example below:

insert into tab (a,b)
with dt as (
select xmltype(
'<tab>
 <rec>
   <a>a1</a>
   <b>b1</b>
 </rec>  
 <rec>
   <a>a2</a>
   <b>b2</b>
 </rec> 
</tab>') col
from dual
)
select x.a,x.b 
from dt,
     XMLTable(
     'for $i in /tab/rec      
      return $i' 
      passing  (dt.col)
      columns
      a varchar2(5) path '//a',
      b varchar2(5) path '//b'
 ) x  
; 


select * from tab;

A     B    
----- -----
a1    b1   
a2    b2

ORA-00928: Getting multiple errors while passing XML in stored procedure

3 Answers3