Wednesday, September 30, 2015

Copy Theme Roller settings from one app to another in APEX 5.0.1

Just a note, more for myself than anything else. So I quickly can find it back, because somehow I keep on forgetting the command and then I'm spending too much time in finding it back.

So this is what you do when you've created a theme roller styling that you like and want to copy to another (or all your other) apps.
What I typically do is created a style in one app. When it's all finished and approved by the customer and I've gathered all signatures  - ;-) - then I run this command in the console of the browser.

Make sure your Theme roller is opened when you open your console of the browser!

> apex.utr.config()

As stated on the prompt, just copy that JSON file and go to the application where you want to import it.
Also there first open Theme Roller and then open the console of your browser.

Simply paste that line and hit enter.

Now your settings are loaded into the Theme Roller of your new app. Use Theme Roller (save as) to save your settings in the new app ...

This is APEX 5.0.1. In APEX 5.0 you have to make a small change to the text and add the "apex." in front when you have pasted it in the new app ...

Happy to share ...

Monday, August 3, 2015

Migrate your apps to Universal Theme in APEX 5

Hi and happy to be back.

Somehow I forgot what blogging was. But constructing a house, making it your home and moving into that new home took quite some time and above all energy. So blogging didn't have my highest prio for the last couple of months.

I know, it's just excuses, but as all that is none of your interest, I'll get on with the work :-)

So, I'm preparing a demo / workshop to show people what, how and why to migrate your apps to the Universal Theme (UT) once you did the technical migration to APEX 5.

This is all very well documented by the Oracle APEX Team, fow which many many thanks!
Here's just an overview of what I needed when doing my research. I'll build up the blog post along with my prep.

Migrating to Universal Theme

What is this UT?

So what is this UT and what's all the fuss about it?
Well, these insights from my point of view, not a commercial talk, nor a high-specialist talk. But an every day APEX developer and Oracle specialist.
I believe that my profile of developer is exactly why they have developed the UT to begin with.
I was trained by Oracle in the mid-nineties. This means PL/SQL, Oracle forms, PL/SQL, Oracle Reports, PL/SQL, Designer, PL/SQL and also some PL/SQL.

I have gradually picked up some HTML knowledge. Learned to deal with XML and more recently also JSON file formats.
I've upgraded my knowledge via self-study on CSS, Javascripting and I'm following some blogs on web-design in general. I try to keep up with the latest, which is not always easy. From the web-design/development point of view: I'm an old guy.

So UT for me exactly takes away those deep technical requirements that I'm not specialised in.
Still I urge you to have some knowledge of the web development skills mentioned above. But UT will help  you a lot!

And there is one more thing I like so much about the UT: 
as developer we can come up with great ideas of what an app can/should do. We're also capable of actually building just that. But what about the design, the user interface? It's often not our strongest skill. I know it's not mine. On top of that: I'm color blind. I literaly see things differently than other people.
Now with the UT, they can build the app and don't bother too much on the color schemes. Because it can be changed once you're done developing. Or during development. There even is a Theme Roller which allows you to change colors of the objects and it picks colors that fit together. More later, but ... honestly: WAW !

Other than all that, there are a number of reasons why you should migrate to UT. Or at least try to, because there are also some down sides. But all this is well explained in the Oracle documentation.

 So my first link: The Oracle Migration Guide.

How to decide to migrate or not?

Yeah, well, good question.
You may have good reasons to oppose against UT. Perhaps you have invested a lot in your own custom theme. With lots of nice looking stuff in there that works just as you want it to.
Although perhaps UT has those customizations built-in standard, it will take an effort to match and align them. Not even having spoken about the testing efforts after migration.

UT follows the latest stuff in web design. Certainly concerning navigation it is quite a change to the old style of navigation. But what if you actually like that? Perhaps you or your users are not so keen on change?

And another important - if not the most important - reason why not to migrate: you have invested already a lot in development of the app(s) in the first place. Now you have to invest in those same apps.

Bottom line: think about these things upfront and discuss them fairly with your boss or customer.
We as developer probably all wouldn't like anything more than to migrate. Don't forget it comes with a price tag!

Backup there ... slowly, one step at the time

Of course, first backup the app you want to migrate. And also of course, I'm assuming that the technical upgrade to APEX 5 has been successfully done.
One reason to take a backup is the obvious one: it's a backup.

But I hadn't thought of a second one myself until it was pointed out to me: once you have migrated your app to the new UT, it's a good idea to import the old one (with a new APPLICATION ID) so you can run both versions at the same time. And do all your tests.
If there is something you don't like, you can start off again from the original version and migrate all over again.
Probably a good idea to run each version in another browser.

Now let's take it step by step

Basically all I'm doing here is following the steps in Oracle Migration Guide!
So it seams a bit over the top to retype those steps here. and I don't want you to think that I came up with all those steps by myself ;-)
So open that migration guide in a different window or tab and leave it open!

Therefore again: The Oracle Migration Guide.
But there are some things you need to know about your old app before you migrate.
UT is far more grid orientated then the old themes. That is, if you had already a GRID theme to begin with!
So take some time to investigate on this.
Another very important one is the TAB-levels used in your old theme. UT doesn't use tabs anymore (thank you very much!) and it the Wizard is not capable of migrating apps that have multi-level tabs.
If this is your case, then first go and adapt the original theme to one-level tabs!

Follow Oracle's guidelines on the migration when switching from old to new.

Match template classes?

The migration guide tells you to switch this option to "No", without explaining why.
So what do I do? Yup, I left it to its default ("Yes").
And it all worked out fine. I was lucky? No, I wasn't ... I just had a very simple app to start off with.

However there must be a reason why they say to switch it to "No".
And there is. It's all the next step: the mapping of the classes.
APEX will try to help you with this mapping. But when this option was set to "Yes", it will only offer templates that are from the same class as the original ones. This will often NOT be the case and you will be left with quite some frustrations when migrating the more complex apps!
So keep control and believe Oracle there ... switch it to "No"!

Map your templates tot the UT templates

Although this is just a small step as described in the guide, take your time here. You even may want to try different versions of migrations in order to find the right settings.
It will save you a lot of time if you do the correct mapping here, rather then just picking one and you're left with a whole lot of individual changes on every page of your app to correct the situation.
Here once more the backup of the original app may come in handy!

Because this is such an important step, I'm taking some more time on it:

So here is a very simple example of an app created with the Blue responsive theme 25.
UT was not created yet in this application before I started.

And this is how the working app looks like in APEX 5 without having migrated it to UT:

So I create the UT in the application.
And I start the Switch wizard.

First attempt with the Match Template Classes set to "Yes" (against Oracle's advice).


Note the mapping options for Standard Region:

This actually is a reminder for you that you have the mapping option set to "Yes". Because see what happens if you go back one step and set it to "No".

You can actually just go back here with the "<" button.

Note the mapping options for Standard Region now:

Already here you should be convinced to set that parameter to "No". Actually, it beats me why the "Yes" exists in the first place and even more why that is set as default. But that's probably just me ;-)

And now comes the nicest part of this step. the Universal Theme Migration Helper.
Today (3rd of August 2015) it's up to version 1.1 supporting themes 21, 22, 23, 24, 25 and 26.
If your custom theme is based on one of these, then certainly pay attention to this section of the migration guide.
I have now added the Bookmarklet to my bookmarks (in my case the browser is Chrome):

For the more complex apps, this will make a huge difference in the mapping proposed by APEX. In my case there's not a lot of difference as my app is a very basic example.
But adding this Bookmarklet is an easy thing to do and you need to this only once. Well, for each browser you're using in case you use multiple browsers.

And this is what it looks like in APEX 5 in Universal Theme:

My conclusion

It's all relatively easy to do. But do your homework first.
Know where you start from.
And thoroughly test your migrated apps on look and feel AND on functionality.

Mind you, now you've only done a migration. You should investigate also if you can replace some custom stuff with standard APEX 5 + UT stuff. A lot of things are in there that weren't in the old themes (or APEX version). You've just taking the effort to migrate, now is the good time to look into the app to see if you can replace/remove some custom work. I strongly believe that it's way better to stick as much as possible to the standards of APEX!

And my advice: try to migrate. It will pay off in the end.
If you have many applications - may be even linked to one another - start with one and see if it still works all together.
And then take it one by one. Keeping in mind that the user interface changes. So apps that belong together, probably need to have the same UI feeling.
Grouping your apps is never a bad idea.

Theme Roller

I promised some more on the Theme Roller.
Well, following screenshots speak for themselves. Id didn't write one single line of code. I didn't know any of the names of the colors used. I just turned the wheel ... very easy to go and find (or create) your company color scheme. And the different styles can be saved and re-applied to other apps.



I'm not pretending this color combination is a good idea! Just wanted to point out how easy it is to change it ... :-p

 And if you're still not convinced, have a look here:

Thanks for sticking with me up to here :-)
and happy to share ...

Tuesday, February 17, 2015

A nice query to have when dealing with DATES in Oracle

Dates in Oracle. 

It's not always easy, whereas in fact it should all be plain and simple.
Only too often I've had problems with dates myself in the past. The other day I was asked by someone to have a look at his code and while debugging the source of the problem was ... yup, a wrongly interpretted date format.

So I thought digging up a query I made once when I gave a beginners training for SQL. This is a query that might put you in the right direction ... I hope it's useful for you!

The ':' is used for you to enter a date in the given format. You may need to replace this ':' depending on which query tool you're using.

select to_date( ':ddmmyyyy', 'ddmmyyyy') date_reference
     , to_char( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'day') weekday
     , to_char( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'IW') week_number
     , trunc( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'yyyy') start_year
     , trunc( add_months( to_date( ':ddmmyyyy', 'ddmmyyyy'), 12), 'yyyy') - 1  end_year
     , trunc( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'IW') start_week
     , trunc( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'IW') + 6 end_week
     , trunc( trunc( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'yyyy'), 'IW') start_week_one
  from dual

Happy to share!

Tuesday, February 10, 2015

Attachments in APEX Application

Uploading Attachments

So often it is not enough to be able to display data and to provide a good UI for data handling.
Every now and then you need to be able to store complete files in your DB.
That can be technical drawings or a paper that has a signature on it.

Whatever reason you have for it, I will show you two options how you can include the attachment functionality in your APEX apps.

Following examples are all built with APEX 4.2.x.
Things change in APEX 5. Roel Hartman blogged about it a couple of months ago.

Include the file in your main table

E.g. you want to support the possibility to attach a file to a screen.  One single file only.
I once had to do this to support a sort of workflow driven process.
In the process of data gathering, at some point to be able to move to the next status, it was required that a specific document was attached.

The page was built using an Automated Row Fetch and Automated Row Processing.
So there I opted to include the file in the dedicated table for that page.

Your table definition

You need to have some columns in your table, of course you can add them via an alter table statement if this requirements comes up during your development process.
Those dedicated columns are:

Column name Data type

In most cases those columns will be NULLS ALLOWED. But that depends on your functional specifications.

Example in the APEX page before the upload

The APEX solution

You will have on your page following page items:

Filebrowser P73_DOC_BLOB

Storage Type: I opted here for the Storage Type "BLOB", because this is the case where the file is stored in the table specified by Automated Row Processing.

MIME Type column: It is in fact not required that this column exists as page item on your page. I did it anyway, but made it hidden.
In this column the mime type of the uploaded column will be stored.

Filename Column: this is where the filename is stored. This can be easily retrieved via SQL to display elsewhere that a file has been attached. Possibly with a download link.

BLOB Last Updated Column: will contain the timestamp as used by browser caching to identify if the file has changed.

Download Link text: this text will be shown once the file is uploaded (see picture below)

Content disposition: (as copied from the APEX help info:) Content Disposition is used by the browser to decide if the downloaded content should be displayed inline within the browser window or if the open dialog should be displayed. Not all content will be displayed inline, it depends on the supported mime types.

This identifies that the file will be uploaded to the column DOC_BLOB of the table specified by the Automated Row Processing.

I added a Read Only condition. Once a file is uploaded, it will be shown in R/O modus. The page has a specific button to delete the file.

Example in the APEX page after the upload

Use a dedicated attachments table

A second method to store attachments is to make use of a dedicated table that stores all files.
you link the file to its specific function with a foreign key link.

This method will be often used when you must be able to add multiple files to a functional area.
You can think of a person-record in an HCM database for which the scanned ID-card must be attached, but also the (paper scanned) CV of that person. 

Example in APEX

As often, there are multiple ways to implement. You can make a separate page (perhaps a modal window) where you can upload as many files as you want.
But in a specific case I was asked that the number of attachments had to be limited to a maximum of 10 files. I opted to stay on the same page and implemented the following:

This comes down to the following region on my page. 
I admit that I would not choose for this implementation for more than 10 attachments. To be honest, I think now that ten is already too much. But the requirement started with 3 files max and I had already built the solution when the customer explained they needed "a small change on the attachment functionality" and they needed up to 10 files ...

The REMARK field is a simple text field on your page. Nothing special.
The ATTACHMENT field needs some explanation.

Filebrowser P27_ATTACHMENT1

This filebrowser needs less specification compared to the one explained above. But following are important:

The storage type now points to the table WWV_FLOW_FILES. This is a table used by APEX to store the files. It is intended as a temporary table and you should after upload move your files to your own table. Moving in our case comes down to copy to the new table and then delete it from the WWV_FLOW_FILES table.

To do so, you will need a separate page process.

Page process to upload the attachment

I had created a page process that fires upon page submit. But of course you can also add a button that triggers a dynamic action and performs the same logic.

How to deal wit WWV_FLOW_FILES

You can access this table by using your page-item (e.g. P27_ATTACHMENT1) that holds your file.
And that you use to copy (insert) the file to your own table:

insert into my_attachments_table
   ( doc_blob
   , doc_mime_type
   , doc_file_name
   select blob_content
        , mime_type
        , :P27_ATTACHMENT1
     from wwv_flow_files
    where name = :P27_ATTACHMENT1;

So that will be the first step in your process.
This must be immediately followed - as per good practise and advised by Oracle - by deleting the file from the WWV_FLOW_FILES table:

delete from wwv_flow_files where name = :P27_ATTACHMENT1;

A smart thing of course to do is to only execute this code if an attachment was added. Do this via a condition (:P27_ATTACHMENT1 must not be null).

Downloading Attachments

I've provided you with 2 options to upload attachments. But of course you must also be able to download them.

As shown in the first option of uploading attachments, when you use the BLOB mechanism of APEX, the download link is automatically created for you. Nice and easy! 

But you can also add a download link in a report. When you have some nice icons uploaded or you have integrated something like Font Awesome, you can even use a nice icon in stead of the "download" text to do this.

But here's a quick example:

The SQL behind it:
select att.attachment_id
     , att.proposal_id
     , att.remark
     , att.doc_file_name
     , substr( att.doc_file_name, instr( att.doc_file_name, '/') + 1) as short_file_name
     , dbms_lob.getlength(att.doc_blob) as doc_blob
     , null as delete_me
  from my_attachments_table att
 where att.proposal_id = :P14_PROPOSAL_ID

Two important columns for us now:
  • delete_me
  • doc_blob

Delete Me
Let me start with the delete option.
I hadn't spoken about it, but you will probably want to allow the end user to remove a file again from the database.
To do this, I've created a (hidden) page item to capture the attachment-id.
This item triggers a dynamic action (on change) that will remove the specified attachment.
And to change that hidden item (and by consequence trigger the dynamic action), I use a link on the column name with in the URL following javascript code:


This is what the column link looks like:

Do not forget to Display the delete (and download) column as "Display as text":

When you click the link, the javascript will fill the page item :P14_DELETE_ATT_ID with the value captured in #ATTACHMENT_ID#, which is the value of the specified line in your report.

Following mechanism will be triggered:

The PL/SQL to be executed is fairly simple:

(do not forget to submit the page-item)

The refresh will make sure your page is immediately refreshed with the new content (removed file).

Doc Blob
The DOC_BLOB contains your download settings.

This is the somewhat tricky bit. Specify following syntax in the Column definition's Number / Date format of your report query:

The Blob Column Attributes point to your (custom) attachments table:

Happy to share ...

Friday, January 23, 2015

Classic report with checkbox

When an action needs to be taken on specific records of a classic report, you need some kind of row-selector.
I came accross a situation where I couldn't used multi-row processing.
So I used the checkbox (apex_item.checkbox) in the SQL for a classic report.
The process fired at page-submit should now only process those rows that are checked.

This is not a difficult thing to do, just some logical steps you need to follow.

In this example you see a screen a supervisor can approve multiple holiday requests in one go for all the people in his team.

The supervisor can only reject a request by going to a detailled screen (clicking on the edit icon). But as a quick approve solution, the supervisor can select multiple requests and approve them in one go.

The checkbox in the first column is displayed with the apex_item.checkbox function:

In the header you notice a "select-all" / "de-select-all" checkbox. This works as a toggle function by editing the header of the column, entering following line:
 <input type="checkbox" title="Selecteer of de-selecteer alle aanvragen" onclick="$f_CheckFirstColumn(this)" />

The button "Goedkeuren" ( = approve button) performs a submit page. And so the process "Multi Select Approve" is launched.

The process needs to figure out wheater or not a line is selected. To play it nice, when you press the approve button, but no rows are selected, you get an error message.

You see that in the select statement in the first step, the internal ID that is used = 1: apex_item.checkbox( 1, p.proposal_id).
By doing this, I can refer to it on the processing side by using the function apex_application.g_f01.

So first thing that is done is to see if there are rows selected. If not the error message is posted to the screen and an error is raised. Hence the page processing is stopped immediately.
If at least one is selected we will go in a loop, performing the action - in this case launching the kick_proposal procedure - for each SELECTED row.

Here you go. Nothing too difficult, but can be handy in many situations.
Happy to share ....

Thursday, January 22, 2015

Logout-URL for APEX applications

Closely linked to my previous post is how to set the Logout URL (&LOGOUT_URL.). Because when working with linked applications, the Logout-URL will possibly need to redirect the user to the login application that contains some kind of portal page for the different applications.

Most often this URL is displayed as a link on top of every page, usely next to the login-user-name.

Setup is done via Navigation Bar Entries in the Shared Components section.

As target there are two available options: a Page in this Application or a URL.
In this case most easy solution is to opt for the URL, pointing to the variable &LOGOUT_URL. that is available throughout the application.

It's as easy as this. But now of course you want to know how to influence this &LOGOUT_URL.-variable.
Well, this is done via Shared Components > Authentication Schemes.

Pick the Current scheme and navigate to the Post-Logout URL tab.

This identifies the URL that the user will navigate to after having correctly logged out of the application. The first parameter after f?p= represents the application alias (or application ID) of the APEX application you want to return to. In this case the application I want to start form has an alias called MYTEST.

For information: the second parameter is the page ID (or alias) of that application.

That's really all there is to it.
Happy to share.

Monday, January 5, 2015

Multiple (linked) applications in one workspace

Shouldn't I start this blog post with the same sentence as I started all the e-mails I wrote today??
Well, yes: HAPPY NEW YEAR to you all and above everything else, I'm wishing you good health for 2015 and beyond!

This blog is about how to manage multiple linked APEX applications in a single workspace.
A workspace is considered a logical work area which is associated with some APEX applications (one or many).
It's no rocket science and has been done many times before. I'm just pointing out how I got around with the issue.
I'm using APEX 4.2.5 on an Oracle 11g database.

Question often is: how do I structure all that? Will I put multiple applications in one single workspace or do I create a workspace per application.

I believe you can't just say one option is better than the other.
Multiple workspaces creates a bit more maintenance work for a DBA, but it's probably nicer to structure your Application Landscape if you have many different applications to maintain for different business areas.
One single workspace is easy, also for the developer by the way. But it's easy to get lost in the list of applications that may or may not have things in common.

My general thought on the matter is to bundle logical applications in one workspace.
But of course, sometimes it may be that you start with one application. You add a second one that is not necessarily linked with the first one, but to go fast you didn't create a second WS. And before you know it you've got too many apps in one WS.
Fact is, if you want to link - in an easy way - from app 1 to app 2, it is far easier if they are in the same WS.
So for this blog, I started from the assumption that all applications are in the same work space.

I came accross a customer who's operating in one single workspace. The main idea is that some of the apps will be made available for their employees (internal only) as a self-service platform. In this case HR or Payroll related.

When I came into play, the whole set of apps already existed and I was extending the existing ones with new functionality. By doing so, I tried to introduce also somewhat more integration and structure  with a minimal effort.
So I also created a new Administrator module, used by the system administrators for declarative work. In this app I created some shared objects like List of Values. Other applications are subscribed to those LOV's to be able to use exactly the same values.

The main (already existing) app is considered as a home page or portal page as you wish.
I'm showing some screenshots, but in order not to invade my customer's privacy, I removed all things that may refer to the customer (any resemblance to actual individuals, or companies, or events are purely coincidental).
The following LIST is part of that main page:

Each of these links points you to a different APEX application.

To be able to make use of links between those applications, it's best to use Application Alias names. You could also refer to application-id's, but that is not my preferred way of doing things. You never know what may happen with ID's once you start moving applications from one environment to another (from DEVELOPMENT to TEST tot PRODUCTION).

Therefore, per application come up with a (short) Application Alias name. In the APEX Builder you edit your application definition and in the name tab, make sure to enter a short, but meaningful Application Alias.

In the example I was writing about, this application 150 is the so called home page. So a sort of banner was created that in APEX is displayed as an image-button that looks like this:

And that button was added to the Breadcrumbs region on Position 01 (you understand that this is just an example and can be put anywhere).

Now the actual thing to do concerning the subject of this blog:

The URL Target is the important part here. It's always structured in the same way and all parts are devided by ':'. But you don't necessarily need to provide all parameters. So keep it as short as possible, but know you can extend with parameters like you would do in refering to (by example) Interactive Reports or passing on bind variables ...

In this simplified case: "f?p=" is the starting point.
First parameter is your application alias (or if you insist the application ID).
Followed by the page you want to refer to in the given application.
Followed by the session, easily referred to as "&SESSION."
You URL Tagret could look like this: f?p=HOME:1:&SESSION.

Oh, you will not be surprised that you can refer to a Page Alias in the same way as you are refering to an Application Alias. But it's not so likely that those page-ID's are going to change. So personally I've rarely given an alias to a page. Perhaps the alias HOMEPAGE would be appropriate if you have a dedicated home page. But other than that I don't really see the point.

Well, as I told you ... no rocket science and no new mind blowing things here, but ...

Happy to share ...