This content has been marked as final. Show 10 replies
The XMLType overloading working with a BFILE is not available in your version.
You have to first load the file as a CLOB (via DBMS_LOB APIs), then use the appropriate constructor :
Copy paste from the XMLDB FAQ, by Mark Drake...The easiest way to do this is to place the file in a directory on the file system of the machine that is running the database and then using a bfile to load access the contents of the file.
The first step is to create a SQL Directory object that points at the operating system folder containing the file
In 10.1.x and later you can then create an XMLTYPE using a BFILE constructor
CREATE OR REPLACE DIRECTORY XMLDIR as 'c:\temp' /
9.2.x does not support the BFILE constructor so we need a small PL/SQL procedcure that will help...
XMLTYPE ( BFILENAME ('XMLDIR','filename.xml'),nls_charset_id('AL32UTF8'))
We can then call this to create an XMLType using the same parameters as the BFILE based 10.1x constructor
create or replace function getXML(file bfile,charset number) return XMLTYPE is XML xmltype; tempCLOB CLOB; tempFile BFILE; dest_offset number := 1; src_offset number := 1; lang_context number := 0; conv_warning number := 0; begin tempFile := file; DBMS_LOB.createTemporary(tempCLOB,true,DBMS_LOB.SESSION); DBMS_LOB.fileopen(tempFile, DBMS_LOB.file_readonly); DBMS_LOB.loadClobfromFile ( tempCLOB, tempFile, DBMS_LOB.getLength(file), dest_offset, src_offset, charset, lang_context, conv_warning ); DBMS_LOB.fileclose(tempFile); XML := xmltype(tempCLOB); dbms_lob.freeTemporary(tempCLOB); return XML; end; / show errors --
To create the SQL Directory you must have 'CREATE ANY DIRECTORY' role. The values passed to the NLS_CHARSET_ID is the Oracle name for the character set equivalent to the the encoding of the file in, eg XML 'UTF-8' encoding is oracle 'AL32UTF8'.
OEF Original Content by Mark Drake
Your question was already answered in your earlier question by A_Non...
read a xml to table
...it took me only two clicks to be able to copy paste the content above...
Edited by: Marco Gralike on Aug 23, 2012 1:17 AM
Thank you and sorry for my ask
when I call xmltype is as:?
show me error:
getxml( bfilename('XMLDIR', 'teste.xml'),nls_charset_id('AL32UTF8'))
Edited by: muttleychess on Aug 23, 2012 9:36 AM
INSERT INTO testeXML (conteudo) VALUES (getxml( bfilename('XMLDIR', 'teste.xml'),nls_charset_id('AL32UTF8'))) ORA-22288: file or LOB operation FILEOPEN failed No such file or directory ORA-06512: at "SYS.DBMS_LOB", line 504 ORA-06512: at "SEI.GETXML", line 16
Here's an example:
If it doesn't work, also check privileges (read/write) for DIRECTORY database alias and on the operating system (directory & specific XML file)
Edited by: MGralike on Aug 23, 2012 6:15 AM
What's the target location defined for XMLDIR?
Can the db server access it?
I tried , but no work :-(
When I call only bfilename('XMLDIR', 'teste.xml') like
DROP DIRECTORY XMLDIR; CREATE OR REPLACE DIRECTORY XMLDIR AS 'C:\xmlteste'; GRANT READ, WRITE ON DIRECTORY SYS.XMLDIR TO SEI WITH GRANT OPTION; GRANT READ, WRITE ON DIRECTORY SYS.XMLDIR TO SYS; GRANT READ, WRITE ON DIRECTORY SYS.XMLDIR TO SYSTEM;
value of x is
declare x bfile; begin x:= bfilename('XMLDIR', 'teste.xml'); end;
XMLDIR/teste.xml, exists=F, length=0, open=FEdited by: muttleychess on Aug 23, 2012 10:31 AM
You didn't answer the main question : is the location "C:\xmlteste" reachable by the db server?
It's not on your local drive, is it?
To expand on Odie's question of
Can the db server access it?Is the DB on the same machine as this "C:\xmlteste" directory is?
Sorry all , I did know, but was local machine , now I create in server
and work fine
CREATE OR REPLACE DIRECTORY XMLDIR AS '/bdados3/ujava/p6m/download';