This discussion is archived
4 Replies Latest reply: Jan 23, 2013 1:16 AM by Purvesh K RSS

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

user3714906 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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