Changing Report Column Date Format

Category APEX Tricks

In APEX there is build in date formats SINCE and SINCE_SHORT that I use often in report date columns. Unfortunately you can't use SINCE format in application Globalization Attributes, so you need separately set it to columns. But, sometimes you need to see the actual date value in column.

Here is simple example how you can switch between application global date format and SINCE.

First create application item G_DATE_DISPLAY_FORMAT.
Application item

Create two entries to Navigation Bar list as follows.
First list entry:

  • List Entry Label: Show Since
  • Target page: &APP_PAGE_ID.
  • Request: SET_DATE_FORMAT_SINCE_ON
  • Condition: Value of User Preference in Expression 1 != Expression 2
    • Expression 1: U_DATE_DISPLAY_FORMAT
    • Expression 2: SINCE

Second list entry is almost same. Just change label, request and condition

  • List Entry Label: Show Date
  • Target page: &APP_PAGE_ID.
  • Request: SET_DATE_FORMAT_SINCE_OFF
  • Condition: Value of User Preference in Expression 1 = Expression 2
    • Expression 1: U_DATE_DISPLAY_FORMAT
    • Expression 2: SINCE

Create On Load: Before Header application process.
PL/SQL Code:

declare
  l_since varchar2(40);
begin

  l_since := 
    case when :REQUEST = 'SET_DATE_FORMAT_SINCE_ON'
    then 'SINCE'
    end
  ;
  :G_DATE_DISPLAY_FORMAT  := l_since;

  /* save user selection to preference */
  apex_util.set_preference(
     p_preference => 'U_DATE_DISPLAY_FORMAT'
    ,p_value      => l_since
    ,p_user       => :APP_USER
  );

end;

Set process conditionally by request containing SET_DATE_FORMAT_SINCE_ON and SET_DATE_FORMAT_SINCE_OFF
Application process condition

Application process stores information also to preference. If you like you can set application item value for new sessions in Post-Authentication Procedure.

Place code to authentication schema source PL/SQL Code and Enter to Post-Authentication Procedure Name field set_date_display_format

procedure set_date_display_format
as
begin
  :G_DATE_DISPLAY_FORMAT := apex_util.get_preference(
    p_preference => 'U_DATE_DISPLAY_FORMAT'
    ,p_user => :APP_USER
  );
end;

Authentication schema

Then edit report columns and set format mask to application item substitution string &G_DATE_DISPLAY_FORMAT.
 
Report column format mask


Here you can see example where right top there is link to switch date format mask. When you click the link in navigation bar, you can see report Hired on column format changes.

Oracle Database Developer Choice Awards

Category APEX Community

I'm finalist for Oracle Database Developer Choice Awards in Oracle Application Express (APEX) category.

I'm very impressed and surprised for this honor. I want thank all who have filled nomination form and voted.

Special thanks to all who have also leave comments and supported me by blog posts and spreading word other ways.

If you haven't vote yet, now it's time. Voting ends at 15th of October. Please remember you can vote multiple persons in all categories.

I hope Denes Kubicek's blog post encourage everybody to vote.

SQL Developer ORDS administration "peer not authenticated" error

Category Oracle SQL Developer

If your web server use self signed certificate for https you most probably get "peer not authenticated" error when you try administrate Oracle REST Data Service (ORDS) using Oracle SQL Developer.

Peer not authenticated error message

To resolve this issue, you need import server certificate to Java cacerts keystore.

You can download server certificate e.g. using browser. In this example I use Firefox.

First navigate to your server URL and click lock icon next to URL. From popup click More Information.

Get server certificate using Firefox step 1

From opening window click View Certificate

Get server certificate using Firefox step 2

Click Details tab and then Export button

Get server certificate using Firefox step 3

Change the ‘Save as type’ to ‘X.509 Certificate with chain (PEM)’ and save the certificates to a file.

Get server certificate using Firefox step 4

Open command prompt and import certificate to Java cacert keystore. Please note that you need import certificate to Java installation/version witch SQL Developer uses.

Here is example of command how import certificate in Windows

C:\sqldeveloper\jdk\jre\bin\keytool.exe -import -noprompt -trustcacerts ^
-alias vbox-apex.localdomain ^
-file C:\Temp\vbox-apex.localdomain.crt ^
-keystore C:\sqldeveloper\jdk\jre\lib\security\cacerts ^
-storepass changeit

Import certificate using Java keytool

Now administrating Oracle REST Data Service using Oracle SQL Developer over https works.

ORDS connection

Global Right Column

Search

Archives

Subscribe in a Reader