Skip navigation.

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

Next ORCLAPEX-BE Meetup - Feb 2nd

Dimitri Gielis - Mon, 2015-01-19 17:30
Roeland organised a new APEX Meetup in Belgium on February 2nd.


This time no specific topic, everybody can get on stage for maximum 5 minutes to show something they did or to ask some feedback or help on a specific problem.

If Belgium is to far, no worries, there're meetups planned all over the world.

The next meeting in the Netherlands is on February 18th organised by Alex, Christian and Roel.

But as I said, many others, just check out http://apexmeetups.com for a more local event.
Categories: Development

Setting up your own local Oracle Development environment in less than 15 minutes

Dimitri Gielis - Sun, 2015-01-18 17:30
The fastest and easiest way to setup your own local Oracle Development environment is by using the "Oracle Technology Network Developer Day" Database Virtual Box Appliance.

It contains:

  • Oracle Linux 7
  • Oracle Database 12c (12.1.0.2) EE (CDB/PDB)
  • Oracle Rest Data Services (ORDS)
  • Oracle Application Express (APEX)
  • Oracle SQL Developer and Oracle SQL Developer Data Modeler
Step 1: Download and install Oracle VM VirtualBox

Step 2: Download the VM image that contains all the pieces you need

Step 3: Import the VM: File > Import Appliance

Done...

You can now start using the VM.

Some extra steps I did was adding a Shared folder and setup the Network connection


Once you add the Shared Folder open up a Terminal in your VM and type: "mount -t vboxsf Downloads /mnt"  (Downloads is my OSX Downloads folder and /mnt is my mount directory in VirtualBox), that makes it easier to transfer files to the VM.

Here's a screenshot once you run the VM:

The nice thing with this VM is that everything is ready to be used and it includes labs, so you can do some exercises too and there's even a reset script to put everything back how it was.

Exercises are available for: Database 12c (including JSON, XML DB, ...), SQL Developer, APEX, REST Data Services and Cloud Services.

Who said installing Oracle was hard? :)

Categories: Development

How to download a plugin from apex-plugin.com

Denes Kubicek - Sun, 2015-01-18 05:18
I am not sure how many times it has happened to me that I can't use my login for this site. Today, it is not possible to login into that application - again. I was trying to reset my password several times for both accounts I have there. I received the emails with a token, entered that token and after changing the password, nothing happens. I didn't even receive a message that the login isn't possible or wasn't successful. Simply nothing shows up. Maybe I am just not getting it and there is a step I am missing...

It is o.k. to track who downloads what and use the accounts for advertising. However, you should make sure your login and the password reset works. That is so simple.
Categories: Development

Generate nested JSON from SQL with ORDS and APEX 5

Dimitri Gielis - Sat, 2015-01-17 17:30
When creating web applications, at some point you will need to interact with JSON. Either you consume JSON or you need to generate it to be able to use that cool widget you found.
A few years ago when I wrote about interacting and customising charts, XML was the data format. Today JSON is more common to use as it works so well with JavaScript. I needed to pass some nested JSON - here's an example: multiple customers have multiple orders and an order consists out of multiple items: 
What is the easiest way to generate that JSON data?
Luckily we have Oracle REST Data Services (ORDS), it literally is just one SQL statement!Here you go: 

Save and presto you're done :) Hit the TEST button and copy/paste the generated JSON in for example JSONLint to validate the output and yep we have valid JSON.


But that is not all - it gets even better. APEX 5 comes with a new package apex_json which contains so many goodies, really a wonderful piece of code. The same SQL statement I call with the package and again I get the JSON I need. It's slightly different from what ORDS generated - ORDS has an items tag surrounding the data, but again it's valid JSON.Note that APEX 4.2 has some JSON support too (apex_util.json_from_sql), but it doesn't go as far as the package you find in APEX 5.0 - for example the cursor syntax is not working there, but for straight forward JSON it does it job.

So this shows how easy it is to generate JSON these days. It has been different where we had to use the PL/JSON package (which was good too, but build-in is what I prefer).

And finally with Oracle Database 12c, and the JSON support straight in the database, consuming and querying JSON has been made so much easier, but that is for another post. If you can't wait, the link will bring you to the official 12c Database JSON doc.
Categories: Development

Understanding the APEX url - passing variables to items and (interactive) reports

Dimitri Gielis - Fri, 2015-01-16 17:30
Did you know you can recognise an Oracle APEX application through the url?

Here's the syntax:

f?p=appid:pageid:session:request:debug:clearcache:params:paramvalues:printerfriendly&p_trace=YES&cs=...

Let me give some examples:

  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:::::

The first part: https://www.apexrnd.be/ords/ depends on your configuration. I'm using https, my domain name is apexrnd.be and I'm using the Oracle REST Data Services as connection to the database. This is a configuration in your webserver.

The next part is f?p= f is a procedure in the database with as paramaters (p=). f comes from flow - once APEX was called "Project Flows".

209 is my application id, 12 is my page id and 12351378808570 is my session
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:CSV::::
The first part is the same, but now as request I've CSV, page 12 is an Interactive Report and the fastest way to see your IR data as CSV is to give CSV as the request in the url
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:IR_REPORT_BASIC::::
I changed the request again to the name of a saved Interactive Report (Alternative) IR_REPORT_: so the page goes straight to that layout of the report.

  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570::YES:::
Here I'm calling the page in DEBUG mode
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:::::
In the clear cache section you have a couple of options, specifying the below will clear the cache for : - APP: whole application- SESSION: current user session- RIR: reset interactive report to primary report- CIR: reset to primary report but with custom columns- RP: reset pagination- 12: page 12- P12_ID: item P12_ID
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:::P12_ID:1:
Here I pass a variable with the url, we'll fill P12_ID with the value 1You can use comma separated list of items and values for example: P12_ID,P12_NAME:1,DIMITRI
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570::::IREQ_CUST_LAST_NAME:Dulles
If you want to filter an interactive report you can call IR??_:In the above case we set the customer last name = Dulles.There are many other parameters: IREQ_ (equal), LT_ (less), IRLTE_ (less or equal), IRGT_, IRGTE_ (greater or equal), IRLIKE_ (like), IRN_ (null), IRNN_ (not null), IRC_ (contains), IRNC_ (not contains).
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570::::::YES
In this case we run the page in Printer-friendly mode
  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570::::::&p_trace=YES
This url will generate a trace file on the system for this request.
You find some more information in the APEX Documentation or you can try online at https://www.apexrnd.be/ords/f?p=209:12:0:.
One remark: if you enable Session State Protection (SSP) you might not be able to call the parameters like this as it requires a checksum at the end which you have to calculate for example by using apex_util.prepare_url().

And finally, if you want more readable urls, some people did some posts about that, here's one of Peter.
Categories: Development

Master Detail - Detail in APEX with FOEX

Dimitri Gielis - Thu, 2015-01-15 17:30
In the previous post I talked about Master-Detail and that it wasn't that easy to do Master-Detail-Detail today declaratively in APEX 4.2 (and 5.0).
Below is a screenshot of FOEX (a framework build on top of APEX) and a Master-Detail-Detail in there.
You can see the live example at https://www.apexrnd.be/ords/f?p=FOEX_FDOCS:MASTER_DETAIL_DETAIL:0::NO:::

Here's a screenshot of the page behind the scenes:


At first it might seem complex, but it isn't. In FOEX you can put regions in different places on the screen (center, east, west pane etc.), so many regions are to control those areas.
The most important regions are the "List of Customers", "List of Orders" and "Order Items", those are the regions that you see on the first screenshot. The other region "Manage Order Items" is a Modal Dialog that comes when you want to add an order item.


My goal is not to explain FOEX in great detail here, you can read about it on their website, but basically they extended APEX with a custom theme, many (many!) plugins and a builder add-on (you see in the screenshot the "Create FOEX Region", so it's really like you are working natively in APEX. Here's a screenshot when you hit the button to create a FOEX region:


So almost natively you can build your Master-Detail-Detail, through their wizards.

I thought to mention this solution here as well, as although my first choice is to make simple and clean web applications, if you do want a lot of information on your screen (like in a master-detail-detail), and you like ExtJs (which is used behind the scenes), FOEX is probably one of the best choices you have.

APEX R&D is a partner of FOEX, so if you need some more info, don't hesitate to contact us.

Categories: Development

Master Detail (-Detail) in APEX

Dimitri Gielis - Thu, 2015-01-15 09:17
In the last posts we used following tables to show (Report) and edit (Form) the data:

Another way to work with this data is using a Master-Detail report/form.
In the master we have our customer info and in the detail the products he's interested in.

Oracle APEX provides a wizard to create a Master-Detail Form
You just follow the wizard to set it up:


By default the wizard will only show related tables, which is most logical - if you don't see your table, you probably don't have the FK relation on the tables.

You've some options for the layout - edit the records directly on the detail screen or as a separate form. Which one to pick? It depends... for small detail tables I would go for a Tabular Form, but for larger or more complex ones I would go with another Form. Tabular Forms are not the most flexible in APEX, but for now that is the only declarative option you have to edit multiple records at the same time. Alternatives are to use Modal dialogs, code your own custom solution or use a solution of somebody else. FOEX for example has a nice solution which I'll cover in the next post.

Tabular forms got improved only a little bit in APEX 5.0, but Modal Dialogs come native in APEX 5.0.  Tabular Forms will be more enhanced in APEX 5.1 which can then do master - detail - detail and it will also come with another solution - a new "Multi-Row Edit" region type - which could work well in this case.

You find the Master Detail result online at https://www.apexrnd.be/ords/f?p=DGIELIS_BLOG:MASTER_DETAIL


What if our tables were a bit more complex and we need Master-Detail-Detail today?
We would need to create our own custom "tabular forms", basically a report where we use the apex_item api... but that is for another post.
Categories: Development

Keep ready to test the final EA of APEX 5.0

Dimitri Gielis - Tue, 2015-01-13 13:04
Oracle is gearing up to release APEX 5.0... the final early adopter release (EA3) will be released soon. Over 6000 people participated in APEX EA2...


Here's the email of Joel Kallman:



As EA3 will be very close to the final release of APEX 5.0 many more people will probably join EA3, so keep ready for it! I look forward what color scheme people will create with Theme Roller and how they make the universal theme look like, fun guaranteed :)

Categories: Development

Using Shuttles in a many-to-many relationship (Form)

Dimitri Gielis - Mon, 2015-01-12 16:36
In the previous post I showed some options how you can represent a many-to-many table relationship in a report using the LISTAGG Oracle function.

In this post we will edit a record and see how we can represent the data in a Form and save the data back to the different tables.

First I create a Form on the main table (customers) by just following the wizards.


Next I'll add a Shuttle item to the page: P2_PRODUCT_IDS
The SQL statement for the LOV (List of Values) looks like this, so just like in a select list, the shuttle is able to show the name, but store the id. Note there's no where clause in the statement as we want to show all possible products on the left in the shuttle.

Finally for the item source value we can't use a Database Column as the data is in a different table, so we enter the select statement to get all the product ids for that customer. Note that the Source Type needs to be set to SQL Query (return colon separated value), so it returns 1 or more product ids.
The selected products will be shown on the right in the shuttle.


Here's how the Form looks like when we select John Dulles who's interested in two products (Jacket, Business Shirt):


When we move Products from left to right and the other way and hit Apply Changes we need to store those values.

Add a new Process after the build-in Process and call it Save Products with this code:

There're many ways to store the values, but let me walk you through this one.
We first store the selected products in an array (l_vc_arr2) which apex_util.string_to_table is doing.

Next we delete all (possible) records that are not selected. You could remove the last line in the where clause so all products for that customer are deleted (if you add all the selected ones later again), but if you're auditing that table your info in not correct as that person might not have actually deleted it.

I added some debug info in the process too.

Finally we loop through the array and check if the record already exists in our table, if it doesn't we add it. Again here you could not do the lookup if you are dropping all records in the delete statement and just add all selected again.

I typically have a condition on this request to not run when the request is Delete.

In the online example (click on the edit icon in the report) I dropped the Create and Delete buttons in the Form, but if you keep them and want everything to work, there're two more things you have to do:

-) For the Create - in the "Automatic Row Processing (DML)" Process (of the Customer table) you need to specify P2_ID in "Return Key Into Item" field so the next process (the one you see above) has a value for P2_ID (= customer id).

-) For the Delete - you need to add another process before the "Automatic Row Processing (DML)", so the child records get deleted first, before the automatic row process deletes the customer.

In the next post I'll give an example of working with this data in a Master-Detail form.
Categories: Development

Representing many-to-many tables in a Report

Dimitri Gielis - Sun, 2015-01-11 14:08
Consider my case: I've a customer table and a products table. Customers can select multiple products they want to have. The diagram look like this: Customers on the left, Products on the right and a table in between that says which customers like which products. Customers might have multiple products and products might be linked to multiple customers - so a many-to-many relationship.


There're many ways to represent this data in a report. Here's a SQL statement that brings the three tables together:

A default Report created on top of this SQL statement looks like this:


The same SQL in an Interactive Report gives us a bit more possibilities, you could break by Customer and show the products underneath for example:

But another technique I use frequently as well, is by using the listagg (analytical) function.
This function was introduced in Oracle DB 11gR2. Here's a great article that compares listagg with previous possibilities you had in the Oracle Database and also talks about the performance.

Here's my SQL statement:


This shows for every customer, which products they like as a comma separated list:

You could do the reverse as well; for a certain product get a list of customers. I found myself using the listagg function a lot over the years, so hopefully you also find it useful if you didn't know about it yet...

You find the above examples online here.

Next to learning about the LISTAGG Oracle function, I also wanted to give this example to show that although APEX can do a lot of reporting out of the box, the more you know SQL the more options you have to show your data. Long-live SQL!!!

Categories: Development

APEX and Font Awesome integration

Dimitri Gielis - Fri, 2015-01-09 17:30
In the previous post I talked about scalable vector images. In this post we will integrate Font Awesome in our APEX app, but the steps for the same if you would pick another library.

Step 1: we need to make the library available in our APEX application.

The easiest way to make the library available in your entire application is to add the following line in your Page Template:


How do I know it's that string you might ask. If you go to the Getting Started section of the library you chose it will tell you:

You're actually done now with the integration... You'll find some examples on the site to see the properties you can use (size etc.). The list of icons is also available here.

Here're some examples I use the image fonts for:

An image in a region

Sometimes I just want an image in a region, so I included the paypal icon (fa-paypal) and gave it a bigger size (fa-5x).


You see the result here:

Edit icon in a report

During the creation of a report region you can select the image you want, those are either images or text. Change the Link Icon to following:


Replacing the image will give your report immediately a fresher and more modern look




Icons in the Navigation List

For my navigation list I mainly use the "Vertical List with Subtext and Icon" list template.
By default it looks like this:

We want to say for every list item which icon we want to use. To do that edit the list template and change in the Template Definition:

#LIST_LABEL#

to:





Note: I changed it in both the Current as Noncurrent List Template.

Now we can define the icon we want to use in our List at Attribute 02:


The final step to make the list look nice we need to make the icon float left so it's next to the text and we make it white when you hover. As we use an icon font we can just change the color with some css (you can add that on your page or in your page template in the Inline CSS position).


This is the result:

You find the online example at https://www.apexrnd.be/ords/f?p=DGIELIS_BLOG:FONTAWESOME
Categories: Development

APEX and Scalable Vector Icons (Icon Fonts)

Dimitri Gielis - Thu, 2015-01-08 17:30
For a couple of years now webdesigners and developers don't use image icons anymore, instead we moved to scalable vector icons.

Before you had to create different images for the different formats and colours you wanted. Then to gain performance we created one big image with all those smaller images in it (called a sprite).
Next with some CSS we showed a part of the bigger image. A hassle...

In fact the evolution of using icon images you can perfectly see in APEX too. If you go to your images folder you will see many .gif files, all different icons:


In a later release APEX (till APEX 4.2) moved to sprites (see /images/themes/theme_25/images/), for example the Theme 25 sprite you see here.


The scalable vector icons (or icon fonts) solve the issues the image icons had. With the vector icons you can define the color, size, background etc. all with CSS. It makes building responsive applications so much easier, the icons stay fresh and crisp regardless of the size of the device and it's next to no overhead. This is exactly what APEX 5.0 will bring to the table: nice scalable vector icons, handcrafted and made pixel perfect by the Oracle team.

Image from Font Awesome 
In fact you don't have to wait till APEX 5.0 is there, you can add icon fonts to your own APEX application today.

There're many icon fonts out there, but here're some I like:

  • Font Awesome - One of the most popular ones and probably included in APEX 5.0 too (next to Oracle's own library)
  • Glyphicons - This library is for example used by Bootstrap
  • Foundation Icon Fonts 3 - Used by the Foundation framework
  • NounProject - I saw the founder at the TEDx conference and was very intrigued by it - they build a visual language of icons anyone can understand
  • IcoMoon - This is more than an icon library, you can actually create your own library with their app. When my wife creates fonts in Illustrator, IcoMoon transforms them into a font.
  • Fontello - You can build your own library based on other libraries on the net (the site shows many other popular font libraries I didn't put above), so if you can't find your icon here, you probably want to build it yourself :)

In the next post I'll show you how to integrate one of those in your APEX app.
Categories: Development

APEX Conferences in 2015

Dimitri Gielis - Wed, 2015-01-07 17:30
In 2015 most APEX-only conferences are scheduled in history. I definitely recommend to attend at least one of the conferences as it's a great time to meet other people, industry experts and the APEX Development Team.

You'll find me at following conferences:

25-MAR-2015: APEX World - Rotterdam, the Netherlands

Every year a hit - and this year extra special as it will be at a different location - the beautiful SS Rotterdam. It's not decided yet which topics I'll present in the Netherlands.



9/10-JUN-2015 - APEXConnect - Düsseldorf, Germany

It will be my first time at a conference hosted by DOAG. I've heard so many great things about the excitement there is in Germany for APEX, so I look forward meeting the people over there.

On the 10th at 11h I'll present about the integration of APEX and Microsoft Sharepoint.



21/25-JUN-2015 - ODTUG KScope - Hollywood, Florida

I attended KScope for the first time in 2006 and since then returned almost every year. One of my most favourite conferences for sure. On Sunday the APEX Development is hosting the APEX Symposium: a full day of talks by the development team themselves. It also gives you an opportunity to meet them and many other experts in a nice informal setting.

This year these are my talks:

  • A primer on Web Components in APEX
  • How to make APEX print through node.js




There are some other conferences with great APEX tracks, for example GLOC, Collaborate, DOAG, UKOUG, ...

Next to the conferences there are many local APEX Meetups too. It's a totally different setting and concept compared to a conference, so definitely something to check out too. You can take your code to the meetups and ask questions or show what you did yourself.

You'll find me at most (if not all) of the Belgium APEX Meetups and I might attend some other meetups or conferences depending my schedule.



Look forward seeing you at one of the conferences or meetups.

Categories: Development

Generating sample data for your APEX application

Dimitri Gielis - Tue, 2015-01-06 17:30
You need some sample data sometimes too? When I'm showing some new concepts at a customer or when I'm doing some training I just want some "random" data. Well, it's not really random data, it's a specific type of data I need depending the column and it should be a text that is somewhat meaningful and not hard to read like "1RT3HFIY".
When my wife is doing design and lay-out and she needs text, she's using Lorem Ipsum. In fact it's build in the Adobe tools she's using and the text looks readable (although it isn't). It would be so cool if for example SQL Developer had that feature "populate my table(s) with sample data" (even keeping relationships into account).
Before, I used data from all_objects or generated data with dbms_random and a connect by clause for the amount of records I wanted, but it wasn't ideal. I also looked at scrambling my data, which is nice because existing relations keep intact, but for me it didn't really work nicely if I needed to scramble a lot of columns. There're some companies having solutions for generating/scrambling data too, but below I want to share what I'm currently doing.
Go to generatedata.com and enter the definition of your table and which kind of data you want per column.

Once the definition is there you can define how you want to receive the data. I found the SQL tab didn't really work well, so I use CSV as output.
Next in Oracle SQL Developer I right click on my table and say "Import data" and select the csv.It automatically knows the format etc. and maps it correctly to my table. Hit Next and you have your sample data available :) 

You can also load the data straight from the Data Workshop in APEX.


Categories: Development

Highlight negative numbers in an APEX Report (css only)

Dimitri Gielis - Mon, 2015-01-05 17:30
Here's a screenshot of the result we want: the negative numbers are highlighted in red.

There're many ways to achieve highlighting certain areas in a report, but depending the complexity of the logic that defines what gets highlighted I use one of the following three techniques:
  1. CSS only
  2. CSS and a Dynamic Action with one line of JQuery
  3. CSS and a column in the SQL query that defines the class
In this post I will explain the first technique, the other two are for future posts.
CSS only solution to highlight a negative number
Create a classic Report (the same technique works for an Interactive Report). Edit the number column(s) you want to turn red in case it's negative and modify the Column Formatting as below.

I'm wrapping a span around my column (AMOUNT) and use the HTML5 data- attribute to store the number in that attribute. Doing this will allow me to use a CSS selector to see if it's a negative number or not. Unlike JQuery, CSS doesn't have a :contains selector, if it did, we didn't have to create the extra data-number attribute.

In the Page Attributes, in the CSS section we add following inline css (note you can add this to your Page Template too, so it works for all pages):

What the CSS is doing: every span with as attribute data-number that contains a - (dash - which means it's a negative number) we give the color red. 
That's it...

You find the online example here: https://www.apexrnd.be/ords/f?p=DGIELIS_BLOG:REPORT_HIGHLIGHT_CSS

Categories: Development