Monday, 21 September 2009

Installing Oracle APEX in run-time only mode.

I recently learned it is possible to install Oracle Application Express on two different ways.

  1. Developer install : The normal install of Oracle APEX, including APEX administration console and all the developer features.

  2. Run-time install : Completely stripped version of Oracle APEX without any developer features or administration console.


Usually I install APEX using the first option. However with all the new stuff I've learned over the last couple of months I thought.. Why don't I give the run-time only install a try ? Perhaps it's not a bad idea to use this type of configuration for production systems ?

Well today I tried it. And it turned out to be not that difficult.

APEX installation to Oracle XE

  1. This is basically the most important step. Normally I use @apexins.sql to install. But for the run-time version I use a different sql file.
    @apxrtins.sql SYSAUX SYSAUX TEMP /i/


  2. Then change the default password
    @apxchpwd.sql


  3. This is an optional step : Install the necessary images. This isn't mandatory but if you plan on using any theme I suggest you install this.
    @apxldimg.sql /opt/apex


  4. Then change the password one more time
    @apxchpwd.sql



Now after this our APEX run-time version is installed. I pointed my browser to http://localhost:8080/apex/
And all I saw... was an error. Err-1014 Application not found

Meaning absolutely nothing was installed.

Now I wanted to test a the installation of a new application. This proved to be a bit more difficult, because every change to the APEX administration needs to be done using a PL/SQL command, and not the console (which doesn't exist). For more information concerning this API : http://download.oracle.com/docs/cd/E10513_01/doc/apirefs.310/e12855/apex_instance.htm

Using the following steps I installed the Oracle Event Packaged application

  1. Create demo user (add some grants offcourse : CONNECT,RESOURCE, etc..)
    CREATE USER demo IDENTIFIED BY demopwd;


  2. Create a new workspace (with the matching workspace id from the SQL file)
    BEGIN

          APEX_INSTANCE_ADMIN.ADD_WORKSPACE(269990401159459463,'WKS_DEMO','DEMO','');

    END;


  3. Load the SQL file. (I performed this as SYSDBA, however it is a lot smarted to create a specific APEX deployment user. Using the APEX_ADMINISTRATOR_ROLE privilege)
    @events_installer_1.0.sql



As by magic the application is deployed an available at the url http://localhost:8080/apex/f?p=180 without having used the administration console.

Well I hope you folks found it useful and there are a lot of extra functions available using the ADMIN API so check em out to make your APEX environment a bit more secure.

Note : This was just a small selection of the features that are "disabled" in the run-time mode. For more information read the APEX_INSTANCE_ADMIN documentation or ask on the Oracle forum.

Sunday, 6 September 2009

Perform APEX administration tasks straight from PL/SQL

Recently I had to find a way to create new APEX users without having an admin manually creating them or having to create some sort of request page.
Now the easy way was to let the users do that themselves... Small hick-up though, you need to be an APEX Workspace administrator to create new end users. Meaning that 'normal' users had to be admin before being allowed to create new users.

Now in order to bypass this I just created a small batch that checks a simple table every now and then to see if new usernames where requested. A special administrator then automatically creates this user.

However in order to perform this task, I had to create a piece of PL/SQL code that would be able to run, connect to APEX without being integrated in a valid APEX session (from browser).  I used (a more complex version of) the following code to set the correct workspace, log in as the JOB_MANAGER APEX ADMIN and create the new user.

declare
  l_workspace VARCHAR2 := 'WORKSPACE';
  l_wks_id NUMBER;
begin
  -- find workspace security id
  l_wks_id := apex_util.find_security_group_id (p_workspace => l_workspace);
  dbms_output.put_line('Workspace id : ' || l_workspace);
  -- set workspace security id
  wwv_flow_api.set_security_group_id (l_wks_id);
  -- login user
  apex_custom_auth.login (p_uname       => 'JOB_MANAGER',
                          p_password    => 'welcome1',
                          p_session_id  => V('APP_SESSION'));
  -- create user
  apex_util.create_user (p_user_name       => 'username',
                         p_first_name      => 'first_name',
                         p_last_name       => 'last_name',
                         p_description     => 'description',
                         p_web_password    => 'welcome1',
                         p_email_address   => 'first.last@mail.com');
end;
/


A very short post but hope you guys find it useful.