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
  decode(IMPORT_DATE,null,null,'<!-- ' || to_char ( IMPORT_DATE, 'yyyymmdd' ) || ' -->' || IMPORT_DATE) as IMPORT_DATE

For more information take a look into this forum thread:
APEX-AT-WORK no image

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.  

Create a new validation as:
"Function Returning Error Text"
 v_text varchar2(16000) := :P1_TEXT;
 v_text_cnt number;
 v_text_err varchar2(1000);
 select count(*) into v_text_cnt from dual
 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;

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

 I added a forum entry to it:
APEX-AT-WORK no image

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 = (
         FROM tbl_department y
        WHERE y.dept_id = x.dept_id);

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

/* 3. Updatable view (needs foreign key connection) */
SELECT x.department AS old_val, 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
   SET x.department =;
APEX-AT-WORK no image

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;

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

APEX-AT-WORK no image

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.

Region Footer:
APEX-AT-WORK no image

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:
 owa_util.mime_header('text/html', FALSE);
     expires => sysdate + 30);

 apex_application.g_unrecoverable_error := true;
 exception when others then null;
Update code from Patrick (for more information check the comments):
owa_util.mime_header('text/html', FALSE);
value => '1',
expires => sysdate + 30 );

apex_util.redirect_url (
p_url => 'f?p=&APP_ID.:1:' || :SESSION,
p_reset_htp_buffer => false );
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
 cookie  OWA_COOKIE.cookie;  

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

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


APEX login example for more then one application: 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-AT-WORK no image

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]+$/; */
Important links to the topic:
APEX-AT-WORK no image

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: