5 Things I wish I knew about APEX when I just started (part 2)

In my first post of this series, I talked about subscriptions. The next post in this series is about cookie sharing in APEX, I hope you enjoy it.

Apex SSO by Cookie sharing

In the spirit of the previous post, this post will also be one that comes in handy when running multiple applications in the same workspace. When running multiple applications in the same workspace, it’s possible to share the authentication across multiple applications so you don’t have to login every time you switch applications. The only problem is that the feature is tucked away pretty good in the authentication scheme settings.

First we will Create 2 standard Applications

Untitled-1

Now we will create a simple page with a link to a page in the other application, to test if our setup works.

Untitled-2

If we would click the link now we would be redirected to the login page of the other application since, we’re not yet authenticated.

Untitled-3

What we need to do now is set up our authentication, to keep it simple I will make a hardcoded function where the process checks for username and password admin. But feel free to use your own custom login procedure. :-)

Untitled-4

Now for the important part, scroll down in the authentication scheme until you see a block called “Session Cookie Attributes”

In the cookie name field, you can set a name for your cookie, the important thing for this to work is that the name is the same in both applications, you also have the option here to define extra settings for your cookie. Whether it should be secure or not and the cookie path and domain.

Untitled-5

In the second application create the same authentication scheme as a copy of an existing authentication scheme. You can subscribe the authentication scheme to the first application.

For more information about subscription you can look at my first post.

If we log in to one of our applications now, we should be able to switch applications by clicking on the link we have created

That’s it! The third blog in this series will be about build options, and is coming very soon.

.

5 Things I wish I knew about APEX when I just started (part 1)

I’ve been doing apex for about 7 years now, and along the way I discovered a few things that made my life as a developer a lot easier. I made a list of 5 things that I wish I had known wen I just started. Those things will be explained to you in a series of 5 posts.

Subscriptions

One of the most tiresome things to do when managing multiple applications is to keep things like templates, and security up-to-date across all applications.

Apex has a nice built-in system for this called ‘subscriptions’.

If you just start with a new project, the easiest way to set-up the subscriptions is by creating a new application and call it “MASTER APPLICATION” or something similar, so you know this will be the parent application for all subscriptions. For an existing application the same method applies, but it will be a bit more work intensive to get everything right.

In this application, you define your, security scheme, authorization schemes, templates, list of values.

Once you have your basic configuration done, you are ready to start working on your new applications.

To link the new application to the MASTER application let’s say for example an authorization scheme.

You go to shared components, authorization schemes, create.

Select “create as copy”.

apex_P1_1

Copy from the master application and then select copy and subscribe.

apex_P1_2

If you edit the authorization scheme now you will see that in the subscription part there is an application referenced.

apex_P1_3

By now you are wondering: “What’s the point of all this? Couldn’t I just as easily have copy pasted the code?”

Yes, you could have, but imagine having 20 or more applications using the same authorization scheme, and then one day you may have to change something inside, you would have to modify all 20 schemes in all 20 applications again.

But because now we reference the master template this is no longer necessary. For instance let’s say we want a function to return false instead of true, what we can do now is go to the master application, edit the authorization scheme, change it, save it, and press the publish scheme button.

In the long run this could save you tons of work!

apex_P1_4

For subscribing templates in apex there is a way to do all templates at the same time.

Go to shared components, templates.

On the righthand side you have a menu called task, click the link to replace templates in this application with templates from another application.

apex_P1_5

Select the master application again, in the next screen select the template, and in the action drop down select, replace/subscribe, and press replace templates.

apex_P1_6

Read the next blog in this series:  Apex SSO by Cookie sharing.

Export/Import Apex applications in SQL Developer

Using SQL Developer to export or import an Apex application is not only easy, it’s also fast!

Export

To export, follow these steps:

Open the connection to the schema where you’re application is located and search for the “Application Express” directory:

image_1

Right click on the application you want to export and choose “Quick DDL” > “Save to File…”:

image_2

When done, you’ll have the same file as you would have exported from the Apex GUI.

 

Import

To import an application, right click on the “Application Express” directory, and choose “Import Application”. You can also right click on an existing application; the next steps are the same.

Follow the wizard. When this screen appears, you can choose to do a number things:

image_3

 

  • Auto Assign Application ID
  • Use the application ID from the export
  • Specify New ID

When the ID that you want to use is already assigned in your workspace, don’t forget to check the “Overwrite” checkbox.

At the end of the wizard you see the progress of the installation:

image_4

When finished, you’re application is up and running.

Triggering Dynamic actions from javascript code in APEX

Since APEX 4.0, dynamic actions have made our lives easier. But sometimes they feel a little restrictive in what you can do with them. Executing them upon the successful completion of another dynamic action, for example, or forcing a dynamic action to fire, triggered by something that happened in your javascript code.

APEX 4.2

Since APEX 4.2, there is a way to do this easily.

We start by creating the application process that needs to be triggered. It is important that you choose the event type “Custom”, and give it a name.

da1

To keep the example simple, I made a true action executing some javascript and printing it to a region on the page.


$('#dynamicactionresult > .uRegionContent').html('Dynamic action triggered successfully');

The hardest part is over now. All that’s left to do, is trigger the dynamic action from javascript. This can be done with the following piece of code:


$.event.trigger('TriggerMe');

Where TriggerMe references the name of your “custom event”.

Older versions of APEX 4.x

The above only works from APEX 4.2. However, for the older versions of APEX 4 there is a workaround!

First, we make a hidden page item, allow it to be accessed by javascript and set protected to “No”.

da2

Next, we create a dynamic action that triggers on change of this item, executing some javascript code as mentioned before.

da3

Finally, we trigger this application process by forcing a change-event on the page item. This can be done with the following piece of code.

apex.event.trigger($("#P1_DYNAMIC_ACTION_TRIGGER"),"change","");

Using Apex Authorization schemes in PL/SQL

The problem with using APEX authorization schemes in PL/SQL has been addressed several times in blogs and forums, but we occasionally still get questions  on how to solve this:

I have a page where users with admin roles can modify data and other users can only view it. Hiding the button to save the record is easily done with an authorization scheme:

Capture

However, now I want my items to be displayed as “Read Only” too. There is no option to select your authorization scheme, but Apex wouldn’t be Apex if there hadn’t been an easy solution.

The function “apex_authorization.is_authorized(‘authoutization_scheme’)” does the trick. It will check the authorization scheme and return a boolean. Add a small PL/SQL block in the Read Only-part of your item like this:

Capture

Now your item is read only for persons without the admin role.


Some additional information:

With this function it’s also possible to combine multiple authorization schemes:

IF apex_authorization.is_authorized('isAdmin')
   OR apex_authorization.is_authorized('isWrite')
   OR :P3000_USER = 'TEST' THEN
  RETURN FALSE;
ELSE
  RETURN TRUE;
END IF;

Attention: if you want to use this functionality prior to Apex 4.2, you need to use “apex_util.public_check_authorization“!

Apex Interactive Report: The difference between CIR and RIR

You’ve probably already used the reset functionality in an Interactive Report, but do you know the exact the difference between CIR (Clear Interactive Report) and RIR (Reset Interactive Report)?

RIR or CIR, what is it?

First, let’s explain what CIR and RIR is and how you can use it.

With CIR and RIR you can clear or reset your Interactive Report after using filters. This can be handy if you’ve applied a filter on a page, but also want to (re)use the full report.

If you want to apply a filter on an interactive report when linking from another page you can use the following operators.

Function Meaning
IREQ_<COLUMN_NAME> Equals
IR_<COLUMN_NAME > Same as IREQ
LT_<COLUMN_NAME > Less than
IRLTE_<COLUMN_NAME > Less than or equal to
IRGT_<COLUMN_NAME > Greater then
IRGTE_<COLUMN_NAME > Greater then or equal to
IRLIKE_<COLUMN_NAME > Like operator
IRN_<COLUMN_NAME > Is Null
IRNN_<COLUMN_NAME > Is not Null
IRC_<COLUMN_NAME > Contains
IRNC_<COLUMN_NAME > Not Contains

You can also use above functions in a saved report, then you have to use IR_REPORT_<ALIAS>.

Sometimes the page with the interactive report can be accessed by multiple pages and you would like to reset the filters already applied to the interactive report.

i.e. You have a page with Countries and you want to filter your existing Customer Report to all customers in selected state. But, when you click on Customers on another page you want to see all customers, not only the ones you just filtered. In this case you can use CIR or RIR.

You can simply enter these options in the URL or the Clear Cache option in your button / branch.

APEX_CIR_RIR

But what is exactly the difference between using the CIR and the RIR?

As the name suggests, CIR Clears and RIR Resets.. But isn’t that the same?

Almost. The main difference is that CIR Clears the report, clearing all breakpoints, filters and other defined actions on your report, ignoring the settings of the primary report. RIR Resets the interactive report to the primary report.

In the following table you can see which user defined modifications to an Interactive Report will be lost or kept when using the CIR/RIR clear cache option.

  CIR RIR
Main Function Clears Interactive Report Resets Interactive Report
Maintains:
–  Column visibility YES* NO
–  Primary Report NO YES
–  Filters NO NO
–  Breakpoints NO NO
–  Pagination NO NO
–  Sort YES NO
–  Highlight NO NO
–  Computation NO NO
–  Aggregate NO NO
–  Chart NO NO
–  Group by NO NO

* Please note that when a CIR has been given, the columns displayed are still the columns of the primary report, but ‘stripped’. But if you alter the shown columns as a user it will display these columns.

For a demonstration click HERE.

Conclusion

In conclusion, use CIR to clear all filters and other settings set by the user or in the primary report. Use RIR if you want to reset to the primary report keeping all filters and columns of the primary report.

Change the owner at runtime in Apex

One of our clients needed an application for the follow-up of the production in their plants. The main feature of the application was to show all the data of the current year, but the client also wanted to be able to consult the historical data. Unfortunately the historical data was not in the same schema as the current data.

The two schemas:

  • SCHEMA_A
  • SCHEMA_A_HISTORY

As you could already guess, the second schema contained the historical data. Each year, the old data was moved from SCHEMA_A to SCHEMA_A_HISTORY.

As SCHEMA_A was our parsing schema, we had all the privileges on the tables with the current data, but we didn’t have access to the historical data. We could have created separate screens for the historical data, but we decided to switch owner at runtime when the user wanted to see the historical data.
For this, we also need the select privilege on schema_a_history

In APEX we can do this with just a single line of code:

apex_application.g_flow_owner := 'SCHEMA_A_HISTORY';

This line is best placed under “shared components > security > database session > initialization code”; this way it is fired every time APEX takes up a database session from the session pool:

Knipsel

In older versions of APEX this field was called “Virtual Private Database”.

As you can see in the screenshot, we have used an application item. When the users asks to see the historical data, we fire a process that changes the value of this item from ‘SCHEMA_A’ to ‘SCHEMA_A_HISTORY’.

That’s it, just a quick tip that can save you a lot of time!