Tuesday, 25 December 2007

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

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:
  1. Region Type: Report
  2. Report Implementation: SQL Report
  3. Title: Add Row to Report
  4. Region Template: Reports Region
  5. SQL Query: view
  6. 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:
  1. Select a Region for the Button: Add Rows to Report
  2. Position: Create a button in a region position
  3. Button Name: ADD_ROW
  4. Label: Add Row
  5. Button Type: HTML Button
  6. Action: Redirect to URL without submitting page
  7. Target is a: URL
  8. 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

Sunday, 16 December 2007

Mac OSX, Bootcamp and a Missing Hash Key

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

Thursday, 25 October 2007

Multiple Verison of I.E on one machine

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

Sunday, 1 July 2007

Custom Authentication / Authorisation Schemes (Part 1)

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

Sunday, 3 June 2007

Centrally Managed TNSNAMES and SQLNET

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.

Wednesday, 9 May 2007

Render Excel Spreadsheet in IE

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.

Credit for this one goes to my colleague David Blake as he did the lifting on this one.
  1. 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

Monday, 23 April 2007

Stop Page Submission When Enter is Pressed

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

Tuesday, 17 April 2007

Custom Error Handling in APEX

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

Monday, 26 March 2007

Flatten Out a Heirarchy using SYS_CONNECT_BY_PATH

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

Tuesday, 20 March 2007

Translate Columns into Rows (Subquery Factoring)

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

Tuesday, 13 February 2007

Where Did My PL/SQL Error?

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

Sunday, 28 January 2007

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

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.

Wednesday, 10 January 2007

Optimistic Locking Using ORA_ROWSCN

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.

Tuesday, 9 January 2007

New Line Character Using UTL_FILE

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.

Friday, 5 January 2007

Reference JavaScript from the File System in Application Express

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

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.

Thursday, 4 January 2007

The New Year Blues

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.