Tuesday, 25 August 2009

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

Friday, 22 May 2009

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.

Sunday, 29 March 2009

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

Tuesday, 24 March 2009

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