SQL & PL/SQL

Hello All,

Any guess what is causing error in below procedure:

exec XML_FILE_VALIDATION('ORS_FUN_CLA_CLAIMS_VW', 'Testing purpose', 'C:\USERS\ABC\DESKTOP','ORS_FUN_CLA_CLAIMS_VW.XML')

Procedure:

CREATE OR REPLACE PROCEDURE XML_FILE_VALIDATION

                                           (

                                              CODE IN VARCHAR2,

                                              DESCRIPTION IN VARCHAR2,

                                              DIR IN VARCHAR2,

                                              XML\_FILE IN VARCHAR2

                                           )

IS

XML_TYPE XMLTYPE;

V_ERRMSG VARCHAR2(200);

V_OTHER_INFO VARCHAR2(200);

V_CODE VARCHAR2(50);

V_DESCRIPTION VARCHAR2(100);

V_DIR VARCHAR2(50);

V_XML_FILE VARCHAR2(50);

V_SQL_STMT VARCHAR2(200);

BEGIN

V\_CODE := CODE;

V\_DESCRIPTION := DESCRIPTION;

V\_DIR := DIR;

V\_XML\_FILE :=XML\_FILE;

DELETE FROM XML_DATA;

V_SQL_STMT :='CREATE OR REPLACE DIRECTORY XML_DIR AS :1';

EXECUTE IMMEDIATE V_SQL_STMT USING V_DIR;

INSERT INTO XML_DATA (CODE, DESCRIPTION, VIEWDEF)

VALUES

(

  V\_CODE,

  V\_DESCRIPTION,   

  XMLTYPE(

           BFILENAME('XML\_DIR', V\_XML\_FILE)

          , NLS\_CHARSET\_ID('AL32UTF8') 

         )

);

SELECT VIEWDEF INTO XML_TYPE FROM XML_DATA;

IF

XML_TYPE.ISSCHEMABASED() = 1 THEN

DBMS_OUTPUT.PUT_LINE('SCHEMA BASED');

XML_TYPE.SCHEMAVALIDATE();

DBMS_OUTPUT.PUT_LINE ('XML DOCUMENT WITH CODE '||V_CODE||' IS VALID');

ELSE

DBMS_OUTPUT.PUT_LINE('NON-SCHEMA BASED');

XML_TYPE :=XML_TYPE.CREATESCHEMABASEDXML('view_generator2.xsd');

--DBMS_OUTPUT.PUT_LINE(XML_TYPE.GETCLOBVAL);

XML_TYPE.SCHEMAVALIDATE();

DBMS_OUTPUT.PUT_LINE ('XML DOCUMENT WITH CODE '||V_CODE||' IS VALID');

END IF;

EXCEPTION

WHEN OTHERS THEN

V_ERRMSG:=SQLCODE||':'||SQLERRM;

V_OTHER_INFO:='XML provided is having error. Please provide a valid XML input.';

DBMS_OUTPUT.PUT_LINE (V_CODE||' = INVALID => '||V_ERRMSG||CHR(10)||V_OTHER_INFO);

RAISE;

END;

Procedure compiled without any error.

Here xml provided a valid xml. error message shows invalid because that is user defined error message, so please ignore.

Actual error message is system defined- ORA-01780

Thanks,

Rajneesh