Skip navigation.

Development

Easy quiz: rownum < NaN

XTended Oracle SQL - Tue, 2015-02-10 15:25

As you know, NaN is a “Not a Number”.
How do you think, what would be the result of the following query? (0f/0 == NaN)

select count(*) cnt from dual where rownum < 0f/0;

Answer
SQL> select count(*) cnt, 0f/0 from dual where rownum < 0f/0;

       CNT       0F/0
---------- ----------
         1        Nan

1 row selected.

[collapse]
Ok, when you know the result, try to guess what will return this query:

select count(*) cnt from dual where 1f/0 < 0f/0;
Answer
SQL> select count(*) cnt from dual where 1f/0 < 0f/0;

       CNT
----------
         1

1 row selected.

[collapse] PS

1 * X < 0 * Y

So we know now which non-negative values we should to substitute for X and Y in this expression, to make it true.

[collapse]
Categories: Development

select * from table where rownum=1

XTended Oracle SQL - Sun, 2015-02-08 17:40

I never thought I would have to optimize so simple query as

select col1, col2, col4, col7 from table where rownum=1

(even though I read recently “SELECT * FROM TABLE” Runs Out Of TEMP Space)
But a few days ago frequent executions of this query caused big problems on the one of our databases(11.2.0.3) because of adaptive serial direct path reads.

I don’t know why, but I felt intuitively that full table scan with “First K rows” optimization (“_optimizer_rownum_pred_based_fkr“=true) should turn off adaptive serial direct path reads. It seems quite logical to me.

PS. Unfortunately I had a little time, so I didn’t investigate what process and why it was doing that, I just created profile with “index full scan” access, and it completely solved the problem.

Categories: Development

Changing Target ownership after the discovery of Targets in EM12c

Arun Bavera - Thu, 2015-02-05 16:29
Building emcli commands to Change ownership of  Targets


select 'emcli change_target_owner -target="'||target_name||':host" -owner="EM_ADMIN" -new_owner="WEB_ADMIN"' from mgmt$target where target_type='host' and target_name like 'mypattern%'

Ref:
http://docs.oracle.com/cd/E24628_01/em.121/e17786/cli_verb_ref.htm#CACGFIDA

Categories: Development

APEX 5.0 Fixed Headings for Interactive Reports

Dimitri Gielis - Wed, 2015-02-04 17:30
When you click on the Attributes of your Interactive Report there's a neath little new features included called "Heading - Fixed to" with as options: None, Region and Page


We've got this request from many customers and it has always been a pain to get it working in every condition with APEX 4.x, but no hacking anymore - it's there now in APEX 5.0.
When you start to scroll the column header will move up and the content will scroll underneath it.

Here's a quick demo:

Categories: Development

APEX 5.0 EA3 - Universal Theme Styles - Gray!

Dimitri Gielis - Wed, 2015-02-04 16:17
In APEX 5.0 theme 42 is included - this is the Universal Theme. The theme comes with two styles: Blue and Gray.


The UI is so much improved in APEX 5, it's really a game changer. When you look at the details for example from such a style you will see it's using a Less file (Less is a CSS pre-processor) behind the scenes. So it's very easy to make changes to the CSS. But you probably even don't need to do that as the development team allows you to make customisations with ThemeRoller which generates JSON (another nice example where JSON is used - interested in JSON - I did some other posts on this blog about JSON).

To change the color for example, below you see the JSON for that.


Note that Theme Roller will generate the JSON for you. Accept from those customisations, the blue and gray style are using the exact same css.

From the Blue style many screenshots have been posted already, but I love the Gray style too, here's a screenshot when you switch the sample application's style to Gray.

Categories: Development

Developing On-Device Java Mobile Apps for iOS...and Android Too

Shay Shmeltzer - Wed, 2015-02-04 15:25

At the last JavaOne conference I presented a session titled "Developing On-Device Java Mobile Apps for iOS...and Android Too"

The recording of this session just became available, and I wanted to share it with you.

This session should be a good introduction to how Oracle enables Java developers to take their skills to the mobile world.

The first 28 minutes provide the overview, but if you are not into slides fast forward to minute 29 and start watching the extensive demo of developing an iOS application with Java and Eclipse. 

&amp;amp;lt;br /&amp;amp;gt;


Categories: Development

Moving Oracle Inventory from one location to Another

Arun Bavera - Wed, 2015-02-04 13:03
Moving Oracle Inventory from one location to Another:

cp -pR /home/emuser/oraInventory/*  /opt/app/oracle/oraInventory

sed -i 's|'/home/emuser/oraInventory'|'/opt/app/oracle/oraInventory'|g' oraInst.loc
sed -i 's|'/home/emuser/oraInventory'|'/opt/app/oracle/oraInventory'|g' orainstRoot.sh


grep -rl 'oraInventory' ./ | xargs sed -i 's|'/home/emuser/oraInventory'|'/opt/app/oracle/oraInventory'|g'

vi /etc/oraInst.loc
inventory_loc=/opt/app/oracle/oraInventory
inst_group=oinstall


[root@myhostname oraInventory]# ./orainstRoot.sh
Changing permissions of /opt/app/oracle/oraInventory
Adding read,write permissions for group,Removing read,write,execute permissions for world.
Changing groupname of /opt/app/oracle/oraInventory to oinstall.
The execution of the script is complete
 
Categories: Development

INDEX FULL SCAN (MIN/MAX) with two identical MIN()

XTended Oracle SQL - Wed, 2015-02-04 09:39

I’ve just noticed an interesting thing:

Assume, that we have a simple query with “MIN(ID)” that works through “Index full scan(MIN/MAX)”:

SQL> explain plan for
  2  select
  3     min(ID)      as x
  4  from tab1
  5  where ID is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 4170136576

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |     4 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |         |     1 |     4 |            |          |
|   2 |   FIRST ROW                 |         |     1 |     4 |     3   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TAB1 |     1 |     4 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ID" IS NOT NULL)

Test tables
create table tab1(id, x, padding)
as 
   with gen as (select level n from dual connect by level<=1000)
   select g1.n, g2.n, rpad(rownum,10,'x')
   from gen g1,gen g2;
create index ix_tab1 on tab1(id, x);
exec dbms_stats.gather_table_stats('','TAB1');

[collapse]
But look what will happen if we add one more “MIN(ID)”:

SQL> explain plan for
  2  select
  3     min(ID)      as x
  4   , min(ID)+1000 as x1000
  5  from tab1
  6  where ID is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 3397888171

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     4 |  3075  (17)| 00:00:02 |
|   1 |  SORT AGGREGATE       |         |     1 |     4 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IX_TAB1 |   999K|  3906K|  3075  (17)| 00:00:02 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID" IS NOT NULL)

At first I thought it was the old limitation from “MIN/MAX index access is not used if query has multiple MIN/MAX functions (Doc ID 316467.1)”, but we can get same plan by using hint:

SQL> explain plan for
  2  select/*+ index(tab1) */
  3     min(ID)      as x
  4   , min(ID)+1000 as x1000
  5  from tab1
  6  where ID is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 4170136576

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |     4 |  3433  (22)| 00:00:02 |
|   1 |  SORT AGGREGATE             |         |     1 |     4 |            |          |
|   2 |   FIRST ROW                 |         |   999K|  3906K|  3433  (22)| 00:00:02 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TAB1 |   999K|  3906K|  3433  (22)| 00:00:02 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ID" IS NOT NULL)

So, we can see that the real problem lies in the wrong cardinality calculation.

Of course, we can avoid this problem using the old method – with simple inner view:

SQL> explain plan for
  2  select x, x+1000
  3  from (
  4        select
  5           min(ID)      as x
  6        from tab1
  7        where ID is not null
  8       );

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2347179087

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  VIEW                        |         |     1 |    13 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE             |         |     1 |     4 |            |          |
|   3 |    FIRST ROW                 |         |     1 |     4 |     3   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN (MIN/MAX)| IX_TAB1 |     1 |     4 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("ID" IS NOT NULL)
Categories: Development

APEX 5.0 - Run Applications in New Tabs

Denes Kubicek - Wed, 2015-02-04 02:31
If you are using Firefox you will probably have an issue with running pages from APEX Builder 5.0 in new Tabs. Now, the links are working differently and normally clicking at button "Save and Run Page" will open a new window. In order to get it opened in a new tab, you will need a plugin Tab Mix Plus. Once you have it, you will need to change the settings there as shown in the screenshots below.



Categories: Development

Test Drive Oracle APEX 5.0 Early Adopter 3!

Patrick Wolf - Sun, 2015-02-01 05:49
It’s here! Oracle Application Express 5.0 Early Adopter 3 is available at https://apexea.oracle.com/ and we are really looking forward to get your feedback (via the Feedback link in the Builder)! A list of all new features of EA3 and a … Continue reading →
Categories: Development

APEX 5.0 EA3 released with Universal Theme

Dimitri Gielis - Sat, 2015-01-31 16:30
Today Oracle released the latest Early Adopter release of APEX 5.0.


The release is packed with new features, but one of the biggest improvements you can find in the UI.

For me the Universal Theme with Theme Roller, Theme Styles and Template Options is the absolute killer feature of APEX 5.0. I can't better describe Universal Theme as how Oracle is describing it:

Universal Theme is an all-new user interface for your applications that has been built from the ground up for Application Express 5. It is a simpler, yet more capable theme that does away with excessive templates and enable effortless customization with Theme Roller, Template Options, and Theme Styles. Universal Theme is the culmination of all of our UI enhancements and aims to empower developers to build modern, responsive, sophisticated applications without requiring expert knowledge of HTML, CSS, or JavaScript.



The issue with APEX 4.x was the build-in themes became out-dated today, so many of us looked at integrating Bootstrap, Foundation or other frameworks. But APEX 5.0 has send us at WARP-speed to a new UI, suddenly out-of-the-box your applications are modern, responsive, flat, ... everything you can expect from a UI today. Many new components are now part of the UI: like the new Carousel and Cards region are some of my favourites.

If you are interested to know everything about the new UI, Roel and I teamed up with a one-day APEX 5.0 UI Training day.

Happy playing with APEX 5.0 EA3 and don't forget to give feedback to the development team, this is the last time you can before APEX 5.0 hits production.
Categories: Development

Getting started with iOS development using Eclipse and Java

Shay Shmeltzer - Fri, 2015-01-30 16:05

Want to use Eclipse to build an on-device mobile application that runs on iOS devices (iPhones and iPads)?

No problem - here is a step by step demo on how to do this:

Oh, and by the way the same app will function also on Android without any changes to the code :-)  

This is an extract from an online seminar that I recorded for one of Oracle's Virtual Technology Summits - and I figured people who didn't sign up for that event might still benefit from having access to the demo part of the video.

In the demo I show how to build an on-device app that access local data as well as remote data through web services, and how easy it is to integrate device features too.

If you want to try this on your own, get a copy of the Oracle Enterprise Pack for Eclipse, and follow the setup steps in the tutorial here.

And then just follow the video steps.

The location of the web service I accessed is at: http://wsf.cdyne.com/WeatherWS/Weather.asmx?WSDL

And the Java classes I use to simulate local data are  here.



Categories: Development

Preparing architecture for APEX 5.0 upgrade

Dimitri Gielis - Thu, 2015-01-29 18:10
I doubted to set the title of this post to "Running APEX 4.2 and 5.0 in the same Oracle instance", but decided not to do that, but that is basically what I will do. Before going into details, I'll share my architecture.

In December 2013 I wrote it was time to update your APEX environment and I gave a quick overview of the architecture we're using. I thought it's time to review that post, so below you find how my preferred APEX architecture is today and tomorrow (once APEX 5 is production).


I'm using Apache as a reverse proxy in front of Tomcat. I'm not going in too much detail about which version to take: Apache v2.2 vs 2.4 and Tomcat v7 vs 8. There're many threads on the internet about that and I guess it depends your environment and your personal feeling. I've been using both versions but currently I'm on Apache v2.2 because it comes as a default with RHEL / OEL6 and SELinux is configured out of the box to protect the webserver. And for Tomcat I'm using v8 as that's the basis for the future versions of Tomcat (v9) and when you want to do Websockets for example, v8 has a more improved spec.

A few years ago we had the discussion about mod_plsql vs APEX Listener (now Oracle REST Data Services - ORDS). I think today, it's clear ORDS is the way to go as it gives you much more features and is proven technology.

For APEX I'm always on the latest version as fast as I can, as with every new release there're great improvements and fixes.

And finally the Oracle Database I'm on 12cR1 because I like the pluggable database concept and the other features it brings. I guess most people will go to 12c very soon, as 11.2 premier support ends this month. You can read more about when what is still supported in this doc.

But in this post I want to show you how easy it is to prepare your environment for APEX 5 and to test the upgrade with an architecture as above. I basically want to run APEX 4.2 and 5.0 next to each other. I'll clone my PDB and apply the APEX 5 installation on the new PDB. Next I'll configure ORDS so it knows to which database it needs to point to depending the url I'll call.

Step 1. Clone the PDB 

sqlplus / as sysdba

create pluggable database APEX50_PDB from APEX42_PDB file_name_convert=('/u01/app/oracle/oradata/cdb/APEX42_PDB','/u01/app/oracle/oradata/cdb/APEX50_PDB');


Step 2. Open the PDB and install APEX 5.0

alter pluggable database apex50_pdb open;

So now we have a new database which is a copy of our existing database open and ready to be used. Next we will need to install APEX 5.0. As the time of writing APEX 5.0 is not available yet, but it will probably be - connect to the new PDB and run @apexins... (follow the installation guide of APEX 5.0 once available!)


Step 3. Configure ORDS

With SQL Developer you can configure ORDS and add the connection to the new PDB.
In SQL Developer 4.1, first setup a connection to your ORDS (Tools > Manage REST Data Services Connections > Add Connection). Next open the ORDS Administration window (View > REST Data Services > Administration). Right click on the REST Data Services and Connect to ORDS:


You'll see the current configuration.

In order to connect to the new database we need to add a Database. Right click in Database Settings and add a new database. Before writing it back (the icon with the green up arrow - click the Test Settings button first (the icon with a v) to make sure everything is fine.

Final step is to let ORDS know that if we put in our url /apex50 we want to connect to the new database. You can do that by adding an entry in URL Mapping:


That's it...

Note: sometimes I've issues with adding the database and URL Mapping in SQL Developer, but it's as fast to do it command line too. The doc has a great example which commands to run: https://docs.oracle.com/cd/E37099_01/doc.20/e25066/config.htm#AELIG7191


Step 4. Test

When you navigate to your normal url e.g. http://localhost/ords/f?p=ABC you will see your APEX 4.2 instance, but if you navigate to http://localhost/ords/apex50/f?p=ABC you'll see the APEX 5.0 instance.

You can play a bit more with making it nicer urls or do some redirects in Apache, but I hope you get the idea how to start testing APEX 5.0 while still running APEX 4.2 too.

Categories: Development

Announcement: APEX 5.0 UI Training - May 12th

Dimitri Gielis - Tue, 2015-01-27 13:27

APEX 5.0 will be released between now and the end of May. People who have already spent some time on the Early Adopter versions know this version is packed with new features aimed to make APEX developers even more productive, like the Page Designer.

Another striking new subset of features is aimed at creating better looking user interfaces for your APEX applications in an easy and maintainable way.

The definition of user interface components in APEX 5.0 is very different to what we're used to. For example there is a new Universal Theme with Template Options and a Theme Roller.

To get you up and running with this new toolset as quickly as possible, Dimitri Gielis of APEX R&D and Roel Hartman of APEX Consulting have joined forces and set up a one day course fully aimed at APEX 5.0 UI. So if you want to know not only how to use the new Theme, but also how to modify it to fit your needs, this is the event you should attend!

The training will be at the Mitland Hotel in Utrecht (NL) on Tuesday May 12.
More information and registration see www.apextraining.eu.

If you are from another country and think this training should be available in your country as well, please contact us - then we'll see what we can do!

Categories: Development

Free Version Control Tool for PL/SQL is now available for download

Gerger Consulting - Mon, 2015-01-26 01:57
We are pleased to announce that Gitora, the free version control tool for PL/SQL, is available for download.
Categories: Development

EM12c Filtering ORA-600 till the issue is resolved

Arun Bavera - Fri, 2015-01-23 15:22



Example alert recieved:
Host=xyz.domain.net
Target type=Database Instance
Target name=mydatabaseinstance1
Categories=Diagnostics, Fault
Message=Internal error (ORA 600 [kwqitnmphe:ltbagi]) detected in /opt/app/oracle/diag/rdbms/hogcprd/hogcprd1/alert/log.xml at time/line number: Sat Jan 17 22:53:57 2015/40570.
Severity=Critical
Event reported time=Jan 17, 2015 11:08:38 PM EST
Target Lifecycle Status=Production
Department=OEM12c
Line of Business=IT
Location=Atlanta
Operating System=Linux
Platform=x86_64
Associated Incident Id=541447
Associated Incident Status=New
Associated Incident Owner=
Associated Incident Acknowledged By Owner=No
Associated Incident Priority=None
Associated Incident Escalation Level=0
Event Type=Metric Alert
Event name=adrAlertLogIncidentError:genericInternalErrStack
Metric Group=Incident
Metric=Generic Internal Error
Metric value=Errors in file /opt/app/oracle/diag/rdbms/xyxyx/xyxyxprd1/trace/hogcprd1_q002_31608.trc (incident=365969):~ORA-00600: internal error code, arguments: [kwqitnmphe:ltbagi], [1], [0], [], [], [], [], [], [], [], [], []
Key Value=Sat Jan 17 22:53:57 2015/40570
Key Column 1=Time/Line Number
Rule Name=OEM12c_ManagementAgentRuleset,OEM12c_Performance_metric_rule
Rule Owner=EM_ADMIN
Update Details:
Internal error (ORA 600 [kwqitnmphe:ltbagi]) detected in /opt/app/oracle/diag/rdbms/xyxyxy/xyxyxprd1/alert/log.xml at time/line number: Sat Jan 17 22:53:57 2015/40570.




Original:.*ORA-0*(54|1142|1146)\D.*
New One:.*ORA-0*(54|1142|1146|600:.*\[kwqitnmphe[^\]]*\])\D.*

 Reference: EM 12c : How To Filter Specific ORA-00600 Error In Order To Avoid ORA-600 Alerts to be Raised In Console ? [ID 1592764.1]
Categories: Development

Customizing EM12c Email Format for Notification

Arun Bavera - Fri, 2015-01-23 15:16
Customization highlighted below:
========================BODY=================================
-- Enterprise Manager Default Event Template

-- Subject of an e-mail is rendered in one line.
-- The resulting text from the following logic will be concatenated together into one line.

-- if this is a repeat email
-- show the repeat count
[IF NOTIF_TYPE EQ "NOTIF_REPEAT"]
    \[[REPEAT_LABEL] #[REPEAT_COUNT]\]
[ENDIF]
[EM_EVENT_PREFIX]:

-- if it is an email for success or failure of corrective action
-- show the name and execution status of the corrective action
[IF NOTIF_TYPE EQ "NOTIF_CA"]
    CA:[CA_JOB_NAME]:[CA_JOB_STATUS]
[ELSE] -- Regular email for metric alert
    [TARGET_LIFECYCLE_STATUS]:[TARGET_NAME]:=>[SEVERITY]
[ENDIF]
-- Show message if available
- [TARGET_NAME]:[ORCL_GTP_DEPARTMENT]:[MESSAGE]


===========================SUBJECT==============================

-- Enterprise Manager default template

[IF ORCL_GTP_DEPARTMENT NOT NULL]
 ===========================================================
 *****ITSM Please contact  [ORCL_GTP_DEPARTMENT]  Department for this Incident Alert******
 ===========================================================
[ENDIF]


-- if the email is for the status of corrective action
-- show the details of the execution of the corrective action
[IF NOTIF_TYPE EQ "NOTIF_CA"]
    [CA_JOB_NAME_LABEL]=[CA_JOB_NAME]
    [CA_JOB_OWNER_LABEL]=[CA_JOB_OWNER]
    [CA_JOB_STATUS_LABEL]=[CA_JOB_STATUS]
    [CA_JOB_STEP_OUTPUT_LABEL]=[CA_JOB_STEP_OUTPUT]
[ENDIF]

-- Source object name is the entity raising the issue.
[IF SOURCE_OBJ_NAME NOT NULL]
    [SOURCE_OBJ_TYPE] [NAME_LABEL]=[SOURCE_OBJ_NAME]
    [SOURCE_OBJ_TYPE] [OWNER_LABEL]=[SOURCE_OBJ_OWNER]
[ENDIF]
[IF SOURCE_OBJ_SUB_TYPE NOT NULL]
    [SOURCE_OBJ_TYPE] [TYPE_LABEL]=[SOURCE_OBJ_SUB_TYPE]
[ENDIF]
[IF HOST_NAME NOT NULL]
    [HOST_NAME_LABEL]=[HOST_NAME]
[ENDIF]
-- Target name links to the respective target home page
-- in Enterprise Manager console
[IF TARGET_NAME NOT NULL]
    [TARGET_TYPE_LABEL]=[TARGET_TYPE]
    [TARGET_NAME_LABEL]=[TARGET_NAME]
[ENDIF]


[IF CATEGORIES NOT NULL]
    [CATEGORIES_LABEL]=[CATEGORIES]
[ENDIF]
[MESSAGE_LABEL]=[MESSAGE]
[IF ACTION_MSG NOT NULL]
    [ACTION_MSG_LABEL]=[ACTION_MSG]
[ENDIF]
[SEVERITY_LABEL]=[SEVERITY]
[EVENT_REPORTED_TIME_LABEL]=[EVENT_REPORTED_TIME]

[IF TARGET_LIFECYCLE_STATUS NOT NULL]
    [TARGET_LIFECYCLE_STATUS_LABEL]=[TARGET_LIFECYCLE_STATUS]
[ENDIF]
[USER_DEFINED_TARGET_PROP]

[IF ASSOC_INCIDENT_ID NOT NULL]
    [ASSOC_INCIDENT_ID_LABEL]=[ASSOC_INCIDENT_ID]
    [ASSOC_INCIDENT_STATUS_LABEL]=[ASSOC_INCIDENT_STATUS]
    [ASSOC_INCIDENT_OWNER_LABEL]=[ASSOC_INCIDENT_OWNER]
    [ASSOC_INCIDENT_ACKNOWLEDGED_BY_OWNER_LABEL]=[ASSOC_INCIDENT_ACKNOWLEDGED_BY_OWNER]
    [ASSOC_INCIDENT_PRIORITY_LABEL]=[ASSOC_INCIDENT_PRIORITY]
    [ASSOC_INCIDENT_ESCALATION_LEVEL_LABEL]=[ASSOC_INCIDENT_ESCALATION_LEVEL]
[ENDIF]
[EVENT_TYPE_LABEL]=[EVENT_TYPE]
[EVENT_NAME_LABEL]=[EVENT_NAME]
-- if it is a repeat email, show the repeat count
[IF NOTIF_TYPE EQ "NOTIF_REPEAT"]
    [REPEAT_COUNT_LABEL]=[REPEAT_COUNT]
[ENDIF]
-- Event Dedup related Attributes
[IF TOTAL_OCCURRENCE_COUNT NOT NULL]
    [TOTAL_OCCURRENCE_COUNT_LABEL]=[TOTAL_OCCURRENCE_COUNT]
[ENDIF]
[IF CURRENT_OCCURRENCE_COUNT NOT NULL]
    [CURRENT_OCCURRENCE_COUNT_LABEL]=[CURRENT_OCCURRENCE_COUNT]
[ENDIF]
[IF CURRENT_FIRST_OCCUR_DATE NOT NULL]
    [CURRENT_FIRST_OCCUR_DATE_LABEL]=[CURRENT_FIRST_OCCUR_DATE]
[ENDIF]
[IF CURRENT_LAST_OCCUR_DATE NOT NULL]
    [CURRENT_LAST_OCCUR_DATE_LABEL]=[CURRENT_LAST_OCCUR_DATE]
[ENDIF]
[EVENT_TYPE_ATTRS]

[IF RCA_STATUS NOT NULL]
    [RCA_STATUS_LABEL]=[RCA_STATUS]
[ENDIF]

-- Root Cause Analysis details shows up when available. This is
-- normally applies to availability alerts for service targets
[RCA_DETAILS]

[RULE_NAME_LABEL]=[RULE_NAME]
[RULE_OWNER_LABEL]=[RULE_OWNER]
-- Check if any updates
[IF UPDATES NOT NULL]
[UPDATES_LABEL]:[UPDATES]
[ENDIF]
Categories: Development

JSON for APEX Developers (part 2)

Dimitri Gielis - Thu, 2015-01-22 17:30
In the previous post we created a service that allowed us to give our data in JSON format.
Now let's focus on consuming that JSON. In this post I want to show how to use JSON data in the client (your browser), in a future post I'll show how to use JSON on the server (in the database).
If you want to play with JSON, open the console of your browser and create some text in JSON format - easy to do - and use the JSON.parse() function to create an object from it:
var text = '{"items": {"emp":[{"empno":7369, "ename":"SMITH"},{"empno":7499, "ename":"ALLEN"} ]}}';var obj = JSON.parse(text);  
As you will probably do a call somewhere to get JSON, I'll move on with such an example, so we will call the service we created with ORDS in the previous post and we want to use that data in our APEX page.
So edit your APEX Page in the JavaScript section "Execute when Page Loads" and put
(note you can put your own url that generates the json):
$.getJSON("https://www.apexrnd.be/ords/training/emp_json/", function(json) {  console.log(json);});
We just called the url from JavaScript and output the result to the console of the browser so we see what we got back:

We see the JSON (javascript object/array) we get back from the url. Note that the array starts with 0 (and not 1).
We can now do anything we want with that data. Here we set some items on the page of the first employee:
  $('#P10_EMPNO').val(json.items[0].empno);  $('#P10_ENAME').val(json.items[0].ename);
A more interesting example is when you want to integrate Flickr foto's on your web page.The concept is the same, call a url, once received loop over the array (see .each) and create an image tag on the fly on your page:

Another example would be when you want to include a visualisation in your page and as data it needs the data in JSON format... You could do that with an AJAX call for example (application process, plugin, ...), but that is for another post.

Hopefully this post showed how you can interact with JSON within your APEX page by using JavaScript.You find the online example at https://www.apexrnd.be/ords/f?p=DGIELIS_BLOG:JSON
Categories: Development

JSON for APEX Developers (part 1)

Dimitri Gielis - Thu, 2015-01-22 15:30
After my post Generate nested JSON from SQL with ORDS and APEX 5 I got some requests to explain more about REST and JSON, so let me start with JSON. I'll go more into REST in some future posts.

JSON stands for JavaScript Object Notation, it's a text based format to store and transport data.

It all comes from exchanging data, and finding a format that can easily be used by the "client" who needs to do something with the data. In the past XML (and SOAP) was used a lot to fill that need, between tags you found your data. With JSON it's kinda the same, but because many "clients" are now web pages, it makes sense to use something that is very easy to use by a browser.

Here's an example of how it was with XML:


The above XML looks like this in JSON:
{"items": {  "emp":[    {"empno":7369, "ename":"SMITH"},    {"empno":7499, "ename":"ALLEN"}      ]}}
To generate the XML, Oracle build that straight into the database. Here's a SQL statement that does it for you:
SELECT XMLELEMENT("items", XMLAGG(         XMLELEMENT("emp",           XMLFOREST(             e.empno AS "empno",             e.ename AS "ename")         )       )) AS employeesFROM   emp e
To generate the JSON from within the Oracle database takes a bit more effort. You find some nice posts by Morton and Lucas with examples to generate JSON with SQL. If we use the listagg technique our query looks like this:

select '{"items": { "emp":[' 
       || listagg( '{ '
       ||' "empno":"'||empno||'"'
       ||',"ename":'||ename
       ||'} ', ',') within group (order by 1) 
       || ']} }'as  json
  from   emp

Oracle database 12.1.0.2 has JSON support, but that is more to consume JSON, not to generate. As said in my previous post, APEX 5 has a nice package to generate the JSON or you can use ORDS to generate the JSON.
Let's look step-by-step how we can generate the JSON by using ORDS.

In APEX, go to SQL Workshop > RESTful Services and hit the CREATE button and fill in the details as below:


Once you hit Create Module it has generated a REST Webservice, but more important for this post is that you have now a url that you can provide to somebody to get the data in JSON format:

There are many options for this service, but if you don't want Pagination, put a 0 in Pagination Size and if you don't run in HTTPS, put a No in Require Secure Access.

By running the url https://www.apexrnd.be/ords/training/emp_json/ we now see our data in JSON.
In the next post we will consume that data in our web page.
Categories: Development

Setting up Node and Oracle Database driver

Dimitri Gielis - Tue, 2015-01-20 17:30
Today Oracle introduced the node-oracledb driver, so you can easily connect from node to your Oracle database.
Previously I blogged about the Oracle VM you can download so you have your own local Oracle environment. Below are the steps to install the node driver and run a first example in that environment.
(Also read the official node-oracledb installation guide for a local database - 4. Installation with a local database).

First we need to install git (a source control system) - as the source of the oracle node driver is there.

$ sudo yum install git

Download the source of the node-oracledb driver

$ git clone https://github.com/oracle/node-oracledb.git

Next we need to get node, you can download it or I used wget to get the file for linux


Install Node

tar -zxf node-v0.10.35-linux-x64.tar.gz

We follow the guide to set the PATH variable

export PATH=$HOME/node-v0.10.35-linux-x64/bin:$PATH

and we set other variables to the Oracle client and run the installer from within the node-oracledb directory:


Depending your system it might take a few seconds and it should come back after a while with this:


So we have now node installed and the oracle node driver as a module available.

Next we run the first example. It's a select on a table in the HR schema, but that schema is by default locked. So we unlock it first:

sqlplus sys/oracle as sysdba



We need to change the dbconfig.js in the examples directory to point to our database:


And now we can run the first example:


The above lets you run SQL, PL/SQL etc. commands from within Node.

Happy playing with Node! But be warned, once you start with Node its addictive to try different node modules... for example run "npm install node-tts-api" and you have a node module to do text-to-speech :)

And this module is something you can call from within your APEX application (so we go the other way compared to above - we go from Oracle to Node in this case).
It takes only one Dynamic Action - on change of the item - execute Javascript:

var url = 'https://www.apexrnd.be/node/tts/' + $v(P13_TEXT);
$.get( url, function( data ) {
  var url = data;
  var snd = new Audio(url);
  snd.play();
});

And here's a small video that shows the TTS in action



If somebody knows a Node module with a women's voice I would love to hear that :)

The TTS demo is just a quick way to show the integrating of APEX with Node, but I've some more cool (and useful!) stuff coming up in future posts :)
Categories: Development