rasti.hil@hilandco.com +41 79 367-9677

Wednesday, 25 November 2009

ORA-24247: network access denied by access control list (ACL)

This mean that your user can't access to network.
Solution without any description pleas read the following source

create or replace
PROCEDURE CREATE_ACL(IN_USERNAME IN VARCHAR2,
IN_HOST IN VARCHAR2,
IN_PORT_START IN NUMBER DEFAULT NULL,
IN_PORT_END IN NUMBER DEFAULT NULL)
AS
ACL VARCHAR2(4000);
ACL_NAME VARCHAR2(4000);

l_port NUMBER;
u_port NUMBER;
BEGIN
-- create ACL name
acl_name := LOWER(in_host);
acl_name := REPLACE(acl_name, '.', '_');
acl_name := acl_name||'.xml';

-- chech if ACL is existing for a given host
-- if not than create a new ACL list
-- if yes than just add a user to list

FOR C1 IN (SELECT ACL FROM SYS.dba_network_acls where ACL='/sys/acls/'||acl_name)
LOOP
acl := C1.ACL;
END LOOP;

IF acl IS NOT NULL THEN
-- add to an existing list the user with CONNECT privilige
DBMS_NETWORK_ACL_ADMIN.add_privilege (acl => acl_name,
principal => upper(in_username),
is_grant => TRUE,
privilege => 'connect',
position => NULL,
start_date => NULL,
end_date => NULL);
COMMIT;
-- add to an existing list the user with RESOLVE privilige
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => acl_name,
principal => upper(in_username),
is_grant => true,
privilege => 'resolve');
COMMIT;
ELSE
-- create an ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => acl_name,
description => in_host||' connection list',
principal => upper(in_username),
is_grant => true,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
-- assign host to ACL
-- now you can define port number
-- note[1] witn an existing ACL you can't define port numbers
-- if port numbers are NULL's then create a default one
IF in_port_start IS NULL THEN
l_port := 1;
ELSE
l_port := in_port_start;
END IF;
IF in_port_end IS NULL THEN
u_port := 10000;
ELSE
u_port := in_port_end;
END IF;
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => acl_name,
host => in_host,
lower_port => l_port,
upper_port => u_port);
COMMIT;
-- add to newly created list the user with RESOLVE privilige
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => acl_name,
principal => upper(in_username),
is_grant => true,
privilege => 'resolve');

COMMIT;
END IF;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END CREATE_ACL;

Step 2) use it:
note[1] I assume that CREATE_ACL procedure is created under sys user.
SQL> exec CREATE_ACL(IN_USERNAME   => 'scott',
IN_HOST => 'www.google.com',
IN_PORT_START => 80,
IN_PORT_END => 80);
SQL> conn scott/tiger
Connected.
SQL> select utl_http.request('http://www.google.com') from dual;
UTL_HTTP.REQUEST('HTTP://WWW.GOOGLE.COM')
--------------------------------------------
<!doctype html><html><head ...



janos ujvari @ 25th November 2009

Tuesday, 24 November 2009

Change image prefix /i/ after instalation of APEX

OK here is a new one

if you install or upgrade APEX your command look like this
sql> @apexins APEX APEX TEMP /i/

and the most common error is that you forgot the trailing slash at the and of image prefix
sql> @apexins APEX APEX TEMP /i

and when you are trying to access to APEX your image, CSS and JavaScript files are disappeared, checking the source code you get something like this
<script src="/ijavascript/apex_builder.js" type="text/javascript">

Probably now you had noticed that the slash is missing after letter i

Solution: Go to your apex install source change working directory to utilities and run reset_image_prefix.sql
sql> @reset_image_prefix.sql



janos ujvari @ 24th november 2009

Thursday, 19 November 2009

Oracle 11g and JAVA and ORA-29516

Hi, so if you encounter with the following error when you want to call a JAVA Wrapper which source class was uploaded to db by loadjava:
ORA-29516: Aurora assertion failure: Assertion failure at eox.c:332
Uncaught exception System error: java/lang/UnsupportedClessVersionError



as the error says your class version is not recognized by Oracle.
  1. check your JAVA version by invoking:

  2. java -version

  3. check which JAVA version is used by Oracle 11g by invoking:

  4. [($ORACLE_HOME/jdk/bin/)|( %ORACLE_HOME%\jdk\bin)]java -version


probably the two versions are not the same.
Solution use the same version for compiling *.java files, or if you want to be 100% sure use
[($ORACLE_HOME/jdk/bin/)|( %ORACLE_HOME%\jdk\bin)]javac
for compiling.

janos ujvari @19th november 2009