4 Replies Latest reply: Jan 23, 2013 3:16 AM by Purvesh K RSS

    PLS-00201: identifier 'DBMS_NETWORK_ACL_ADMIN.DROP_ACL' must be declared

    user3714906
      Hi ALL,

      We are using database 10.2.0.4. when I am running below command
      begin
      dbms_network_acl_admin.drop_acl
      (acl => 'utl_http.xml');
      end;

      it gives error:

      ORA-06550: line 3, column 7:
      PLS-00201: identifier 'DBMS_NETWORK_ACL_ADMIN.DROP_ACL' must be declared
      ORA-06550: line 3, column 7:
      PL/SQL: Statement ignored

      kindly help to resolve this problem.
        • 1. Re: PLS-00201: identifier 'DBMS_NETWORK_ACL_ADMIN.DROP_ACL' must be declared
          Purvesh K
          Please check if you have an explicit Grant of executing the DBMS package you are trying to.

          Privileges acquired via Roles are not valid in PL/SQL. hence an explicit Grant may be required.

          I guess, DBMS_NETWORK_ACL_ADMIN is introduced in 11g, where as you are on 10.2. Hence, you are unable to use the package.

          11g new Features

          Edited by: Purvesh K on Jan 23, 2013 1:34 PM
          • 2. Re: PLS-00201: identifier 'DBMS_NETWORK_ACL_ADMIN.DROP_ACL' must be declared
            user3714906
            Dear,

            We checked in dbs_objects but we didn't found this package. please suggest or provide us the package script.
            • 3. Re: PLS-00201: identifier 'DBMS_NETWORK_ACL_ADMIN.DROP_ACL' must be declared
              user3714906
              Dear Purvesh,

              hmmm. So, any alternative package available for this Oracle 10g. because I need to create some function that use translate.google.com to translate the words into Arabic. following steps i found in the blog:

              SYS@orcl_11gR2> begin
              2 dbms_network_acl_admin.drop_acl
              3      (acl     => 'utl_http.xml');
              4 end;
              5 /

              PL/SQL procedure successfully completed.

              SYS@orcl_11gR2> begin
              2 dbms_network_acl_admin.create_acl
              3      (acl     => 'utl_http.xml',
              4      description => 'HTTP Access',
              5      principal => 'SCOTT',
              6      is_grant => true,
              7      privilege => 'connect');
              8 dbms_network_acl_admin.assign_acl
              9      (acl     => 'utl_http.xml',
              10      host     => 'translate.google.com',
              11      lower_port => 80,
              12      upper_port => 80);
              13 commit;
              14 end;
              15 /

              PL/SQL procedure successfully completed.

              SYS@orcl_11gR2> connect scott/tiger
              Connected.
              SCOTT@orcl_11gR2> set define off
              SCOTT@orcl_11gR2> create or replace function translation
              2 (p_words in clob,          -- words to be translated
              3      p_to     in varchar2 default 'ar', -- language to translate to
              4      p_from     in varchar2 default 'en') -- language to translate from
              5                          -- ar = Arabic
              6                          -- en = English
              7                          -- es = Spanish
              8                          -- fr = French
              9 return     clob
              10 as
              11 l_res     clob;
              12 l_words     clob;
              13 begin
              14 l_res := httpuritype
              15           ('http://translate.google.com/?hl=' ||
              16           p_from || '&layout=1&eotf=1&sl=' || p_from ||
              17           '&tl=' || p_to || '&text=' ||
              18           utl_url.escape (p_words) || '#').getclob();
              19 l_res := substr (l_res, instr (l_res, '<span title="' || p_words || '"'));
              20 l_res := substr (l_res, 1, instr (l_res, '</span>') + 6);
              21 l_words := XmlType (l_res).extract ('/span/text()').getStringVal();
              22 return l_words;
              23 end translation;
              24 /

              Function created.

              SCOTT@orcl_11gR2> show errors
              No errors.
              SCOTT@orcl_11gR2> select translation ('cats and dogs', 'es') from dual
              2 /

              TRANSLATION('CATSANDDOGS','ES')
              --------------------------------------------------------------------------------
              gatos y perros

              1 row selected.
              • 4. Re: PLS-00201: identifier 'DBMS_NETWORK_ACL_ADMIN.DROP_ACL' must be declared
                Purvesh K
                user3714906 wrote:
                Dear Purvesh,

                hmmm. So, any alternative package available for this Oracle 10g. because I need to create some function that use translate.google.com to translate the words into Arabic.
                I do not have any first hand experience about the Web interaction with Pl/SQL, but UTL_HTTP might be the package you should be looking for.

                See a demonstration and lengthy discussion about UTL_HTTP at AskTom.