Next
How to make any table header sticky - 28-Apr-2017 05:41 - Marko Gorički

The Problem

If you're using Interactive Reports and Grids in your apps you may know that there's a property Fixed To that fixes the report's column headers to the top of the page or region so that the column headers remain visible as the user vertically scrolls the report. But there's no such option for Classic report regions or your custom HTML tables.

The Solution

You can easily do it by using two JS widgets that are build into APEX - Sticky Table Header and Sticky Widget.

How To 

To add it to the Classic report region you have to create After Refresh dynamic action that fires after refresh of your Classic report region:


and define true action that executes the following JavaScript code:

var vRegion$ = $(this.triggeringElement);
vRegion$.setTableHeadersAsFixed();
vRegion$.find('.js-stickyTableHeader').stickyWidget();
Dynamic action should look like this:



Remember to set property Fire on Initialization to Yes.

For the additional options you can check out the file libraries/apex/widget.stickyWidget.js in APEX installation folder or through page source view when you're running your application in debug mode.

For custom HTML tables you can use the same principle and if your custom HTML table is not rendering dynamically fire the JS code above only on page load.

The demo is avaliable here.

Enjoy!

Tested on APEX 5.1.1.00.08

For the Apex Dashboard Competition of the DOAG in 2016 I created a Apex dashboard. You can read about it in this post
One of the elements of this dashboard was a group of four infocards.

These cards display the key values for a country on a certain subject. Apart from the text the cards differ in color and the icon used.
These cards are created using a named report template. This is a special kind of report template. Using a normal report template the query columns and rows map to the columns and rows in a HTML table. A named report template consists of a HTML template in which the query columns are referenced by name. For the infocards the query and template below are combined:

select 'Population'  title
     , population    data
     , 'Number of inhabitants'  text
     , 'fa-users'   as  icon_class
     , 'db2_red'     as  container_class
from   cnt

Row Template 1 within the Report Template:

The substitution strings in the template are replaced with the corresponding values from the query.
As you can see the column values can also be used to define CSS classes as in the enclosing DIV element. The value of the column container_class defines the name of the class to applied. This class defines the color of the card. The class icon_class defines the Font Awesome icon to be used.

The cards are styled with the following CSS:

The color of the card can be defined by selecting the db2_color class as container_class in the query. 

The icon is positioned absolute relative to the card with the class db2_icon_container. Space is created for the icon container by defining  left:80px; for the main DIV containing the text.

The rest is quite straight forward CSS styling.


You can create your own Named Report Template:
– navigate to Shared Components > Templates
– press Create
– chose Report
– create the template From Scratch
– enter the name for your template and check Named Column (row template)
– then enter the HTML for the template

Named Report Templates provides us developers with more freedom to style the output. The downside is that the templates are more specific and less widely applicable. 

Happy Apexing

Source Article from http://dickdral.blogspot.com/2017/04/displaying-info-cards-using-oracle-apex.html

For the Apex Dashboard Competition of the DOAG in 2016 I created a Apex dashboard. You can read about it in this post
One of the elements of this dashboard was a group of four infocards.


These cards display the key values for a country on a certain subject. Apart from the text the cards differ in color and the icon used.
These cards are created using a named report template. This is a special kind of report template. Using a normal report template the query columns and rows map to the columns and rows in a HTML table. A named report template consists of a HTML template in which the query columns are referenced by name. For the infocards the query and template below are combined:

select 'Population'  title
, population data
, 'Number of inhabitants' text
, 'fa-users' as icon_class
, 'db2_red' as container_class
from cnt

Row Template 1 within the Report Template:


The substitution strings in the template are replaced with the corresponding values from the query.
As you can see the column values can also be used to define CSS classes as in the enclosing DIV element. The value of the column container_class defines the name of the class to applied. This class defines the color of the card. The class icon_class defines the Font Awesome icon to be used.

The cards are styled with the following CSS:


The color of the card can be defined by selecting the db2_color class as container_class in the query. 

The icon is positioned absolute relative to the card with the class db2_icon_container. Space is created for the icon container by defining  left:80px; for the main DIV containing the text.

The rest is quite straight forward CSS styling.


You can create your own Named Report Template:
- navigate to Shared Components > Templates
- press Create
- chose Report
- create the template From Scratch
- enter the name for your template and check Named Column (row template)
- then enter the HTML for the template

Named Report Templates provides us developers with more freedom to style the output. The downside is that the templates are more specific and less widely applicable. 

Happy Apexing
The Interactive Report has this great download feature where you can export everything you can see.
Anyway there are circumstances where the customer doesn't want that feature open for everyone.

In APEX you can only choose if you want the download button or not.
Even so APEX can't do it out of the box. There is a way to make your application able to do it.

Since APEX 5 you can't download when the "Download" is disabled. If you try an almost empty page occurs. Ok that means the "Download" functionality must be activated an I have to disable it manually.

You need to focus on three steps:
 1. Add an authorization scheme.
 2. Hide the download button in the front end. (Visualization)
 3. Disable the download functionality in the back end. (Security)

1. Add an authorization scheme
The authorization scheme will handle the rights that only the correct person is allowed to download from the Interactive Report.
I my case I call it "ROLE_DOWNLOAD" and it works like that:
Type: PL/SQL Function Returning Boolean 
Function Body: return security_pkg.has_role(:APP_USER,'ROLE_DOWNLOAD');
Validate: Once per session

2. Hide the download button
Add a static report id


Add a new "Dynamic Action" on "Click".
jQuery Selector: #STATIC_REPORT_ID_actions_button
Event Scope: Dynamic
Security > Authorization: {Not ROLE_DOWNLOAD}



Add some Javascript to remove the button:
$('#STATIC_REPORT_ID_actions_menu .icon-irr-download').parent().parent().parent().remove();

3. Disable the download functionality
When APEX is exporting something from an "Interactive Report" itjust does a simple redirect on the same page and adds a REQUEST for the specific download type. In my case it is the request "CSV" I want to block.

Add a "Branch" executed "Before Header":


And to disable the download I just redirect on the same page without any request. The trick is to add the right PL/SQL Condition. In this example check for the request and the authorization scheme.

Code:
:REQUEST = 'CSV' and APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION('ROLE_DOWNLOAD') = false

 
In my mind this is simple and secure and shows how flexible APEX really is.
Here is an overview of a few sessions Track Lead Tiffany Briseno is most looking forward to at ODTUG Kscope17 and why she will be attending them:
Partial Rollbacks - 27-Apr-2017 06:28 - Martin Giffy D'Souza

The ability to rollback transactionsin PL/SQL is very helpful when something goes wrong and you want to undo what was just done. At a very high level, most code that use rollback look like the following:

1
2
3
4
5
6
7
begin
...
exception
when others then
rollback;
raise;
end;

The above code makes sense as the rollback occurs when an error happens. (Note for APEX users, an implicit rollback occurs in processes if an exception occurs) The caveat is that it rollsback the entire transaction (i.e. from the start). What if the code is part of a larger block of code and you only want to rollback to the previous step? The following pseudo code is an example:

1
2
3
4
5
6
7
begin
do_step_1;
do_step_2;
-- Step 3 may error out and if it does, still want to preserve the work of steps 1 and 2.
do_step_3;
do_step_4;
end;

In do_step_3 if a generic rollback was used in the exception block it would undo any chnages that were done in step’s 1 and 2 which is not the desired outcome. Thankfully PL/SQL rollback functionality supports this by using savepoint. If we want do_step_3 to work as intented this is what it should look like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create or replace procedure do_step_3 as
begin
savepoint start_step_3;
...
exception
when some_expected_error then
-- This will rollback all changes to the start of do_step_3
rollback to savepoint start_step_3;
-- no raise as this was an expected error
when others then
raise;
end do_step_3;

A few things to note about savepoints:

  • The rollback to savepoint x doesn’t need to be called in the exception block. I’ve used it in other places as well. Ex: if <> then rollback to savepoint...
  • Try not to litter your code with savepoints. It can get very confusing and tough to debug if you have a lot of them. The business logic will really determine when you need to use them.
  • In the past ten years I’ve only needed to use them a handful of times. It’s not a common feature that you’ll need to use but good to know when you need it.

Oracle documentation for savepoint can be found here.

Validating JSON in the database - 24-Apr-2017 19:04 - Christoph Ruepprich

Creating REST services and consuming JSON inside the Oracle database has become quite easy. The APEX_JSON package provides a number of useful procedures and functions to work with JSON. However, one thing I haven’t found is a way to validate JSON before parsing it.

To my knowledge the is not yet a PL/SQL way to check whether a JSON string is valid, i.e. to make sure that all the brackets, commas, colons, etc. are there. There is however, a column check constraint you can use in 12c that will only allow valid JSON to be inserted into a table. So here is a quick workaround for validating JSON using the check constraint: I simply take the JSON and attempt to insert it into the table. If the check constraint is violated, I’ll get an error.

I wrapped to whole thing in a function that will then simply return a boolean value for the JSON passed into it.

create table json_validator ( 
	 id   number generated always as identity primary key
	,json clob
	,constraint validate_json check (json is json format json)
);

create or replace function validate_json (l_json in clob)
return boolean
is
  l_id pls_integer;
  e_invalid_json exception;
  pragma exception_init (e_invalid_json,-2290);
  pragma autonomous_transaction;
begin
	insert into json_validator (json) values (l_json);
	rollback;
	return true;
exception
	when e_invalid_json then
	  return false;
end;
/

set serveroutput on
declare
  l_json  clob;
  l_return boolean;
begin
  l_json := '
				{"widget": {
					"debug": "on",
					"window": {
						"title": "sample konfabulator widget",
						"name": "main_window",
						"width": 500,
						"height": 500
					},
					"image": { 
						"src": "images/sun.png",
						"name": "sun1",
						"hoffset": 250,
						"voffset": 250,
						"alignment": "center"
					},
					"text": {
						"data": "click here",
						"size": 36,
						"style": "bold",
						"name": "text1",
						"hoffset": 250,
						"voffset": 100,
						"alignment": "center",
						"onmouseup": "sun1.opacity = (sun1.opacity / 100) * 90;"
					}
				}}    
  ';

  l_return := validate_json(l_json => l_json );

  if l_return then 
	dbms_output.put_line('JSON is valid');
  else
	dbms_output.put_line('JSON is NOT valid');
  end if;

end;
/


Here is an overview of Kscope17 sessions Kevin McGinley is most looking forward to and his thoughts on why you should attend them, too:
Warn Before Closing APEX Modal Dialogs - 24-Apr-2017 07:12 - Jorge Rimblas

As you may already know, APEX 5.1 implements native Warn Before Exit functionality. Basically, if the user makes changes on a page and tries to navigate away before they save their changes, they get a warning. Modal Pages also get this feature, with one caveat; the close “X” on the window does not warn.
As I understand, this is was the intended behavior. However, for some use cases, I disagree. For this reason, and after a client’s request, I came up with this solution.
On the Modal Page attributes, we specify a “beforeClose” function. Like so:

beforeClose: warnOnClose

Then, on the calling page (or globally) you want the following code:

function warnOnClose(event, ui){
  var apexiFrame = $("iframe").get(0).contentWindow.apex;
  var hasChange = apexiFrame.page.isChanged();

  if (hasChange) {
    // var lMessage = apex.lang.getMessage( "APEX.WARN_ON_UNSAVED_CHANGES" );
    var lMessage = "There are unsaved changes. Do you want to continue?";
    var ok = confirm(lMessage);
    if ( !ok ) {
      event.preventDefault();
    }
  }
  return hasChange;
}

Line 2 and 3 are the most important to this whole solution. Modal Dialogs run inside an iframe. As such, the calling page needs to take a peek inside the dialog and see if something was changed.
Line 2 gets a hold of the “apex” namespace for the dialog. Line 3 can then check is anything has changed within it.

Finally, line 10, prevents the close even from happening and maintains the dialog opened if they user opts to stay.

I should mention, I tried to use apex.message.confirm instead of the more rudimentary confirm, however because we’re already inside a modal, calling apex.message.confirm would invariably end up closing the modal I wanted to keep open. Perhaps, there’s a way to accomplish this, if you have any ideas, please share in the comments.

AUSOUG APEX 5.1 webinar series - 23-Apr-2017 21:53 - Lino Schildenfeld

Oracle APEX 5.1 webinar series

Australia/New Zealand ORACLE APEX webinars

Great speakers from USA, EU, AU and NZ


I am pleased to announce there is a huge interest not only among the speakers but also from the audience for this years 2017 AUSOUG APEX webinar series.
There is plenty of time for you to join us, please check AUSOUG Calendar for more details. First session is starting 27/04/2017 - registration.

Some of the speakers already confirmed


  • Dimitri Gielis, APEX RnD, Belgium
  • Tyson Jouglet, Skillbuilders, USA  
  • Scott Wesley, Sage Computing, Australia
  • Roeland Van den Eynde, APEX RnD, Belgium
  • Niels de Bruijn, MT AG, Germany
  • Oliver Lemm, MT AG, Germany
  • Aljaz Mali, Abakus Plus, Slovenia
  • Menno Hoogendijk, Qualogy, Netherlands
  • Matt Nolan, FOEX GmbH, Austria
  • .....more to come soon
in no particular order :)


More information on ausoug.org.au. Hope to see you there.
 
Happy APEXing,
SLino
Next