6 Replies Latest reply: Dec 16, 2008 3:21 AM by Marco Gralike RSS

    XMLfile can not insert into XMLType table

    674564
      I've just install oacle 11g, change the http port to 8080 and ftp port to 2111, create a table like this:

      create table invoiceXML_tbl of XMLtype;

      then I use the following sql to insert a record:

      Insert into invoiceXML_tbl values (1,XMLType(bfilename('XMLDIR', 'web.txt'),nls_charset_id('AL32UTF8') ));

      'XMLDIR' is already created.

      the error msg is :
      ORA-31020: For security reasons, ftp and http access over XDB repository is not allowed on server side
      ORA-06512: in "SYS.XMLTYPE"

      http://localhost:8080/ can access the xdb. how could I change the server side privilege?
        • 1. Re: XMLfile can not insert into XMLType table
          Marco Gralike
          The error code is new for me, but my gut feeling says for a 11g database this is probably an role security issue and/or DBMS_NETWORK_ACL_ADMIN related.

          http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/afterup.htm#BABFCBJI

          Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.
          • 2. Re: XMLfile can not insert into XMLType table
            674564
            DBMS_NETWORK_ACL_ADMIN.DROP_ACL('localhost.xml');
            dbms_network_acl_admin.create_acl('localhost.xml', 'ACL for 127.0.0.1', 'SCOTT', true, 'connect');
            DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('localhost.xml', 'SCOTT', true, 'resolve');
            dbms_network_acl_admin.assign_acl('localhost.xml', '127.0.0.1');
            dbms_network_acl_admin.assign_acl('localhost.xml', IP);


            SELECT DECODE(
            dbms_network_acl_admin.check_privilege('localhost.xml',
            'SCOTT', 'connect'), 1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
            FROM DUAL;

            privilege has granted. but it doesn't work. the same error.

            any other settings?
            • 3. Re: XMLfile can not insert into XMLType table
              Marco Gralike
              Do a quick try via granting XDBADMIN and or the DBA role.
              • 4. Re: XMLfile can not insert into XMLType table
                674564
                i've tried, but still doesn't work.


                ORA-31020: 理由: For security reasons, ftp and http access
                over XDB repository is not allowed on server side
                ORA-06512: "SYS.XMLTYPE", 行272
                ORA-06512: 行1
                • 5. Re: XMLfile can not insert into XMLType table
                  674564
                  maybe the encoding is not right. xml can not parse.
                  my system default is shift_jis,
                  NLS_LNANG is JAPANESE_JAPAN.JA16SJISTILDE
                  • 6. Re: XMLfile can not insert into XMLType table
                    Marco Gralike
                    Thats a good idea. Maybe that is causing the issue...

                    Try altering NLS settings for example in
                    INSERT INTO invoiceXML_tbl 
                    VALUES
                      (1,XMLType(bfilename('XMLDIR', 'web.txt'),nls_charset_id('AL32UTF8') ))
                    ;
                    or set your NLS settings to AMERICAN AMERICA and UTF32 before you startup SQL*Plus. Are you using SQL*Plus for the statement show above...? Also use the exact same SQL*Plus version as the database version. If allowed use the sqlplus executable on the database server.

                    --> Is file "web.txt" readable by the Oracle database OS software OWNER (aka the OS owner that starts the Oracle database instance...) ?

                    BTW - WHAT DATABASE VERSION ARE YOU USING...?

                    Edited by: Marco Gralike on Dec 16, 2008 10:18 AM