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!

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
DOC_BLOB	BLOB
DOC_MIME_TYPE	VARCHAR2(100)
DOC_FILE_NAME	VARCHAR2(250)
DOC_UPDATE_DATE	DATE

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:

javascript:$s('P14_DELETE_ATT_ID','#ATTACHMENT_ID#')

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:

DOWNLOAD:MY_ATTACHMENTS_TABLE:DOC_BLOB:ATTACHMENT_ID::::::Attachment:download

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

Happy to share ...

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 ....

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.

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 ...

Base URL for EBS integrated with APEX

A question that is asked to me by many different clients running APEX application integrated with  the E-Business Suite: "how can I reference environment dependant things?".

It all comes down to the many environments that are bing used. Obvioiusly you have the absolute minimum: a Development environment (we'll call it DEV) and a Production environment (which we'll call PROD).
You can have a number of environments in between those, which have different names everywhere I go. For example you may have a specific "Project Environment" or a "User Acceptance Test Environment". You may also have an "Integration Test Environment".
All these environments have obviously a different URL-construction. It all depends on how your IT department is structuring its architecture. There are no strict rules ... it can be WHATEVER!

But sometimes you just need an exact reference to say where you can find things. Perhaps you're forced to use a CSS-strategy where IT wants you to store all CSS things in one place.
Or you have a specific logout-link that needs to point to the login URL of you application (in fact, being EBS or not makes no difference here). But for suer this URL is different in your DEV compared to the one in PROD!

So I came up some years ago with a solution for EBS integrated APEX applications. Using the available profile options.
This solution was built on an EBS 12.0.3 environment, but it still works also on later versions.
The APEX version it was first developped in was APEX 4.0, but it is still working on APEX 4.2.6 and it's using absolutely only standard APEX stuff. No rocket science whatsoever. So no reason to think why this would stop working once APEX 5.0 gets introduced.

Here are the ingredients and the recipe

• Application item AI_BASE_URL to store the base of the URL;
• Application process Init items after login to fill the Application Item;

and optional, just as examples:

• A list entry with path reference;
• Navigation Bar entry to your Home page;
• Navigation Bar entry to your Logout page.

For now I make the assumption that you have correctly integrated your EBS environment with your APEX environment. If you have done this, you will be able to use the secured views of EBS in your APEX app and you will be able to use the EBS API's as well. This is a must for this solution to work properly!

Step by step approach

In the Application Builder, navigate to the shared components to create the Application Item and the Application Process.

1. Application Item

Create a new Application Item. It is ALWAYS a good idea to use the prefex "AI" for an application item. It's just easy to recognize it every where in your code.

As the item serves to capture the base of the URL, I believe AI_BASE_URL is a good name.

Tip: use functional meaningful names for your items. Certainly application items. But also keep in mind that you will be typing them several times. Even if you have 30 characters to spare ... you may want to choose short names!

Creating an Application Item is very simple. Just remember to set your scope, in this case Application. And think about security: Restrict it to be set from the browser.
It would have been a good idea to enter in the comment field e.g. the place where you are using/setting the item. Although of course the help functionality in APEX is pretty good as well to find that out!

2. Application Process

We need an application process that fires just after login. Remember, you're working in an EBS integrated environment. So the login page is omitted, but the login DOES happen.

Create a new process:

We're using an EBS API to capture a standard profile option that keeps the ... yes exactly: base URL information.
In the example I set the PROD URL to be used in case the API would return no value. This is done because profile options may be updated by the system administrator. Normally that shouldn't be the case. But better save than sorry! This obviously means you have to test your stuff. Because when you use it in a logout-url, after logging out from a DEV environment for which the BASE-URL is not found, it will point to the PROD URL. This can be very confusing!!

Tip: only fill the PROD-URL that you use in the NVL construciton just before you move your app to production. Or as a minimum after you have decently tested everything on different environments. I usually set it to "google.be" until the very last moment!

 In fact ... you're now done. You did everything to fill the AI. This is really all there is to it. Now all you have to do is use it in a proper way.

Here are some examples ...

3. References to images (and other stuff)

In a given situation it was opted to upload a whole set of images and a general CSS file to a specific location on the server. This was a sub-structure of the "AI_BASE_URL".
In (e.g.) the menu list entries some of these images where used as reference icons. I used the AI_BASE_URL to reference these images:

4. Navigation Bar entry using URL target

In EBS the Home page is a specific page from which you start every action. It identifies who you are and what role (or roles) you have. And for each role (authorisation) which actions you are allowed to do.
In most cases your created APEX extension will be one of the actions (called form functions in EBS).
So you want in your integrated APEX app a link to this home page. This is done because it would be impossible to navigate back to your EBS environment if you hadn't foreseen this. So the - bad - alternative is that you logout or even kill your browser session. And then obviously a new login in required to continue with your work.



In this example that "Home" URL is constructed, making use of the AI_BASE_URL:

And exactly the same was done for the Logout URL:

Happy to share ...

Starting to blog at the end of 2014 ... why do that?

Hey all

I thought of starting my own blog many times before. Why go ahead with it now? Isn't it old style of communicating?

Well ... no, I don't think so. This is not a diary. Certainly not for private stuff.
I intend to write down professional experiences that I want to share.
So I can find things back for my own reference. And in the same way perhaps every now and then somebody else may benefit from it as well.

It is the very active APEX community who's actually bringing me to the point that I am now: launching my own blog. Thank you #ORCLAPEX for that!

Well, I'm not the kind of person who always thinks he's right in what he writes, says, does.
Though of course I'm trying very hard to be right or as close as possible anyway.
So if you would find an error here or there ... please do not hesitate to correct me. Though I urge you to do it in a polite way. I will not tolerate some responses I read now and then on blogs or forums. I mean well and if you can't see that in my blogs, then please: just stay away ;-)

I hope you enjoy reading or even learn from my posts.
See you on-line!

Roeland