Browsing "Older Posts"

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");

Update 03.09.2018
This CSS style disables a textfield as well: style="pointer-events: none;"
Veröffentlicht von Tobias Arnhold Am 06:02
Kategorien:

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
Veröffentlicht von Tobias Arnhold Am 15:32

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.
Veröffentlicht von Tobias Arnhold Am 09:42
Kategorien:

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.
Veröffentlicht von Tobias Arnhold Am 06:03
Kategorien:

Oracle CONNECT BY Anzeige der maximalen Verkettung

Von Tobias Arnhold → 12.26.2014
Der Umgang mit CONNECT BY ist für mich immer wieder ein Highlight. Ob Positiv oder Negativ lasse ich mal außen vor. :)
Unbeachtet meiner Meinung ist es die sinnvollste Lösung um Baum-Verkettungen zu generieren.

Eine Anforderung die ich zuletzt gleich zweimal lösen musste war die Darstellung der finalen Ketten.

Beispiel Quell-Daten:
1:
1:2
1:2:3
1:2:4
1:6
1:6:1

Beispiel Ziel-Daten:
1:2:3
1:2:4
1:6:1

Das Ganze war recht einfach mit Hilfe einer analytischen Funktion zu lösen:
-- Tabele Definition
  CREATE TABLE "T_ROUTE_EXAMPLE" 
   ( 
    "ID" NUMBER, 
 "P_ID" NUMBER, 
 "NAME" VARCHAR2(50), 
 "TYPE" VARCHAR2(20),
 PRIMARY KEY ("ID")
   ) ;

-- Data
REM INSERTING into T_ROUTE_EXAMPLE
SET DEFINE OFF;
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('1',null,'Haus S','BUILDING');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('3','1','Switch_S_1','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('4','3','1/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('5','3','2/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('6','3','3/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('7','1','Switch_S_2','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('8','7','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('9','7','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('10','4','DHCP Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('11','5','orclapex DB Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('12','6','ORDS Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('13','8','WEB Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('14','9','AD Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('15',null,'Haus G','BUILDING');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('16','15','Switch_G_1','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('17','16','1/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('18','16','2/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('19','16','3/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('20','17','Win Client 1','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('21','18','Win Client 2','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('22','19','Mac Client 1','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('23',null,'Haus D','BUILDING');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('24','23','Switch_D_1','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('25','24','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('26','24','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('27','23','Switch_D_2','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('28','27','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('29','27','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('30','23','Switch_D_3','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('31','30','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('32','30','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('33','28','Mac Client 2','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('34','29','Mac Client 3','CLIENT');
Hier das Select um das entsprechende Ergebnis zu generieren: 
SELECT         
      PATH_LENGTH, 
      ID,
      P_ID,
      NAME,
      TYPE,
      ID_LIST,
      NAME_LIST,
      TYPE_LIST
FROM 
  (
  SELECT
        case 
           when instr(':'||LEAD(ID_LIST) OVER (ORDER BY ID_LIST)||':',':'||ID_LIST||':') > 0 
            then null 
            else 'OK' 
        end as FINAL_PATH,
        PATH_LENGTH, 
        ID,
        P_ID,
        NAME,
        TYPE,
        ID_LIST,
        NAME_LIST,
        TYPE_LIST
  FROM 
      (
      SELECT 
            LEVEL as PATH_LENGTH, 
            ID,
            P_ID,
            NAME,
            TYPE,
            SUBSTR(SYS_CONNECT_BY_PATH(ID, ':'),2) as ID_LIST,
            SUBSTR(SYS_CONNECT_BY_PATH(NAME, ':'),2) as NAME_LIST,
            SUBSTR(SYS_CONNECT_BY_PATH(TYPE, ':'),2) as TYPE_LIST
      FROM T_ROUTE_EXAMPLE M1
      start with M1.P_ID is null
      CONNECT BY prior M1.ID = M1.P_ID
      )
  )
WHERE FINAL_PATH = 'OK'
Veröffentlicht von Tobias Arnhold Am 15:23
Kategorien:

APEX, HTML und Jahresausklang...

Von Tobias Arnhold → 12.05.2014
Die letzten Wochen waren verdammt arbeitsintensiv und es wird bis Weihnachten nicht viel besser werden. Meetings, Telko's, PL/SQL, SQL, APEX alles verpackt in unterschiedlichsten Projekten.

Umso mehr waren die 3 Tage #DOAG2014 eine gute Erholungspause und eine Quelle für neue Motivation und Ideen.

Mein eigener Vortrag zum Thema "Dynamisches Arbeiten mit Grafiken in APEX" kam sehr gut an und zeigt einmal mehr das APEX Enthusiasten auch am letzten Tag 09:00 Uhr bereit sind sich weiterzuentwickeln. ;)

Für Alle die es verpasst haben oder gar nicht auf der DOAG waren. Habe ich eine Version der Anwendung auf apex.oracle.com hochgeladen: https://apex.oracle.com/pls/apex/f?p=78451

Wenn jemand noch Interesse haben sollte, kann er sich gern bei mir melden.

Auf der kommenden #APEXConnect werde ich ebenfalls wieder über Plugins berichten und werde meinen Fokus auf neue WEB-Techniken setzen.

Eine Technik wurde im letzten APEX Community Tipp bereits näher beschrieben: D3js" in APEX-Anwendungen integrieren

Selbst wenn Sie kein Interesse an der gezeigten Technik haben, so ist die beschriebene APEX Integration allemal sehr informativ und lehrreich.

Außerdem habe ich auf Twitter noch einer sehr interessanten Link zu den besten Javascript Erweiterungen in 2014 gefunden (Dank an ).

Das zeigt einmal mehr was heutzutage alles möglich ist und verleiht einem das Gefühl wieder rumspielen zu wollen, wie damals unter APEX 3 mit AJAX.

Ps: Denkt dran Morgen ist Nikolaus https://apex.oracle.com/pls/apex/f?p=25787
Veröffentlicht von Tobias Arnhold Am 21:11
Kategorien: , , , ,
Subscribe to: Comments ( Atom )