Back to posting...

It's been a while.. to say the least.

But I'm back to posting blog entries on my personal blog so be prepared to see some updates.

APEX-Plugin Site

With the release of apex 4.0 there has also a new site that contains the latest plug-in for this amazing framework.

The url is incredibly easy : http://www.apex-plugin.com/

Enjoy.

Oracle APEX 4.0

Finally, the next generation development framework has been launched!!

http://www.oracle.com/technology/products/database/application_express/index.html

Can't wait to get started and get my fingers dirty with this excellent product.

Hope you folks are also filled with joy.

APEX 4.0 Early Adopters

For those who have been living under a rock for the past couple of days/weeks the APEX 4.0 Early Adopters website has been launched..
Join me in yelling "WOHOOOO".

To be honest I'm very happy of all the new features that have been added, a very good webservice support has especially taken my attention. This will allow for a much beter integration of the APEX framework into the SOA priciples of design. I hope :)

APEX 4.0 EA can be found at : http://tryapexnow.com

Enjoy.

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.

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.

Disable APEX applications after installation

When exporting an application the export file contains exactly the same configuration as your development platform. Thank god. However when moving an application from development to test or production environments you might want some features disabled.

Recently I created a script that after deployment disables some development features.
Here is a short script that disables the following features from all running applications

• disable DEBUG mode.


• disable developer toolbar + edit links.


• sets application alias to application id.


• places the application in RUN only mode.

DECLARE
      CURSOR c_apps
      IS
        select application_id as app_id
        from apex_applications;

      l_flow_status VARCHAR2(100) := 'AVAILABLE';
      l_debug       VARCHAR2(10) := '0'; -- 0 = NO, 1 = YES
      l_app_alias   VARCHAR2(10);
BEGIN
      -- IDENTIFY FLOW_APPLICAITON SECURITY GROUP
      wwv_flow_api.set_security_group_id(p_security_group_id=>9999999999999999);

      FOR r_app IN c_apps
      LOOP
        l_app_alias := to_char(r_app.app_id);
        -- SET APPLICATION
        wwv_flow.g_flow_id := r_app.app_id;
        wwv_flow_api.g_id_offset := 0;

        -- DISABLE DEBUGGING
        wwv_flow_api.set_enable_app_debugging(P_FLOW_ID => wwv_flow.g_flow_id,
                                              P_DEBUGGING  => l_debug);
        -- SET APPLICATION TO ONLY AVAILABLE
        wwv_flow_api.set_flow_status(P_FLOW_ID => wwv_flow.g_flow_id,
                                      P_FLOW_STATUS => l_flow_status);                                 
        -- REWRITE APPLICATION ALIAS
        wwv_flow_api.SET_APPLICATION_ALIAS(P_FLOW_ID => wwv_flow.g_flow_id,
                                           P_ALIAS => l_app_alias);
        -- SET APPLICATION RUN ONLY
        wwv_flow_api.SET_BUILD_STATUS_RUN_ONLY(P_FLOW_ID => wwv_flow.g_flow_id);
      END LOOP;
      COMMIT;
END;
/