10 Replies Latest reply on Aug 24, 2012 1:49 PM by muttleychess

    Error  ora-06553 in XMLTYE

    muttleychess
      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
          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
            Marco Gralike
            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
              Marco Gralike
              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
                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
                  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
                    What's the target location defined for XMLDIR?

                    Can the db server access it?
                    • 7. Re: Error  ora-06553 in XMLTYE
                      muttleychess
                      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
                        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)
                          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
                            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