This discussion is archived
10 Replies Latest reply: Aug 24, 2012 6:49 AM by muttleychess RSS

Error  ora-06553 in XMLTYE

muttleychess Newbie
Currently Being Moderated
Hi

I tried to create my first example using XML,but return me erro whene I Tried to insert using XMLTYPE

Work fine
create table testeXML (conteudo SYS.XMLType);
Work fine
create directory xmldir as 'c:\xmlteste';
No Work_
INSERT INTO testeXML (conteudo)
  VALUES (XMLType(bfilename('XMLDIR', 'teste.xml'),nls_charset_id('AL32UTF8')));
ORA-06553: PLS-306: wrong number or types of arguments in call to 'XMLTYPE'
Where is error ?
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE     9.2.0.8.0     Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
Edited by: muttleychess on Aug 22, 2012 2:42 PM
  • 1. Re: Error  ora-06553 in XMLTYE
    odie_63 Guru
    Currently Being Moderated
    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 :
    http://docs.oracle.com/cd/B10501_01/appdev.920/a96616/arxml24.htm#1014689
  • 2. Re: Error  ora-06553 in XMLTYE
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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
    CREATE OR REPLACE DIRECTORY XMLDIR as 'c:\temp'
    / 
    In 10.1.x and later you can then create an XMLTYPE using a BFILE constructor
    XMLTYPE ( BFILENAME ('XMLDIR','filename.xml'),nls_charset_id('AL32UTF8'))
    9.2.x does not support the BFILE constructor so we need a small PL/SQL procedcure that will help...
    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
    --
    We can then call this to create an XMLType using the same parameters as the BFILE based 10.1x constructor

    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
  • 3. Re: Error  ora-06553 in XMLTYE
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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
  • 4. Re: Error  ora-06553 in XMLTYE
    muttleychess Newbie
    Currently Being Moderated
    Thank you and sorry for my ask

    when I call xmltype is as:?
    getxml( bfilename('XMLDIR', 'teste.xml'),nls_charset_id('AL32UTF8'))
    show me error:
    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
    Edited by: muttleychess on Aug 23, 2012 9:36 AM
  • 5. Re: Error  ora-06553 in XMLTYE
    MGralike Newbie
    Currently Being Moderated
    Here's an example:

    [getXML example|https://forums.oracle.com/forums/thread.jspa?messageID=1450357&#1450357]

    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
  • 6. Re: Error  ora-06553 in XMLTYE
    odie_63 Guru
    Currently Being Moderated
    What's the target location defined for XMLDIR?

    Can the db server access it?
  • 7. Re: Error  ora-06553 in XMLTYE
    muttleychess Newbie
    Currently Being Moderated
    I tried , but no work :-(
    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;
    When I call only bfilename('XMLDIR', 'teste.xml') like
    declare 
    
      x bfile;
      
    begin
       x:= bfilename('XMLDIR', 'teste.xml');
     
    
    end;
    value of x is
    XMLDIR/teste.xml, exists=F, length=0, open=F
    Edited by: muttleychess on Aug 23, 2012 10:31 AM
  • 8. Re: Error  ora-06553 in XMLTYE
    odie_63 Guru
    Currently Being Moderated
    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?
  • 9. Re: Error  ora-06553 in XMLTYE
    Jason_(A_Non) Expert
    Currently Being Moderated
    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?
  • 10. Re: Error  ora-06553 in XMLTYE
    muttleychess Newbie
    Currently Being Moderated
    Sorry all , I did know, but was local machine , now I create in server
    CREATE OR REPLACE DIRECTORY 
    XMLDIR AS 
    '/bdados3/ujava/p6m/download';
    and work fine

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points