APEX - a better way of arranging regions

I've always struggled with getting the regions to look decent, and since all of my Apps have been for my personal use or internal to the company I haven't worried too much about it.

However, I'm now writing one for a client so the look is important.

I have a couple of Gauge charts that I want to put side-by-side, but using the APEX region settings just couldn't get it to work.

Then I discovered the "Region Attributes" field and the "float" attribute.

To get the charts to sit side by side:

Create an html region, in my example I call it "Sessions" and leave it blank.

Edit the region, and in the field near the bottom called "Region Attributes" put this:

style="width:700px"

(or however wide you want it).

Next. create a new chart region, I called it "Total Active Sessions", and make the "Sessions" region the parent.

Make the Template "No Template"

Make the "Start New Row=Yes" in Grid Layout

Scroll down to the "Region Attributes" field and enter

style="float:left; width: 100px"

Create the chart, I used a gauge, this was the SQL:

select count(*) value,150 max_value from v$session where status='ACTIVE' and username is not null

Create the second region, again make the parent "Sessions" and No Template,
Start New Row is "NO", Column Span is Automatic and New Column is Yes.

Scroll down to Region Attributes and enter

style="float:left; width: 100px; clear:none"

The second chart SQL is virtually the same as the first:

select count(*) value,150 max_value from v$session where status='INACTIVE' and username is not null


The charts should now sit next to each other. You can fiddle with the values to shift the charts around.

New Tablespace size script

I found this on Toadworld:
http://www.toadworld.com/platforms/oracle/w/wiki/4887.script-to-report-tablespacedatafile-space-utilization.aspx

There are lots of useful scripts there: http://www.toadworld.com/platforms/oracle/w/wiki/4861.instant-scripts.aspx


I've changed a couple of the column formats from the original.

REM LOCATION:   Object Management\Tablespaces and DataFiles\Reports
REM FUNCTION:   Generate a report of Tablespace Space Availability
REM             including autoextend related space availability.
REM
REM TESTED ON:  10.2.0.3 and 11.1.0.6
REM PLATFORM:   non-specific
REM REQUIRES:   dba_tablespaces, dba_data_files
REM
REM  This is a part of the Knowledge Xpert for Oracle Administration library.
REM  Copyright (C) 2008 Quest Software
REM  All rights reserved.
REM
REM ******************** Knowledge Xpert for Oracle Administration ********************
SET lines 160 pages 66 feedback off
COLUMN tablespace_name        format a20             heading 'Tablespace|(TBS)|Name'
COLUMN autoextensible         format a6              heading 'Can|Auto|Extend'
COLUMN files_in_tablespace    format 999             heading 'Files|In|TBS'
COLUMN total_tablespace_space format 99,999,999,999,999 heading 'Total|Current|TBS|Space'
COLUMN total_used_space       format 99,999,999,999,999 heading 'Total|Current|Used|Space'
COLUMN total_tablespace_free_space format 99,999,999,999,999 heading 'Total|Current|Free|Space'
COLUMN total_used_pct              format 999.99      heading 'Total|Current|Used|PCT'
COLUMN total_free_pct              format 999.99      heading 'Total|Current|Free|PCT'
COLUMN max_size_of_tablespace      format 99,999,999,999,999 heading 'TBS|Max|Size'
COLUMN total_auto_used_pct         format 999.99      heading 'Total|Max|Used|PCT'
COLUMN total_auto_free_pct         format 999.99      heading 'Total|Max|Free|PCT'

TTITLE left _date center Tablespace Space Utilization Status Report skip 2

WITH tbs_auto AS
     (SELECT DISTINCT tablespace_name, autoextensible
                 FROM dba_data_files
                WHERE autoextensible = 'YES'),
     files AS
     (SELECT   tablespace_name, COUNT (*) tbs_files,
               SUM (BYTES) total_tbs_bytes
          FROM dba_data_files
      GROUP BY tablespace_name),
     fragments AS
     (SELECT   tablespace_name, COUNT (*) tbs_fragments,
               SUM (BYTES) total_tbs_free_bytes,
               MAX (BYTES) max_free_chunk_bytes
          FROM dba_free_space
      GROUP BY tablespace_name),
     AUTOEXTEND AS
     (SELECT   tablespace_name, SUM (size_to_grow) total_growth_tbs
          FROM (SELECT   tablespace_name, SUM (maxbytes) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'YES'
                GROUP BY tablespace_name
                UNION
                SELECT   tablespace_name, SUM (BYTES) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'NO'
                GROUP BY tablespace_name)
      GROUP BY tablespace_name)
SELECT a.tablespace_name,
       CASE tbs_auto.autoextensible
          WHEN 'YES'
             THEN 'YES'
          ELSE 'NO'
       END AS autoextensible,
       files.tbs_files files_in_tablespace,
       files.total_tbs_bytes total_tablespace_space,
       (files.total_tbs_bytes - fragments.total_tbs_free_bytes
       ) total_used_space,
       fragments.total_tbs_free_bytes total_tablespace_free_space,
       (  (  (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
           / files.total_tbs_bytes
          )
        * 100
       ) total_used_pct,
       ((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
       ) total_free_pct,
       AUTOEXTEND.total_growth_tbs max_size_of_tablespace,
       (  (  (  AUTOEXTEND.total_growth_tbs
              - (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes
                )
             )
           / AUTOEXTEND.total_growth_tbs
          )
        * 100
       ) total_auto_used_pct,
       (  (  (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
           / AUTOEXTEND.total_growth_tbs
          )
        * 100
       ) total_auto_free_pct
  FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
 WHERE a.tablespace_name = files.tablespace_name
   AND a.tablespace_name = fragments.tablespace_name
   AND a.tablespace_name = AUTOEXTEND.tablespace_name
   AND a.tablespace_name = tbs_auto.tablespace_name(+);

Using a Date Picker in APEX

Adding a Date Picker to a page

Gee, this took me ages to figure out. 

I wanted a date picker on a home page that could have a start date and an end date selected, and then open a page with the information for just between those dates. 

I messed around with format masks, application globalisation settings, to_date, to_char etc and it drove me mad. Every time I tried to open the page, it said "no data found".

I still don’t know if this is possible – passing a date from one page to another, so the solution is to not use the home page, but direct to a new page, then enter the dates there, then have a ‘go’ button that submits the page with the dates. Phew.

Create a new page, select a Report, and enter the query something like this

 

select distinct user_name,sum(billable_hours) from client_data

where entry_date between :P17_START_DATE and :P17_END_DATE

and client_name not like '%one%'

and client_name not like '%MGA%'

and client_name is not null

group by user_name

order by 2 desc

Next, create the 2 items - P17_START_DATE and P17_END_DATE as Date Picker Items:

 

You can let everything default and just click "Next", then "Create Item" .

If you try and run the page, you'll see this:

 You need to add a button - Call it something like 'Go', and all you need to do is select 'Submit" for the action:

Save this, run the page, enter some dates using the calendar icons:

 

Then click the "Go" button:

 You can change the "No Data Found" to something like "Pick a Start and End Date Using the Calendar Icons" to make it more intuitive.

On the Home page, just put a button called "Date Range" and have it direct to this page (17 in this case).