Next
Getting to Grips with the APEX Interactive Grid API - 12-Dec-2017 20:01 - Christoph Ruepprich

Matt Mulvaney has an excellent post regading the APEX interactive grid: http://www.explorer.uk.com/getting-grips-apex-interactive-grid-api/

 


Interactive Grid: How to Fix Blank Toolbar DIV - 12-Dec-2017 05:43 - Marko Gorički
One of the best functionalities of the Interactive Grid is declarative master-detail option. That's the reason why I like to use them also in readonly mode. Most of the time I don't need toolbar in detail regions so I turn it off (Region Attributes > Toolbar > Show = No).

But, currently (in APEX 5.1.4.00.04 and less) there's little bug that I can't ignore. It's 9px blank div above report headers:



At first I thought that's easy - I'll just use :empty CSS pseudo selector and hide DIV element with CSS class .a-IG-header:
.a-IG-header:empty{
display:none
}
But that breaks sticky headers when you scroll down. To fix that I had to use different CSS:
.a-IG-header:empty{
padding:0;
border-bottom:0
}
If you want to use other regions for master-detail pages take a look at my plugin on apex.world.

Demo is available here.

Tested on apex.oracle.com (v5.1.4.00.04).

Enjoy!
Very recently, an OBIEE v12.2.1.1 upgraded to v12.2.1.3 on an Exalytics machine. Though it wasn't a major upgrade, we had to do it to fix some bugs encountered in v12.2.1.1. For sure, it wasn't a easy walk in the park during the upgrade and post upgrade.

I am here to discuss a particular issue we faced post upgrade. This should be interesting.

Post upgrade to v12.2.1.3, we heard from the application team that all the reports working perfectly fine, except the dashboard reports with HTML tag unable to save. The below error was thrown when try to save the existing HTML reports:

"You do not currently have sufficient privileges to save a report or dashboard page that contains HTML markup"

It appeared to be a very generic error, and we tried all the workarounds we found on various websites and Oracle docs. Unfortunately, none of the solution fixed the issues. One of the popular advice is the following:

you need to go and check the HTML markup privileges in Administration.


1. Login with OBIEE Admin user,
2. Go to Administration link - Manage Privileges,
3. Search for "Save Content with HTML Markup" and see if it has necessary privileges. else assign the necessary roles and re-check the issue. 


After giving an additional privilege we could save the existing HTML reports. However, still the issue exists when we create a new dashboard report, with HTML tag. This time the below error msg was appeared:

Multi-Cursor Editing with Sublime Text - 10-Dec-2017 21:57 - Jorge Rimblas

This little question on Twitter prompted me to record this brief video.

Some useful links:

Sublime Text for Oracle Apex Developers


http://apextips.blogspot.com/2015/09/compiling-oracle-code-from-atom-text.html

Oracle APEX – Das umfassende Handbuch - 10-Dec-2017 11:19 - Carsten Cerny

Wie habe ich mich gefreut, als ich von diesem Buch erfahren habe. Die Anzahl deutschsprachiger APEX Literatur ist doch sehr überschaubar. Nachdem ich mich die letzten Wochen darin vertieft habe, schreibe ich diesen Blog mit gemischten Gefühlen.

Titel: Oracle APEX – Das umfassende Handbuch
Autor: Jürgen Sieben
Verlag: Rheinwerk-Verlag
Erscheinungsdatum: 2017
ISBN: 978-3-8362-4530-2
Seitenzahl: 895

Mit fast 900 Seiten handelt es sich um ein Schwergewicht und mit einem Preis von knapp 80 Euro gehört es nicht gerade zu der Literatur, die ich so im vorbeigehen mitnehmen würde. Meine Erfahrungen mit APEX kann ich als umfassend bezeichnen, schließlich habe ich in meinem letzten Job mein Geld damit verdient, Anwendungen in APEX zu entwickeln. Leider ist das schon ein paar Jahre her und die Entwicklung von Apex ist nicht stehen geblieben. Ich kann also sagen, dass ich mich sowohl als Umsteiger, als auch als fortgeschrittenen Anwender bezeichnen würde. Laut Autor soll das Buch aber genauso gut für Einsteiger geeignet sein.

Oracle Apex - Das umfassende Handbuch

Oracle APEX – Das umfassende Handbuch

Und da fangen meine Zweifel an. Jemand, der noch nie mit Apex gearbeitet hat, wird den einen oder anderen Screenshot im Text vermissen, um sich orientieren zu können. Stattdessen arbeitet der Autor mit sogenannten „Treffpunkten“. Diese sollen wiederholenden Text im Buch vermeiden und werden am Ende des Buches in einem Überblick zusammengefasst. Das es auch anders und meiner Meinung nach besser geht, zeigen die PDF-Dateien, die der Autor als Download zum Buch bereitstellt. Dort ist jeder Schritt mit einem beschreibenden Screenshot aufgeführt.

Für den Leser, der den Downloadlink zu den Beispielen und den erwähnten PDF-Dateien im Buch vermisst, er ist auf der Buch-Seite des Verlages zu finden. Weshalb nicht prominent im Buch, z.B. auf dem Außencover? Für die nächste Auflage sollte der Autor noch einmal die im Buch und in der Realität verwenden View-Namen vergleichen. Es hat sich so manche Ungleichheit eingeschlichen (z.B. Buch Seite 165: EMP_UI_HOME_EMP_COUNT, Realität: EMP_UI_HOME_COUNT). Oder ein Typo auf Seite 188 (P4_DEP_Filter). Ebenso sollte der Leser ein paar Grundkenntnisse für Oracle Datenbanken mitbringen. Der Autor geht dafür zu wenig auf evtl. auftretende Fehler oder die Einrichtung der Datenbank/Benutzer ein. Um mit dem Buch und den Beispielen arbeiten zu können, liegen im Download-Paket Skripte bereit, die auf der Datenbank ausgeführt, eine fertige Umgebung bereitstellen.

Aber genug der Kritik. Mit dem Buch bekommt der Leser eine Lektüre an die Hand, die dazu einlädt, direkt loszulegen und erste Ergebnisse werden nicht lange auf sich warten lassen. Nebenbei erfährt man ein paar Best Practice (z.B. Namenskonventionen, reichhaltige Verwendung von Views, Code in der Datenbank) vom Autor, die auf eine lange Erfahrung zurückzuführen sind.

Während der erste Teil „Basics“ eine gute Einführung mit einer kleinen Mitarbeiteranwendung startet, der zweite Teile „APEX im Detail“ den Leser Informationen zu „Anwendungsseiten, Regionen, Seitenprozessen, Berichten, Grafiken und Charts, Dynamischen Aktionen“ vermittelt, geht es im dritten Teil „Fortgeschrittene APEX-Konzepte“ mit „Plugins, Webservices, Mobiler Entwicklung, APEX-Administration, Sicherheit“ schon sehr speziell zu.

Zusammenfassend sei gesagt, das Buch liest man nicht mal so nebenbei. Und als Nachschlagewerk dient es eher weniger. Wenn man aber die Beispiele direkt selber ausprobiert und sich durch die Eigenheiten der „Treffpunkte“ gearbeitet hat, wird es mit der Zeit flüssiger.

Disclaimer: Das Buch wurde mir vom Rheinwerk Computing Verlag kostenfrei zur Verfügung gestellt.

Der Beitrag Oracle APEX – Das umfassende Handbuch erschien zuerst auf CC13.com.

Text Area Enlarger - 10-Dec-2017 11:19 
Anguished with too small text areas and not enough room to enlarge them? This plugin will solve your problem. The plugin adds a small button in the top right corner of a textarea, with which the action is triggered: A large jQuery-UI-Dialog is created with a textarea inside, and the original text area APEX item-content is transfered into that. The dialog is set to modal, so that no accidental page submit or something else can happenes, while the popup is open and the new content may go lost. The plugin checkes if changes are made, and when an attempt is made to cancel, the user will be prompted with a confirm message. How too: Create a dynamic action for Page Load, select this plugin and enter all text areas, that you want to be able to enlarge. – As simple as that. Plugin Attributes allow renaming of the buttons Apply and Cancel , as well as the confirm text, when the popup is closed without applying the changes.
I was in a video call this morning with a great customer from England (and by the way, this customer is in the process of transforming the healthcare industry across the UK).  They asked me a very simple question:

How do I create a responsive Rich Text Editor item on a page?

Simple question and answer, right?  Well, you'd be wrong.  While we pride ourselves on the responsive user interfaces that you can easily create with Oracle Application Express (APEX), unfortunately, the item type of Rich Text Editor is not responsive, out of the box.

So - I did what all smart people do, and I reached out to the Oracle APEX Development team, in this case, the ever-intelligent Carsten Czarski.  And in a few minutes, he showed me exactly what I needed to do.

  1. Open up Application Builder, and in Page Designer, edit the page with the Rich Text Editor item.  In my example, my Rich Text Editor page item name is P3_RESUME.
  2. Navigate to the attributes of the Rich Text Editor item, and in the Advanced section, enter the following code in the "JavaScript Initialization Code" attribute:
    function (o) {
    o.width = $("#P3_RESUME").closest(".t-Form-inputContainer").width() - 5;
    o.height = 300; // Specify your desired item height, in pixels
    return o;
    }
    This code determines the width of the region container of the item, subtracts 5, and returns the object initialized to this size.  This will take care of the Rich Text Editor when the page is initially displayed. But it won't handle the case when the browser is resized. To handle that case, we'll need to add a dynamic action.
  3. Click the Dynamic Actions sub-tab in Page designer (the lightning bolt)
  4. Select Events in the upper-left, right-click your mouse and choose "Create Dynamic Action".
  5. In the attributes, enter "Resize" for Name, and select "Resize" for the Event.
  6. Select the True action of the dynamic action (it should be "Show").  Change the Action to "Execute JavaScript Code".
  7. In the Code attribute, enter the code:
    CKEDITOR.instances.P3_RESUME.resize( $("#P3_RESUME").closest(".t-Form-inputContainer").width() - 5, 300);
    This is an absolute reference to the Rich Text Editor item on the page, named P3_RESUME. And like the code before, this will determine what the width is of the container of the item, subtract 5 from it, and invoke the resize() method of the Rich Text Editor (CK Editor) element.
That's all there is to it!



Obviously, this item type (like all others) should be responsive, out of the box.  And Carsten is looking at this for the next version of APEX.  In the meantime, if you're using Universal Theme with Oracle APEX 5.1, all it takes is a tiny amount of JavaScript to get a responsive Rich Text Editor.
Reverse engineer existing Oracle tables to Quick SQL - 08-Dec-2017 14:21 - Dimitri Gielis
If you didn't hear about Oracle Quick SQL, it's time to read about it as it's something you have without knowing (it's a packaged app in Oracle APEX) and I believe you should start using :)

Quick SQL enables you to rapidly design and prototype data models using a markdown-like shorthand syntax that expands to standards-based Oracle SQL. You can easily create master detail relationships, check constraints, and even generate sample data.

In my blog post Create the Oracle database objects I go over the history how I created database objects and why I think Quick SQL is great and why I use it.

I guess most people typically use Quick SQL at the start of a new project, as it's the quickest way to create your data model and Oracle database objects. That is my primary use case too, but I started to use Quick SQL even on projects where database objects already exist.

In the project I'm currently involved in, the datamodel was generated by another tool, but as we iterate through the project, tables change, columns get renamed and added, row version were requested, triggers need to be made Oracle APEX aware...

Now we could do those changes manually, but I thought it made much more sense to create the data model in Quick SQL and use the features that come with Quick SQL. By clicking a checkbox we can include a Row version, Quick SQL generates the triggers automatically in an APEX aware form, we can generate as much sample data as we want by adding /insert and we can use all the other features that come with Quick SQL. For example when you want to include a history table in the future it's just another checkbox to click.


It's also easy to check-in the Quick SQL script into source control, together with the generated DDL.
If changes need to be done, we can adapt in Quick SQL and generate the DDL again and we see the changes immediately. It would be nice if Quick SQL could generate the ALTER statements too, but that's not the case yet. But it's easy enough to see the changes that were done by comparing the scripts in source control.

If you also want to reverse engineer an existing model into Quick SQL, here's a script that gives you a head start generating the markdown style format.


I tried the script on the Quick SQL data model itself - the result you see below:


Hopefully you see the benefit of using Quick SQL in existing projects too and the script helps you get there. Also Quick SQL gets frequent updates - in the upcoming release (17.3.4), which is already online, you can add a Security Group ID to every table (to make your app multi-tenant) and you can rename the audit columns to your own naming conventions.

Whilst attending Tech17 at Birmingham this year, I was pleased to see David Peake provide a short demonstration of the Oracle Interactive Grid API documentation. The documentation format/style, using JSDoc, differs from the traditional APEX documentation style we are used to. However, from the quick demo I saw, it does look great, and I cannot wait to get started with it.

From the little I did see, I noticed at least one IG JavaScript method I was unfamiliar with, so of course, when released with APEX 5.2 it will come shipped with all the brand new methods available for APEX 5.2.

In the future, therefore, any APEX 5.1 Interactive Grid developers referring to the APEX 5.2 documentation will mostly likely encounter problems where they are trying to use a documented function, hoping it may be available in APEX 5.1 – ultimately to discover it is not available in their version of APEX.

I am not expecting the documentation to include an APEX version number (see next paragraph); therefore your best bet in this scenario is to upgrade to APEX 5.2. Be aware though that there is also an outside chance that Oracle could have removed an IG function feature that your APEX 5.1 Grid relies on – which is always the danger when using undocumented features.

In the patch sets that follow 5.2, I would ideally like to see the documentation enhanced with each release. For example, in this blog, John Synders mentions, “the information in this article applies to APEX version 5.1.1. Some things may work in 5.1 but I’m not going to bother distinguishing which. Anyone programming Interactive Grid should move to 5.1.1 as soon as possible”. Therefore I reading three things here:

  1. New Interactive Grid functions have previously been released within patch sets, and this suggests it will continue this way; which is my point that the documentation needs to be kept up to date else more features will remain undocumented and the risk of feature removal starts over again.
  2. Oracle are unlikely to distinguish version numbers on documentation.
  3. Upgrade ASAP.

Right at this moment, given the absence of official Oracle Interactive Grid API documentation for APEX 5.1, developers are currently required to turn to alternative sources of information.

For me, these sources include:

In addition to the above, I add my own presentation “Getting to Grips with the APEX Interactive Grid API” – the video below is a recording I made in preparation for my Tech17 presentation of the same name.

In this video, I wanted to demonstrate the basics of the Interactive Grid API, which are:

  • How to customise the Interactive Grid
  • What is the model and associated views
  • How to loop through the model and selected records
  • How to extend functionality using your own menus, buttons and processes

You can also follow the presentation by using the same APEX application here

The Interactive Grid supports much more functionality, but these things are the basics – master these and the more advanced features do not seem as hard as first thought.

The post Getting to Grips with the APEX Interactive Grid API appeared first on Explorer | Award Winning UK Oracle Partner.

Friday Fun SQL Lesson - union all - 08-Dec-2017 01:15 - Scott Wesley
Our office kitchen is unavailable this Friday, so the call was put out for pub lunch.

After a couple of replies I decided to enter my reply, in full nerd mode.
select * from people_coming_to_lunch;

People
--------
Kate
Scott
Karolina

3 rows selected.
And of course one of the other SQL geeks (name redacted) replied to extend the data set.
select * from people_coming_to_lunch
union
select 'Shanequa'
from dual;
And I couldn't help myself. I had to play the performance card and suggest that UNION ALL would be the appropriate usage, and should be the default you type out. Always. Until you decide otherwise.

That's because sorts are expensive. And a UNION will need sorting to check for duplicates.

That sort of all the rows isn't necessary
And it will sort the data set even if there is a unique key on the data.
create table people_coming_to_lunch (people varchar2(30));
insert into people_coming_to_lunch values ('Scott');
insert into people_coming_to_lunch values ('Kate');
insert into people_coming_to_lunch values ('Karolina');

create unique index lunch_people on people_coming_to_lunch(people);

select * from people_coming_to_lunch
union all
select 'Shanequa' from dual
By using UNION ALL instead of UNION, you're telling the database not to even bother sorting the set to eliminate any potential duplicates, since your advanced human brain knows there will be no duplicates.

With only a few rows, the timing of sheer throughput is barely noticable.
iterations:1000
0.30 secs (.0003 secs per iteration) -- UNION
0.25 secs (.00025 secs per iteration) -- UNION ALL

iterations:10000
1.72 secs (.000172 secs per iteration)
1.09 secs (.000109 secs per iteration)

iterations:50000
10.94 secs (.0002188 secs per iteration)
8.48 secs (.0001696 secs per iteration)
So I turned it up a notch and added about 5000 rows to the table.
insert into people_coming_to_lunch
select table_name from all_tables;

5000 rows inserted
Here's the explain plan without the sort.

That's one less chunk of 5000 rows to process

Now the differences in performance stand out.
iterations:1000
6.79 secs (.00679 secs per iteration) -- UNION
2.85 secs (.00285 secs per iteration) -- UNION ALL

iterations:5000
42.91 secs (.008582 secs per iteration)
19.89 secs (.003978 secs per iteration)

iterations:5000
31.70 secs (.00634 secs per iteration)
22.83 secs (.004566 secs per iteration)

iterations:5000
30.75 secs (.00615 secs per iteration)
16.76 secs (.003352 secs per iteration)
Upto twice as long for the same statement?
No thanks, not when I could just type 4 extra characters to get an easy performance win.

Turns out this topic formed my first technical post. Back in 2009, after almost 10 years of using SQL, that was the first thing I blogged about. How about that.
Next