Generating sample data for your APEX application

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.

Highlight negative numbers in an APEX Report (css only)

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

Creating a new APEX application

Previously I announced I would start a chain of blog posts related to Oracle Application Express. Next to the blog posts I'll build up a supporting APEX application where you can see the result of what I explain in real-time.

So, to start the chain I'll create a new APEX application. Almost in every project I start my APEX application the same way. Note that the screenshots are from APEX 4.2.6, as of the time of writing, that is the current latest production release, but I'll mention how things change with APEX 5.0

First I create a APEX application with the wizard (Create > Database application > Next > Next > Next > and use following settings for the Application Attributes:

For my navigation in the application I use Lists. Tabs are old school, so in this screen I changed the default One Level Tabs to No Tabs. If you like Tabs you can always apply a Tab template to your List. Note that in APEX 5.0 this is standard behaviour - APEX will create a "Navigation List" which you can give any look and feel you want.

The date format I filled in too. I like the DD-MON-YYYY format as there's no discussion what the format is (unlike dd/mm/yy or mm/dd/yy etc.)

In APEX 4.2 I start from Theme 25 as that is the responsive theme, so it will adapt to the type of screen it will be rendered on.

In APEX 5.0 I would go for the Universal Theme. That will be responsive and fully customisable through Theme Roller, but I will blog more about that once APEX 5.0 EA3 is released.

Next I give the application an alias (in Application Attributes), in my case DGIELIS_BLOG. That allows me to reference the application in an easier way than to remember the id of the app. 

I'll always create two pages: 

1. the Global Page (Typically Page 0) which will serve the content that needs to be available on every page for example my List for navigation and my Breadcrumbs region.
2. the Feedback Page, so people can immediate feedback of my application. This feedback will be captured in Team Development, a fully integrated project management feature of APEX.

The Feedback Page I customise further:

The Application and Page ID I make hidden as they are of less use for the customer. The A, X, Y items I delete. The APEX Development team created those items to align the items better (start-stop table), but with the responsive theme 25 they are not necessary and just overkill. I hope in APEX 5.0 those items are not there anymore.

Finally I give the buttons a template (button Template) and make the Submit button hot.

One of my best practices is to make at least one button per page "hot" - the button that is likely to be used most of the time - as that makes the page more lively and user friendly.

The last step for my new application is to create my main navigation. So I create a List (Shared Components > Lists). I create a static List with as name Navigation and I enter the Home Page as first item in the list. Note that in APEX 5.0 such a list will automatically be created for you.

I will add this list to the Global Page (0) - add new Region without Template and select the list.
If you like Tabs, you can chose the Page Level Tabs List for your List Template:

If you selected a List Template different than Tabs you are done, but if you want to see real Page Tabs, you need to read further.
Before those Tabs are visible we need to make sure our Page Template supports tabs.
As during the creation of the application we chose not to use tabs, the default Page template didn't include a "Tab space", so we need to change that.
Go to Shared Components > Themes - Edit Theme (Tasks) - and select for your Page: One Level Tabs:

Now if you place the List Region on Page 0 at Position 6 the List will show up as Tabs as you expect.

You find the app at https://www.apexrnd.be/ords/f?p=DGIELIS_BLOG.

Tomorrow we'll add a report and highlight the negative numbers (with just some css!).

2015 - "The" Year of Oracle Application Express (APEX)

The year 2014 was the 10th anniversary of Oracle Application Express (APEX). I still find it unbelievable 10 years have pasted. Time flew by... you might think that after 10 years of building APEX applications the technology is out-dated or you get tired of it, but more the opposite is true.

Oracle Application Express is a web technology and the web evolves fast, which keeps it interesting and fun. We can follow the latest and greatest in web world, integrate it with APEX and give our apps any look and feel we want. Next to that, APEX is build on top of the Oracle Database, so we can leverage all the functionalities of the database.

So the longer you work with the Oracle database and the more you know of web technologies, the more you can do in APEX. That's why I don't find it boring after 10 years - it's fun!

And now 2015 is just around the corner, so what about APEX?

The 5.0 release of APEX will go live in Q1 - it's the biggest release of Oracle Application Express in the last 10 years. It has the most advanced development interface in history. Every single page is updated within the Builder and it comes with a gorgeous new UI.
In short: it's the best release ever.

So the year 2015, will be "The" year of Oracle Application Express.

To celebrate "The" APEX year - I put myself a challenge ... on January 5th I'll start my chain of blogging and will do a new (APEX related) blog post every single day.  I hope to get a chain of at least a 100... let's see how far I get :) thanks for being part of it!

MS Sharepoint and Oracle APEX integration

At Oracle Open World I gave a presentation about the integration of Microsoft Sharepoint and Oracle Application Express (APEX).

I see a lot of companies using Microsoft Sharepoint as portal for their intranet. For many people it’s the first place they go to when they start their day. But to do their job they make also use of other applications, some which are build in Oracle Application Express (APEX). This presentation will show the different options you have to integrate both worlds of Sharepoint and APEX.

The integration can be both ways;

• in Sharepoint you get data or screens from APEX 
• in APEX you want to use data (or a screen) maintained and coming from Sharepoint. 

In the next weeks I'll add some more detailed blog posts, things I told during the presentation... for example how to setup your own MS Sharepoint environment.

APEX 4.2.6 patch available

Oracle released APEX 4.2.6 yesterday, it's also available from My Oracle Support as a patch set 19595896.

Here's the table of bug fixes.

Table 6 Bugs Fixed in the Oracle Application Express 4.2.6.00.03 Patch Set

Bug Number	Description
18393429	Importing pre-4.2 application with data loading results in runtime error
18609856	APEX_WEB_SERVICE.CLOBBASE642BLOB converts incorrectly
18658967	Corrupted HTML mail messages are created from APEX_MAIL.SEND
18719750	ORA-01858 during application import
18733989	Errors picking up session with cookie when TOP CALL is not F, SHOW, or ACCEPT
19021052	Oracle Application Express 4.2.4 throwing error while generating check boxes
19313700	Check box fails with ORA-06502 after upgrade from release 4.0 to 4.2.4
19482775	ORA-06502 when importing application containing translations

Applying the patch took less than 7 minutes.

This is probably the latest patch before APEX 5.0 hits the road...

SSH Tunnel in SQL Dev 4.0.3

Oracle SQL Developer and Oracle SQL Developer Data Modeler got a new version (4.0.3).

You can now setup an SSH tunnel in your connection - it saves you to setup a tunnel first.

This new release also feels a lot faster to me.

APEX 5.0: Bye bye Tabs, welcome to Navigation Lists

In previous versions of Oracle APEX (< 5.0) you could use Tabs for the navigation in your application.

Tabs were not that flexible, they were typical on top of your page in a specific look and feel. Since APEX 4.x I started to dismiss using Tabs in most of the cases, instead I would use a List with the "Page Tabs" template if people wanted that look and feel.

APEX 5.0 introduces the concept of a "Navigation List" that replaces the tabs. It's the same mechanism as before (a normal List which you find in Shared Components), but you can define in your User Interface which list to use as your Navigation List.

Go to Shared Components > User Interface Attributes:

Next in the User Interface section, click on Desktop (or the User Interface you want to adapt):

In the Attributes section you can define the List you want to use as "Navigation List"

Behind the scenes the Navigation List is put on the screen where the #NAVIGATION_LIST# token is specified in your Page Template.

The Navigation List is another example where APEX 5.0 makes common behaviour of developers more declarative and embedded in the product.

Interested in Oracle APEX in Belgium and around? - join ORCLAPEX-BE Meetup

Over the last months different Oracle Application Express (APEX) meetups were initiated around the world. You can read a bit of background on how the meetups started on Dan's blog.

I think it's a great idea to have local meetups to meet other people working or interesting in APEX too. When you just start with APEX it's interesting to talk to other people how they got started. But equally for more seasoned developers it's a great place to share ideas and have discussions about where the product and industry is going, all casual and in a relaxed environment.

That's why we started an APEX Belgium meetup group too. Our first meetup is planend on September 11th in the APEX R&D office in Leuven, Belgium.

Previously with the Oracle BeNeLux User Group (OBUG) we created one day APEX events, which were great, but had a fixed schedule. The APEX meetups we want to do more frequently and accessible to everybody - a very low entry. We'll try to make all those meetups free-of-charge and flexible in nature. We'll have a presentation about a specific topic, but equally important (or even more important) is that you can network, have a voice and get some help of peers.

I talked to the OBUG board too and they found it an interesting idea and are happy to sponsor the event. We can enjoy pizza thanks to OBUG :)

Every local meetup can decide how they organise their meetup.

Here are the details of the first ORCLAPEX-BE meetup on September 11th (2014):

The first Oracle APEX meetup will go over the concept of the meetups and as first topic APEX 5.0 will be covered.

We'll have an "Open Mic" too, so anybody can get on stage for 5-10 minutes and show what they did or share their ideas or ask for some ideas where they struggle with.

The meetup is completely free.

Agenda

• 16.00  Registration
• 16.30  Outline concept APEX Meet-up
• 16.45  Presentation APEX 5.0
• 18.00  Pizza break, Networking and "Open Mic"
• 21.00  End

Interesting in Oracle APEX and in Belgium or around? Join the ORCLAPEX-BE meetup now!
Can't make the 11th? Make you a member of the meetup and receive updates when the next one is :)

Look forward seeing you.

KScope14 (Sunday) - APEX Symposium - before noon

Sunday is typically the day where the Oracle development team is on stage to talk about what they do, give insight in the product etc. - for me personally one of the highlights of the conference.

The Awesome Evolution of Oracle Application Express 5.0 by Joel Kallman

Joel demoed HTML DB 1.6 again, the predecessor of APEX - went live in 2004, so 10 years ago (time goes fast!). Next he timed doing development in APEX 4.2 compared to APEX 5.0.  

Conclusion: APEX 5.0 is more productive, efficient, intuitive, modern and easy.

Joel highlighted the other new features in APEX 5.0, most of them I already blogged about or you find them here.

The Game Changed - APEX Designer by Patrick Wolf

Patrick showed the time it takes to build components and items in previous versions of APEX. Next he compared it to how you do things with the new Page Designer in APEX 5.0.

There're so many new features in the Page Designer, just give it a try to explore them. It will take a bit of time to get used to them, but it will be the future. 

Turbo Mobile Development by Marc Sewtz

APEX uses jQuery Mobile behind the scenes. APEX 5.0 includes the latest version (jQuery Mobile 1.4). The philosophy is to create "mobile first" applications.

APEX 5.0 will include a new mobile theme, which support the new jQuery Mobile swatches. It's very easy to use ThemeRoller to create your own swatch and upload the zip in Shared Components in APEX and add a style to the theme and make it active.

There are many more new features in APEX 5.0 for mobile development like for example a new region type called "Reflow table".

APEX 5.0 - Page Designer; immediate feedback and more

In APEX 5.0 you (can) develop in the new Page Designer.

The Page Designer makes you way more productive, less clicks and quicker results. You have to get used to it, and you probably want a big monitor (time to ask your boss!), but once all that is done - you will love it.

The Page Designer is so intuitive and attention was put in the details. When you make a mistake APEX gives you immediate feedback. Here's a screenshot:

The region where the error is, is highlighted.
You get a notification message top right in red with the error message and inside the property panel it's highlighted what you need to change. Once you click on the field it will give another text notification e.g. that it is required.

There's also the Messages tab which gives you an explanation of what is wrong. Clicking on the link will bring you right where you need to go.

But just look at the Page Designer for a while; notice the small top left red triangles; it identifies it's a required field. The "Show Common" and "Show All" tabs are great too.

So many things, small, large, ... but so useful.

Here's another one - Developer Comments for the page. If there are comments you see a number in the comment icon. When clicking on the icon you can add more comments. I believe it would also be useful to see the existing comments, hopefully that will be in the final release.

This post is based on Oracle APEX 5.0 EA2, but there's more coming it looks like. Linked to the previous feature, I see a tighter integration with Team Development already too.

So many things to explore in the APEX 5.0 Page Designer... definitely worth your time.

APEX 5.0 - Button Appearance (template options + Font Awesome)

With the new template, the new buttons for example are highly customisable in APEX 5.0

(icon on the left or right, spacing between, etc.)

In the Appearance section you find kinda the same options as in APEX 4.x, but when you look a bit further, there's so much more now.

You can declaratively change the way your button looks like, by clicking the Template Options:

The icons you can chose for your button are based on Font Awesome, a great scalable vector icons library.

I use Font Awesome in APEX 4.x too, but I had to create a new button template and put the name of the icon in the class section. This is now all integrated and declarative in APEX 5.0.

APEX 5.0 - Keyboard shortcuts

Develop even faster? use the keyboard shortcuts in Oracle Application Express (APEX) - you find them defined here:

Here's the list:

• Display From HereCtrl+Option+D
• Display From PageCtrl+Option+T
• Go to Dynamic ActionsOption+2
• Go to Gallery ButtonsOption+9
• Go to Gallery ItemsOption+8
• Go to Gallery RegionsOption+7
• Go to Grid LayoutOption+5
• Go to HelpOption+F1
• Go to MessagesCtrl+F1
• Go to Page Shared ComponentsOption+4
• Go to ProcessingOption+3
• Go to Property EditorOption+6
• Go to RenderingOption+1
• Keyboard ShortcutsOption+Shift+F1
• Page SearchCtrl+Option+F
• RedoCtrl+Y
• Restore/ExpandOption+F11
• SaveCtrl+Option+S
• Save and Run PageCtrl+Option+R
• Toggle Hide Empty PositionsCtrl+Option+E
• UndoCtrl+Z

For Mac users like me; the Option key is "alt". For the F1 etc. use "fn".

Oracle APEX 5.0 EA2 - first impressions

You can now request a workspace in the brand-new version of Oracle APEX 5.0 (EA2).

Once requested a workspace you will get an email to activate it - and you're up-and-running!

The login screen looks awesome:

And then you see the new APEX Builder - new theme, with all new icons:

The Application Builder looks different now too - look at the nice icons and new style of Interactive Report:

Creating a new application - the wizard is more streamlined:

Creation of a new page is now with a modal window implementation:

When finished it opens the page in the new Page Designer:

It looks like all the components are now available in the Page Designer (Shared Components for ex. wasn't available in EA1 - but it is now)

Creating new pages work well and the new universal theme (theme 42) looks nice too.

Oracle APEX went flat design, with bright color blue and grey and nice icons (which are available as a font).

More to come in other blog posts... have fun! and thanks to the APEX Development team for another great release.

APEX 5.0 EA2 available in the next days

Joel just blogged that the 2nd Early Adopter release of APEX 5.0 is around the corner.

Here are some screenshots posted on twitter:

I'm sure this new EA will carry many changes and looking at some screenshots it looks awesome.
I especially look forward to the new universal theme.

Here's what should be in - based on the statement of direction of APEX 5.0 :

Oracle Application Express 5.0

Oracle Application Express 5.0 will focus on both new features and enhancements to existing functionality to improve developer productivity and is planned to incorporate the following:

• Page Designer - New page definition IDE which incoroporates tree controls, drag and drop layout editor, and a property editor.
• Multiple Interactive Reports – Allow any number of Interactive Reports to be defined on a single page.
• Modal Dialog - Enhance the ability to declaratively define modal dialogs.
• Navigation Lists - Ability to define hierarchical lists for navigation, with pull-down menus and sub-menus, instead of being constrained by tabs.
• Mobile - Enhanced responsive tables, including reflow tables and column toggles, and introduction of panels.
• Calendar – New calendar region which allows duration based events, improved functionality, and better control over drag and drop operations.
• Universal Theme – A new central theme which readily allows developers to customize simply using CSS.
• HTML5 Capabilities – Improve native capabilities for handling HTML5 constructs.
• Application Builder Security – Allow different authentication schemes to be used to control developer access to the Application Builder.
• Numerous functional improvements.

Oracle APEX Cookbook: Second Edition

For the first Oracle APEX Cookbook I was involved as a reviewer.

Michel and Marcel updated their book end of last year, but I didn't take the time to blog about it yet - and months fly. The concept stayed the same as the first edition, but it got updated with the latest info for APEX 4.x.

"People who followed a beginner training or learned APEX at their own and they want to know how to do a specific thing which is covered in the book, it's great to have the book, as you can just follow what the authors wrote and you also have an idea why it's done like that."

If you need onsite Oracle APEX training, you can also contact my company APEX R&D :)

Social Authentication (Facebook) in WC2014Challenge

People expect these days from a public website you can authenticate with Facebook, Google+, Linkedin, Microsoft etc. It's very convenient as you don't need to create a specific account per website.

Background

All of the social networks have very good documentation how to call their APIs.
Here's for example the Facebook Login explained.

Most of the API's use the OAuth2.0 protocol, there's an application key and tokens that are send with the requests. Here's an overview how it works with Google+

So how easy is it in Oracle Application Express (APEX) to do such social authentication?

Unfortunately Oracle APEX doesn't provide us with a native social authentication mechanism just yet. But nothing prevents you from building it yourself.

Here are the options I reviewed:

• Custom build; in PL/SQL you call the different url's and make some procedures public so when the social network comes back you can intercept the call and move on.
• Oracle REST Data Services supports OAuth 2.0 and the calls are mostly REST calls, so I also looked into writing the logic in ORDS (and PL/SQL) and integrate that way with my APEX application.
• Some people in the community wrote an authentication plugin which does the hard work for you.

I went with a combination of the Facebook plugin in combination with my own PL/SQL code.
Peter was so nice to share his work with me, thanks again for that Peter. I first thought that the authentication plugin would be plug-and-play, just like the other APEX plugins... but that is not the case.
It hasn't much to do the way Peter's team implemented it, it has more to do with the complex setup of SSL certificates etc. So when downloading the plugin, know that it will take some time to configure it. Luckily Peter provides good documentation so it makes it a bit easier.

So, to see the authentication to work, login with your Facebook account on the wc2014challenge.com site. I extended the plugin a bit so it will automatically create a site account for you behind the scenes so regardless if you create a site account or login with Facebook it can hook up the scores, bets etc. in the same way.

Challenges with Social Authentication

If you want to provide Facebook, Google+, LinkedIn and a normal site account in your app, I found some challenges with that. How do you hook-up a person that logs-in with Facebook the first time, with the same player logging in with Google+ the same time? You could use the email address maybe? But what if they use different ones? There are many blog posts about this topic and how to get around it, but it would bring me to far in this post. I might do a follow-up post later as it's an interesting challenge.

Future 

I really believe that most public sites will allow social authentication, so I hope the team of ORDS or the APEX development team will make something available to do the social authentication natively in the future. I believe that would be the best solution (fast to implement and secure).

Security Audit of WC2014Challenge

A few weeks ago I asked my friends at RecX to do a security audit of the World Cup 2014 Challenge app.  The result was a security assessment document which explained what they tested, an explanation why it was important and the results they found. I found it very interesting to see how other (security) people approach your code.

Here are the areas they went into:

Access Control

• Hidden items
• Item Protection
• Page Access Protection

Configuration

• Session Timeout

Cross-Site Scripting 

• Column From LOV/Query (make use of )
• Direct Output
• Indirect Output
• Report Column Display Type
• Template Variables

Tip: make use of apex_escape.html, apex_escape.html_attribute, utl_url.escape

Data Protection 

• Page Autocomplete

Tip: Ensure sensitive data is not held in the browser cache

Warnings

• Direct URL

You can read more about security in their Hands-On Oracle Application Express Security book.

Thanks Nathan and Tim.

Automatic Time Zone Support in WC2014Challenge

How do you show to people in different timezones the schedule in their own time?

That is the issue I had when building the wc2014challenge.com site.

So I started to just show the schedule in the "local time" of the stadium the match was in, so I didn't have to deal with the issue :)

But as you might think, people started to ask to see the schedule in their own time.

In previous years I solved the issue by adding a select list, so people could select the timezone they wanted to see the game in. Behind the scenes I reran the query and added the offset to the time - that worked just fine. Now the challenge this year is that the Brazilian timezone exist out of two timezones, so I couldn't really use the mechanism of before.

In the Oracle database, instead of a date column, you can use a timestamp with timezone column and can better calculate the difference. Another way is to use the "timestamp with local timezone", so you see the data in your timezone (after alter session set time_zone = your timezone).

Instead of doing the timezone conversion, I also thought of doing it on the client (browser), with momentjs for example.

They all have advantages and disadvantages... but at the end I decided to use the native APEX way.

Step 1: make sure your column is of type TIMESTAMP WITH LOCAL TIME ZONE:

Step 2: set Automatic Time Zone to Yes in "Edit Globalization Attributes" (Edit definition of your app).

And you are done!

Looking at the schedule it shows the times in my timezone, automatically. The nice thing is that this is cross application, so the calendar automatically shows the times in your timezone too. Very, very nice - no additional code.

So it's very easy to make your APEX application time zone aware... the only drawback I find is that this solution requires a redirect the first time you hit the site. That is not really good for Google rankings, but the advantages weight way more than that for now.

You can also read Joel's blog about automatic time zone in APEX 4.0, he build another example which you can follow.