Wednesday, February 11, 2015

APEX - Changing the colour of a row based on the value

I'm writing an app that shows the status of various database things, and I want the colour of the row to be red when there is an issue.

The solution is in an APEX forum post but it took some finding so I thought I'd put it here for my future reference.

The forum post is here : https://community.oracle.com/thread/2602690

by Jari.

In my app, I have a table (dbstatus) that is fed from a cron job that connects to the database and runs a script:

select instance_name,startup_time from v$instance;

The table just has the database name and the startup time as columns.

If the database is down and this fails, the startup_time will be null in the table, so that's what I check on.

 The query in the report looks like this:

select dbname,start_time,
case when start_time is null
then 'red'
end as fcolor
 from dbstatus;



Fairly straight forward.

You now need to edit the report columns. You will see a new column has appeared called FCOLOR - uncheck the 'SHOW' box so that it is hidden.

Click on the edit icon for the dbname column, and scroll down to the "Column Formatting section and enter this into the 'HTML Expression' box:




Do the same for the 'Start Time' column:





Now if the start time is null, the database name will show up in red:



 To make the others show up in green is a simple change to the SQL - just add the "else 'green'" line:

select dbname,start_time,
case when start_time is null
then 'red'
else 'green'
end as fcolor
 from dbstatus;




I think this is pretty neat.







Monday, January 5, 2015

Changing session settings automatically for a user when they login

We needed to set some parameters for specific users when they logged in since the client didn't want them changed at database level.

This was achieved by using login triggers to run commands when the specific user logged in.

Hopefully this won't need much explanation:

create or replace TRIGGER set_params_at_login
after logon on database

DECLARE
  lv_username VARCHAR2(100) := NULL;
begin
  lv_username := UPPER(SUBSTR(SYS_CONTEXT('USERENV', 'SESSION_USER'),
                              1,
                              100));

   /* Oracle Parameters for USER1 to reduce CPU */

   /* USER1 */
    if NVL(lv_username, 'X') IN ('USER1' ) then
      execute immediate 'alter session set optimizer_features_enable=''10.2.0.5''';
      execute immediate 'alter session set "_unnest_subquery"=true';
      execute immediate 'alter session set query_rewrite_enabled=false';
      execute immediate 'alter session set optimizer_index_cost_adj=100';
      execute immediate 'alter session set optimizer_index_caching=50';
    end if;

  exception
  when others then
    null;
end;