Friday, 16 August 2013

Oracle APEX 4.2 - Data Loading gets more fun!

In Oracle APEX 4.2, Oracle have very kindly extended the functionality on offer with the Data Loading page type. See my post on Data Loading as per Oracle APEX 4.1 which you can find here.

First of all we have some new transformation rule types. The options now include:
  • PL/SQL expression
  • PL/SQL Function
  • SQL Query( returning single value)
  • SQL Query (returning colon separated value)
This gives us much more control over the data coming in. The PL/SQL expression means we can now use any of the PL/SQL functions to change the values eg TO_CHAR, ADD_MONTHS etc or we could just call our own function eg my_function(:HIREDATE,:DEPTNO) - this would mean the function return value would become the new value for the column for which the transformation rule is defined. In my little example :HIREDATE and :DEPTNO are the column names of the uploaded data.

The second option means we can code in our own function body code eg:







If we created this transformation rule and uploaded the following data:

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
2,FRED,CLERK,7839,12-NOV-91,200,0,20

then on the final page of the wizard you would see the effect it has on the data ie as Fred's job is a clerk it gives him a 10% pay rise:



The final two options revolve around using SQL queries. The first returning a single value the second a colon separated list of values. For both of these the query needs to return a single column eg



The second enhancement, whilst not as exciting as the new transformation rules, allows up to 3 columns in the Look-up Definition to help uniquely identify the return column:



In the above example we are using bothe value in the DNAME and LOC columns to identify the DEPTNO value.

The final enhancement allows us to provide aliases for column names. Sometimes end users can have problems correctly identifying the columns to upload based on the table's column names. This feature now means we can define aliases for each column. These will be displayed to the end user while uploading the data. If created, the aliases will automatically be picked up by the application during the data column mapping phase.


No comments:

Post a Comment