APEX-AT-WORK by Tobias Arnhold

Browsing "Older Posts"

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