Monthly Archives: January 2017

Oracle APEX 5.1 Documentation Bug

The installation documentation for Oracle APEX 5.1 has the following code in it for enabling network access from the database in a pre-12c database:

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to '*' and give apex_050100
  -- the "connect" privilege if apex_050100 does not have the privilege yet.
 
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
 
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'apex_050100',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'apex_050100', TRUE, 'connect');
  END IF;
 
EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'apex_050100', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

You’ll notice that the username in the above is ‘apex_050100’. If you run the above code you’ll get the following:

01435. 00000 -  "user does not exist"
*Cause:    
*Action:

Changing each instance of ‘apex_050100’ to ‘APEX_050100’ will address the error and you’ll be able to run the code.

Interestingly enough the new 12c APIs are apparently doing an UPPER on the passed in username because the following code works just fine:

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'apex_050100',
                           principal_type => xs_acl.ptype_db));
END;
/

I’ve logged a bug request with Oracle Support (of course).

Happy APEXing!


Possible ORDS 3.0.9 ‘Doc Bug’

While upgrading a server to ORDS 3.0.9 (from ORDS 3.0.5) and APEX 5.1 I noticed the following output during the ORDS upgrade…

$ java -jar ords.war install advanced
[snip...]
Jan 05, 2017 3:59:43 PM  
 INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt
 Upgrading Oracle REST Data Services schema 3.0.5.124.10.54 to version 3.0.9.348.07.16
 ... Log file written to /home/apache/ords_upgrade_2017-01-05_155943_00336.log
 Upgrading ORDS schema to 3.0.6
 Completed upgrade for Oracle REST Data Services version 3.0.9.348.07.16.
Elapsed time: 00:00:16.437 

You might notice that the output has “Upgrading ORDS schema to 3.0.6” even though the version of ORDS is 3.0.9.

Of course the following query does return the right value and ORDS is the correct version:

select * from ords_metadata.ords_version;
VERSION                      
------------------------------
3.0.9.348.07.16