Generate Days in Month (PIPELINED Functions)

This cool example is not one I can take the credit for but since it is used pretty heavily in our organisation, I thought I would share it as it's not only pretty cool buy also demonstrates how useful Oracle Pipelined functions can be.

In essence a Pipeline table function (introduced in 9i) allow you use a PL/SQL function as the source of a query rather than a physical table. This is really useful in our case to generate all the days in a calendar month via PL/SQL and query them back within our application.

To see this in operation, simply create the following objects:

CREATE OR REPLACE TYPE TABLE_OF_DATES IS TABLE OF DATE;

CREATE OR REPLACE FUNCTION GET_DAYS_IN_MONTH 
(
 pv_start_date_i IN DATE
) 
 RETURN TABLE_OF_DATES PIPELINED 
IS
 lv_working_date DATE;
 lv_days_in_month NUMBER;
 lv_cnt NUMBER;
BEGIN
   
  lv_working_date := TO_DATE(TO_CHAR(pv_start_date_i, 'RRRRMM') || '01', 'RRRRMMDD'); 
  lv_days_in_month := TRUNC(LAST_DAY(lv_working_date)) - TRUNC(lv_working_date);
    
  PIPE ROW(lv_working_date);
  
  FOR lv_cnt IN 1..lv_days_in_month
  LOOP
    lv_working_date := lv_working_date + 1;
    PIPE ROW (lv_working_date);
  END LOOP;
  
  RETURN;

END GET_DAYS_IN_MONTH;
/

Once your objects are successfully complied, you can generate all the days in a month by executing the following query:

SELECT column_value the_date
     , TO_CHAR(column_value, 'DAY') the_day 
  FROM TABLE (get_days_in_month(sysdate));

THE_DATE        THE_DAY
------------------------
01-AUG-09 SATURDAY 
02-AUG-09 SUNDAY   
03-AUG-09 MONDAY   
04-AUG-09 TUESDAY  
05-AUG-09 WEDNESDAY
06-AUG-09 THURSDAY 
07-AUG-09 FRIDAY   
08-AUG-09 SATURDAY 
09-AUG-09 SUNDAY   
10-AUG-09 MONDAY   
11-AUG-09 TUESDAY  
12-AUG-09 WEDNESDAY
13-AUG-09 THURSDAY 
14-AUG-09 FRIDAY   
15-AUG-09 SATURDAY 
16-AUG-09 SUNDAY   
17-AUG-09 MONDAY   
18-AUG-09 TUESDAY  
19-AUG-09 WEDNESDAY
20-AUG-09 THURSDAY 
21-AUG-09 FRIDAY   
22-AUG-09 SATURDAY 
23-AUG-09 SUNDAY   
24-AUG-09 MONDAY   
25-AUG-09 TUESDAY  
26-AUG-09 WEDNESDAY
27-AUG-09 THURSDAY 
28-AUG-09 FRIDAY   
29-AUG-09 SATURDAY 
30-AUG-09 SUNDAY   
31-AUG-09 MONDAY

I hope someone finds this example as useful as we do. The credits go to Simon Hunt on this one as it was "borrowed" from one of his apps. Since I offered to buy him a beer he has promised not to make too big a deal out it :)

As always, you can read up on this topic here

Extolling the Virtues of Apple Products (MacBook's, Time Capsule et al)

I have long been a fan of Apple products ever since I took the plunge and bough a MacBook Pro about 3 years ago. More recently my girlfriend decided to leave the world of Windows behind and bought herself one of the new MacBook's (which i might add is not massively dissimilar to the specification of my 3 year old MBP). Not only are the build quality of their products very impressive but you hear several anecdotal tails of things that "Just Work" on Mac's. It is this point that I want to pursue during the rest of this post.

1. I added a wireless HP Printer (HP Photoshop C6180) to my network at home and started to install the drivers supplied by HP on a Thinkpad Windows XP laptop. The install took about 30 minuets and added around 400MB of binaries to the Program Files directory. Only after this laborious install could I see the printer as a Network Printer. I might add that none of the remote programs such as Scan or Copy have ever worked even though I have updated the drivers to the latest ones provided by HP.

Compare this to the setup on my MacBook Pro which involved opening System Preferences, clicking on Print & Fax. My printer was automatically detected and 10 seconds after clicking on the Add button, I was ready to go. (Note that no drivers were needed to perform the most basic of actions.... PRINTING). I did however have to install the software to carry out remote Scan / Copy operations but at least they work after installation.

2. After my Linksys router died (WRT54G) I attempted to replace it with another Linksys (WRT160N) which was probably a mistake as I have had nothing but trouble with the original Linksys. Anyway thinking that they must have moved on and stability MUST be greatly improved on the new models, I handed over the £70 at PC World for the WRT160N.

My expectation at this stage was that I could plug the new router in, plug my Cable Broadband into the appropriately named "Internet" port and hey presto wireless broadband. Alas, this was a far cry from what Linksys expect you to go through during router setup. Without wanting to labour the point, 30 minutes and 2 configuration wizards later a report popped up informing me that the router was successfully set up but no Internet connection could be detected.

This battle has been ongoing until I bought a 500GB Time Capsule.

It's setup involved plugging it in, hooking the Broadband into the back and plugging an ethernet cable into my MBP. An automated wizard fired up, updated the Time Capsule firmware and configured the Wireless with WPA2 encryption etc in about 2 minuets. Furthermore, all machines were wirelesslesy connected within 5 minutes and are now fully working as expected.

This is the level of service I have become accustomed to with Apple products and I guess I am pretty spoilt but when you pay the money you do for their kit, you expect it to work right out of the box.... which 99 times out of 100 it does. Bottom line is that Apple kit does cost more but the quality of components, build and functionality is second to none.

And Finally......

I rather embarrassingly spilt a cup of boiling hot tea over my MBP about a year ago. This shorted it out and I though it was a goner. When I got it home, I removed the keyboard (which seemed to collect 99% of the liquid) and washed it under some cold water and left it to air dry. I dried the Motherboard with a can of compressed air and re-assembled it. Fearing the worst, I pressed the power button and to my amazement, the machine powered back up at the 1st time of asking and everything (and I mean everything) was fully functional. I can't begin to tell you have relieved I was that I had not shafted a £2500 laptop with a cup of tea. This machine is still in full use today (I am blogging on it right now) and I am thinking of writing to Apple and imploring them to change their company strap line to.....

"Apple, Products that just work........ even when soaked in Tea"

:)

VMWare ESXi Hypervisor

Server virtualisation is something I have been using for a few years now both at work and at home. I mainly use VMWare Fusion on my MacBook Pro and VMWare Workstation / Player on an old Windows Laptop. For everyday tasks these products are amazing as i can run an XP VM on my MacBook Pro for all those times I need to open MS Project or run TOAD.

On my server at home, I didn't want to install a host OS and then install VMWare Workstation / Server to host several Linux VM's. Instead I explored the option of a "Bare Metal" hypervisor from VMWare (ESXi 3.5 Update 3). A hypervisor is a very small linux kernel that runs natively against your servers hardware without the burden of having to install a host OS. From here you can create and manage all your VM's remotely using the VMWare Client tool.

For more information in ESXi, check out VMWare's website here

Reading the documentation suggested that ESXi was very particular about the hardware it supports and so began the quest to build a "White Box" ESXi server at home. Whilst this is not supported by VMWare, I wanted to share the process and components utilised in case anyone else was thinking of building there own ESXi Server.

Step 1. Download the ESXi 3.5 ISO from the VMWare Website (You will need to register for an account)

Step 3. Create a Bootable USB Stick (yes you can boot the Hypervisor from a USB stick if your Motherboard supports it) by following this concise guide

Step 3. Plug your USB stick into your server and configure the IP Address, DNS Server, Hostname and Gateway.

Step 4. Open a Browser window on a client machine and navigate to: http://ip address to download the VMWare Client tool.

Step 5. Enjoy using ESXi

My Server Configuration:

CPU: Inter Core i7 920
MOBO: Gigabyte GA-EX58-UD5
Memory: 12GB of Corsair DDR3 XMS3 INTEL I7 PC10666 1333MHZ (3X2GB)
SATA Controller: Sweex PU102
NIC: 3Com 3c90x

The following sites list loads of compatible hardware with notes and issues encountered:

http://ultimatewhitebox.com/index.php

http://www.vm-help.com/esx/esx3.5/Whiteboxes_SATA_Controllers_for_ESX_3.5_3i.htm

With the setup outlined above, I can run 5 Linux machines running Oracle Database, Application Server, APEX and OBIEE without any issue.

If this is something you are interested in evaluating, i can recommend spending a few hundred pounds on the components as its beats spending thousands on a supported Server from HP.

Sum to Parent Nodes in Hierarchy Queries: CONNECT_BY_ROOT

In one of our Applicaitons, the table DEPT contains a self referncing join (Pigs Ear) as it models our organsational department hierarchy.

For example:

CREATE TABLE DEPT
(
 DEPT_ID NUMBER NOT NULL
,PARENT_ID NUMBER
,DEPT_NAME VARCHAR2 (100) NOT NULL
,EMPLOYEES NUMBER NOT NULL
,CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID)
,CONSTRAINT DEPT_FK01 FOREIGN KEY (PARENT_ID) 
 REFERENCES DEPT (DEPT_ID)
);

INSERT INTO DEPT VALUES (1,NULL,'IT', 100);
INSERT INTO DEPT VALUES (2,1,'DEVELOPMENT', 12);
INSERT INTO DEPT VALUES (3,1,'SUPPORT', 15);
INSERT INTO DEPT VALUES (4,1,'TEST', 25);

INSERT INTO DEPT VALUES (5,2,'PL/SQL', 2);
INSERT INTO DEPT VALUES (6,2,'Java', 1);
INSERT INTO DEPT VALUES (7,2,'SQL', 11);
INSERT INTO DEPT VALUES (8,2,'C++', 3);

INSERT INTO DEPT VALUES (9,4,'Functional', 3);
INSERT INTO DEPT VALUES (10,4,'Non Functional', 5);

COMMIT;

A quick tree walk using CONNECT BY PRIOR shows you the Parent / Child relationships between all departments and the number of employees in each department:

SELECT rpad( ' ', 1*level, ' ' ) || dept_name dept_name
      ,employees
  FROM dept
 START WITH parent_id is null
CONNECT BY PRIOR dept_id = parent_id;

DEPT_NAME             EMPLOYEES
-------------------- ----------
 IT                         100
  DEVELOPMENT                12
   PL/SQL                     2
   Java                       1
   SQL                       11
   C++                        3
  SUPPORT                    15
  TEST                       25
   Functional                 3
   Non Functional             5

We had a requirment to work out the total number of employees at each parent level in the Organisational Hierarchy. For example, the report sum up all employees in the parent node and all its children.

Taking the department TEST as an example, the report should sum the figures 25 (employees in the TEST department), 3 and 5 (employees in the Functional / Non Functional child departments) to give a figure of 33.

This can easily be achieved by using CONNECT_BY_ROOT. Straight from the Oracle Documentation:

"CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.

Restriction on CONNECT_BY_ROOT You cannot specify this operator in the START WITH condition or the CONNECT BY condition."

To meet our requirement, CONNECT_BY_ROOT was utilised as follows:

select dept_name, employees, tot_employees 
from (select 
           employees,
           dept_name,
           level lev,
           sum(employees) over(partition by connect_by_root
                                           (dept_id)
                               ) tot_employees
      from dept
    connect by prior dept_id = parent_id)
    where lev=1;

DEPT_NAME             EMPLOYEES TOT_EMPLOYEES
-------------------- ---------- -------------
IT                          100           177
DEVELOPMENT                  12            29
SUPPORT                      15            15
TEST                         25            33
PL/SQL                        2             2
Java                          1             1
SQL                          11            11
C++                           3             3
Functional                    3             3
Non Functional                5             5

Check All / Uncheck All Checkbox

There is a really cool JavaScript function in Apex called: $f_CheckFirstColumn that allows you to Check / Uncheck all checkboxes that exist in the 1st column position of a Tabular Form / Report.

To implement this, all you need do is add the following HTML to the Column Heading of the 1st Column in your Tabular Form (i.e. the Checkbox Column):

<input type="Checkbox" onclick="$f_CheckFirstColumn(this)">

Check out an example here

Web Cache Compression and MOD_GZIP

Some of my colleagues are working on a project where bandwidth is massively limited (64k). One suggestion to increase application response time was to use MOD_GZIP (an open source compressor extension to Apache) to compress the outbound HTTP traffic. The only drawback is that MOD_GZIP is not supported by Oracle.

Since we are using Oracle Application Server, Web Cache achieves exactly the same by simply adding a compression rule to Web Cache for the URL Regular Expression /pls/apex/.*$

We noticed that without any compression of the HTTP outbound traffic, our test page took 30 seconds to fully render on a 64k link. Turning on compression reduced the rendering time to 7 seconds. Very impressive.

Navigating through an application with compression turned on was noticeably quicker than one without compression.

To test if your outbound HTTP traffic is compressed, I would grab the Live HTTP Headers extension to Firefox and you are looking for a line like: Content-Encoding: gzip in the outbound response.

I configured both APEX and Discoverer Viewer to use compression by following the metalink article: 452837.1

Reset the APEX internal password

I noticed in one of the comments of Dimitri Gielis articles that Jornica pointed out a script called apxXEpwd.sql

I ran this as the SYS user and sure enough, it allows you to reset the ADMIN password for the internal workspace.

This script can be found in the root apex directory that you download from OTN.

Very useful when you forget what that password is!