Duncan Mein's Blog

We're Hiring

2011-12-07T06:04:00.000-08:00

We are looking for an APEX developer for an initial 3 month contract with definite scope for long term extension for a role in Hampshire (UK).

Candidates must be SC cleared or willing to undergo clearance to work on a UK MoD site.

Any interested parties, please send me an up to date copy of your CV with availability and rate to: duncanmein@gmail.com

Native String Aggregation in 11gR2

2011-06-14T01:07:00.000-07:00

A fairly recent requirement meant that we had to send a bulk email to all users of each department from within our APEX application. We have 5000 records in our users table and the last thing we wanted to do was send 5000 distinct emails (one email per user) for both performance and to be kind on the mail queue / server.

In essence, I wanted to to perform a type of string aggregation where I could group by department and produce a comma delimited sting of all email address of users within that department. With a firm understanding of the requirement, so began the hunt for a solution. Depending on what version of the database you are running, the desired result can be achieved in a couple of ways.

Firstly, the example objects.

CREATE TABLE app_user
(id NUMBER
,dept VARCHAR2 (255)
,username VARCHAR2(255)
,email VARCHAR2(255)
);

INSERT INTO app_user (id, dept, username, email)
VALUES (1,'IT','FRED','fred@mycompany.com');

INSERT INTO app_user (id, dept, username, email)
VALUES (2,'IT','JOE','joe@mycompany.com');

INSERT INTO app_user (id, dept, username, email)
VALUES (3,'SALES','GILL','gill@mycompany.com');

INSERT INTO app_user (id, dept, username, email)
VALUES (4,'HR','EMILY','emily@mycompany.com');

INSERT INTO app_user (id, dept, username, email)
VALUES (5,'HR','BILL','bill@mycompany.com');

INSERT INTO app_user (id, dept, username, email)
VALUES (6,'HR','GUS','gus@mycompany.com');

COMMIT;

If you are using 11gR2, you can expose the new LISTAGG function as follows to perform your string aggregation natively:


SELECT dept
    ,LISTAGG(email,',') WITHIN GROUP (ORDER BY dept) email_list
FROM app_user
GROUP BY dept;

DEPT     EMAIL_LIST
-----------------------------------------------------------------
HR       emily@mycompany.com,bill@mycompany.com,gus@mycompany.com
IT       fred@mycompany.com,joe@mycompany.com
SALES    gill@mycompany.com

If running 11g or earlier, you can achieve the same result using XMLAGG as follows:


  SELECT au.dept
        ,LTRIM
         (EXTRACT
          (XMLAGG
           (XMLELEMENT
            ("EMAIL",',' || email)),'/EMAIL/text()'), ','
        ) email_list
    FROM app_user au
GROUP BY au.dept;

DEPT     EMAIL_LIST
-----------------------------------------------------------------
HR       emily@mycompany.com,bill@mycompany.com,gus@mycompany.com
IT       fred@mycompany.com,joe@mycompany.com
SALES    gill@mycompany.com

The introduction of native string aggregation into 11gR2 is a real bonus and a function that has already proved to have had huge utility within our applications.

A Right Pig's Ear of a Circular Reference

2011-06-08T14:31:00.000-07:00

If you have ever used a self referencing table within Oracle to store hierarchical data (e.g. an organisations structure), you will have undoubtedly used CONNECT BY PRIOR to build your results tree. This is something we use on pretty much every project as the organisation is very hierarchy based.

Recently, the support cell sent the details of a recent call they received asking me to take a look. Looking down the call, I noticed that the following Oracle Error Message was logged:

"ORA-01436: CONNECT BY loop in user data"

A quick look at the explanation of -01436 and it was clear that there was a circular reference in the organisation table i.e. ORG_UNIT1 was the PARENT of ORG_UNIT2 and ORG_UNIT2 was the PARENT of ORG_UNIT1. In this example, both ORG_UNITS were the child and parent of each other. Clearly this was an issue which was quickly resolved by the addition of a application and server side validation to prevent this from re-occurring.

The outcome of this fix was a useful script that I keep to identify if there are any circular references within a self referencing table. The example below shows this script in action:

CREATE TABLE ORGANISATIONS (ORG_ID NUMBER
, PARENT_ORG_ID NUMBER
, NAME VARCHAR2(100));

INSERT INTO ORGANISATIONS VALUES (1, NULL, 'HQ');
INSERT INTO ORGANISATIONS VALUES (2, 1, 'SALES');
INSERT INTO ORGANISATIONS VALUES (3, 1, 'RESEARCH');
INSERT INTO ORGANISATIONS VALUES (4, 1, 'IT');
INSERT INTO ORGANISATIONS VALUES (5, 2, 'EUROPE');
INSERT INTO ORGANISATIONS VALUES (6, 2, 'ASIA');
INSERT INTO ORGANISATIONS VALUES (7, 2, 'AMERICAS');

COMMIT;

A quick tree walk query shows the visual representation of the hierarchy with no errors:

SELECT LPAD ('*', LEVEL, '*') || name tree
      ,LEVEL lev
    FROM organisations
START WITH org_id = 1
CONNECT BY PRIOR org_id = parent_org_id;

TREE           LEV
---------------------
*HQ            1
**SALES        2
***EUROPE      3
***ASIA        3
***AMERICAS    3
**RESEARCH     2
**IT           2

Now lets create a circular reference so that EUROPE is the parent of SALES:

UPDATE ORGANISATIONS SET parent_org_id = 5 WHERE NAME = 'SALES';
COMMIT;

Re-running the query from the very top of the tree completes but gives an incorrect
and incomplete result set:

TREE         LEV
---------------------
*HQ          1
**RESEARCH   2
**IT         2

If you running Oracle Database 9i and backwards, this Experts Exchange article provides a nice procedural solution and a quick mod to the PL/SQL gave me exactly the information I needed:

SET serveroutput on size 20000

DECLARE
 l_n   NUMBER;
BEGIN
 FOR rec IN (SELECT org_id FROM organisations)
 LOOP
    BEGIN
       SELECT COUNT ( * )
         INTO l_n
         FROM (    SELECT LPAD ('*', LEVEL, '*') || name tree
                 , LEVEL lev
                     FROM organisations
               START WITH org_id = rec.org_id
               CONNECT BY PRIOR org_id = parent_org_id);
    EXCEPTION
       WHEN OTHERS
       THEN
          IF SQLCODE = -1436
          THEN
             DBMS_OUTPUT.put_line 
            (
              rec.org_id 
              || ' is part of a Circular Reference'
            );
          END IF;
    END;
 END LOOP;
END;
/

As Buzz Killington pointed out in the comments section, Oracle Database 10g onwards introduces CONNECT BY NOCYCLE which will instruct Oracle to return rows even if it is involved in a self referencing loop. When used with the CONNECT_BY_ISCYCLE pseudocolumn, you can easily identify erroneous relationships via SQL without the need to switch to PL/SQL. An example of this can be seen by executing the following query:


WITH
  my_data
AS
(  SELECT  ORG.PARENT_ORG_ID
         ,ORG.ORG_ID
         ,ORG.NAME org_name
    FROM  organisations org
)
SELECT
  SYS_CONNECT_BY_PATH (org_name,'/') tree
  ,PARENT_ORG_ID
  ,ORG_ID
  ,CONNECT_BY_ISCYCLE err
FROM
  my_data
CONNECT BY NOCYCLE PRIOR org_id = parent_org_id
ORDER BY 4 desc;

TREE                    PARENT_ORG_ID ORG_ID ERR 
------------------------------------------------
/SALES/EUROPE           2             5      1
/EUROPE/SALES           5             2      1
/EUROPE                 2             5      0
/EUROPE/SALES/ASIA      2             6      0
/EUROPE/SALES/AMERICAS  2             7      0
/ASIA                   2             6      0
/AMERICAS               2             7      0
/SALES                  5             2      0
/SALES/ASIA             2             6      0
/SALES/AMERICAS         2             7      0
/HQ                     1             0
/HQ/RESEARCH            1             3      0
/HQ/IT                  1             4      0
/RESEARCH               1             3      0
/IT                     1             4      0

Any value > 0 in the ERR column indicates that the row is involved in a self referencing join. This is a much neater and more performant way to achieve to desired result. Thanks to Buzz for pointing out a much better way to original PL/SQL routine.

Pen Test Tool for APEX

2011-06-08T05:13:00.001-07:00

Just a quick plug for a cool Penetration Test tool that we have been using on-site for a few months now. The application is called: Application Express Security Console and developed by a company called Recx Ltd

This can be used to identify areas of you APEX applications that are vulnerable to:
SQL Injection, XSS as well as inadequate access control etc. It kindly suggests ways in which the vulnerability can be addressed as well.

We have built the use of this into our formal release process now and has definitely proved value for money to organisation.

Beware of the Byte

2010-11-03T05:29:00.000-07:00

Recently our test department raised a bug against one our applications that occurred when trying to insert a record into a table.

The error message encountered was a fairly innocuous "ORA-01704: string literal too long".

Following the test case to the letter, I successfully generated the same error and located the table that the APEX form was inserting into. A quick check of the Data Dictionary confirmed that the column in question was of type VARCHAR2(10). At this stage, I though the obvious cause was that there was no limit on the APEX form item (a Text Area) of 10 characters. Having checked the item in question, not only was there a “maxWidth” value of 10, the text area had been created with a “Character Counter”. Strange then how a form item accepting 10 characters was erroring whilst inserting into a column of VARCHAR2(10).

A little while later...... (after some head scratching and several discussions with our DBA’s and a colleague) the problem was all too clear. Somewhere between Database Character Sets, VARCHAR2 column definitions and non ASCII characters lay the answer.

Please forgive the rather verbose narrative but allow me to delve a little deeper.

Firstly the facts:

1. The character set of our database is set to AL32UTF8


SELECT VALUE
  FROM v$nls_parameters
 WHERE parameter = 'NLS_CHARACTERSET';

2. Definition of table causing the error:


CREATE TABLE nls_test
(
 col1 VARCHAR2(10 BYTE)
);

3. SQL Statement causing the error:

INSERT INTO nls_test VALUES ('““““““““““');

NB: 10 individual characters.

The character used in this insert is typical of a double quote produced by MS Word (it was in fact a copy and paste from a Word document into our Text Area that caused our error).

Explanation

The reason we encountered the error was all to do with the attempt to insert a 'Multi Byte' character (a double quote from word in our case) into our table as opposed to typical single byte characters (A-Z, 0-9 etc).

Performing a simple LENGTHB to return the number of bytes this character uses demonstrated this perfectly:


SELECT lengthb('“') from dual;

LENGTHB('“')
------------
           3

Because our column definition is of type VARCHAR2(10 BYTE), we are only permitted to store values that do not exceed 10 bytes in length.

Beware, 1 character in our case definitely does not = 1 byte. As already proved, our single character (a Word double quote) occupies 3 bytes so the maximum number of this Multi Byte Character we could possible insert according to our table definition is worked out simply as:

10 bytes (Column Definition) / 3 (length in bytes of our character) = 3

So whilst the APEX form item does not distinguish between single and multi byte characters and will allow you to input the full 10 characters, Oracle Database will bounce it back in our case as the total bytes in our string is 30 hence the error.

One solution suggested was to alter the Data Type to be of type VARCHAR2(10 CHAR) instead of BYTE. This in theory would force the database to respect the actual number of characters entered and not worry too much about single vs. multi byte occupancy. This would allow us resolve our immediate issue of 10 multi byte characters inserting into our table however there are further considerations.

As it turns out, even when you define your columns to use CHAR over BYTE, Oracle still enforces a hard limit of up to 4000 BYTES (given a mixed string of single and multi byte characters, it implicitly works out the total bytes of the string).

So beware that even if your column for example accepts only 3000 CHAR and you supply 2001 multi byte characters in an insert statement, it may still fail as it will convert your sting into BYTES enforcing an upper limit of 4000 BYTES.

Sorry for the really long post but it was a much for my own documentation as anything else.

APEX - Identify Report Columns Vulnerable to XSS

2010-02-23T02:10:00.000-08:00

The following query is a very simple way of identifying all report columns within your APEX application that may be exposed by Cross Site Scripting (XSS).

XSS allows an attacker to inject web script (JavaScript) into an application and when this is rendered in the report, the script is interpreted rather than rendered as text.

To safe guard against this attack, APEX provides a "Display as Text (escape special characters)" report column attribute that can be applied to classic and Interactive Reports. This causes the script text to be displayed as text rather than interpreted by the browser. If you have any markup (HTML) within your query that the report is based on, this markup will also be displayed as text and not interpreted. I personally think this is a good by product as you should not really be coding look and feel into your raw SQL.

Anyway I digress. Here is the query that will identify all vulnerable report columns within your APEX application:


  SELECT application_id,
         application_name,
         page_id,
         region_name,
         column_alias,
         display_as
    FROM apex_application_page_rpt_cols
   WHERE display_as NOT IN
               ('Display as Text (escape special characters, does not save state)',
                'CHECKBOX',
                'Hidden',
                'Text Field')
         AND workspace != 'INTERNAL'
         AND application_id = :APP_ID
ORDER BY 1, 2, 3;

Enjoy

Oracle SQL Developer on OS X Snow Leopard

2010-02-19T03:40:00.001-08:00

I have been using Oracle SQL Developer Data Modeller for a while now within a Windows XP environment. It seems pretty good (albeit a little slow but hey show some an Oracle Java client application that is quick. Oracle Directory Manager?, OWB Design Centre? I shall labour this point no more) and I was looking forward to trying it out on my new 27" iMac.

I promptley downloaded the software from OTN and a quick read of the instructions suggested I need to do no more other than run the datamodeler.sh shell script since I already had Java SE 6 installed.

As it turns out, the datamodeler.sh script in the root location does little more than call another script called datamodeler.sh found in the /datamodeler/bin directory which is the once you actually need to execute to fire up SQL Data Modeler

When this script runs, it prompts you for a the full J2SE file path (which I had no idea where it was) before it will run. After a quick look around google and I came across the command: java_home which when executed like:

cd /usr/libexec
./java_home

prints the full path value that you need to open SQL Data Modeler

e.g. /System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home

Now we are armed with the full path needed, opening up SQL Data Modeller from a virgin command window goes like this:

cd Desktop/datamodeler/bin
. ./datamodeler.sh

Oracle SQL Developer Data Modeler
Copyright (c) 1997, 2009, Oracle and/or its affiliates.All rights reserved.

Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path will be stored in ~/jdk.conf
/System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home

and hey presto, SQL Data Modeller is up and running.

Once you have pointed the shell script at your J2SE installation, you wont have to do it again.

Now I can finally use Data Modeler on my 27" Screen :)

Generate Days in Month (PIPELINED Functions)

2009-08-25T06:57:00.001-07:00

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)

2009-05-26T06:51:00.000-07:00

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

2009-05-22T04:01:00.000-07:00

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

2009-03-29T04:49:00.000-07:00

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

2009-03-24T07:14:00.000-07:00

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

2008-09-26T02:07:00.000-07:00

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

2008-07-24T07:25:00.000-07:00

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!

Import APEX application via SQL Developer

2008-07-14T11:43:00.000-07:00

I will be honest and admit that I have not been SQL Developers biggest fan since it's release a few years ago. Having always used 3rd party products such as TOAD and PL/SQL Developer, I found certain things a little irritating and not overwhelmingly obvious when forced to use SQL Developer.

Recently however, I found an absolute god send of a feature in SQL Developer and that was the ability to import / export APEX applications. This means that the Web GUI for such actions is no longer required.

Very simply, you connect to your parsing schema's database account via SQL Developer, right click on the Application Express tree directory and select Import Application. This opens a simple wizard and off you go.

One other cool feature of this is that you can open the details window and see exactly what your import is doing, something that is not possible when using the web GUI.

Download SQL Developer from: OTN

APEX 3.1.1 Released

2008-06-10T08:19:00.000-07:00

Just upgraded from APEX 3.1 to 3.1.1 on an Oracle Enterpise Linux 4 Update 4 platform.

Intall took: 5:39 and termintaed without error.

The patch can be downloaded from metalink (patch number 7032837)

All in all, a very simple upgrade and now onto the task of regression testing our current 3.1 apps

Hide Show Regions on an Apex Page

2008-04-29T05:32:00.000-07:00

One of the really nice features of APEX is the ability to hide / show regions when editing item or page attributes. This is really useful if you have to update the same attribute for multiple items.

If you want to implement this type of approach on a page you simply need to:

Create a New Region Template

Either create a new region template (or copy an existing one) and add a DIV tag with the id="XXX#REGION_STATIC_ID#" around the Definition Template.

Don’t forget to close the DIV at the end of the template. In this example I copied our region called “Reports Region” and named it “Reports Region 680 Width (Static ID)”

Add the following JavaScript to your Page Header: view

Create several regions on your page and assign a unique Static ID i.e. REGION1, REGION2 etc. Set the Region Template to the one you created in the 1.

Create a button for each Region you defined in the previous stage and add the following JavaScript call as the URL target of each button:

javascript:hideDiv(‘XXXREGION1’); // For the button called Region 1
javascript:hideDiv(‘XXXREGION2’); // For the button called Region 2
javascript:hideDiv(‘ALL’); // For the button called Show All

Create a hidden item on the page called CURRENT_DIV

Finally edit the page attributes and place the following command in the “Page HTML Body Attributes” section:

onload="hideDiv('&CURRENT_DIV.')";

And that’s it. The key thing to note here is that once a region is hidden, all form items are still active in the DOM and will be submitted along with all the visible form items. This is a great way to break out long, complex forms and enhance the user experience.

An exmaple can be seen here

Tab Order of Elements on an APEX Page

2008-04-21T03:45:00.001-07:00

To set the TAB order of your form, you simply need to add the attribute: TABINDEX="1" to the "HTML Form Element Attributes" of your items replacing the number with the desired sequence.

This is a great way of enhancing the usability of your forms.

JavaScript API Documentation

2008-04-19T04:39:00.001-07:00

I have just been reading the Apex 3.1 API documentation and noticed that the JavaScript API that are commonly used in Apex are now documented:

http://download.oracle.com/docs/cd/E10513_01/doc/appdev.310/e10499/api.htm#CHDBJJDC

There is some very useful stuff in there!!

Multiple Interactive Reports on One Page

2008-03-28T05:56:00.000-07:00

If you have been using Interactive Reports since Apex 3.1 landed, you are probably as impressed with them as I am.

The other day I tried to create more than 1 IR on a page and the Wizard prevented me saying "Only 1 Interactive Report can be declared on this page"

I then tired to copy a region that contained an Interactive Report and sure enough I suddenly had 2 IR's on the one page!

Whilst this is probably not supported or suggested, my requirement meant that only 1 IR was to be shown at once (i.e. I had a conditional diaply on both IR)

So if you need to create multiple IR's on the one page but will only display one at run time, copy of a region appears to work!

Add / Delete a row from a SQL based Tabular Form (Static ID)

2007-12-25T13:40:00.000-08:00

The current application I am working on involves a re-write to a 12 screen wizard that was written 18 months ago. Several of the screens make use of manually built tabular forms (SQL report regions) and collections to hold the values entered. Some of the screens in the wizard have multiple tabular forms on them as well.

Currently all tabular forms have 15 lines which cannot be added to or deleted from. In the new version, we removed this limit and allow the user to add as many rows as he / she needs. Furthermore, specific rows can now be removed from the Tabular form. Since all entered data is written into collections, we wanted to avoid "line by line" processing i.e. submitting the form for each time, updating the collection and branching back to the page. By utilising some simple JavaScript and the new "Static ID" of the Reports Region new to APEX 3.0, all requirements could be met.

The Static ID attribute of the reports region allow us to add our own (unique) ID to a report region. From here we can simpley navigate down the DoM, clone a row in the form using cloneNode and append it to the table using appendChild.

The JavaScript will work even if you have multiple report regions on the same page providing each report region has a unique Static ID value.

Method:

Create a new region on your page using the following details:

Region Type: Report
Report Implementation: SQL Report
Title: Add Row to Report
Region Template: Reports Region
SQL Query: view
Report Template: Standard

Add the following JavaScript to your Page Header: view

Copy the region template: Reports Region and name it Reports Region (Static ID)

Edit the region template: Reports Region (Static ID) and replace the Substitution String #REGION_ID# with #REGION_STATIC_ID# in the Definition section

Edit the region: Add Rows to Report and insert the value: REPORT1 into the Static ID textbox found in the Identification seciton. Note that the values entered into the Static ID textbox must be unique to the page if using multiple report regions where you are specifying a Static ID. Then change the template of the region to use the newley created Reports Region (Static ID) template

Copy the report template: Standard and name it Standard (Static ID)

Edit the report template: Standard (Static ID) and replace the text: id="#REGION_ID#" with: id="datatable_#REGION_ID#" in the before rows section.

Edit the report attributes and change the report template to use the newley created: Standard (Static ID)

Add a button to the page using the following details:

Select a Region for the Button: Add Rows to Report
Position: Create a button in a region position
Button Name: ADD_ROW
Label: Add Row
Button Type: HTML Button
Action: Redirect to URL without submitting page
Target is a: URL
URL Target: javascript:addRow('REPORT1');

Please note that REPORT1 refers to the Static ID of the region you want to add your row to

Test your Add and Delete a row functionality

An example with all the source code can be seen here

Mac OSX, Bootcamp and a Missing Hash Key

2007-12-16T10:58:00.000-08:00

I bought a Mac Book Pro about 8 months ago as my main business Laptop. Coupled with a copy of Parallels, I built my Oracle Server (Database and Apps Server) on a Windows VM environment which left Mac OSX free for Development using SQL Developer, Dreamweaver etc

A couple of weeks back I decided to upgrade to OSX Leopard and install windows natively using Bootcamp to utilise both core's on the CPU and all 3 Gig of memory. All well and good until i tried to use the Hask key (Alt + 3 in OSX) when working on some APEX templates. After much research on the web, it appears that most OSX key mappings are installed when using bootcamp but in order to print the hash (#) symbol, you must use Ctrl + Alt + 3

Simple when you know how

Multiple Verison of I.E on one machine

2007-10-25T00:39:00.000-07:00

After the lastest round of updates were installed from Microsoft, I foolishly forgot to uncheck the "Upgrade to I.E 7" box and hence after a reboot, a fresh new version of Internet Explorer was waiting for me. On face value this appeared ok until I tried to access Mercury Test Director.

According to the error message, only I.E 6 was supported.

A quick search on google and I happened upon this website: http://tredosoft.com/Multiple_IE
and downloaded the installer which contained multiple version of I.E from 3.0 to 6.0 that run in standalone.

Not only has this fixed my problem of accessing applicaitons, it also allows me to test my applications against earlier versions of I.E.

Very useful indeed

I.E is not one of the SELECT few!!!

2007-08-22T00:00:00.000-07:00

I recently had the requirement to disable several elements in a SELECT list. "No trouble" I thought and promptly altered my code to include: disabled="disabled" attribute for the selected elements.

A quick test using Firefox and sure enough all seemed well. That was however until I ran the same web page in Internet Explorer. All of a sudden, the elements I had marked as disabled were anything but.

Not wanting lambaste Microsoft for "Standards Avoidance", I feel it necessary as W3C clearly state that the disabled attribute can be used in the option tag.

A quick poke around Google and a happened upon this website which offers a JavaScript workaround.

All that remains to say is, thanks Internet Explorer. How can the most commonly used browser not conform to some of most basic HTML4 standards?

Here is an example so answers on a post card please.

Custom Authentication / Authorisation Schemes (Part 1)

2007-07-01T08:52:00.000-07:00

I often see posts on the APEX forum asking how to implement custom Authentication / Authorisation schemes within their applications. The following is something I have used in several apps over the last couple of years and provides a great base for securing your application.

The code base is largley based on the rather excellent article "Storing Passwords in the Database" found here with a few tweaks and changes.

Firstly, just to clarify, Authentication Schemes control access to the application and Authorisation Schemes control access to page items / regions and even pages themselves.

Implementing your own Authentication

I tend to set up an APP_USERS table that stores Username and encrypted passwords that I Authenticate against when page 101 is submitted. All this will be explained in detail as we go.

Create the APP_USERS table


CREATE TABLE APP_USERS 
(
USERNAME VARCHAR2(10),
PASSWORD VARCHAR2 (255)
);

Create the Application Security Package


CREATE OR REPLACE PACKAGE app_security_pkg
AS
PROCEDURE add_user 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
);

PROCEDURE login
(
 p_uname IN VARCHAR2
,p_password IN VARCHAR2
,p_session_id IN VARCHAR2
,p_flow_page IN VARCHAR2
);

FUNCTION get_hash 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
)
 RETURN VARCHAR2;

PROCEDURE valid_user2 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
);

FUNCTION valid_user 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
)
 RETURN BOOLEAN;

END app_security_pkg;
/

CREATE OR REPLACE PACKAGE BODY app_security_pkg
AS
PROCEDURE login 
          (
           p_uname IN VARCHAR2
          ,p_password IN VARCHAR2
          ,p_session_id IN VARCHAR2
          ,p_flow_page IN VARCHAR2
          )
IS
 lv_goto_page NUMBER DEFAULT 1;
BEGIN
 
 -- This logic is a demonstration of how to redirect 
 -- to different pages depending on who successfully 
 -- authenticates. In my example, it simply demonstrates 
 -- the ADMIN user going to page 1 and all other users going
 -- to page 100. Add you own logic here to detrmin which page 
 -- a user should be directed to post authentication.
 IF UPPER(p_uname) = 'ADMIN'
 THEN
  lv_goto_page := 1;
 ELSE
  lv_goto_page := 100;
 END IF;

 wwv_flow_custom_auth_std.login 
 (
  p_uname => p_uname,
  p_password => p_password,
  p_session_id => p_session_id,
  p_flow_page => p_flow_page || ':' || lv_goto_page
  );

EXCEPTION
WHEN OTHERS
THEN 
 RAISE;
END login;

PROCEDURE add_user 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
)
AS
BEGIN
INSERT INTO app_users (username, PASSWORD)
VALUES (UPPER (p_username),
get_hash (TRIM (p_username), p_password));

COMMIT;

EXCEPTION
WHEN OTHERS
THEN 
 ROLLBACK; 
 RAISE;
END add_user;

-- Function to Perform a oneway hash of the users 
-- passwords. This cannot be reversed. This exmaple 
-- is a very week hash and if been used on a production 
-- system, you may want to use a stronger hash algorithm.
-- Read the Documentation for more info on DBMS_CRYPTO as 
-- this is the supported package from Oracle and 
-- DBMS_OBFUSCATION_TOOLKIT is now depricated.
FUNCTION get_hash (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN DBMS_OBFUSCATION_TOOLKIT.md5 (
input_string => UPPER (p_username) 
                || '/' 
                || UPPER (p_password));
END get_hash;

PROCEDURE valid_user2 (p_username IN VARCHAR2, p_password IN VARCHAR2)
AS
v_dummy VARCHAR2 (1);
BEGIN
SELECT '1'
INTO v_dummy
FROM app_users
WHERE UPPER (username) = UPPER (p_username)
AND PASSWORD = get_hash (p_username, p_password);
EXCEPTION
WHEN NO_DATA_FOUND
THEN raise_application_error (-20000, 'Invalid username / password.');
END valid_user2;

FUNCTION valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
BEGIN
valid_user2 (UPPER (p_username), p_password);
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN RETURN FALSE;
END valid_user;

END app_security_pkg;
/

Create a Test User

Log into the APEX Workspace and navigate to the SQL Workshop. If access to the workspace is restricted, this can be done in SQL*Plus providing you log in as the schema owner
Execute the following command:

EXECUTE app_security_pkg.add_user (‘testuser’, ‘password’)
/

Create your own Authentication Scheme

Navigate to: Shared Components > Authentication Schemes
Click [Create >]
Select “From Scratch” and click [Next >]
Enter “My Auth Scheme” and click [Create Scheme]
Click on the “My Auth Scheme” icon
Enter this into the Authentication Function Section: RETURN APP_SECURITY_PKG.valid_user
Click [Apply Changes]

Switch on your Custom Scheme

Click on the “Change Current” link. This can be found under the “Tasks” area
Select “My Auth Scheme” from the drop down list and click [Next>]
Click [Make Current]

Alter the Login Process on Page 101

Page 101 is created by default when you create your application definition. Assuming that the Username and Password text boxes have remained unaltered, their names will be P101_USERNAME and P101_PASSWORD respectfully. If these item names have remained unchanged you will need to do very little. If you have changed them, update the item names in the following procedure call.

Edit Page 101
Navigate to the “Login” process
Replace the procedure call (if one exists) with the following:

app_security_pkg.login (P_UNAME => :P101_USERNAME, P_PASSWORD => :P101_PASSWORD, P_SESSION_ID => v('APP_SESSION'), P_FLOW_PAGE => :APP_ID );

Click [Apply Changes]

Test Access to the Application

Access to the Application should be only be gained by the Test User account set up in Step 3. I tend to build a screen that allows Users to be created using the add_user procedure executed in step 3.

This is just the bare bones of a more complete security layer. In previous applications, I have built in password expiration, session timeout and force password change on first login to name but a few. Check back for part 2 which will explain how to implement your own Authorisation Scheme

Centrally Managed TNSNAMES and SQLNET

2007-06-03T03:38:00.000-07:00

For a while now I have had 3 Oracle Homes on my Laptop (Database, 10gIDS and 10gBI Tools) and several more on my server at home. It annoyed me that every visit to a new client site requried configuring multiple tnsname entries in all the Client Side apps.

A collegue at work demonstrated how to centrally manage your tnsnames so that all client apps use the same file.

Simply save your tnsnames and sqlnet files into a directory on your server (d:\My Documents\TNSNAMES)

Change the tnsnames and sqlnet entries in all the clients to:

ifile = d:\My Documents\TNSNAMES\tnsnames.ora
ifile= D:\My Documents\TNSNAMES\sqlnet.ora

And there you go, any changes to the centrally manages tnsnames / sqlnet files will be visible by all client apps pointed to use them.

Render Excel Spreadsheet in IE

2007-05-09T06:30:00.000-07:00

A while back we had a requirement to generate a lot of heavily formatted Excel type reports out of our application. The users also wanted this integrated seamlessly into the application so that when the report was run, it displayed in the same Internet Explorer window. After a bit of research and plenty of goggling, we came up with this solution (this demo uses a copy of the emp table from the Scott schema):

Appologies in advance if the SQL / PL/SQL or HTML format gets a bit messed up but it should compile ok.

N.b. I must first point out that this only works in Office 95/2003 and IE. Firefox will generate the report but opens Excel to do so. Please take any of this code and extend it to support Office 2007 and Firefox. It is only meant as a guide / proof of concept.

Create this package (I create this in its own schema and have a public execute but creating it in the schema you are working is fine)



CREATE OR REPLACE PACKAGE excel_lib
IS

PROCEDURE create_column (
pv_span_i IN VARCHAR2 DEFAULT '1',
pv_style_i IN VARCHAR2);

PROCEDURE create_cell (
pv_class_i IN VARCHAR2 DEFAULT NULL,
pv_align_i IN VARCHAR2 DEFAULT NULL,
pv_colspan_i IN VARCHAR2 DEFAULT NULL,
pv_rowspan_i IN VARCHAR2 DEFAULT NULL,
pv_value_i IN VARCHAR2 DEFAULT NULL,
pv_value_number_i IN NUMBER DEFAULT NULL,
pv_valign_i IN VARCHAR2 DEFAULT NULL
);

PROCEDURE create_file_header (
pv_sheetname_i IN VARCHAR2,
pv_send_to_excel_i IN BOOLEAN DEFAULT TRUE
);

PROCEDURE new_row (
pv_height_i IN VARCHAR2 DEFAULT NULL,
pv_style_i IN VARCHAR2 DEFAULT NULL
);

PROCEDURE end_row;
PROCEDURE end_file;
END excel_lib;
/

CREATE OR REPLACE PACKAGE BODY excel_lib
IS
 PROCEDURE create_column (pv_span_i IN VARCHAR2 DEFAULT '1', pv_style_i IN VARCHAR2)
 IS
 BEGIN

 HTP.p ('<col style="' || pv_style_i || '" span="' || pv_span_i || '">');

 END create_column;

 

 PROCEDURE create_cell (

 pv_class_i          IN   VARCHAR2 DEFAULT NULL,

 pv_align_i          IN   VARCHAR2 DEFAULT NULL,

 pv_colspan_i        IN   VARCHAR2 DEFAULT NULL,

 pv_rowspan_i        IN   VARCHAR2 DEFAULT NULL,

 pv_value_i          IN   VARCHAR2 DEFAULT NULL,

 pv_value_number_i   IN   NUMBER DEFAULT NULL,

 pv_valign_i         IN   VARCHAR2 DEFAULT NULL

 )

 IS

 lv_html_string   VARCHAR2 (500) DEFAULT '<td';

 BEGIN

 -- Add class attribute

 IF pv_class_i IS NOT NULL

 THEN

 lv_html_string := lv_html_string || ' class="' || pv_class_i || '"';

 END IF;

 

 -- Add align attribute

 IF pv_align_i IS NOT NULL

 THEN

 lv_html_string := lv_html_string || ' align="' || pv_align_i || '"';

 END IF;

 

 -- Add x:num attribute

 IF pv_value_number_i IS NOT NULL

 THEN

 lv_html_string :=

 lv_html_string || ' x:num="' || TO_CHAR (pv_value_number_i)

 || '"';

 END IF;

 

 -- Add colspan attribute

 IF pv_colspan_i IS NOT NULL

 THEN

 lv_html_string :=

 lv_html_string || ' colspan="' || TO_CHAR (pv_colspan_i)

 || '"';

 END IF;

 

 IF pv_rowspan_i IS NOT NULL

 THEN

 lv_html_string :=

 lv_html_string || ' rowspan="' || TO_CHAR (pv_rowspan_i)

 || '"';

 END IF;

 

 

 IF pv_valign_i IS NOT NULL

 THEN

 lv_html_string :=

 lv_html_string || ' valign="' || TO_CHAR (pv_valign_i)

 || '"';

 END IF;

 

 -- Finish <td> tag

 lv_html_string :=

 lv_html_string

 || '>'

 || REPLACE (SUBSTR (pv_value_i,

 1,

 LENGTH (pv_value_i) - LENGTH (LTRIM (pv_value_i))

 ),

 ' ',

 '&nbsp;'

 )

 || LTRIM (pv_value_i)

 || '</td>';

 -- write out the cell details to the page

 HTP.p (lv_html_string);

 END create_cell;

 

 PROCEDURE new_row (

 pv_height_i   IN   VARCHAR2 DEFAULT NULL,

 pv_style_i    IN   VARCHAR2 DEFAULT NULL

 )

 IS

 BEGIN

 IF pv_height_i IS NULL AND pv_style_i IS NULL

 THEN

 HTP.p ('<tr>');

 ELSE

 HTP.p ('<tr height="' || pv_height_i || '" style="' || pv_style_i || '">');

 END IF;

 END new_row;

 

 PROCEDURE end_row

 IS

 BEGIN

 HTP.p ('</tr>');

 END end_row;

 

 PROCEDURE create_file_header (

 pv_sheetname_i       IN   VARCHAR2,

 pv_send_to_excel_i   IN   BOOLEAN DEFAULT TRUE

 )

 IS

 BEGIN

 IF pv_send_to_excel_i

 THEN

 OWA_UTIL.mime_header ('application/vnd.ms-excel');

 END IF;

 

 HTP.p

 (   '

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">

<meta NAME=ProgId content=Excel.Sheet>

<meta NAME=Generator content="Microsoft Excel 9">

<style>

<!--table

 {mso-displayed-decimal-separator:"\.";

 mso-displayed-thousand-separator:"\,";

 font-family:Arial, sans-serif;

 font-size:9pt;

 color:black;}');

 

htp.p ('@page

 {margin:1.0in .75in 1.0in .75in;

 mso-header-margin:.5in;

 mso-footer-margin:.5in;}

-->

</style>

<!--[if gte mso 9]><xml>

 <x:ExcelWorkbook>

 <x:ExcelWorksheets>

 <x:ExcelWorksheet>

 <x:Name>' || pv_sheetname_i || TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24.MI') || '</x:Name>

 <x:WorksheetOptions>

 <x:Zoom>75</x:Zoom>

 <x:Selected/>

 <x:ProtectContents>False</x:ProtectContents>

 <x:ProtectObjects>False</x:ProtectObjects>

 <x:ProtectScenarios>False</x:ProtectScenarios>

 </x:WorksheetOptions>

 </x:ExcelWorksheet>

 </x:ExcelWorksheets>

 <x:WindowHeight>15210</x:WindowHeight>

 <x:WindowWidth>21900</x:WindowWidth>

 <x:WindowTopX>480</x:WindowTopX>

 <x:WindowTopY>120</x:WindowTopY>

 <x:ProtectStructure>False</x:ProtectStructure>

 <x:ProtectWindows>False</x:ProtectWindows>

 </x:ExcelWorkbook>

</xml><![endif]-->

<title>' || pv_sheetname_i || TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24.MI') || '</title>

</head>

<BODY LINK=blue vlink=purple>

<TABLE x:str border=0 cellpadding=0 cellspacing=0 style="border-collapse:collapse;table-layout:fixed">'

 );

 END create_file_header;

PROCEDURE end_file

IS

 BEGIN

 HTP.p ('</table></body></html>');

 END end_file;

END Excel_Lib;
/

2. Create your CSS to format the report


CREATE OR REPLACE PROCEDURE report_css
AS
BEGIN
htp.p ('.xlBlue
 {vertical-align:middle;
 border:.5pt solid black;
 background:aqua;
 font-weight:700;}
.xlSilver
 {vertical-align:middle;
 border:.5pt solid black;
 background:silver;
 font-weight:700;}
.xlPlain
 {mso-number-format:"\#\,\#\#0\.00\;\\\(\#\,\#\#0\.00\\\)";
 vertical-align:top;}
.xlText
 {mso-number-format:"\@";
 vertical-align:top;}
.xlYellow
 {mso-number-format:"\#\,\#\#0\.00\;\\\(\#\,\#\#0\.00\\\)";
 background:yellow;
 font-weight:700;}
.xlHighlightedNumber
 {mso-number-format:"\#\,\#\#0\.00\;\\\(\#\,\#\#0\.00\\\)";
 background:#FF8080;}
.xlBold
 {mso-number-format:"\#\,\#\#0\.00\;\\\(\#\,\#\#0\.00\\\)";
   font-weight:700;}
.xlUnderline
    {mso-number-format:"\#\,\#\#0\.00\;\\\(\#\,\#\#0\.00\\\)";
    font-weight:700;
    text-decoration:underline;
 text-underline-style:single;
}');

END report_css;
/

3. Create your Report. Note that you build up your reports using an HTML Table row / cell layout format


CREATE OR REPLACE PROCEDURE report1
IS
   CURSOR c_emp
   IS
      SELECT *
        FROM emp;
BEGIN

   -- Writes the MIME type etc to allow excel to run in IE
   excel_lib.create_file_header ('My Report ');
   
    report_css; -- Writes your custom CSS to the page
      
   excel_lib.new_row ();
   excel_lib.create_cell (pv_value_i      => 'Emp No',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Emp Name',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Job',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Manager',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Hire Date',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Sal',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Commission',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Dept No',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.end_row;

   FOR x IN c_emp
   LOOP
      excel_lib.new_row ();
      excel_lib.create_cell (pv_value_i      => TO_NUMBER (x.empno),
                             pv_align_i      => 'right'
                            );
      excel_lib.create_cell (pv_value_i => x.ename);
      excel_lib.create_cell (pv_value_i => x.job);
      excel_lib.create_cell (pv_value_i => x.mgr);
      excel_lib.create_cell (pv_value_i      => TO_CHAR (x.hiredate,
                                                         'DD-Mon-RRRR'
                                                        ),
                             pv_align_i      => 'right'
                            );

      IF x.sal >= 3000
      THEN
         excel_lib.create_cell (pv_value_i      => TO_NUMBER (x.sal),
                                pv_align_i      => 'right',
                                pv_class_i      => 'xlBlue'
                               );
      ELSE
         excel_lib.create_cell (pv_value_i      => TO_NUMBER (x.sal),
                                pv_align_i      => 'right'
                               );
      END IF;

      IF x.comm IS NULL
      THEN
         excel_lib.create_cell (pv_value_i => ' ');
      ELSE
         excel_lib.create_cell (pv_value_i      => TO_NUMBER (x.comm),
                                pv_align_i      => 'right',
                                pv_class_i      => 'xlHighlightedNumber'
                               );
      END IF;

      excel_lib.create_cell (pv_value_i      => TO_NUMBER (x.deptno),
                             pv_align_i      => 'right'
                            );
      excel_lib.end_row;
   END LOOP;
END report1;
/

4. Run your Report

Issue a public grant on the report package and then enter the following url (note that any requests going via mod_plsql will be fine here. I used the apex dad as it is the simplest way to demo)

http://localhost:7777/pls/apex/duncan.report1

Hope that all makes sense

Stop Page Submission When Enter is Pressed

2007-04-23T01:46:00.000-07:00

If you have an HTML page containing only 1 Textbox, standard HTML / browser behavior is to allow the page to be submitted when entered is pressed.

Normally I don't have an issue with this but in APEX, no request value is picked up when the page is submitted this way. Consequently, you usually get an error implying there is no page to branch to as the page was submitted without a request value.

The solution to this problem is very simple.

Create a dummy textbox on the page and in the: HTML Form Element Attributes attribute, enter the following: style="display:none;"

Now because the browser thinks 2 textboxes exist, page submission by pressing enter will not occur.

Simple solution to a small but annoying problem

Custom Error Handling in APEX

2007-04-17T05:21:00.000-07:00

If like me you have written several PL/SQL procedures within your APEX app, you will have had to deal with the issue of error handling. As a general rule, I try to hide all Oracle Error Messages from the user and replace these with something more meaningful for example:

If a user breaks a unique key by trying to insert the same value twice, the following error message is not uncommon:

ORA-00001 Unique constraint violated

There is little or no merit rendering this message to a user as those outside the Oracle community, may not be able to deduce what went wrong. We can capture this error and translate it to a user friendly message explaining what went wrong for example:

The record you tried to create already exists. Please try again

In order to achieve this in APEX, the process is relativly simple.

Set up your Custom Error Page

1. Create a new page with the following attributes:

Page type: Blank Page
Page Number: your choice (for this example I used page 500)
Page Alias: ERRPAGE
Name: Custom Error Page
Title: Custom Error Page

2. Create a new HTML region on the page with the following attributes:

Title: Custom Error Page
Region Template: Wizard Region
Region Source: There was an error processing your request.<br><br>&P500_ERR_MSG.

3. Create the following hidden items (replace 500 with the page number of your error page):

P500_ERR_MSG
P500_GOTO_PAGE
P500_GOTO_PAGE_CC
P500_GOTO_PAGE_REQ
P500_GOTO_PAGE_ITEMS
P500_GOTO_PAGE_VALUES
P500_GOTO_PAGE_RP
P500_GOTO_PAGE_LINK_DIS

4. Create a button in the Custom Error Page HTML Region with the following attributes:

Button Name: OK
Label: &P500_GOTO_PAGE_LINK_DIS.
Button Type: HTML
Action: Redirect to URL without submitting page
Target is a: Page in this Application
Page: &P500_GOTO_PAGE.
Request: &P500_GOTO_PAGE_REQ.
Clear Cache: &P500_GOTO_PAGE_CC.&P500_GOTO_PAGE_RP.
Set these items: &P500_GOTO_PAGE_ITEMS.
With these values: &P500_GOTO_PAGE_VALUES.

Create the Error Handling Package

CREATE OR REPLACE PACKAGE error_pkg
AS
PROCEDURE error_redirect (
pv_custom_msg IN VARCHAR2,
pv_goto_page IN NUMBER DEFAULT v ('APP_PAGE_ID'),
pv_goto_page_cc IN VARCHAR2 DEFAULT NULL,
pv_goto_page_req IN VARCHAR2 DEFAULT NULL,
pv_goto_page_items IN VARCHAR2 DEFAULT NULL,
pv_goto_page_values IN VARCHAR2 DEFAULT NULL,
pv_goto_page_rp IN BOOLEAN DEFAULT FALSE,
pv_goto_page_link_dis IN VARCHAR2 DEFAULT 'Ok'

);
END error_pkg;
/

CREATE OR REPLACE PACKAGE BODY error_pkg
AS
PROCEDURE error_redirect (
pv_custom_msg IN VARCHAR2,
pv_goto_page IN NUMBER DEFAULT v ('APP_PAGE_ID'),
pv_goto_page_cc IN VARCHAR2 DEFAULT NULL,
pv_goto_page_req IN VARCHAR2 DEFAULT NULL,
pv_goto_page_items IN VARCHAR2 DEFAULT NULL,
pv_goto_page_values IN VARCHAR2 DEFAULT NULL,
pv_goto_page_rp IN BOOLEAN DEFAULT FALSE,
pv_goto_page_link_dis IN VARCHAR2 DEFAULT 'Ok'

)
IS
BEGIN
wwv_flow.g_unrecoverable_error := TRUE; -- Quit the APEX Engine

HTP.init;-- Initiate the HTML Header incase it was closed

-- Set session of items on the error page
apex_util.set_session_state ('P500_ERR_MSG', pv_custom_msg);
apex_util.set_session_state ('P500_GOTO_PAGE', pv_goto_page);
apex_util.set_session_state ('P500_GOTO_PAGE_CC', pv_goto_page_cc);
apex_util.set_session_state ('P500_GOTO_PAGE_REQ', pv_goto_page_req);
apex_util.set_session_state ('P500_GOTO_PAGE_ITEMS', pv_goto_page_items);
apex_util.set_session_state ('P500_GOTO_PAGE_VALUES', pv_goto_page_values);
apex_util.set_session_state
('P500_GOTO_PAGE_LINK_DIS', pv_goto_page_link_dis);

-- Resets the pagination scheme for the page redirected to
IF pv_goto_page_rp
THEN
apex_util.set_session_state ('P500_GOTO_PAGE_RP', ',RP');
END IF;

-- Redirect the URL to the custom error page
OWA_UTIL.redirect_url ( 'f?p='
|| v ('APP_ID')
|| ':ERRPAGE:'
|| v ('APP_SESSION')
);

END error_redirect;
END error_pkg;
/

You will need to change the page ID of the items that are being set in session in the error_redirect procedure, to the ID of your error page.

Re-Write your Exception Handlers

EXCEPTION
WHEN CUSTOM_EXCEPTION1
THEN
error_pkg.error_redirect
(pv_custom_msg => 'Your Custom Error Message Goes Here',
pv_goto_page => v ('APP_PAGE_ID'));
WHEN OTHERS
THEN
IF SQLCODE = -6502
THEN
error_pkg.error_redirect
(pv_custom_msg => 'A Non Numeric Character was Encountered',
pv_goto_page => v ('APP_PAGE_ID'),
pv_goto_page_cc => '1,500');
ELSE
error_pkg.error_redirect
(pv_custom_msg => 'Please contact Support for further assistance',
pv_goto_page => v ('APP_PAGE_ID'),
pv_goto_page_cc => '1,500',
pv_goto_page_rp => TRUE);
END IF;
END;

The only parameter that is required when calling the error_pkg.error_redirect procedure is pv_custom_msg

All the other parameters let you build a new URL that will be redirected to once the user presses the [OK] button on the Custom Error Page. These parameters are bound by the same rules as constructing a URL anywhere else in the APEX application. Please see: Understanding URL Syntax

The only parameter that behaves slightly differently to the documentation is pv_goto_page_rp
This is a Boolean switch where TRUE will reset the pagination on the page redirected to after the Custom Error Page.

The pv_goto_page_link_dis parameter allows you to set the text of the button on the Custom Error Page.

You now should have a generic approach to providing the user with a friendly error message on your own Custom Error Page in you APEX applications.

There is an example of this in operation here: http://apex.oracle.com/pls/otn/f?p=40923:1

Flatten Out a Heirarchy using SYS_CONNECT_BY_PATH

2007-03-26T12:02:00.001-07:00

In a recent application, we needed to model the Organisational Hierarchy which at its most complex ran to 7 levels deep. This was achieved using a self referencing foreign key (Pigs Ear) similar to that of the EMP table in the Scott schema. In essence, it simply stores the parent / child relationship for each entry in the Hierarchy.

This approach serviced the application very well in that a simply tree walk (CONNECT BY PRIOR) allowed us to construct the Hierarchical tree and bounce our requests off that.

During the production of the Discoverer End User Layer, it became evident that this Hierarchy needed to be flattened out (un-normalized) in order for it to reported on. This is because Discoverer (or any other BI product) does not support the CONNECT BY and START WITH clause. Discoverer needs to know how many levels exist within a Hierarchy and that every thread in the Hierarchy has the same number of levels in order to build a folder structure that can be reported on.

After a bit of investigation, I happened upon the: SYS_CONNECT_BY_PATH new in 9i

This can be used to construct the "Directory Path" for all values in the EMP table i.e. we can construct the reporting path for all employees in the EMP table

For Example:

SELECT empno, ename, job, SYS_CONNECT_BY_PATH (ename, '/') || '/' chain
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;


EMPNO ENAME   JOB       CHAIN
----- ------  -----     ---------------------
 7839 KING    PRESIDENT /KING/
 7566 JONES   MANAGER   /KING/JONES/
 7788 SCOTT   ANALYST   /KING/JONES/SCOTT/
 7876 ADAMS   CLERK     /KING/JONES/SCOTT/ADAMS/
 7902 FORD    ANALYST   /KING/JONES/FORD/
 7369 SMITH   CLERK     /KING/JONES/FORD/SMITH/
 7698 BLAKE   MANAGER   /KING/BLAKE/
 7499 ALLEN   SALESMAN  /KING/BLAKE/ALLEN/
 7521 WARD    SALESMAN  /KING/BLAKE/WARD/
 7654 MARTIN  SALESMAN  /KING/BLAKE/MARTIN/
 7844 TURNER  SALESMAN  /KING/BLAKE/TURNER/
 7900 JAMES   CLERK     /KING/BLAKE/JAMES/
 7782 CLARK   MANAGER   /KING/CLARK/
 7934 MILLER  CLERK     /KING/CLARK/MILLER/

14 rows selected.

From here, we can simply parse the CHAIN string to work out what our Hierarchy would look like to any number of levels. The following function can be used to construct out flat Hierarchy

CREATE OR REPLACE FUNCTION parse_string (
pv_str_i IN VARCHAR2 DEFAULT NULL,
pv_first_occ IN NUMBER DEFAULT NULL,
pv_last_occ IN NUMBER DEFAULT NULL
)
RETURN VARCHAR2
IS
lv_retval VARCHAR2 (4000);
lv_str VARCHAR2 (4000);
lv_tester VARCHAR2 (4000);
lv_substr_pos1 NUMBER;
lv_substr_pos2 NUMBER;
BEGIN
SELECT INSTR (pv_str_i, '/', 1, pv_first_occ)
INTO lv_substr_pos1
FROM DUAL;

SELECT INSTR (pv_str_i, '/', 1, pv_last_occ)
INTO lv_substr_pos2
FROM DUAL;

lv_tester :=
SUBSTR (pv_str_i, lv_substr_pos1 + 1,
(lv_substr_pos2 - lv_substr_pos1));

lv_str := REPLACE (lv_tester, '/', NULL);

RETURN lv_str;
END parse_string;
/

If we wanted to then create a flat hierarchy to 3 levels deep, we can simply then write a query like follows:

SELECT chain,
NVL (parse_string (chain, 1, 2), '<- ' || ename) level1,
NVL (parse_string (chain, 2, 3), '<- ' || ename) level2,
NVL (parse_string (chain, 3, 4), '<- ' || ename) level3
FROM (SELECT empno, ename, job,
SYS_CONNECT_BY_PATH (ename, '/') || '/' chain
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr);


CHAIN                    LEV1  LEV2    LEV3
----------------------   ----  ----    ----
/KING/                   KING  <- KING <- KING 
/KING/JONES/             KING  JONES   <- JONES 
/KING/JONES/SCOTT/       KING  JONES   SCOTT 
/KING/JONES/SCOTT/ADAMS/ KING  JONES   SCOTT 
/KING/JONES/FORD/        KING  JONES   FORD 
/KING/JONES/FORD/SMITH/  KING  JONES   FORD 
/KING/BLAKE/             KING  BLAKE   <- BLAKE 
/KING/BLAKE/ALLEN/       KING  BLAKE   ALLEN 
/KING/BLAKE/WARD/        KING  BLAKE   WARD 
/KING/BLAKE/MARTIN/      KING  BLAKE   MARTIN 
/KING/BLAKE/TURNER/      KING  BLAKE   TURNER 
/KING/BLAKE/JAMES/       KING  BLAKE   JAMES 
/KING/CLARK/             KING  CLARK   <- CLARK 
/KING/CLARK/MILLER/      KING  CLARK   MILLER 

14 rows selected.

And now we have a table that can be used to report on with BI products such as Discoverer

Translate Columns into Rows (Subquery Factoring)

2007-03-20T01:20:00.000-07:00

It has always been a fairly rudimentary task pivoting a result set so that rows are displayed as columns. More recently I had the requirement to translate a result set the other way so that the columns would be displayed as rows.

For Example

Your result set starts out like this:

SITE COST1 COST2 COST3 COST4
------------------------------------------------------------------
SITE_ONE 2000 255
SITE_TWO 100
SITE_THREE 145 5000

The desired output should look like this:

SITE VALUE
------------------------------
SITE_ONE 2000
SITE_ONE 255
SITE_TWO 100
SITE_THREE 145
SITE_THREE 5000

Thanks to the help from Mr. Tom Kyte at http://asktom.oracle.com I was able to produce the desired results using the following query:

WITH data AS
(SELECT LEVEL l
FROM dual CONNECT BY LEVEL <= 4) SELECT site, decode(l, 1, cost1, 2, cost2, 3, cost3, 4, cost4) cost FROM data, costs WHERE decode(l, 1, cost1, 2, cost2, 3, cost3, 4, cost4) IS NOT NULL ORDER BY 1

It uses something called Subquery Factoring which allows you to create a pseudo table with N rows (CONNECT BY LEVEL <= N) in your query. You then cartisian join to your other tables(s) and use a simple DECODE to extract the column you are interested in.

Note that this technique will only work in 9i and above but I have had great success in the past with it The thread on asktom can be found here

Where Did My PL/SQL Error?

2007-02-13T04:56:00.000-08:00

When your PL/SQL block errors, it has always been a rudimentary task in identifying the error generated using a combination of SQLCODE and SQLERRM.

These 2 functions however do not tell us the exact line of code that propagated the error. This feature would be very handy when debugging code.

In Oracle 10g, there is a function called: DBMS_UTILITY.format_error_backtrace

This will return the line number that generated the error. It does not tell us what the SQL Error message is so I use it in conjunction with SQLERRM to quickly debug problematic code.

In a recent application, I used it as part of my error logging and reporting function that allows the support team to quickly troubleshoot errors. The more information about the error we can give them, the quicker they should be able to response. At least that’s the theory.

For a more detailed explanation of how to best utilise this function, have a look at this atricle

Back to work!!!!!!!!!

2007-01-28T14:43:00.000-08:00

Well, our week skiing in Meribel came and went too quickly. We all had am awesome time in the "Best British Ski Resort" France has to offer.

The Skiing was good (not excellent) but enough to thoroughly exhaust every one of us and our Chalet was very comfortable.

Living costs in the Trois Valleys was not cheap however. Beer ranged between 8 and 10 Euros a pint which made for some very expensive nights out. Fortunately, food was included in our Chalet costs. I was devastated parting with 40 Euros for a lasagne, chips and a coke in Courcheval but hey.

For anyone interested, some pictures can be found here

I shall be putting them all on my website in the next few weeks.

Optimistic Locking Using ORA_ROWSCN

2007-01-10T00:41:00.000-08:00

For anyone who has manually written an Update Statement in a Web Application, the issue of Optimistic Locking always took a while to address.

The normal convention is to use a series of hidden elements for every form element that is submitted and doing an "old vs new" comparison in the Update statement to see if any valus in the database have changed.

I found the main drawback was the need to create large amounts of hidden elements that are then passed into the DML procedure for evaluation.

I came across ORA_ROWSCN in 10GR2 that reduces the amount of hidden form elements you need to submit.
Its basically a new pseudo column in that database that provides the SCN (System Clock Number for the last time the row was updated) associated with individual rows when they were read.

The basic steps to utilise this approach are as follows:

1) create table users (id number,
name VARCHAR2 (100),
address VARCHAR2 (100),
tel NUMBER)
ROWDEPENDENCIES
/

ROWDEPENDENCIES need to set as SCN by default is implemented at block level. By setting ROWDEPENDENCIES, SCN should now track at row level.

2) query that a form on page 100 is based on would look like this (note that a hidden element P100_USER_RCN should be created at page level):

SELECT name, address, tel, ORA_ROWSCN
INTO :P100_USER_NAME, :P100_ADDRESS, :P100_TEL, :P100_USER_RCN
FROM users
WHERE id = 1;

3) Your update statement would then look like this:

UPDATE users
SET id = :P100_USER_ID,
name = :P100_USER_NAME,
WHERE id = 1
AND ORA_ROWSCN = :P100_USER_RCN;

-- Check to see if update took place
IF SQL%ROWCOUNT <= 0
THEN
RAISE_APPLICATION_ERROR (-20002, 'Optimistic Locking Violation');
END IF;

Of course my first advice would be let Application Express handle your DML updates if you can, but for the times when you have to manually create your own update, this may be of some help to you.

Here are some benchmarked examples of this in operation on AskTom

I also posted this in the Application Express Forum a while back. There were some good comments / additions made by others so here is the link to the post.

New Line Character Using UTL_FILE

2007-01-09T03:55:00.000-08:00

A while ago I came across an issue exporting the contents of a BLOB to a flat file on my file system. My processing was to upload a .csv file into the wwv_flows_files table, and write a file back to my Oracle directory using the UTL_FILE api. Upon inspecting the file produced, a newline character was inserted after every line.

To cut a long story short, a colleague of mine came up with a workaround that involved parsing the newley produced file, removing any spurious lines and creating a new file minus the blank lines. As you can imagine, for large files, this added a fair amount of time to the Upload process.

There is light at the end of the tunnel however.

This article was sent to me by another colleague. At the end there is a solution to the problem. Cheers Kris

"Oracle 10g includes extra open modes (rb, wr, ab) to signify byte mode operation. The "wb" open mode can be used along with the PUT_RAW procedure to prevent extra newline characters being added on a Windows platform"

I shall refrain from any Windows bashing as it would be too easy as the problem does not occur on Linux. If anyone else wants to jump in and have a dig at Microsoft, feel free.

Here is a link to the a thread on the Application Express Discussion Forum as well.

Reference JavaScript from the File System in Application Express

2007-01-05T06:07:00.000-08:00

The strangest thing happened the other day whilst performing an application import within Oracle XE. The import was successful but running the application however was less so.

Some of my pages contained a lot of JavaScript in the HTML Header section of the Page Attributes. On some of these pages, the import appeared to add a carriage return to one of the lines and thus breaking my script. The knock on effect was that my application ground to a rather unimpressive halt.

Without having the time to investigate why, I decided that it would be better to store all my JavaScript in a .js file on the server and write a reference to it on the pages where the scripts were needed.

Once you have created your .js file (I store in a sub directory if the /i/ path) on the server, you can reference these files by adding the following to the HTML Header of your page:

Storing your long JavaScript externally to your application also makes debugging a bit easier as well.

Goodbye Notepad, Hello Firebug

2007-01-05T05:30:00.000-08:00

For those web developers out there using FireFox,you have to install the Firebug extension. It lets you explore the DoM in far more detail than ever before.

Simply download and install the extension, restart FireFox and hit F12. You can then see all your HTML source, JavaScript, CSS etc. It also has a cool debugging tool for JavaScript that lets you step through line at a time.

I can't tell you how much time this extension has saved me this year. Get it, install it and give it a whirl. You wont be disappointed I promise.

Microsoft have released their Developer Toolbar for Internet Explorer which goes some way to providing the same functionality for the Microsoft die hards out there but its a million miles away from our friends over at Mozilla.

The New Year Blues

2007-01-04T01:19:00.000-08:00

Well, finally made it into work on the 3rd January and it's always tough after the holidays. However its not long to wait until the 13th January when I will be spending a week in Meribel. Hopefully we will all return in one piece this year as well. My girlfriend had quite a nasty accident last year in Austria which resulted in a large laceration to her leg and required several rather large stitches.

I digress however and just wanted to offer some advice for those who are struggling with the "back to work" bug doing the rounds. A colleague of mine told me yesterday that his girlfriend always has to have a holiday to look forward to. I thought this was a brilliant way to beat off those New Year Blues and so why not start planning your Easter getaway. Surly the prospect of another holiday is the best way to cure the disappointment of one just ending.........

Just a thought.