APEX-AT-WORK by Tobias Arnhold

Browsing "Older Posts"

APEX IR - Date Filter usage like String columns

Von Tobias Arnhold → 5.25.2012

If you want to filter date values like this:

Then extend the code to your date columns (inside the region source) like this:
My date column name is: IMPORT_DATE
select
...
decode(IMPORT_DATE,null,null,'' || IMPORT_DATE) as IMPORT_DATE
from
...

For more information take a look into this forum thread:
https://forums.oracle.com/forums/thread.jspa?threadID=2346579&start=15&tstart=0

APEX examples

The IN operator trap

Von Tobias Arnhold → 5.23.2012

If you look for workarounds when you reach the edge of the IN operator functionality then take a look here:
http://www.williamrobertson.net/documents/comma-separated.html

A very nice collection of different workarounds regarding the IN operator.

APEX examples

APEX character validation whichs shows all not allowed characters

Von Tobias Arnhold → 5.18.2012

This time I will present a solution for a character validation on the APEX item level.
Normally you create a simple validation based on a REGULAR EXPRESSIONS which checks for a set of characters which are allowed inside your item. If there are characters inside your item which are not in the list an error will be displayed. Unfortunately the end user doesn't know the exact character which is not allowed. If you have a text-box and you allow 500 characters then this will be a problem for the usability of your application.

Workaround
Create a new validation as:
"Function Returning Error Text"

declare
 v_text varchar2(16000) := :P1_TEXT;
 v_text_cnt number;
 v_text_err varchar2(1000);
begin
 select count(*) into v_text_cnt from dual
 where
 REGEXP_LIKE (v_text,'^[[:alpha:] .,-:;!?[:digit:]]+$');
 /* List of allowed characters */

 if v_text_cnt = 0 then
   select regexp_replace (v_text,'[[:alpha:] .,-:;!?[:digit:]]+'
      )   invalid_characters into v_text_err
   from dual;

   return 'Error. The following characters are not allowed: ' || v_text_err;
 end if;
end;

Advantage:
The end user can search for the wrong character and fix it himself.

I added a forum entry to it: https://forums.oracle.com/forums/thread.jspa?messageID=10237706

APEX examples

Different ways updating a table with sub select (join table)

Von Tobias Arnhold → 5.15.2012

/* 1. Standard sub query */ 
UPDATE tbl_employees x
   SET department = (
       SELECT y.name
         FROM tbl_department y
        WHERE y.dept_id = x.dept_id);

/* 2. Sub query with exist clause */
UPDATE tbl_employees x
   SET department = (
       SELECT y.name
         FROM tbl_department 
        WHERE y.dept_id = x.dept_id)
 WHERE EXISTS (
       SELECT y.name
         FROM tbl_department y
        WHERE y.dept_id = x.dept_id);

/* 3. Updatable view (needs foreign key connection) */
UPDATE (
SELECT x.department AS old_val,
       y.name AS new_val
  FROM tbl_employees x, tbl_department y
 WHERE y.dept_id = x.dept_id)
   SET old_val = new_val;

/* 4. Update trough merge statement */
MERGE INTO tbl_employees x
 USING (SELECT name, dept_id
          FROM tbl_department y ) y
    ON (y.dept_id = x.dept_id)
WHEN matched THEN
UPDATE
   SET x.department = y.name;

APEX examples

Shift Left example with Oracle SQL

Von Tobias Arnhold → 5.13.2012

I'm currently working on a integration of the jQuery Real Person (Captcha) plugin into APEX.

On the server side I had to execute a shift left. Which normally works like this:
5381<< 5
With Oracle SQL you can do like this:
select 5381*power(2,5) from dual;

Info:
I had some issues with really big numbers. The function acts different to my Firefox browser Javascript function.

Cloud Plugin Plugin Extension

Von Tobias Arnhold → 5.10.2012

I really like the Label Cloud Plugin by Carsten Czarski but a customer complained that there are not so much visual differences by words when the occurences are very similar.
For example you have 20 words inside your cloud:
10 words with a value of 1
04 words with a value of 2
02 words with a value of 4
02 words with a value of 8
01 word with a value of 10
01 word with a value of 12

What I did to get more differences into the cloud was a "font-weight:bold" for each second word and I added a padding to make the words not standing to close to each other.

How To:
Inside your label cloud region add a static id.
Static ID: MYCLOUD

Region Footer:

<script>
$('#labelcloud_plugin_MYCLOUD').find('span:odd').css('font-weight','bold');
$('#labelcloud_plugin_MYCLOUD').find('span').css('padding','2px');
</script>

APEX examples

APEX simple cookie example

Von Tobias Arnhold → 5.09.2012

This is an example for the use of a simple cookie which gets created after submit when click on a button. Afterwards a region inside my page shouldn't be shown anymore. The cookie itself is valid for 30 days and will save the value "1".

Page process:

begin
 owa_util.mime_header('text/html', FALSE);
 owa_cookie.send(
     name=>'APEX_AT_WORK_COOKIE',
     value=>'1',
     expires => sysdate + 30);

 apex_application.g_unrecoverable_error := true;
 owa_util.redirect_url('f?p=&APP_ID.:12:&SESSION.');
 exception when others then null;
end;

Update code from Patrick (for more information check the comments):

begin
owa_util.mime_header('text/html', FALSE);
owa_cookie.send(
name => 'APEX_AT_WORK_COOKIE',
value => '1',
expires => sysdate + 30 );

apex_util.redirect_url (
p_url => 'f?p=&APP_ID.:1:' || :SESSION,
p_reset_htp_buffer => false );
end;

The condition checks for the cookie and reads the value. If it is NULL then the region will be displayed.
Region Condition:

-- Type: PL/SQL Function Body Returning a Boolean
DECLARE
 cookie  OWA_COOKIE.cookie;  
BEGIN

   cookie := OWA_COOKIE.get('APEX_AT_WORK_COOKIE');

   if cookie.vals.First IS NULL then 
    return true;
   else 
    return false; 
   end if;

END;

Documentation:
http://docs.oracle.com/cd/B14099_19/web.1012/b15896/pscook.htm

APEX login example for more then one application:
http://www.oracle.com/webfolder/technetwork/de/community/apex/tipps/apex-mini-sso/index.html Update 19.01.2015
I case you use the cookie technique with the safari browser. Be aware that the first character is not allowed to be a sepecial character like space or comma. Otherwise the cookie stays emtpy.

APEX Competition Reminder

Von Tobias Arnhold → 5.08.2012

Less then one week is left and only 4 plugins has made there way to the competition:
http://www.apex-plugin.com/oracle-apex-plugins/odtug-competition.html
That is your chance to take the opportunity and win the challenge.

Good luck. Tobias

Using jQuery and Regular Expression to validate form fields

Von Tobias Arnhold → 5.03.2012

This is a simple example to validate an APEX item with regular expressions (on client side):

v_text = $('#P1_MY_ITEM').val();          
v_regex = /^[a-zA-Z0-9_ .,-:;!?&\(\)"\t\r\n]+$/;   
/* only number: /^[0-9]+$/; */
/* only character: /^[a-zA-Z]+$/; */
if(!v_regex.test(v_text)){
    alert('ERROR');
}else{
    alert('OK');
}

Important links to the topic:
http://www.jquery4u.com/syntax/jquery-basic-regex-selector-examples/
http://www.designchemical.com/blog/index.php/jquery/form-validation-using-jquery-and-regular-expressions/
http://mdskinner.com/code/email-regex-and-validation-jquery

APEX report column with %-sign

Von Tobias Arnhold → 5.01.2012

If you want to beautify your number column inside an APEX report with a percent-sign then most of you would do like this:
select column_name || '%' as column_name
from my_table

This creates the correct output but the sort will not work anymore as expected because a type conversion will automatically be applied. It is not longer a number column and the sort will be assigned as it would be a character column.
Luckily there is a easy workaround for this. Let the select as it is:
select column_name
from my_table

And add this under the "Column Attributes">"Columns Formatting">"HTML Expression": #COLUMN_NAME#%

That's it.

Update 09.05.2012 You might want to do the same with an Interactive Report then this trick could help you: http://cntrint.blogspot.com.au/2012/05/interactive-report-column-custom.html