Duncan Mein's Blog

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_work...

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 th...

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 rel...

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