Extract value, procedure

Question

Extract value, procedure

Navigation

#1 by (0 votes)

0

Initially I needed to create a procedure that read an xml file and put the data into an oracle table, create the table, the directory, I have the following structure:

create or replace package body PACK_GUSTAVO1 as

v_id            INFO_XML.ID%type;
cursor C1 is    SELECT NVL(MAX(ID)+1,1) AS ID FROM INFO_XML;

PROCEDURE IMPORTA_XML IS
BEGIN

/* ABRE CURSOR */
    OPEN C1;
    LOOP
        /* LÊ UM REGISTRO DO CURSOR */
        fetch C1 into v_id;
         /* ABANDONA O LOOP CASO SEJA O FINAL DO CURSOR */
        EXIT WHEN C1%NOTFOUND;
    END LOOP;
    /* FECHA O CURSOR */
    CLOSE C1;

DBMS_OUTPUT.PUT_LINE('proximo:' || v_id);

DELETE FROM INFO_XML WHERE ID=v_id;
COMMIT;

INSERT INTO INFO_XML (ID, CONTEUDO) VALUES
    (v_id, XMLTYPE(BFILENAME('CARGA_CSV', 'funcionarios.xml'), NLS_CHARSET_ID('WE8ISO8859P1')));
COMMIT;    

/* grava na tabela */

END;

end PACK_GUSTAVO1;

and my select extract value:

SELECT extractValue(CONTEUDO, '/funcionarios/contato/nome/value') as NOME FROM INFO_XML;

But it is giving error, I do not know what to do.

oracle procedure

asked by anonymous 08.12.2014 / 00:34

1 answer

UIImage Temporary Facebook post redirect

score 0 · Accepted Answer

I came to the following conclusion, by a link I found from TOAD, I posted in the comments. I'm currently here, and what I'm solving is the issue of my xml file pops up with Japanese font in my table, I thought it was the xml header, change it to UTF-8, so it could read in my dialect .. but came again the Japanese source, or Chinese, I do not know, rs' So I decided to upload the actual files I had here.

create or replace PACKAGE BODY TESTE2 AS


-- PARAMETROS PARA TESTE : CARGA_CSV , funcionarios.xml

PROCEDURE XXX( dir VARCHAR2, file VARCHAR2, name VARCHAR2 := NULL)  IS
 theBFile   BFILE;
  theCLob    CLOB;
  theDocName VARCHAR2(200) := NVL(name,file);
BEGIN 
DELETE FROM xml_documents;
    COMMIT;

  INSERT INTO xml_documents(docname,xmldoc) VALUES(theDocName,empty_clob())
  RETURNING xmldoc INTO theCLob;

  -- (3) Get a BFile handle to the external file
  theBFile := BFileName(dir,file);

  -- (4) Open the file
  dbms_lob.fileOpen(theBFile);

  -- (5) Copy the contents of the BFile into the empty CLOB
  dbms_lob.loadFromFile(dest_lob => theCLob,
                         src_lob => theBFile,
                         amount  => dbms_lob.getLength(theBFile));

  -- (6) Close the file and commit
  dbms_lob.fileClose(theBFile);  


DBMS_OUTPUT.PUT_LINE('Salvo com Sucesso !!!');

END XXX;


END TESTE2;