CODEWITHSUNDEEP
xmloracle-databasestored-proceduresplsql
Arpan Mukherjee
Arpan Mukherjee

Reputation: 33

PL/SQL procedure to store XML data in a table throws Error(23,102): PL/SQL: ORA-00907: missing right parenthesis

I'm writing a PL/SQL procedure where I am reading an XML and will be storing data from the XML in a table.

The input XML is as below:

<MatAnnounceRcv xmlns="http://adc.wed.com/apps/ij/mes/common/v2017">
    <Header>
        <DATESEND>2017-05-07</DATESEND>
        <TIMESEND>13:11:35</TIMESEND>
    </Header>
    <AnnounceItems>
        <MATERIAL>STEEL</MATERIAL>
        <BATCH>Z4895201</BATCH>
        <MATERIAL_TYPE>BR</MATERIAL_TYPE>
    </AnnounceItems>
    <MatBatchChars>
        <MATERIAL>STEEL</MATERIAL>
        <BATCH>Z4895201</BATCH>
        <ATNAM>GRADE_NAME</ATNAM>
        <ATWRT>186C</ATWRT>
    </MatBatchChars>
    <MatBatchChars>
        <MATERIAL>STEEL</MATERIAL>
        <BATCH>Z4895201</BATCH>
        <ATNAM>BREITE</ATNAM>
        <ATNUM>1912</ATNUM>
        <UNIT>MM</UNIT>
    </MatBatchChars>
    <MatBatchChars>
        <MATERIAL>STEEL</MATERIAL>
        <BATCH>Z4895201</BATCH>
        <ATNAM>WEIGHT</ATNAM>
        <ATNUM>30966</ATNUM>
        <UNIT>KG</UNIT>
    </MatBatchChars>
    <MatBatchChars>
        <MATERIAL>STEEL</MATERIAL>
        <BATCH>Z4895201</BATCH>
        <ATNAM>SLAB_ID</ATNAM>
        <ATWRT>Z4895201</ATWRT>
    </MatBatchChars>
    <MatBatchChars>
        <MATERIAL>STEEL</MATERIAL>
        <BATCH>Z4895201</BATCH>
        <ATNAM>SLAB_LEN_ACT</ATNAM>
        <ATNUM>9205</ATNUM>
        <UNIT>MM</UNIT>
    </MatBatchChars>
    <MatBatchChars>
        <MATERIAL>STEEL</MATERIAL>
        <BATCH>Z4895201</BATCH>
        <ATNAM>SLAB_THK_ACT</ATNAM>
        <ATNUM>255</ATNUM>
        <UNIT>MM</UNIT>
    </MatBatchChars>    
</MatAnnounceRcv>

and the create table query is as follows:

CREATE TABLE COMMAT 
(   CONTROLID NUMBER NOT NULL , 
    PKEYITEM VARCHAR2(40 CHAR), 
    ME_ID VARCHAR2(20 CHAR) NOT NULL , 
    BEZEICHNUNG VARCHAR2(40 CHAR) NOT NULL , 
    P_STATUS VARCHAR2(2 CHAR) DEFAULT 'PR' NOT NULL , 
    FA_NR VARCHAR2(20 CHAR), 
    AG_ID NUMBER(4,0), 
    TYP VARCHAR2(2 CHAR), 
    KZGESPERRT VARCHAR2(1 CHAR) DEFAULT 'N', 
    SPERRGRUND VARCHAR2(30 CHAR), 
    BEMERKUNG VARCHAR2(80 CHAR), 
    BESTANDSART VARCHAR2(1 CHAR), 
    ANZ_STUECKE NUMBER(4,0) DEFAULT 1, 
    BREITE NUMBER(12,4), 
    DICKE NUMBER(12,4), 
    LAENGE NUMBER(12,4), 
    DIAGONALE NUMBER(12,4), 
    INNENDM NUMBER(12,4), 
    GEW NUMBER(12,4), 
    GEWBRUTTO NUMBER(12,4), 
    LETZTE_BEARB DATE, 
    ORT_PHYS VARCHAR2(40 CHAR), 
    BEREIT_AB DATE, 
    FOLGEANLAGE VARCHAR2(10 CHAR), 
    SP_STATUS VARCHAR2(2 CHAR), 
    VERPACKUNGSSCHLUESSEL VARCHAR2(20 CHAR), 
    PGBEZEICHNUNGIST VARCHAR2(12 CHAR), 
    DTERZEUGT DATE, 
    HEATNO VARCHAR2(20 CHAR), 
    MAT_ST_ID VARCHAR2(40 CHAR), 
    MAT_ST_VERSION NUMBER(4,0), 
    SUB_NR NUMBER(9,0) DEFAULT 0, 
    GRADE VARCHAR2(32 CHAR), 
    QGRADE VARCHAR2(50 CHAR), 
    QSTANDARD VARCHAR2(50 CHAR), 
    PLANMATNO VARCHAR2(32 CHAR), 
    AU_NRIST VARCHAR2(20 CHAR), 
    UPOS_NRIST VARCHAR2(8 CHAR), 
    SCOPE VARCHAR2(40 CHAR) DEFAULT '1/', 
    ERSTELLER VARCHAR2(32 CHAR), 
    ERSTELLDATUM DATE, 
    AENDERER VARCHAR2(32 CHAR), 
    AENDDATUM DATE, 
    ARCHIVDATUM DATE, 
    UPDATEZAEHLER NUMBER(8,0), 
    SYSTEM VARCHAR2(30 CHAR), 
    CONSTRAINT PK_COMMAT PRIMARY KEY (CONTROLID, ME_ID)
);

And the procedure which I have written is as follows:

create or replace 
PROCEDURE ANNOUNCEITEMS_UPLOAD(XMLINPUT IN CLOB) AS 
controlid VARCHAR2(20);
primarykeyitem VARCHAR2(20);
meid VARCHAR2(20);


BEGIN

SELECT TRUNC(dbms_random.VALUE(100000, 999999)) num INTO controlid from dual;
SELECT x.* INTO primarykeyitem,meid FROM xmltable(XMLNAMESPACES('http://adc.wed.com/apps/ij/mes/common/v2017' as "ns"), 
 '/ns:MatAnnounceRcv/ns:AnnounceItems'
 PASSING xmltype(XMLINPUT)

 COLUMNS 

 PKEYITEM VARCHAR2(40) PATH '//ns:BATCH',
 ME_ID VARCHAR2(20) PATH '//ns:BATCH'

  )x;


INSERT INTO COMMAT(CONTROLID,PKEYITEM,ME_ID,ORT_PHYS,BEZEICHNUNG,TYP,BREITE,DICKE,LAENGE,GEW,GRADE) 
SELECT controlid,primarykeyitem,meid,'--',x.* FROM xmltable (
  XMLNAMESPACES('http://adc.wed.com/apps/ij/mes/common/v2017' as "ns"),
 '//ns:MatAnnounceRcv'
 PASSING xmltype(XMLINPUT)

 COLUMNS 

    BEZEICHNUNG VARCHAR2(40) PATH '//ns:MatBatchChars[ns:ATNAM=''SLAB_ID'']/ns:ATWRT',
    TYP VARCHAR2(2) PATH '//ns:AnnounceItems/ns:MATERIAL_TYPE',
    BREITE NUMBER(12,4) PATH '//ns:MatBatchChars[ns:ATNAM=''BREITE'']/ns:ATNUM',
    DICKE NUMBER(12,4) PATH '//ns:MatBatchChars[ns:ATNAM=''SLAB_THK_ACT'']/ns:ATNUM',
    LAENGE NUMBER(12,4) PATH '//ns:MatBatchChars[ns:ATNAM=''SLAB_LEN_ACT'']/ns:ATNUM',
    GEW NUMBER(12,4) PATH '//ns:MatBatchChars[ns:ATNAM=''WEIGHT'']/ns:ATNUM',
    GRADE VARCHAR2(32) PATH '//ns:MatBatchChars[ns:ATNAM=''GRADE_NAME'']/ns:ATWRT'


)x;

END ANNOUNCEITEMS_UPLOAD;

But while compiling it is showing the error:

Error(23,1): PL/SQL: SQL Statement ignored
Error(24,4): PL/SQL: ORA-00907: missing right parenthesis

I'm not being able to see the parenthesis mismatches.

Upvotes: 1

Views: 416

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30775

Oracle's error message is bogus - the real culprit are the single quotes in the XPath expressions. Just replace them with double quotes, and your procedure compiles just fine, e.g. instead of

PATH '//ns:MatBatchChars[ns:ATNAM=''BREITE'']/ns:ATNUM'

use

PATH '//ns:MatBatchChars[ns:ATNAM="BREITE"]/ns:ATNUM'

Upvotes: 1

Related Questions