CREATE TABLE MALFUNCTION_XML_FILE
(
  FILE_NAME      VARCHAR2(4000 CHAR),
  MLF_ID         NUMBER,
  CONTENT        BLOB,
  CHARACTER_SET  VARCHAR2(200 CHAR)
);

COMMENT ON TABLE MALFUNCTION_XML_FILE IS 'таблица для вставки из xml файла, данные в ней ОТСУТСТВУЮТ.';


CREATE OR REPLACE TRIGGER MALFUNCTION_XML_FILE_INSERT
BEFORE UPDATE
ON RELIABILITY_DATA.MALFUNCTION_XML_FILE 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
   :new.mlf_id :=
      wvp_malfunction_xml_files_api.upload_file (
         p_content         => :new.content,
         p_file_name       => :new.file_name,
         p_character_set   => :new.character_set
      );
EXCEPTION
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END MALFUNCTION_XML_FILE_insert;
/


CREATE OR REPLACE TRIGGER MALFUNCTION_XML_FILE_AFTR_ACTN
AFTER UPDATE
ON RELIABILITY_DATA.MALFUNCTION_XML_FILE 
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
BEGIN
   DELETE FROM   MALFUNCTION_XML_FILE;
EXCEPTION
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END;
/

/* Formatted on 24.10.2013 14:50:24 (QP5 v5.115.810.9015) */
CREATE OR REPLACE PACKAGE BODY wvp_malfunction_xml_files_api
AS

   -- загружаем файл от клиена по протоколу http
   FUNCTION upload_file (                  
                         p_content         IN BLOB,
                         p_file_name       IN VARCHAR2,
                         p_character_set   IN VARCHAR2)
      RETURN NUMBER
   IS
      i_xml               XMLTYPE;
      i_malfunctions_id   malfunctions.mlf_id%TYPE := NULL;
   BEGIN

бла бла бла .............................

      i_xml :=
         xmltype (xmlData   => p_content,
                  csid      => NLS_CHARSET_ID (p_character_set));

бла бла бла .............................

      RETURN i_malfunctions_id;
    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
          NULL;
       WHEN OTHERS
       THEN
          -- consider logging the error and then re-raise
          RAISE;
   END;
END wvp_malfunction_xml_files_api;
/