Skip navigation.

The Anti-Kyte

Syndicate content The Anti-Kyte
Oracle - for when it was like that when you got there
Updated: 9 hours 43 min ago

SQLDeveloper XML Extensions and auto-navigation

Sun, 2015-03-22 09:42

It’s official, England are now the second best cricket team in the British Isles !
After all, Scotland were dispatched with ease and as for Wales…they didn’t even make it to the Cricket World Cup.
OK, technically they did because they’re part of England for the purposes of cricket…although you’d be hard pressed to get them to admit it.
Ireland are, of course, some way in front having actually managed to actually win the odd game against Test Playing Nations.
Whilst it takes quite some effort to find silver lining in the cloud of English Cricket’s latest debacle, the same cannot be said if SQLDeveloper is your Oracle Database IDE of choice …

Why I use SQLDeveloper

Now, I’m well aware that each developer has their own favourite IDE. TOAD and PL/SQL Developer are fine tools in their own right, each with their own strengths. Which of these tools you swear by, or swear at, is always likely to be based on some fairly subjective criteria.

One of the main reasons I have for using SQLDeveloper is that it’s so easy to extend by means of a judicious application of XML, combined with a bit of SQL and/or PL/SQL.

Fun with Foreign Keys

As with all of the mainstream IDE’s, SQLDeveloper displays a table’s constraints as standard. However, what it doesn’t show is which tables have Foreign Keys to the table you’re looking at.

If, for example, you want to find out which tables have a Foreign Key to HR.COUNTRIES, you need to run a query like this :

select owner, table_name, constraint_name
from all_constraints
where constraint_type = 'R'
and (r_owner, r_constraint_name) in 
    select owner, constraint_name
    from all_constraints
    where constraint_type in ('U', 'P')
    and owner = 'HR'
    and table_name = 'COUNTRIES'
order by owner, table_name, constraint_name

OWNER                          TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
HR                             LOCATIONS                      LOC_C_ID_FK


Wouldn’t it be handy if you could somehow add a tab onto the table view in SQLDeveloper and have this information
pop-up for the table you’re looking at.
Well, funny you should say that….

	<item type="editor" node="TableNode" vertical="true">
		<title><![CDATA[Child Tables]]></title>
					select owner, table_name, constraint_name
					from all_constraints 
					where constraint_type = 'R' 
					and (r_owner, r_constraint_name) in
						select owner, constraint_name 
						from all_constraints 
						where constraint_type in ('U', 'P') 
						and owner = :OBJECT_OWNER 
						and table_name = :OBJECT_NAME 
					order by owner, table_name, constraint_name 

It may not be immediately obvious how this xml code is going to help us.
The answer is that it’s the SQLDeveloper extension that we’ve just written. Yep, just that one xml file.

It’s probably worth taking a closer look at the structure of this file in terms of the XML…

	<item type="editor" node="TableNode" vertical="true">
		<title><![CDATA[Enter the Tab Name Here]]></title>
				<![CDATA[Enter your query here]]>

If you want to add a node to a different object type, it seems that you just need to change the node attribute of the item tag as appropriate.
For example, specifying “Viewnode” should enable you to add a Tab to your Views.

Anyway, to add this to SQLDeveloper, we just need to do a bit of pointing and clicking…

To start with, save the above code into an xml file. I’ve called mine deps_fk.xml.

In SQLDeveloper, go to the Tools menu and select Preferences

In the tree on the left hand side expand the Database node. Then click on User Defined Extensions.


Now click the Add Row button at the bottom of the Right Hand Pane and you should see a new Row appear under the Type and Location headings like this :


If you now click in the row you’ve just created under the Type heading, you should get a drop-down list of values.
Select Editor from the list :


In the Location field, you just need to tell SQLDeveloper where your xml file is :


Finally, hit the OK button at the bottom of the window to save your changes and then re-start SQLDeveloper.

Now, if we open the HR.COUNTRIES table, we can see that there’s now an additional tab called Child Tables.
When we go to this tab, we are rewarded with :


This stuff has been done before. As well as this post from Tony Andrews, I also posted something similar several years ago.
I must be getting old, I’m starting to repeat myself !

Whilst all this is quite handy, wouldn’t it be good to be to be able to link directly to the Child table ?

Linking to other objects

Jeff Smith’s post here might be just what we’re looking for.

Using Jeff’s linking technique, we can improve our extension a bit…

	<item type="editor" node="TableNode" vertical="true">
		<title><![CDATA[Child Tables]]></title>
					select owner,  
						    ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' table_name,
					from all_constraints 
					where constraint_type = 'R' 
					and (r_owner, r_constraint_name) in
						select owner, constraint_name 
						from all_constraints 
						where constraint_type in ('U', 'P') 
						and owner = :OBJECT_OWNER 
						and table_name = :OBJECT_NAME 
					order by owner, table_name, constraint_name 

Now when I run this, the table_name appears in blue.


By clicking it, I can navigate directly to the child table…


When the code is run, in this example, the string generated for the table_name column is :


You can link to all sorts of things using this technique. In fact, it would seem to be that you can link to any object that has a node in the SQLDeveloper Navigator Tree. If you want to play around, just select the string from dual in SQLDeveloper (using F9 to execute).

For example :

select 'SQLDEV:LINK:HR:TRIGGER:UPDATE_JOB_HISTORY:oracle.dbtools.raptor.controls.grid.DefaultDrillLink'
from dual;

…returns a link to the UPDATE_JOB_HISTORY trigger in the results grid.
Note that the link syntax is case sensitive in that the owner, object type and object name all need to be in uppercase for this to work.

Another point worth noting, specifying a type of PACKAGE takes you to the package header.
If you want to link to the body, then specify PACKAGE BODY.

Other SQLDeveloper Extension Types

There’s a whole bunch of stuff you can do to customise SQLDeveloper using nothing more than a bit of boilerplate xml.
In addition to Jeff’s blog, there is an Oracle Wiki which contains, among other things,

It’s definitely worth a look, especially if, like me, you need something to take your mind off the cricket.

Filed under: SQLDeveloper Tagged: adding tabs to sqldeveloper, all_constraints, finding child constraints, linking to tabs in sqldeveloper, oracle.dbtools.raptor.controls.grid.DefaultDrillLink, sqldeveloper xml extensions

Installing Oracle XE on CentOS

Sun, 2015-03-01 11:33

Another Cricket World Cup is underway. England are fulfilling their traditional role of making all of the other teams look like world beaters.
To take my mind off this excruciating spectacle, I’ll concentrate this week on installing Oracle XE 11g on CentOS 7.

Before I get into the nuts and bolts of the installation…

Flavours of Linux

Whilst there are many Linux Distros out there, they all share the same common Linux Kernel. Within this there are a few Distros upon which most others are based.
Debian provides the basis for Ubuntu and Mint among others.
It uses the .deb package format.

Red Hat Linux in contrast uses the RPM file format for it’s packages. Red Hat is the basis for Distros such as Fedora, CentOS…and Oracle Linux.

For this reason, the Oracle Express Edition Linux version is packaged using rpm.
Whilst it is possible to deploy it to a Debian based Distro – instructions for which are available here, deploying on CentOS is rather more straightforward.
More straightforward, but not entirely so, as we will discover shortly…

Getting Oracle Express Edition 11G

Open your web browser and head over the the Oracle Express Edition download page.

You’ll need to register for an account if you don’t already have one but it is free.

The file you need to download is listed under :

Oracle Express Edition 11g Release 2 for Linux x64.

NOTE XE 11G only comes in the 64-bit variety for Linux. If you’re running a 32-bit version of your Distro, then you’re out of luck as far as 11G is concerned.

If you’re not sure whether you’re on 32-bit or 64-bit, the following command will help you :

uname -i

If this returns x86_64 then your OS is 64-bit.

Installing XE

You should now have downloaded the zipped rpm file which will look something like this :

cd $HOME/Downloads
ls -l
-rwxrwx---. 1 mike mike 315891481 Dec 16 20:21

The next step is to uncompress…


When you run this, the output will look like this :

   creating: Disk1/
   creating: Disk1/upgrade/
  inflating: Disk1/upgrade/gen_inst.sql  
   creating: Disk1/response/
  inflating: Disk1/response/xe.rsp   
  inflating: Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm 

You now need to switch to the newly created Disk1 directory and become root

cd Disk1

…and then install the package…

rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

If all goes well you should see…

Preparing...                          ################################# [100%]
Updating / installing...
   1:oracle-xe-11.2.0-1.0             ################################# [100%]
Executing post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.
Configuring XE

The configuration will be prompt you for

  1. the APEX http port (8080 by default)
  2. the database (TNS) listener port (1521 by default)
  3. A single password to be assigned to the database SYS and SYSTEM users
  4. whether you want the database to start automatically when the system starts (Yes by default)

Unless you have other software, or Oracle Instances, running elsewhere, the defaults should be fine.

Here we go then, still as root, run :

/etc/init.d/oracle-xe configure

The output, complete with the prompts will be something like :

Oracle Database 11g Express Edition Configuration
This will configure on-boot properties of Oracle Database 11g Express 
Edition.  The following questions will determine whether the database should 
be starting upon system boot, the ports it will use, and the passwords that 
will be used for database accounts.  Press <Enter> to accept the defaults. 
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:8081

Specify a port that will be used for the database listener [1521]:1525

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of 
different passwords for each database account.  This can be done after 
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

Congratulations, you now have a running database. The first thing to do with it, however, is to shut it down.
In fact, we need to do a re-start so that the menu items that have been added as part of the installation are visible.
So, re-boot.

NOTE – from this point on you can stop being root (although you may need to sudo occasionally).

Once the system comes back, you will see the new Menu icons in the Applications menu under others :


Just to confirm that your database is up and running, you can select the Run SQL Command Line option from this menu
and run the following :

conn system/pwd
select sysdate from dual

This should return the current date.

Sorting out the Environment Variables

In the normal run of things, this is the one fiddly bit. There is a bug in one of the scripts Oracle uses to set the environment variables which may cause issues.

To start with, let’s have a look at the main environment script…

cat /u01/app/oracle/product/11.2.0/xe/bin/

This script is as follows :

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export NLS_LANG=`$ORACLE_HOME/bin/`
export ORACLE_BASE=/u01/app/oracle

There is a bug in the that is called from here. If you’re NLS_LANG value contains a space, then it will not be configured correctly. A full list of the affected NLS_LANG values is available on the Oracle XE Installation Guide for Debian based systems I mentioned earlier.

The easiest way to fix this is to just edit the script :

sudo gedit /u01/app/oracle/product/11.2.0/xe/bin/

Right at the bottom of the script where it says :

# construct the NLS_LANG

echo $NLS_LANG

…amend it so that the $NLS_LANG value is quoted :

# construct the NLS_LANG

echo $NLS_LANG

To test the change and make sure everything is now working properly…

cd /u01/app/oracle/product/11.2.0/xe/bin

. ./
echo $NLS_LANG
echo $PATH

You should now see the following environment variable settings :

echo $NLS_LANG

NOTE – the $NLS_LANG should have a setting appropriate for your system (in my case ENGLISH_UNITED KINGDOM.AL32UTF8).

The Oracle bin directory is now at the start of $PATH.

Next, we need to ensure that these environment variables are set for all sessions. This can be done by running …

sudo cp /u01/app/oracle/product/11.2.0/xe/bin/ /etc/profile.d/.

To check this, you can start a new terminal session and echo the environment variables to make sure they have been set.

Getting the Menu Items to Work

To do this, you simply need to make sure that the oracle user, as well as your own user, is a member of the dba group :

sudo usermod -a -G dba oracle
sudo usermod -a -G dba mike

To check :

sudo grep dba /etc/group

The menu items for starting up and shutting down the database etc. should now work.

Enabling the Getting Started Desktop Icon

The final touch. The installation creates a Getting Started icon on the desktop which is designed to open the Database Home Page of the APEX application that comes with XE.

In order to make it work as desired, you simply need to right-click the icon and select Properties.
In the Permissions Tab check the box to “Allow executing file as program”.
Close the window.

You will notice that the icon has transformed into the familiar Oracle beehive and is now called
Get Started With Oracle Database 11g Express Edition.

Clicking on it now will reward you with …


All-in-all then, this installation is reasonably painless when compared with doing the same thing on a Debian system.
I wish the same could be said of following the England Cricket Team.

Filed under: Linux, Oracle Tagged: CentOS,, Oracle 11g Express Edition,

SQL*Plus Terminator Torture

Thu, 2015-02-26 05:42

“Leave that jar of Nutella alone, it’s got my name on it !”
The context in which Deb issued this injunction to me probably requires some explanation.
It was Friday evening.
Wales had just…well…come second in the latest installment of their eternal battle with the English through the medium of Rugby.
There was no alcohol left in the house.
And only one source of chocolate.
From the safety of the Cupboard under the stairs, to which I had retreated at kick-off – the Welsh do take their Rugby quite seriously – I wondered about my better half’s change of name.
Shorn of it’s chocolate hazelnut spread connotations, you might think that Nutella was quite an nice name for a girl.
It certainly seems appropriate if the “Girl” in question is slightly unhinged by a combination of wine and wounded national pride.

I was going to write something here about how Rugby players all look like the Terminator and use this as a way of introducting the topic at hand. However, I realise that this would simply be too contrived…even for me.
Instead, I’ll jump straight in…

The Nature of SQL*Plus

SQL*Plus is the command line interface for the Oracle RDBMS. As such, it supports three categories of statement :

  • SQL*Plus commands – which require no terminator
  • SQL commands – terminated by a “/” on a new line
  • PL/SQL blocks – containing statements that are terminated by “;”

…but that’s not the whole story as we are about to discover….

select 'Hasta La Vista' from dual

set serveroutput on size unlimited

    dbms_output.put_line(q'[Ill be back...from PL/SQL]');

select 'Just you wait until the World Cup!' 
from dual;

Here we can see an example of all three statement types – SQL, SQL*Plus and PL/SQL.
However the final SQL statement is a bit different. The terminator is not the newline “/” as with the first statement, but a “;”.

It is this small quirk that can cause some interesting things to happen and leave you scratching your head for a good while until you figure out exactly what’s happened.

Spot the Terminator

Consider the following, run as an unattended batch job :

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
create table tenuous_links( arnie_quote varchar2(100));

Simple enough – drop the table ( ignoring the ORA-00942 error if it doesn’t exist in the first place), then create it again…

21-FEB-2015 13:12:20

PL/SQL procedure successfully completed.

Table created.

create table tenuous_links( arnie_quote varchar2(100))
ERROR at line 1:
ORA-00955: name is already used by an existing object


At first glance, it would seem that our PL/SQL block to drop the table didn’t work for some reason.
However, if we dig a bit deeper…

select to_char(created, 'DD-MON-YYYY HH24:MI:SS')
from user_objects
where object_name = 'TENUOUS_LINKS'
and object_type = 'TABLE'

21-FEB-2015 13:12:20

So, the table was created at the time we ran our script. We know it still exists (and is not in the recycle bin) because it’s still in USER_OBJECTS.

If the table was dropped then re-created when we ran our script then why did we get the error ?

Let’s have a look at that CREATE TABLE statement again :

create table tenuous_links( arnie_quote varchar2(100));

Notice that “;” at the end of the first line ?
SQL*Plus takes this to mean “execute the last statement in the buffer”.
Then on the next line we have the same directive – expressed using a different syntax – i.e. “/”.

If we correct the script by removing the extraneous “;” all now works as expected :

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
create table tenuous_links( arnie_quote varchar2(100))

21-FEB-2015 13:30:48

PL/SQL procedure successfully completed.

Table created.

Whilst debugging a small script like this is relatively straight forward, you’re likely to be confronted with a much longer script in the real world and spend considerably more time tracking down the problem.

Culling the Terminators

At this point we have a number of questions :

  1. How and why does SQL*Plus allow two terminator characters ?
  2. Can we turn one of them off ?
  3. Can we standardise on one of them and if so, which ?

It’s probably worth taking a closer look at the two terminator characters as they behave slightly differently in
certain circumstances. For example…

create or replace function arnie
    return varchar2
    return(q'[I'll be back!]');

Function created.

However, we get a bit less success if we try :

create or replace function arnie
    return varchar2
        return(q'[I'll be back!]');

SQL*Plus doesn’t recognise the second “;” as a terminator. Putting it on a separate line fares no better.
From this then, we can infer that the “/” is mandatory when generating DDL for PL/SQL stored program units.

The next question is how we end up with two terminators, and whether we can switch one of them off.
Well, let’s take a look at the SQL*Plus settings, or one setting in particular :

SQL> show sqlterminator
sqlterminator ";" (hex 3b)

We can use this parameter to set the terminator to another single character…

SQL> set sqlterminator !

SQL> select 'Another Terminator reference'
  2  from dual
  3  !

Another Terminator reference


SQL> select 'Another Terminator reference' from dual !

Another Terminator reference


SQL> select 'Another Terminator reference!' from dual !

Another Terminator reference!


…but not multiple characters…

SQL> set sqlterminator runbuffer
string "runbuffer" is too long. maximum size is 1 character.

Interestingly, we can also disable it altogether and then re-enable it…

SQL> set sqlterminator off
SQL> show sqlterminator
sqlterminator OFF
SQL> select sysdate
  2  from dual;
SQL> select sysdate
  2  from dual
  3  /


SQL> set sqlterminator ;
SQL> select sysdate
  2  from dual;



Whilst this can be quite useful, especially if you’re running scripts that contain only DDL for stored program units, it does feel a bit odd having to put the terminator on a new line.
Additionally, you may consider that standardising this would require some serious regression testing of any SQL scripts to make sure that they’re not using the “;” terminator, not to mention any scripts that get generated dynamically.

Missing Terminators

Just as vexing as having too many terminators is not having enough.

consider :

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;

create or replace package body the terminator as

    function favourite_terminator
        return varchar2
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;

Warning: Package created with compilation errors.

SQL> sho err

-------- -----------------------------------------------------------------
6/1	 PLS-00103: Encountered the symbol "CREATE"

SQL> select object_type          
  2  from user_objects
  3  where object_name = 'THE_TERMINATOR'
  4  /



Now consider this in a longer script ( which I’ve instrumented with prompt statements) :

prompt Recording Start Time

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;

prompt Dropping TENUOUS_LINKS

    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;

prompt Creating table TENUOUS_LINKS
create table tenuous_links( arnie_quote varchar2(100))

prompt Creating Function ARNIE

create or replace function arnie
    return varchar2
    return(q'[I'll be back !]');

prompt Creating Package THE_TERMINATOR

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;

create or replace package body the terminator as

    function favourite_terminator
        return varchar2
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;

This time, we’ve missed the terminator at the end of the function.
However, this causes the Package to error as well :

Recording Start Time

21-FEB-2015 14:11:39


PL/SQL procedure successfully completed.

Creating table TENUOUS_LINKS

Table created.

Creating Function ARNIE

Warning: Function created with compilation errors.

Warning: Package Body created with compilation errors.

SQL> sho err

-------- -----------------------------------------------------------------
0/0	 PL/SQL: Compilation unit analysis terminated
1/14	 PLS-00304: cannot compile body of 'THE_TERMINATOR' without its

1/14	 PLS-00905: object MIKE.THE_TERMINATOR is invalid
SQL> select object_name, object_type
  2  from user_objects
  3  where object_name in ('ARNIE', 'THE_TERMINATOR')
  4  /

------------------------------ -------------------


There are a couple of things to note here. First is that it may well be worth enhancing the instrumentation in the script by including SHOW ERRORS after each stored program unit creation statement. The second is that there is no “smoking gun” error for either too many terminators, or not enough.
With SHOW ERRORS in place, it becomes a bit easier to spot what’s going wrong :

prompt Recording Start Time

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;

prompt Dropping TENUOUS_LINKS

    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;

prompt Creating table TENUOUS_LINKS
create table tenuous_links( arnie_quote varchar2(100))

prompt Creating Function ARNIE

create or replace function arnie
    return varchar2
    return(q'[I'll be back !]');

show error

prompt Creating Package THE_TERMINATOR

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;

show error

create or replace package body the_terminator as

    function favourite_terminator
        return varchar2
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;

show error

When you run this script, the problem is a bit easier to spot :

Recording Start Time

21-FEB-2015 14:17:03


PL/SQL procedure successfully completed.

Creating table TENUOUS_LINKS

Table created.

Creating Function ARNIE

Warning: Function created with compilation errors.


-------- -----------------------------------------------------------------
8/1	 PLS-00103: Encountered the symbol "SHOW"

Warning: Package Body created with compilation errors.


-------- -----------------------------------------------------------------
0/0	 PL/SQL: Compilation unit analysis terminated
1/14	 PLS-00304: cannot compile body of 'THE_TERMINATOR' without its

1/14	 PLS-00905: object MIKE.THE_TERMINATOR is invalid

There is another SQL*Plus setting that you might consider to at least reduce some of the pain caused by this tangle of terminators.
Remember, the terminator is a directive for SQL*Plus to run what’s in it’s buffer. By default this is the last SQL or PL/SQL statement. SQL*Plus commands are not held in the buffer.
The CLEAR BUFFER command will, well, clear the contents of the buffer…

SQL> select 'I really must watch the movies again'
  2  from dual
  3  /

I really must watch the movies again

SQL> list
  1  select 'I really must watch the movies again'
  2* from dual
SQL> clear buffer
buffer cleared
SQL> list
SP2-0223: No lines in SQL buffer.

So, run a statement and we can then see it’s in the buffer by issuing the list command ( usually abbreviated to “l”).
After the CLEAR BUFFER, there’s nothing to see.


SQL*Plus facilitates the use of two terminator characters which behave in the same way…most of the time.
Whilst it’s possible to disable, or even change the “;”, it may not be practical to go forward using just the “/” as a standard.

Deb has just “asked” me to add a disclaimer to this post.
A certain amount of artistic licence was used in the first couple of paragraphs.
After all, there’s so much junk in the cupboard under the stairs that I’d never fit in.
The Shed, Deb assures me, is an entirely different matter.

Filed under: Oracle, SQL Tagged: clear buffer, prompt, set sqlterminator, show errors, SQL*Plus, statement terminators

Installing and Configuring an Oracle Developer Day VirtualBox Image

Sun, 2015-01-25 15:28

It’s winter. I can tell. First, it’s still dark. Secondly it’s bitterly cold.
Standing on the platform at Milton Keynes Central, it would appear that we now have further evidence that being late is a train driver’s prerogative.
Hiking up the season ticket price is obviously the prerogative of train operators.
The last time I spent this much money to feel this cold, I found myself standing on the top of a mountain in the South Island of New Zealand.

Unlike the “Great” British Railway, installation and configuration of an Oracle Developer Day Appliance is somewhat simpler, not to mention more reliable.

What I’m going to cover here is the installation of a Developer Day Appliance in Virtual Box as well as some settings you might want to tweak to gain access to the Appliance Database from the Host OS.

Environment, Pre-Requisites and terminology

As always, when we’re talking about virtualization, there are going to be mentions of :

  • The Host – the machine and Operating System on which the virtualization software (in this case VirtualBox) is running
  • The Guest – the system that’s running inside the Virtual Machine

These instructions should work, irrespective of the Host OS we’re using.
In my case, I’m using Mint 17 as my Host with VirtualBox 4.3.20 installed.

If you need installation instructions for VirtualBox, you can find them here for Debian based systems or here for Windows.

Alternatively, you can check the fairly comprehensive documentation on the VirtualBox website.

As for the Developer Day Image itself, I’m going to be using the Database Application Development VM.
The system requirements for this are :

  • 2GB RAM for the Guest OS
  • 22GB of disk space (15 GB for the Image plus 5.6 GB for the download file)

One other point to note – the latest incarnation of the Database Application Development VM has port-forwarding for port 1521 enabled by default.
This is the port for the TNS listener on the database in the Image. If you already have an Oracle instance running on your host, you’ll probably want to ensure that either, it’s not using 1521 for TNS connections, or that you disable the port forwarding on the Developer Day Image once you’ve installed it.
We’ll come back to this port-forwarding malarkey in a bit.

Getting the software

Open your browser and head over to Oracle’s Pre-Build Developer VMs page.

This is part of Oracle’s Technet site and you will need a Technet account to download the software, if you don’t already have one. However, registration is free and relatively painless.

Have a look through the available images and select the one that contains the stuff your interested in.
In my case, I want to have a play around with the 12c Database, hence the choice of the Database Application Development VM.

NOTE : this page itself may be a bit out-of-date in terms of what each VM contains, so click the Downloads and Instructions link next to the VM you may be interested in. This will give you a more accurate inventory of what software the VM contains.

Anyway, once you’ve selected your VM, simply hit the Download icon, enter your Technet credentials and – depending on your connection speed – find something else to do for a bit.
Eventually, you will have a file in your Downloads directory called :


In case you were wondering, .ova files are Open VirtualBox Format. Creating a VM with this type of file is slightly different to setting one up for a .iso …

Setting up the VM

Start the Oracle VM VirtualBox Manager on your machine and from the menu, select File/Import Appliance


Appliance to Import is the file you’ve just downloaded :


Appliance Settings are all already completed so just click Import :

…and agree to the License


You’ll then see a progress bar….


And finally, you’ll see the new VM listed in VirtualBox Manager.

Now, let’s power it up and see what happens….

Once you see the desktop, click the big armoured penguin icon…


…and wait…

your patience will be rewarded with the introduction page to the hands-on sessions included in the image :


We can check that the database is up using the copy of SQLDeveloper available in the VM ( a connection for SYSTEM is already there).

Alternatively, we could just open a Terminal and login via SQL*Plus as SYSTEM ( all of the passwords are initially set to oracle).

Actually, that’s probably a point worth reiterating :

All of the accounts that you are likely to want to access on this VM have their password set to “oracle”

Either way connecting as SYSTEM will establish that the database is up. A couple of further queries will confirm the version of the Database and of APEX…

select banner
from v$version
where banner LIKE 'Oracle Database%'
select comp_name||' Version '||version as banner
from dba_registry
where comp_id = 'APEX'

Oracle Application Express Version
Oracle Database 12c Enterprise Edition Release - 64bit Production

… as well as the identity and current status of the database itself :

select, ins.host_name,
    ins.status, ins.logins,
FROM v$database db, v$instance ins

--------- ---------------------------------------------------------------- ------------ ---------- ------------
CDB1	  localhost.localdomain 					   OPEN 	ALLOWED    NOARCHIVELOG
Some Housekeeping Time and Date

The first thing you’ll probably notice is that the time and date is defaulted to US Pacific Standard time (I think).
If you want to change this :

Go to the Applicationsmenu and select Utilities/Settings/Time and Date,
Click the Unlock button and then set the time and date that’s appropriate for your location.
Finally, click the Lock button.

Region and Language (as well as Keyboard)

If, like me, you have trouble remembering where the US keyboard layout puts the “@” symbol (not to mention the “|”) then you’ll probably want to re-configure the Keyboard settings to your locality, along with the Region settings :

Go to Applications/System Tools/Settings/Regions & Language.

To change the region settings, Hit the “+” button under Input Sources and select the appropriate region.
To change the Keyboard itself, first highlight the current Input Source – i.e. English (US) – then click add to add the source you require.
Once this is done, Highlight the English (US) entry again and hit the “-” button to remove it.

For Region Changes to take effect, the system will need to be restarted. The keyboard change, however, should take effect immediately.

Setting the APEX user not to Expire

Given that these images were initially created for Developer Day events, it is perhaps not surprising that the expiration of the APEX_PUBLIC_USER password after 6 months was not a primary consideration.
If you want to avoid this little inconvenience, then the simplest way is to change the DEFAULT profile, to which all database accounts in the image are assigned :

alter profile default limit password_life_time unlimited;

NOTE: In a production environment you’d probably want to take a bit more care here. However, the Developer Day Images are purely for research.

Accessing the Database running on the VM from the Host Accessing the Database via TNS

As mentioned previously, port-forwarding of 1521 (The default SQL*Net port) is already pre-configured.
Therefore, assuming that you have an Oracle Client installed, you can connect to the database on the Guest (whilst it’s running) from the Host as follows :

sqlplus system/oracle@

Once you have a SQL prompt, you can check to see that you now have access to Oracle in all it’s 12c splendour…

select banner from v$version;

Oracle Database 12c Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE	Production
TNS for Linux: Version - Production
NLSRTL Version - Production

For convenience, you can always add the following entries to $ORACLE_HOME/network/admin/tnsnames.ora on your host :

CDB1 =
    (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
      (SERVICE_NAME = cdb1)
    (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
      (SERVICE_NAME = orcl)
Accessing APEX

We can repeat the simple port-forwarding trick to make the APEX home page available on the Host as well.

In Virtual Box Manager, with the Developer Day VM highlighted, click Settings
Select Network
Click the Advanced arrow


Click the Port Forwarding button
Now Click the Add icon on the right-hand side of the window and enter the following values for each of the fields :

  • Name : http
  • Protocol : TCP
  • Host Port : 8080
  • Guest Port : 8080

Note : once again, you’ll probably want to change these values if you have a local instance of Oracle running on the Host.

Anyway, your rules should now look like this :


You can now hit the APEX main page from a browser in the host by entering :



Note – the credentials for the ADMIN user for APEX are :

Workspace : INTERNAL
Username : ADMIN
Password : oracle

And there you have it, a quick and simple (not to mention, cheap) way to get Oracle 12c installed on your local machine. Now, if only they’d do the equivalent for getting from Milton Keynes to London.

Filed under: Oracle, VirtualBox Tagged: acessing apex, connecting to a guest database from the host, default passwords, Developer Day image, keyboard configuration, port forwarding, region settings, VirtualBox, VM

CREATE USER and ALTER USER – changing passwords and a New Year’s Resolution

Sun, 2015-01-11 08:39

Monday morning. The first day back at work in the New Year.
Still groggy, having been awoken from my slumber at the insistence of my Darth Vader Lego Alarm Clock, I stagger downstairs in search of coffee.
The clock was a Christmas present from Deb. Whilst clinking around the kitchen, I wonder whether it was intended as a subtle reminder of how she likes her coffee with only a little milk. Yes, she prefers it on the Dark Side.
Then I remember. I usually avoid New Year’s resolutions. I find them to be not unlike a cheap plastic toy at Christmas – there in the morning and then broken by the first afternoon.
This year however, is an exception.

In a recent post about APEX Authentication Schemes, I went to great lengths to ensure that a dynamic SQL statement to re-set a users password was safe from the possibility of injection.
Fortunately, Jeff Kemp took the time to point out a couple of issues with my approach.
As a result, this year, my resolution is to :

What follows is the result of keeping this resolution ( so far, at least)…

Setting a Password with CREATE USER and ALTER USER

To start with, we’re going to forget about any PL/SQL context for this exercise and simply focus on the ALTER USER command when used to change a user’s password.

First of all, we need a user…

create user marvin identified by 42!

create user marvin identified by 42!
ERROR at line 1:
ORA-00922: missing or invalid option

That’s a bit surprising. There’s no password verify function on the DEFAULT profile in the database :

select resource_name, limit
from dba_profiles
where resource_type = 'PASSWORD'
and resource_name = 'PASSWORD_VERIFY_FUNCTION'
and profile = 'DEFAULT'

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------

…and even if there were – ORA-00922 ?

At this point, much head-scratching can be saved if we follow Jeff’s recommended link to the relevant documentation.

From this we can see that the password has the same rules applied to it as a database object. So…

  • it must start with a letter
  • …and can’t contain spaces…
  • …or characters other than alphanumeric, $, # or _


create user marvin identified by "42!"

User created.

Yes, double-quotes around the password will allow other characters to be used, just as with database objects.
So, does the user need to provide the double-quotes when they connect ?

grant create session to marvin


connect marvin/42!@mydb

…works fine. No need to include the double-quotes when connecting.
If the password contains spaces however, then the double-quotes are required :

alter user marvin identified by "Feeling very depressed"

conn marvin/Feeling very depressed@XE
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]

conn marvin/"Feeling very depressed"@XE
A Sensible Change Password Procedure

We now come back to the question of how to provide the functionality for a user to change their password by means of a PL/SQL procedure. For the sake of simplicity, we’re going to assume that there is no password verify function in place and therefore the old password does not need to be provided :

create or replace procedure change_pwd_pr
    i_new_pwd in varchar2
    authid current_user
    execute immediate 'alter user '||user||' identified by '||CHR(34)||i_new_pwd||CHR(34);

Now, we are dropping user input directly into a dynamic SQL statement, something that rings alarm bells in terms of the potential for injection.
There would appear to be two techniques that are common to injection of such code.

Here, however, we’re only concatenating a password. This is not a database object per se. Once it is set, it will be referenced only as the password hash by Oracle’s internal authentication process and will therefore not be useful as a blind injection string.
Additionally, by enclosing the password in double-quotes, it is simply treated as a string within the dynamic SQL statement itself.

Let’s put this to the test.

First, we need to make the procedure available to MARVIN :

grant execute on change_pwd_pr to marvin;

Now let’s run some tests. Connect as MARVIN and …

set serveroutput on size unlimited
    function test_pwd_fn( i_test_no in pls_integer, i_pwd in varchar2)
        return varchar2
        mike.change_pwd_pr( i_new_pwd => i_pwd);
        return ('Test '||i_test_no||' - Password Changed.');
        when others then
            return('Test '||i_test_no||'- Password Change Failed : '||substr(sqlerrm,1,100));
    end test_pwd_fn;
    dbms_output.put_line(test_pwd_fn( 1, '42!'));
    dbms_output.put_line(test_pwd_fn( 2, 'silly'||'; grant dba to marvin'));
    dbms_output.put_line(test_pwd_fn( 3, q'[I want you to know that I'm feeling very depressed]'));
    dbms_output.put_line(test_pwd_fn( 4, 'How awful!'));

Run this as Marvin and we get :

SQL> @tests.sql
Test 1 - Password Changed.
Test 2 - Password Changed.
Test 3- Password Change Failed : ORA-00972: identifier is too long
Test 4 - Password Changed.

PL/SQL procedure successfully completed.


If we now check, we can confirm that our attempt at injection in test2 was not successful :

select granted_role 
from user_role_privs

no rows selected


Thanks to Jeff, I will henceforth be moderating my paranoia when dealing with programatic password resets.
Oh yes, and I’ll try to remember to read the manual.

Filed under: Oracle, PL/SQL, SQL Tagged: alter user, alter user identified by, create user, create user identified by, dba_profiles, escape new password with double quotes, identified by, ORA-00922, ORA-00972, oracle password rules, password verify function

Installing VirtualBox on Mint with a CentOS Guest

Sun, 2014-12-21 12:48

Christmas is almost upon us. Black Friday has been followed by Small Business Saturday and Cyber Monday.
The rest of the month obviously started on Skint Tuesday.
Fortunately for all us geeks, Santa Claus is real. He’s currently posing as Richard Stallman.
I mean, look at the facts. He’s got the beard, he likes to give stuff away for free, and he most definitely has a “naughty” list.

Thanks to Santa Stallman and others like him, I can amuse myself in the Holidays without putting any more strain on my Credit Card.

My main machine is currently running Mint 17 with the Cinnamon desktop. Whilst I’m very happy with this arrangement, I would like to play with other Operating Systems, but without all the hassle of installing/uninstalling etc.
Now, I do have Virtualbox on a Windows partition, but I would rather indulge my OS promiscuity from the comfort of Linux… sorry Santa – GNU/Linux.

So what I’m going to cover here is :

  • Installing VirtualBox on a Debian-based distro
  • Installing CentOS as a Guest Operating System
  • Installing VirtualBox Guest Additions Drivers on CentOS

I’ve tried to stick to the command-line for the installation steps for VirtaulBox so they should be generic to any Debian based host.


Throughout this post I’ll be referring to the Host OS and the Guest OS, as well as Guest Additions. These terms can be defined as :

  • Host OS – the Operating System of the physical machine that Virtualbox is running on ( Mint in my case)
  • Guest OS – the Operating System of the virtual machine that is running in VirtualBox (CentOS here)
  • Guest Additions – drivers that are installed on the Guest OS to enable file sharing, viewport resizing etc
Options for getting VirtualBox

Before I get into the installation steps it’s probably worth explaining why I chose the method I did for getting VirtualBox in the first place.
You can get VirtualBox from a repository, instructions for which are on the VirtualBox site itself. However, the version currently available ( 4.3.12 at the time of writing) does not play nicely with Red Hat based guests when it comes to Guest Additions. This issue is fixed in the latest version of Virtualbox (4.3.20) which can be downloaded directly from the site. Therefore, this is the approach I ended up taking.

Right, now that’s out of the way…

Installing VirtualBox Step 1 – Prepare the Host

Before we download VirtualBox, we need to ensure that the dkms package is installed and up to date. So, fire up good old terminal and type :

sudo apt-get install dkms

Running this, I got :

Reading package lists... Done
Building dependency tree       
Reading state information... Done
dkms is already the newest version.
0 to upgrade, 0 to newly install, 0 to remove and 37 not to upgrade.

One further step is to make sure that your system is up-to-date. For Debian based distros, this should do the job :

sudo apt-get update
Step 2 – Get the software

Now, head over to the VirtualBox Downloads Page and select the appropriate file.

NOTE – you will have the choice of downloading either the i386 or the AMD64 versions.
The difference is simply that i386 is 32-bit and AMD64 is 64-bit.

In my case, I’m running a 64-bit version of Mint (which is based on Ubuntu), so I selected :

Ubuntu 13.04( “Raring Ringtail”)/ 13.10(“Saucy Salamander”)/14.04(“Trusty Tahr”)/14.10(“Utopic Unicorn”) – the AMD64 version.

NOTE – if you’re not sure whether you’re running on 32 or 64-bit, simply type the following in a terminal session :

uname -i

If this comment returns x86_64 then you’re running a 64-bit version of your OS. If it returns i686, then you’re running a 32-bit version.

A short time later, you’ll find that Santa has descended the chimney that is your browser and in the Downloads folder that is your living room you have present. Run…

ls -lh $HOME/Downloads/virtualbox*

… and you’ll find the shiny new :

-rw-r--r-- 1 mike mike 63M Dec  5 16:22 /home/mike/Downloads/virtualbox-4.3_4.3.20-96996~Ubuntu~raring_amd64.deb
Step 3 – Installation

To virtually unwrap this virtual present….

cd $HOME/Downloads
sudo dpkg -i virtualbox-4.3_4.3.20-96996~Ubuntu~raring_amd64.deb

On running this the output should be similar to :

(Reading database ... 148385 files and directories currently installed.)
Preparing to unpack virtualbox-4.3_4.3.20-96996~Ubuntu~raring_amd64.deb ...
Stopping VirtualBox kernel modules ...done.
Unpacking virtualbox-4.3 (4.3.20-96996~Ubuntu~raring) over (4.3.12-93733~Ubuntu~raring) ...
Setting up virtualbox-4.3 (4.3.20-96996~Ubuntu~raring) ...
Installing new version of config file /etc/init.d/vboxdrv ...
addgroup: The group `vboxusers' already exists as a system group. Exiting.
Stopping VirtualBox kernel modules ...done.
Uninstalling old VirtualBox DKMS kernel modules ...done.
Trying to register the VirtualBox kernel modules using DKMS ...done.
Starting VirtualBox kernel modules ...done.
Processing triggers for ureadahead (0.100.0-16) ...
Processing triggers for hicolor-icon-theme (0.13-1) ...
Processing triggers for shared-mime-info (1.2-0ubuntu3) ...
Processing triggers for gnome-menus (3.10.1-0ubuntu2) ...
Processing triggers for desktop-file-utils (0.22-1ubuntu1) ...
Processing triggers for mime-support (3.54ubuntu1) ...

Note As this was not my first attempt at installing VirtualBox, there are some feedback lines here that you probably won’t get.

Anyway, once completed, you should have a new VirtualBox icon somewhere in your menu.
In my case (Cinnamon desktop on Mint 17, remember), it’s appeared in the Administration Menu :


As part of the installation, a group called vboxusers has now been created.
You’ll want to add yourself to this group so that you can access the shared folders, which is something I’ll come onto in a bit. For now though…

sudo usermod -a -G vboxusers username

… where username is your user.

Now, finally, we’ve set it up and can start playing. Click on the menu icon. Alternatively, if you can’t find the icon, or if you just prefer the terminal, the following command should have the same effect :


Either way, you should now see this :


One present unwrapped, assembled and ready to play with…and you don’t even need to worry about cleaning up the discarded wrapping paper.

Installing the CentOS Guest

I fancy having a play with a Red Hat-based distro for a change. CentOS fits the bill perfectly.
Additionally, I happen to have an iso lying around on a cover disk.
If you’re not so lucky, you can get the latest version of CentOS (currently 7) from the website here.

I’ve created a directory called isos and put the CentOS iso there :

ls -lh CentOS*
-rw------- 1 mike mike 687M Jul  9 22:53 CentOS-7.0-1406-x86_64-livecd.iso

Once again, I’ve downloaded the 64-bit version, as can be seen from the x86-64 in the filename.

Now for the installation.

Open VirtualBox and click New :

In the Name and operating system window enter :

Name : CentOS7
Type : Linux
Version Red Hat(64 bit)


In the Memory Size Window :

Settings here depend on the resources available to the host machine and what you want to use the VM for.
In my case, my host machine has 8GB RAM.
Also, I want to install Oracle XE on this VM.
Given that, I’m going to allocate 2GB to this image :


In the Hard Drive Window :

I’ve got plenty of space available so I’ll just accept the default to Create a virtual hard drive of 8GB now.

Hard Drive File Type :

Accept the default ( VDI (VirtualBox Disk Image))

and hit Next…

Storage on physical hard drive :

I’ll leave this as the default – Dynamically allocated
Click Next…

File location and size :

I’ve left the size at the default…


I now have a new VirtualBox image :
The vdi file created to act as the VM’s hard drive is in my home directory under VirtualBox VMs/CentOS7


Now to point it at the iso file we want to use.

Hit Start and ….



You should now see the chosen .iso file identified as the startup disk :


Now hit start….

Don’t worry too much about the small viewport for now. Guest Additions should resolve that issue once we get it installed.
You probably do need to be aware of the fact that you can transfer the mouse pointer between the Guest and Host by holding down the right CTRL key on your keyboard and left-clicking the mouse.
This may well take a bit of getting used to at first.

Anyway, once you’re guest knows where your mouse is, the first thing is to actually install CentOS into the VDI. At the moment, remember, we’re just running a Live Image.

So, click the Install to Hard Drive icon on the CentOS desktop and follow the prompts as normal.

At the end of the installation, make sure that you’ve ejected your virtual CD from the drive.
To do this :

  1. Get the Host to recapture the mouse (Right CTRL + left-click)
  2. Go to the VirtualBox Menu on the VDI and select Devices/CD/DVD Devices/Remove disk from virtual drive


Now re-start CentOS.

Once it comes back, we’re ready to round things off by…

Installing Guest Additions

It’s worth noting that when CentOS starts, Networking is disconnected by default. To enable, simply Click the Network icon on the toolbar at the top of the screen and switch it on :


We need to make sure that the packages are up to date on CentOS in the same way as we did for the Host at the start of all this so…

sudo yum update

Depending on how recent the iso file you used is, this could take a while !

We also need to install further packages for Guest Additions to work…

sudo yum install gcc
sudo yum install kerenel-devel-2.10.0-123.9.3.el.x86_64

Note It’s also recommended that dkms is installed on “Fedora” (i.e. Red Hat) based Guests. However when I ran …

sudo yum install dkms

I got an error saying “No package dkms available”.
So, I’ve decided to press on regardless…

In the VirtualBox Devices Menu, select Insert Guest Additions CD Image

You should then see a CD icon on your desktop :


The CD should autorun on load.

You’ll see a Virtual Box Guest Additions Installation Terminal Window come up that looks something like this :

Verifying archive integrity... All good.
Uncompressing VirtualBox 4.3.20 Guest Additions for Linux............
VirtualBox Guest Additions installer
Removing installed version 4.3.12 of VirtualBox Guest Additions...
Copying additional installer modules ...
Installing additional modules ...
Removing existing VirtualBox non-DKMS kernel modules       [  OK  ]
Building the VirtualBox Guest Additions kernel modules
Building the main Guest Additions module                   [  OK  ]
Building the shared folder support module                  [  OK  ]
Building the OpenGL support module                         [  OK  ]
Doing non-kernel setup of the Guest Additions              [  OK  ]
Starting the VirtualBox Guest Additions                    [  OK  ]
Installing the Window System drivers
Installing X.Org Server 1.15 modules                       [  OK  ]
Setting up the Window System to use the Guest Additions    [  OK  ]
You may need to restart the hal service and the Window System (or just restart
the guest system) to enable the Guest Additions.

Installing graphics libraries and desktop services componen[  OK  ]

Eject the CD and re-start the Guest.

Now, you should see CentOS in it’s full-screen glory.

Tweaks after installing Guest Additions

First off, let’s make things run a bit more smoothly on the Guest :

On the Host OS in VirtualBox Manager, highlight the CentOS7 image and click on Settings.
Go to Display.

Here, we can increase the amount of Video Memory from the default 12MB to 64MB.
We can also check Enable 3D Acceleration :


Next, in the General Section, click on the Advanced Tab and set the following :

Shared Clipboard : Bidirectional
Drag’n’Drop : Bidirectional


You should now be able to cut-and-paste from Guest to host and vice-versa.

Shared Folders

At some point you’re likely to want to either put files onto or get files from your Guest OS.

To do this :

On the Host

I’ve created a folder to share on my Host system :

mkdir $HOME/Desktop/vbox_shares/centos

Now, in VirtualBox Manager, back in the Settings for CentOS, open the Shared Folders section.

Click the Add icon


Select the folder and make it Auto-mount


On the Guest

In earlier versions of VirtualBox, getting the shared folders to mount was, well, a bit of messing about.
Happily, things are now quite a bit easier.

As we’ve set the shared folder to Auto-mount, it’s mounted on the Guest on


…where sharename is the name of the share we assigned to it on the Host. So, the shared folder I created exists as :


In order to gain full access to this folder, we simply need to add our user to the vboxsf group that was created when Guest Additions was installed :

sudo usermod -a -G vboxsf username

…where username is your user on the Guest OS.

Note – you’ll need to logout and login again for this change to take effect, but once you do, you should have access to the shared folder.

Right, that should keep me out of trouble (and debt) for a while, as well as offering a distraction from all the things I know I shouldn’t eat…but always do.
That reminds me, where did I leave my nutcracker ?

Filed under: Linux, VirtualBox Tagged: centos 7 guest, copy and paste from clipboard, guest additions, how to tell if your linux os is 32-bit or 64-bit, mint 17 host, shared folders, uname -i, VirtualBox

Implementing a Database Authentication Scheme in APEX

Sun, 2014-12-14 12:26

The following tangential opening was written especially for Scott Wesley in the hope that he’ll be minded to point out any errors in what follows. The same applies to Jeff Kemp ( although I don’t know if he’s into the AFL).
Unlike me, both of these guys are APEX experts.

Football. It’s a term that means different things to different people.
To a European, it’s most likely to be a reference to good old Association Football ( or Soccer).
To an American, it’s more likely to be the Grid-iron game.
A New Zealander will probably immediately think of Rugby Union.
An Australian ? Well, it’s probably a fair bet that they’ll think of Aussie Rules Football.

On the face of it, the rules appear rather arcane to an outsider. 18-a-side teams kicking, catching and punching something that resembles a Rugby ball around a pitch that resembles a cricket oval. Then there is the scoring system.
“Nice Behind”, to an AFL player is more likely to be taken as a compliment of their skill at the game than an appreciation of their anatomy.

Then again, it’s easy to scoff at any sport with which you are unfamiliar.
For example, Rugby could be characterised as 30 people chasing after an egg. Occasionally, they all stop and half of them go into some strange kind of group hug. I wonder if the backs ever get paranoid because they think the forwards are talking about them ?

As for soccer, even afficionados will acknowledge that there’s something a bit odd about a game where 22 millionares spend lots of time chasing after one ball…when they’re not rolling around in apparent agony after appearing to trip over an earth worm. I mean, the ball isn’t that expensive, surely they can afford one each ?

The point of all of this ? Well, what is considered to be obscure, eccentric, or just plain odd often depends on the perspective of the observer.

Take APEX authentication schemes for example.
Whilst not the default, Database Authentication is a scheme that is readily available. However, there doesn’t seem to be much written on this subject.

In contrast, there is a fair bit out there about APEX Custom Authentication. A lot of it would appear to re-enforce the idea that implementing security by hand is fraught with difficulty.
Just one example can be seen here.

If we were to approach this topic from the perspective of looking to migrate an elderly Oracle Forms application – where each user has their own database account – to APEX, we might be attracted to the idea of a Database Authentication Scheme and want to find out more.

What follows is my adventure through setting up such an Authentication Scheme.
Specifically, I’m going to cover :

  • Creating an APEX Database Authentication Scheme
  • Default behaviour
  • Adding a Verification Function to restrict access to a sub-set of Database Users
  • The vexed question of password resets

Why use Database Authentication

The Oracle documentation states :

“Database Account Credentials is a good choice if having one database account for each named user of your application is feasible and account maintenance using database tools meets your needs.”

If we’re migrating an application from Oracle Forms, then chances are that this is what we’re doing now, so a Database Authentication Scheme should save us a fair bit of work.
The other major advantage is that utilising the Database’s built-in User and Security management means that we don’t have to try and re-invent the wheel.
So, the objective here is to implement Authentication in our new Application without having to :

  • Create and maintain extra tables
  • Write lots of extra code
  • Figure out a secure way of storing passwords
The Application

Firing up my trusty XE 11g installation, I’ll be using a simple APEX application that consist of a standard login page and, initially at least, a Home Page with two read only fields in an HTML Region called WHOAMI.
These are :

  • Application User – the APP_USER that I’m connected to APEX as
  • Database User – the actual user connected to the database

For the P1_APPLICATION_USER, the Source Type is Item (application or page item name).
The source value is APP_USER.
For the P1_DATABASE_USER, the Source Type is set to SQL Query(return single value).
The source value is simply the query :

select user from dual
A Note on the Design

In this example, I’ve taken the approach that the code required to implement this functionality is included in the parsing schema ( HR in this case). As a consequence, the privileges required to execute this code are also granted to the parsing schema.
I’ve done this for the purposes of clarity.
Careful consideration needs to be given to this design decision if you’re planning to implement it in a “proper” production environment.

Creating a Database Authentication Scheme

After navigating to the Application in Application Builder, rather than do anything to the Application itself, we need to create a Shared Component…


The type of component we want is an Authentication Scheme.

NOTE – Authentication Scheme – controls login to the Application.
Authorisation Scheme – governs which bits of the Application the user can see…once they’re connected.

Anyway, in the Security Region, select Authentication Scheme :


…and then hit the Create button…


We want to create a scheme “Based on a pre-configured scheme in the gallery” …


In the next screen :

Name : HR_DB
Scheme Type : Database Accounts


And finally, we click the Create Authentication Scheme button and…


We can see from this that HR_DB is now the Authentication Scheme currently being used by any Application in the Workspace.

Anyway, now to test it.

To this point, I haven’t setup any users for this application.

So, Can I log in as a user that does exist in the database ?
Well, I have a user called MIKE :

select 1
from dba_users
where username = 'MIKE'



So, if I now run my application and try to connect using my database credentials…


… I can connect using my database credentials.


It’s worth noting that, despite this, the actual database connection from APEX is as the ANONYMOUS user.
If you’re using the APEX Listener instead of the Embedded PL/SQL Gateway (the default in XE), then it’ll probably be APEX_PUBLIC_USER.

So, in order to login to my application, you now have to be a database user.
All the messy password encryption stuff is handled by Oracle and I can now get on with polishing my finely crafted APEX Application….or so you might think.

Just consider this :


…also let’s you connect :

We're not fussy, we'll let anyone in !

We’re not fussy, we’ll let anyone in !

Now, my imaginary Forms application – remember, that’s the one I want to migrate to APEX – may be sitting on a Database Instance with a number of other Applications. So, how do I restrict access to my application to a subset of the users in the database ?
Time for a bit of a re-think then…

The verify function

What we need is a means of identifying a database user as an Application user.
At this point it may well be worth revisiting the role of database roles in APEX applications.
Hang on, you’re thinking, last time you said they were pretty much useless in APEX.
Well, bear with me.

Roles as Privileges, sort of

What we’re going to do here is to simply create an empty role and assign it to a database user :

create role hr_user

grant hr_user to mike

We now have some means of determining which database users are our application users :

select 1
from dba_role_privs
where granted_role = 'HR_USER'
and grantee = 'MIKE'
The function

Now all we need is a function that checks to see if the user attempting to login has this role granted to them.
It’s worth bearing in mind here that, for a function based on the above statement, select privileges on DBA_ROLE_PRIVS is required.

To start with I’m going to grant the privilege to HR :

grant select on sys.dba_role_privs to hr

and then I’m going to create the function in the HR schema :

create or replace function is_hr_user_fn
    return boolean
-- Is this user a database user with privileges to access the APEX Application ?
-- NOTE - the owner of this function requires SELECT privilege on DBA_ROLE_PRIVS
    l_dummy pls_integer;
    select 1 
    into l_dummy 
    from sys.dba_role_privs
    where granted_role = 'HR_USER'
    and grantee = apex_040200.v('APP_USER');
    return true;
    when no_data_found then
        raise_application_error('-20000', 'You are not an application user');

You’ll note that the references to both DBA_ROLE_PRIVS and the V function are done directly on the objects themselves rather than through their public synonyms.
In many cases, but especially where security is concerned, it’s usually a good idea to make sure that you’re referencing the object that you intend rather than relying on a synonym.

If you want to see an example of how public synonyms can be changed to point to objects other than those originally intended, then have a look here.

Now we need to tell our Authentication scheme to use this function as the Verify Function.
In the Application Builder, go back to the Shared Components screen then select Authentication Schemes.

Now click on the pencil icon next to HR_DB – Current :

If you want to be a bit more discerning...

If you want to be a bit more discerning…

In the Session Not Valid section, there is a field called Verify Function Name.
In here, simply enter the name of our function – i.e. is_hr_user_fn :

...add a Verify Function

…add a Verify Function

And save the changes.

So, we should now be able to connect as MIKE, but not any other database user.

Connecting as MIKE works as before. However, for SYSTEM, the results are slighty different :

Your name's not down, you're not coming in !

Your name’s not down, you’re not coming in !

As we can see, the Application Error raised by the function is displayed. If you hit the OK button, you’ll then be returned to the Login Page.

The Principle of Least Privilege

In case your not familiar with the term, it basically boils down to the principle that access to an application should be restricted to the minimum level required for a user, application or program to function.
Have a look here for a proper explanation.

It’s probably worth noting that, implementing this approach to Authentication means that, in order to create a new application user, all that’s required is the following :

create user plugger identified by pwd

grant hr_user to plugger

In case you’re wondering, Plugger is the nickname of a certain Tony Lockett who, apparently, was a pretty good Aussie Rules player in his time.

Anyway, as you can see, our new user requires no system privileges, not even CREATE SESSION. They simply need to be granted the role so that they can be identified as an application user.

Whilst were on the subject of least privilege, you might consider that it is by no means necessary for the parsing schema of an APEX application to have CREATE SESSION priviliges, or indeed, to even be the owner of the application’s database objects.
This applies irrespective of the Authentication Scheme being used.

We now have a robust and efficient Authentication Scheme. There is however, one rather thorny issue that we still need to consider.

Changing Passwords

Whilst we now have a mechanism for authenticating users through their database accounts, unless we give them the facility to change their passwords before they expire, we’ll be storing up a significant amount of admin for the poor, hard-pressed DBA.

The venerable Forms Application we’re migrating was written in the days prior to SSO becoming prevalent and authentication is still managed entirely within the database. Remember, the whole point of chosing Database Authentication is so that we minimise the amount of effort required to migrate this application onto APEX in terms of re-coding the Application’s Security Model.

This is where things get a bit tricky.
Whilst our users are authenticating as themselves, they are actually connecting to the database as ANONYMOUS or APEX_PUBLIC_USER.

Therefore, we need a procedure in a schema with ALTER USER privileges to change passwords from within the APEX application.

So, how do we provide this functionality in our application.

Danger ! Assumption Imminent !

As I’m all too aware ( often through bitter experience), Assumption is the Mother of all cock-ups.
Therefore, the assumption I’m about to make here requires careful explanation.

Here goes then…

I’m assuming that I can safely call a stored procedure from within APEX, passing a user password in clear text.

Clear text ! I hear you cry, Have you gone mad ?
Well, possibly. On the other hand a trawl through of the APEX documentation reveals that there are a few package members in the APEX packages themselves where this takes place.

These are :

Further research reveals that, certainly in the latest versions of APEX, there do not appear to be any exploits available to compromise these procedures. The most recent one I found was for APEX 3.1, an example of which can be seen on the Red Database Security site.

As well as giving the user the ability to change their password at any time, we also want to check immeadiately after the user connects and find out whether their password is near to expiry. If so, then we need to re-direct them to a password change page.
What was Jeff saying about scary code ?

Anyway, the steps to build this functionality are, in order :

  1. Create a Change Password Procedure to be called from the application
  2. Create a Change Password Page where the user can change their password ( and which will call the procedure)
  3. Create a branch in the Application to re-direct a user to the Change Password Page if their password is due to expire

Now in my original post, I had a whole section in here about not allowing quotes in passwords so that our dynamically built password change command would not be susceptible to injection.
Fortunately, Jeff Kemp took the time to point out the error of my ways (see the comments at the end of this post).
I’ve also now posted a follow up which explores this particular aspect of password changes in more detail.

Allowable characters in the password

As per the update above, I’ve now removed this section completely because it’s not necessary.
The password input parameters to the Change Password Procedure that we come up with should simply be enclosed in double-quotes when being concatenated into the ALTER USER statement.

The Change Password Procedure

Once again, this procedure is being created in the HR schema. It will be used to ultimately issue the ALTER USER command to change the passwords. Therefore, we need to grant the ALTER USER privilege to HR :

grant alter user to hr

As this procedure also needs to reference DBA_USERS, we’ll need to grant SELECT on that too.

grant select on sys.dba_users to hr

When writing this procedure, paranoia is the watchword. Objects need to be referenced directly, rather than via synonyms and any user input needs to be sanitised before we plug it into the dynamic SQL statement we need to run.
The result might look something like this :

create or replace procedure change_apex_user_pwd_pr
    i_old_pwd in varchar2,
    i_new_pwd in varchar2
-- Procedure to change the password for a user of the NEW_HR APEX application
-- The old password is required, as well as the new one because, if we're
-- using a verify function in the profile the user is assigned to, the
-- old password must be specified in the ALTER USER statement.
    l_user sys.dba_users.username%type;
    lc_apex_user constant sys.dba_users.username%type := 'ANONYMOUS';
    l_dummy pls_integer;
    cursor c_validate_user( cp_user sys.dba_users.username%type)
        select 1
        from sys.dba_users usr
        inner join sys.dba_role_privs rol
            on rol.grantee = usr.username
        where usr.username = cp_user;
    -- Make sure that the parameter values have been specified
    if i_new_pwd is null or i_old_pwd is null then
        raise_application_error(-20000, 'Both the Old Password and the New Password must be specified');
    end if;
    -- Additionally, check that the password does not exceed the maximum length
    -- allowed ( 50 in 11g)
    if length( i_old_pwd) > 50 
        or length( i_new_pwd) > 50
        raise_application_error(-20001, 'Passwords must not exceed 50 characters in length.');
    end if;
    -- Now validate that the user is indeed 
    --  (a) calling the function from APEX
    --  (b) exists in the database
    --  (c) is a user of this application

    l_user := apex_040200.v('APP_USER');
    if l_user is null 
        or user != lc_apex_user
        raise_application_error(-20002, 'This function can only be called from APEX');
    end if;
    open c_validate_user( l_user);
    fetch c_validate_user into l_dummy;
    if c_validate_user%notfound then
        close c_validate_user;
        raise_application_error(-20003, 'This user is not a NEW_HR Application user');
    end if;
    close c_validate_user;
    -- Now change the password. REPLACE clause is required in case the
    -- user's default profile has a password verify function specified
    -- Enclose the old and new passwords in double quotes...
    execute immediate 'alter user '||l_user||' identified by '||chr(34)
        ||i_new_pwd||chr(34)||' replace '||chr(34)||i_old_pwd||chr(34);

In the procedure itself, we’re taking a number of precautions :

  • Values for both parameters must be supplied
  • The input parameter values must not exceed 50 characters – the maximum length of an 11g password
  • The user currently connected to the database is the Apex user ( in my case ANONYMOUS)
  • A call to the V function for the application user returns a value
  • The application user we’re changing is indeed a valid user of the NEW_HR Apex application – and a database user
  • references to any database objects are done directly and not via synonyms

Hopefully, that’s enough paranoia to prevent the procedure being misused.
We can use a test harness to check the parameter tests at least :

set serveroutput on size unlimited
-- test for the change_apex_user_pwd_pr procedure.
-- Note all of these tests should fail as we're running from SQL*Plus and
-- are not connected as ANONYMOUS.
    type rec_params is record
        old_pwd varchar2(100),
        new_pwd varchar2(100)
    type typ_params is table of rec_params index by pls_integer;
    tbl_params typ_params;
    -- populate the test parameter array
    -- Test 1 - missing old password value
    tbl_params(1).old_pwd := null;
    tbl_params(1).new_pwd := 'Boring';
    -- Test 2 - missing new password value
    tbl_params(2).old_pwd := 'Boring';
    tbl_params(2).new_pwd := null;
    -- Test 3 - old password > 50 characters
    tbl_params(3).old_pwd := 'just leaning on the keyboard until i have printed over 50 characters zzzzz';
    tbl_params(3).new_pwd := 'short_and_to_the_point';
    -- Test 4 - new password > 50 characters
    tbl_params(4).old_pwd := 'short_and_to_the_point';
    tbl_params(4).new_pwd := 'just leaning on the keyboard until i have printed over 50 characters zzzzz';
    -- Test 5 - parameters are valid but we're not connected through APEX...
    tbl_params(5).old_pwd := 'Valid_pwd';
    tbl_params(5).new_pwd := 'anotherboringpassword';
    -- Execute the tests
    for i in 1..tbl_params.count loop
                i_old_pwd => tbl_params(i).old_pwd,
                i_new_pwd => tbl_params(i).new_pwd
            dbms_output.put_line('Test '||i||' - Somthing has gone wrong - no error !');
            when others then
                dbms_output.put_line('Test '||i||' Error : '||sqlerrm);
    end loop;

Running this gives us :

Test 1 Error : ORA-20000: Both the Old Password and the New Password must be specified
Test 2 Error : ORA-20000: Both the Old Password and the New Password must be specified
Test 3 Error : ORA-20001: Passwords must not exceed 50 characters in length.
Test 4 Error : ORA-20001: Passwords must not exceed 50 characters in length.
Test 5 Error : ORA-20002: This function can only be called from APEX

PL/SQL procedure successfully completed.

To test the rest of the function, we will of course, need to be connected via APEX.

The Change Password Page

Now we come to the page we will be using to call the procedure we’ve just created.
The page will have :

  • a password field for the application user to enter their current password
  • a password field for the application user to enter their new password
  • and another one for them to re-type it
  • some validation that the new password and confirm password matches
  • a button to call the change password procedure
  • a field to present a message to the user after the password change call

Sounds simple (dangerous) enough…

In Application Builder hit the Create Page button…

select Blank Page ….

In the Page Attributes…

Page Alias : change_db_pwd


In the Page Name …

Name : Change My Password
HTML Region1 : change password


In Tab Options…

Tab Options : Use an existing tab set and create a new tab within the existing tab set
New Tab Label : Change Password


…and hit Finish.

Now Edit the Page.

Create a new field with an Item Type of Password :


In the Display Position and Name screen,

Item Name : PX_OLD_PWD (where X is the number of the page you’re editing).


In the Item Attributes Screen :

Label : Current Password
Field Width : 50


In the Settings Screen –

Value Required : Yes
Submit when Enter pressed : No


In the Source Screen :

Source Used : Always, replacing any existing session state

Hopefully, the APEX5 Graphical Page Designer will result in fewer screenshots being required in future !

Hopefully, the APEX5 Graphical Page Designer will result in fewer screenshots being required in future !

And hit Create Item.

Now create two further fields with the same properties except :

PX_NEW_PWD has a label of New Password
PX_CONFIRM_PWD has a label of Confirm New Password

Next, we create a Display Only field called PX_MESSAGE.
We’ll use this to provide feedback to the user.
We define this with no label so that it doesn’t show up on the screen, until it’s populated.

Now we’ve got all of the fields on the page the next step is to create the Change Password button :

Accept the defaults for Button Region and Button Position.

In the Button Attributes Page :

Button Name : change_pwd_btn
Label : Change Password


Then just hit Create Button.

Finally, we need to add a Dynamic Action to validate that the values in PX_NEW_PWD and PX_CONFIRM_PWD are not null and identical, and then to call the Procedure.

NOTE – I daresay any APEX experts reading this may have a better way of doing this !

So, Create a Dynamic Action.

In the Identification Page :

Name : change_pwd_da


In the When Page :
Action : Click
Selection Type : Button


In the True Action Page :

Action : Execute PL/SQL Code

The PL/SQL Code is as follows :

    if nvl(:P6_NEW_PWD, 'x') != nvl(:P6_CONFIRM_PWD, 'y')
       :P6_MESSAGE := 'Confirm Password does not match New Password.';
             i_old_pwd => :P6_OLD_PWD,
             i_new_pwd => :P6_NEW_PWD
        :P6_MESSAGE := 'Your password has been changed';
    end if;
exception when others then

Page Items to Return : P6_MESSAGE


Click Create Dynamic Action.

Now to test.
I’m connected as PLUGGER and I want to change my password.
So, I click on the Change Password Tab and I see :


If the new and confirm password fields don’t match, I get an error from the Dynamic Action itself, before it calls the procedure :

Someone's having a fat-finger moment

Someone’s having a fat-finger moment

When I manage to get it right and am rewarded with :


Invoking the Change Password Programatically

All that remains now is for us to arrange for the user to be re-directed to the change password page when they connect and their password is near expiry.

The password expiry_date is available in the DBA_USERS view so we need to grant SELECT on this to HR :

grant select on sys.dba_users to hr

As I’m re-directing them to a page that belongs specifically to the current application, I’m going to put the re-direction in the application itself.
So, I’m going to add a Branch to the Home Page.
Once again we need to pause here for the APEX gurus to explain the proper way to do this !

Edit the Home Page and Create a Branch…

In Branch Attributes

Name : pwd_change_br
Branch Point : On Load : Before Header


In Target

Page : the number of the Change Password Page ( 6 in my case)

In Branch Conditions

Condition Type : Exists( SQL query returns at least one row)
In Expression 1, enter the query :

select 1
from sys.dba_users
where username = apex_040200.v('APP_USER')
and expiry_date < trunc(sysdate) + 7

This will return 1 if the password is due to expire within the next 7 days.


and hit Create Branch.

In order to test the branch, I’ve had a bit of a fiddle with the FIXED_DATE parameter [link to post] so that PLUGGER’s password is now due to expire in less than 7 days.

Now, when I login as plugger…


…I go straight to the Change Password Page…



What started off as a fairly short post about Database Authentication Schemes in APEX has grown quite a bit more than I intended.
I believe that the solution to password management, which I have outlined here, is secure.
Obviously, if anyone can spot any flaws in this, I (and anyone reading this), would find it immensly helpful if you could provide reasons/code as to why and how this approach could be exploited.
Whilst the Change Password functionality is something of an overhead in going down the Database Authentication route, the use of database roles, not to mention the RDBMS itself, does mean that this is an approach worth considering when porting older applications to APEX….or maybe it isn’t.
I wonder if there’s a passing Australian who’d like to share their opinion on this ?

Filed under: APEX, Oracle, PL/SQL, SQL Tagged: APEX Database Authentication Scheme, change password procedure, dba_role_privs, dba_users

APEX 503 – Service Unavailable – And you don’t know the APEX_PUBLIC_USER Password

Sun, 2014-12-07 12:46

It’s probably Monday morning. The caffeine from your first cup of coffee has not quite worked it’s way into your system.
The cold sweat running down the back of your neck provides an unpleasant contrast to the warm blast of panicked users as they call up to inform you that the Application is down.
APEX, which has been behaving impeccibly all this time, has suddenly decided to respond to all requests with :

503 – Service Unavailable.

The database is up. The APEX Listener is up. But something else is up. APEX just doesn’t want to play.
Better still, the person who set up the APEX in the first place has long-departed the company. You have no idea how the Apex Listener was configured.

Out of sympathy with your current predicament, what follows is :

  • How to confirm that this problem is related to the APEX_PUBLIC_USER (the most likely cause)
  • A quick and fairly dirty way of getting things back up and running again
  • How to stop this happening again

Note: These steps were tested Oracle Developer Day VM with a 12c database running on Oracle Linux 6.5. In this environment, APEX is configured to run with the APEX Listener.

Confirming the APEX User name

First of all, we want to make sure that APEX is connecting to the database as APEX_PUBLIC_USER. To do this, we need to check the default.xml file.
Assuming you’re on a Linux box :

cd /u01/oracle/apexListener/apex
cat default.xml

If you don’t see an entry for db.username then APEX_PUBLIC_USER is the one that’s being used.
If there is an entry for db.username then that is the name of the database user you need to check in the following steps.
For now, I’ll assume that it’s set to the default.

Incidentally, there will also be an entry for db.password. This will almost certainly be encrypted so is unlikely to be of use to you here.

Confirming the status of the APEX_PUBLIC_USER

The most likely reason for your current troubles is that the APEX_PUBLIC_USER’s database password has expired.
To verify this – and get the information we’ll need to fix it, connect to the database and run the query :

select account_status, profile
from dba_users
where username = 'APEX_PUBLIC_USER'

If the account_status is EXPIRED, then the issue you are facing is that the APEX_PUBLIC_USER is expired and therefore APEX can’t connect to the database.

The other item of interest here is the PROFILE assigned to the user.
We need to check this to make sure that there is no PASSWORD_VERIFY_FUNCTION assigned to the profile. If there is then you need to supply the existing password in order to change it, which is a bit of a problem if you don’t know what it is.
Whilst we’re at it, we need to check whether there is any restriction in place as to the length of time or number of password changes that must take place before a password can be reused.
In my case, APEX_PUBLIC_USER has been assigned the DEFAULT profile.

select resource_name, limit
from dba_profiles
where profile = 'DEFAULT'
and resource_name in 

When I ran this, I was lucky and got :

RESOURCE_NAME                  LIMIT              
------------------------------ --------------------
PASSWORD_REUSE_TIME            UNLIMITED            
PASSWORD_REUSE_MAX             UNLIMITED            

So, there are no restrictions on password reuse for this profile. Neither is there any verify function.

If your APEX_PUBLIC_USER is attached to a profile that has these restrictions, then you’ll want to change this before re-setting the password.
As we’re going to have to assign this user to another profile anyway, we may as well get it out of the way now.

The New Profile for the APEX_PUBLIC_USER

Oracle’s advice for the APEX_PUBLIC_USER is to set the PASSWORD_LIFE_TIME to UNLIMITED.

Whilst it’s only these four parameters we need to set in the profile for us to get out of our current predicament, it’s worth also including a limitation on the maxiumum number of failed login attempts, if only to provide some limited protection against brute-forcing.
In fact, I’ve just decided to use the settings from the DEFAULT profile for the attributes that I don’t need to change :

create profile apex_public limit
    failed_login_attempts 10
    password_life_time unlimited
    password_reuse_time unlimited
    password_reuse_max unlimited
    password_lock_time 1 
    composite_limit unlimited
    sessions_per_user unlimited
    cpu_per_session unlimited
    cpu_per_call unlimited
    logical_reads_per_session unlimited
    logical_reads_per_call unlimited
    idle_time unlimited
    connect_time unlimited
    private_sga unlimited

As we don’t specify a PASSWORD_VERIFY_FUNCTION, none is assigned to the new profile.

NOTE – it’s best to check the settings in your own default profile as they may well differ from those listed here.

Next, we assign this profile to APEX_PUBLIC_USER…

alter user apex_public_user profile apex_public

The next step is to reset the APEX_PUBLIC_USER password, which is the only way to unexpire the user.

No password, no problem

Remember, in this scenario, we don’t know the current password for APEX_PUBLIC_USER. We don’t want to reset the password to just anything because we’re not sure how to set the password in the DAD used by the Apex Listener.

First of all, we need to get the password hash for the current password. To do this :

select password
from sys.user$
where name = 'APEX_PUBLIC_USER'

You’ll get back a hex string – let’s say something like ‘DF37145AF23CCA4′.

Next step is to re-set the APEX_PUBLIC_USER password :

alter user apex_public_user identified by sometemporarypassword

We now immediately set it back to it’s original value using IDENTIFIED BY VALUES :

alter user apex_public_user identified by values 'DF37145AF23CCA4' 

At this point, APEX should be back up and running.

Once the dust settles…

Whilst your APEX installation may now be back up and running, you now have a database user for which the password never changes.
Although the APEX_PUBLIC_USER has only limited system and table privilges, it also has access to any database objects that are available to PUBLIC.
Whilst this is in-line with Oracle’s currently documented recommendations, you may consider that this is a situation that you want to address from a security perspective.
If there is a sensible way of changing the APEX_PUBLIC_USER password without breaking anything, then you may consider it preferable to simply setup some kind of reminder mechanism so that you know when the password is due to expire and can change it ahead of time.
You would then be able to set the password to expire as normal.
If you’re wondering why I’m being a bit vague here, it’s simply because I don’t currently know of a sensible way of doing this.
If you do, it would be really helpful if you could let me know :)

Filed under: APEX, Oracle, SQL Tagged: APEX 503 Unavailable, create profile, dba_profiles, dba_users.account_status, failed_login_attempts, identified by values, password_reuse_max, password_reuse_time, password_verify_function