Network ACL Issue

[Raj]

Hello experts,

I am trying to create Network ACL in 11.2.0.2.2 single instance
database on HP-UX.
I am getting following errors :

ORA-06553: PLS-306: wrong number or types of arguments in call to
'OBJECT_ID'
ORA-06512: at line 6

wondering where I am going wrong ?

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'kt.xml',
description => 'Permission for KT user',
principal => 'KT',
is_grant => true,
privilege => 'connect',
start_date => null,
end_date => null);
commit;
END;
/


BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'kt.xml',
principal => 'KT',
is_grant => true,
privilege => 'resolve');
commit;
END;
/

SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'kt.xml',
host => 'mailserver',
lower_port => null,
upper_port => null);
commit;
END;
/ 2 3 4 5 6 7 8
BEGIN
*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to
'OBJECT_ID'
ORA-06512: at line 6


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select comp_name, status from dba_registry;

COMP_NAME
------------------------------------------------------------------------------------------------------------------------------------
STATUS
-----------
Oracle XML Database
VALID

Oracle Database Catalog Views
VALID

Oracle Database Packages and Types
VALID

Regards
Rajeev

[Vitalii Levash]

Hello

It's all right.Creating acl correctly

2011/10/7 Raj <rajeev...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "ORACLE_DBA_EXPERTS" group.
To post to this group, send email to oracle_db...@googlegroups.com.
To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.

--
В правильно поставленном вопросе всегда содержится 50% ответа
Виталий Леваш

[Rajeev Tomar]

Hello Vitalli,

If I am doing everything correct then why I am getting above ORA-06553 error ?

Regards

Rajeev

[Raj]

> ---------------------------------------------------------------------------­-----

> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
> Production
> PL/SQL Release 11.2.0.2.0 - Production
> CORE    11.2.0.2.0      Production
> TNS for HPUX: Version 11.2.0.2.0 - Production
> NLSRTL Version 11.2.0.2.0 - Production
>
> SQL> select comp_name, status from dba_registry;
>
> COMP_NAME

> ---------------------------------------------------------------------------­---------------------------------------------------------

> STATUS
> -----------
> Oracle XML Database
> VALID
>
> Oracle Database Catalog Views
> VALID
>
> Oracle Database Packages and Types
> VALID
>
> Regards
> Rajeev

Hi again,

I put up a trace on session and executed the query which was throwing
error :

SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'kt.xml',
host => 'mailserver',
lower_port => null,
upper_port => null);
commit;
END;
/ 2 3 4 5 6 7 8
BEGIN
*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to
'OBJECT_ID'
ORA-06512: at line 6

And I see the below query in trace file:


SELECT A.OBJECT_ID FROM XDB.XDB$ACL A, PATH_VIEW P WHERE
XMLCAST(XMLQUERY(
'declare default element namespace "http://xmlns.oracle.com/xdb/
XDBResource.xsd";
fn:data(/Resource/XMLRef)' PASSING P.RES RETURNING CONTENT) AS REF
XMLTYPE)
= REF(A) AND EQUALS_PATH(P.RES, :B1 ) = 1

It seems to me the error w.r.t to OBJECT ID and this query is related.

Any pointers for moving further ?

Regards
Rajeev

[Kirill]

Try the following

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'kt.xml',
host => 'mailserver',

lower_port => 1,
upper_port => 9999);
commit;
END;
/

-- Kirill, dadbm.com

[Rajeev Tomar]

Hallo Kirill

 

I am still getting the same Error.

 

I tried to execute following scripts but no success.

 

@?/rdbms/admin/catnacl.sql
@?/rdbms/admin/dbmsnacl.sql
@?/rdbms/admin/prvtnacl.plb

 

Regards,

Rajeev

[Kirill]

Hi again,

In case you do not run into a simple issue of executing the statement,
the problem might be the following (Oracle 11.1.0.6 to 11.1.0.7):

1) Check dba_objects to see if an object named EXTRACT exists
select owner, object_name, object_type from dba_objects where
object_name = 'EXTRACT';
2) If the objects are owned by SYS, they can be dropped.
If the objects are owned by another user, that object will have to be
renamed and any packages/procedures associated with that operator
would have to be updated as well

see if this can help, otherwise make sure you run the statement
proberly. One more thing is check if you XDB installation is valid,
had no invalid objects, etc.

-- Kirill, dadbm.com


On Oct 11, 10:00 am, Rajeev Tomar <rajeev.to...@gmail.com> wrote:

[Rajeev Tomar]

--
You received this message because you are subscribed to the Google Groups "ORACLE_DBA_EXPERTS" group.
To post to this group, send email to oracle_db...@googlegroups.com.
To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.

 

 

Hallo Kirill,

 

My oracle version is 11.2.0.2.2. I dont see any issue as per your recommendation :

 

SQL> select owner, object_name, object_type from dba_objects where
object_name = 'EXTRACT';

no rows selected

 

SQL> select comp_name,status from dba_registry;

COMP_NAME
------------------------------------------------------------------------------------------------------------------------------------
STATUS
-----------
Oracle XML Database
VALID

Oracle Database Catalog Views
VALID

Oracle Database Packages and Types
VALID

SQL> SQL> select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner = 'XDB';

no rows selected

Regards,

Rajeev

[jgar the jorrible]

On Oct 13, 12:48 am, Rajeev Tomar <rajeev.to...@gmail.com> wrote:

Looks just like MOS Bug 7134728: DBA_NETWORK_ACL_PRIVILEGES VIEW
INVALIDATED AND WON'T RECOMPILE
but that has no answer either. Is the view valid?

jg
--
@home.com is bogus.
http://www.zdnet.com/blog/crm/oracle-openworld-2011-10110101010no-really/3527

[Rajeev Tomar]

Hi jg

 

yes view is valid :

 

SQL> select OBJECT_NAME,STATUS from dba_objects where OBJECT_NAME like '%NETWORK_ACL%';

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
STATUS
-------
DBMS_NETWORK_ACL_ADMIN
VALID

DBMS_NETWORK_ACL_ADMIN
VALID

DBMS_NETWORK_ACL_UTILITY
VALID

DBMS_NETWORK_ACL_UTILITY
VALID

USER_NETWORK_ACL_PRIVILEGES
VALID

USER_NETWORK_ACL_PRIVILEGES
VALID

DBMS_NETWORK_ACL_ADMIN
VALID

DBMS_NETWORK_ACL_UTILITY
VALID

DBA_NETWORK_ACLS
VALID

DBA_NETWORK_ACLS
VALID

DBA_NETWORK_ACL_PRIVILEGES
VALID

DBA_NETWORK_ACL_PRIVILEGES
VALID

12 rows selected.

Regards

Rajeev