Unicode data extracts using Oracle SQL*Plus in Unix

Introduction

The normal data spooling method using Oracle SQL*Plus will use the locale setting to determine the output file encoding method. Typically we are only interested in data extracts using just ASCII characters, but if you need (for example) to use a unicode character as a data separator, then things tend to go wrong.

Consider the following SQL statement which uses the double dagger unicode character as a separator:-

SELECT 'FIRST_NAME'||unistr('\2021')||'SURNAME' from dual;

If we run this in an SQLPlus session we get the following..

FIRST_NAMEâ¡SURNAME 

 
(nb. I'm going to assume that you are using Putty as your terminal client)

 Setting your Locale in Putty

The data returned by the select looks like it's become mangled, but it's not. Your Putty session needs it's Window/Translation setting to be changed to UTF-8 before it can be displayed correctly.

Select UTF-8 translation in Putty

Now if we run this SQL again we get..

FIRST_NAME‡SURNAME

Similarly we can run the following SQL script using SQL*Plus to produce the file output.csv:-

set feedback off
set heading off
set term off
set echo off
spool output.csv SELECT 'FIRST_NAME'||unistr('\2021')||'SURNAME' from dual;
spool off
exit

The contents of output.csv will also display correctly using 'cat', but only because your locale settings in Putty are UTF-8.

NB. You can also copy the file back to your PC and open it using notepad (which handles UTF8 files correctly).

Scheduling with Cron

The spool script works OK when run interactively, but now let's schedule it from the cron. The script will be run using the machine's locale settings (probably ISO8859), and so SQLPlus will replace the unicode characters with question marks to keep it a valid ASCII format.

FIRST_NAME?SURNAME

But we can fix this by adding the following line near the start of our shell script:-

export NLS_LANG=.AL32UTF8

Oracle APEX - Soft Validation using Dynamic Actions

Introduction

It's often a good idea to have validation on your forms in APEX, especially when you have business rules that govern how data can be entered. On a recent system enhancement we wanted to add a 'target bonus' flag to a page, but only allow it to be set to 'Yes' if the 'rebate level' was not set to 'None'.

This is simple enough to do..

Create a Page Validation, with a validation type of 'PL/SQL', 'Function Returning Boolean', then enter code as follows:-

BEGIN
  IF :P4_TARGET_BONUS = 'Yes' AND :P4_REBATE_LEVEL = 'None' THEN
     RETURN false;
  ELSE
    RETURN true;
   END IF;
END;

But it turns out that it's not quite what the customer wanted. There may be instances were it's OK to break these rules, so in that case can we just warn the user by showing a pop-up confirmation box instead.

Confirmation boxes are a UI thing which needs to happen before the page is submitted, so this sounds like a job for a dynamic action.

Using Dynamic Actions For Validation

I deleted the page validation (above) and set about using jQuery and a Dynamic Action to perform the same field comparison, but this time at the browser level.

Adding a Confirm action for soft validation.

1. Create a dynamic action against your submit button and set the following details in the When section.

Event: Click
Selection Type: Button
Condition: Javascript expression
Value: ($v('P4_TARGET_BONUS') == 'Yes' && $v('P4_REBATE_LEVEL') == 'None');

2. Next add a True action for when validation fails..

Action: Confirm
Fire When Event Result is: True
Text: Target Bonus is not allowed while Rebate Level is 'None', continue?

3. Add another True action which will be used if OK is clicked on the confirm action..

Action: Submit Page
Fire When Event Result is: True
Request/Button Name: UPDATE

4. Finally add a False Action to perform a simple submit if validation passes..

Action: Submit Page
Fire When Event Result is: True
Request/Button Name: UPDATE

That's it!... you can make your own validation tests more complex in the dynamic actions javascript box using the standard comparison and logic operators.