Browsing "Older Posts"

DATE - First and Last day of a month

Von Tobias Arnhold → 1.29.2015
Simple task and simple solution:
-- First day: 
select TRUNC(sysdate,'MON') as first_day from dual
-- Alternative:
select TO_DATE('01.'||TO_CHAR(SYSDATE,'MM.YYYY'),'DD.MM.YYYY') as first_day from dual
-- Last day: 
select LAST_DAY(TRUNC(SYSDATE)) as last_day from dual
Use in APEX as computation for an empty field:

Cookie Applikation

Von Tobias Arnhold → 1.27.2015
Ich habe in den letzten Tagen an einer Mini-Anwendung gearbeitet: "Cookie Applikation".

Hintergrund:
Die Verwendung von Cookies in APEX ist nicht kompliziert, ist aber ohne die passenden Beispiele etwas mühsam umzusetzen. Deswegen habe ich an einer Beispielanwendung gebaut, die genau die Funktionsweise der Cookie-Nutzung erläutert und LIVE darstellt.

Zusätzlich dazu habe ich den Super Cookie (Canvas Fingerprinting) beispielhaft implementiert.

Also schaut mal rein: https://apex.oracle.com/pls/apex/f?p=155555

APEX ist überall

Von Tobias Arnhold → 1.23.2015
Obwohl APEX 5 noch nicht erschienen ist, ist die APEX Community aktiver denn je.

Hiermal eine Überischt der letzten Aktivitäten:

Frankfurt - 23.02.2015
Stuttgart - 22.02.2015
München - 21.02.2015
Colombus (USA) - 15.01.2015

Indien - 09.01.2015
München - 21.11.2014
Fühlt sich an als wäre der Start die DOAG 1014 gewesen:
Außerdem diese Woche am 27.01. in Nürnberg:
http://www.meetup.com/orclapex-nue/events/219081859

Und am 03.02.2015 in Düsseldorf
http://oliverlemm.blogspot.de/2015/01/inhalt-des-apex-usergroup-dusseldorf.html

Weitere aktive Meetupgruppen gibt es in:
Bielefeld - http://www.meetup.com/orclapex-Bielefeld/

Fragt man sich wo die anderen Standorte bleiben:
Berlin, Hamburg, Dresden...

Und nicht zu vergessen: APEX connect Juni 2015


In diesem Sinne allen ein schönes Wochenende..

Working with disabled textfields

Von Tobias Arnhold → 1.21.2015
In APEX you have the possibility to disable text fields.
Unfortunatelly you can not submit those fields after the value was changed by a dynamic action or some javascript code.
Out of a security point of view in most cases this makes sense.

In case you do have to change the value during the runtime and need to send it to the database. You have to pretend that the elements are disabled. Fortunately it is quite easy to do so.

1. First all textfields must be enabled.
2. Now create two dynamic actions using the following code snippets:
2.1 Disable all necessary fields "after page load":
$('#P1_ITEM_NAME').attr('disabled', 'disabled');
2.2 Enable all necessary fields "before page submit":
$("#P1_ITEM_NAME").removeAttr("disabled");

Oracle SQL regular expression - check for numbers or special characters

Von Tobias Arnhold → 1.16.2015
We all know the regular expression syntax is fast and you can do amazing things with little code snippets. But we do know as well that whenever you need it then you have no idea how to write it down. To be able to find a solution for your problem you use the WWW.

So here you have another example you may need one day. :)
I had to check a string for special characters and numbers. In those cases the rows should be highlighted.

Here is a simple example how to do it:
select 
 rn, 
 val as txt, 
 case 
  when regexp_like(val, '[[:cntrl:]]| |[[[:digit:]]|[[:punct:]]') 
  then 'Error' 
  else 'Ok'
  end as type
from 
(
 select 1 as rn, '123' as val from dual
 union all
 select 2 as rn, 'xxxxx' as val from dual
 union all
 select 3 as rn, 'acbAaaBBB' as val from dual
 union all
 select 4 as rn, 'acb Aaa BBB' as val from dual
 union all
 select 5 as rn, '#abc' as val from dual
)
order by rn

Calculate all unique combinations of one list (cartesian product)

Von Tobias Arnhold → 1.15.2015
A couple of days ago a had a task to combine a list with itself. The result should be a two column report of each possible combination.

What should not be part of the result:
- same element combinations should be excluded
- duplicate rows (even if the sides are switched)

Because I couldn't solve it myself *actually it's awful after I now know how to do it* I had to create a forum entry:
Oracle SQL and PL/SQL forum: Generate some kind of cartesian list
Time to try solving it myself (thinking/searching the www): 40 minutes --> Result: fail
Forum reaction time: 12 minutes --> Result: win (Thanks to Marwim and Karthick_Arp)

That's the way how it works:
-- Table name: MY_TAB
-- Columns: ID (VARCHAR2(100))
-- Insert
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.197');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.198');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.199');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.202');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.203');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.204');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.205');  
And here is the select statement:
 SELECT  
  S1.ID as A,  
  S2.ID as B  
  FROM MY_TAB S1  
  CROSS JOIN MY_TAB S2  
  WHERE S1.ID < S2.ID
Mostly important is the "<" it kicks all unnecessary combinations.

Working with XML files and APEX - Part 3: Detail elements in a row with OUTER JOIN

Von Tobias Arnhold → 1.08.2015
After I described how to successfully import XML files into an APEX application and gave some basic information about the analyzing of xml data.
It's time for another example. This time we want to get all detail elements of a master element in one row.
Example XML data:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>  
<data>
 <stadium> 
   <team name="SG Dynamo Dresden" league="3. Liga"/>
   <event name="Konzert"/>
   <event name="Stadionführung"/>
 </stadium> 
 <stadium> 
   <team name="RB Leipzig" league="2. Liga"/>
   <event name="Konzert 1"/>
   <event name="Konzert 2"/>
   <event name="Konzert 3"/>
 </stadium> 
</data>
As you see we have two detail entries for element <stadium>. What I want are two rows including all the data of element <stadium>. Of course this means taking off some flexibility from my selection. Because I need to know what data I will have to deal with.
In my case I assume maximum one team and three events per stadium element.

The magic sql statement:
-- table: IMP_FM
-- XMLType column: XML_FILE
select
    t1_team,
    t1_league,
    e1_event,
    e2_event,
    e3_event
from    IMP_FM nx,
        XMLTable('/data/stadium' passing nx.xml_file
                 columns   
                    "P_T1" XMLTYPE PATH 'team[1]',  
                    "P_E1" XMLTYPE PATH 'event[1]',   
                    "P_E2" XMLTYPE PATH 'event[2]',  
                    "P_E3" XMLTYPE PATH 'event[3]' 
                ) STAD,
        XMLTable('team' PASSING STAD.P_T1
                 columns   t1_team varchar2(200) path '@name',
                           t1_league varchar2(200) path '@league'
                ) (+) T1,
        XMLTable('event' PASSING STAD.P_E1
                 columns   e1_event varchar2(200) path '@name'
                ) (+) E1,
        XMLTable('event' PASSING STAD.P_E2
                 columns   e2_event varchar2(200) path '@name'
                ) (+) E2,
        XMLTable('event' PASSING STAD.P_E3
                 columns   e3_event varchar2(200) path '@name'
                ) (+) E3
where nx.id = 1
Inside the sql I first create a set of four detail xml columns per row:
columns
  "P_T1" XMLTYPE PATH 'team[1]',
  "P_E1" XMLTYPE PATH 'event[1]',
  "P_E2" XMLTYPE PATH 'event[2]',
  "P_E3" XMLTYPE PATH 'event[3]'

Next I select the data of each xml element via an OUTER JOIN.
XMLTable('team' PASSING STAD.P_T1
    columns t1_team varchar2(200) path '@name',
                     t1_league varchar2(200) path '@league'
) (+) T1,

Finally I only have to select the columns I want to see.
If I wouldn't use the OUTER JOIN syntax then no result would appear. Because Oracle handles the XMLTable integration as INNER JOIN.
As I told this example only works if you know the exact definition of your XML detail data elements.