CODEWITHSUNDEEP
oracle-database
reader
reader

Reputation: 37

I am getting compilation error in creating Oracle Proceedure

iam creating a proceedure it is giving compilation error of right paranthesis missing

Line # = 9 Column # = 21 Error Text = PL/SQL: ORA-00907: missing right parenthesis

CREATE OR REPLACE PROCEDURE user1.trns (XMLDATA XMLTYPE) 
IS
BEGIN

        UPDATE table1 SET T$fld1 = 1, T$dat1 = TO_DATE(TO_CHAR(sysdate,'DD/MM/YYYY'),'DD/MM/YYYY') 
        WHERE  table1.T$docn IN
            (
                SELECT DISTINCT docn 
                FROM XMLTABLE  
                    ('/DataSet/Document/DocumentRow'
                        PASSING XMLDATA COLUMNS  
                        docn PATH '@DOCN' 
                    )  
            );

        COMMIT;

END  trns;
/

what am i doing wrong here

Upvotes: 0

Views: 34

Answers (1)

Paras
Paras

Reputation: 338

You are missing data type here as below please provide data type for XMLTABLE column: here i have written varchar2(30)

CREATE OR REPLACE PROCEDURE user1.trns (XMLDATA XMLTYPE) 
IS
BEGIN

        UPDATE table1 SET T$fld1 = 1, T$dat1 = TO_DATE(TO_CHAR(sysdate,'DD/MM/YYYY'),'DD/MM/YYYY') 
        WHERE  table1.T$docn IN
            (
                SELECT DISTINCT docn 
                FROM XMLTABLE  
                    ('/DataSet/Document/DocumentRow'
                        PASSING XMLDATA COLUMNS  
                        docn varchar2(30) PATH '@DOCN' 
                    )  
            );

        COMMIT;

END  trns;

Here is my output Here is my output

Upvotes: 1

Related Questions