Development

This simple example is showing how to create a success message using dynamic actions. One thing needs to be mentioned though. The last one of the three actions depends on your current template:

var success_message = $('#P299_MESSAGE').val();

$('.t10messages').empty();
$('.t10messages').append('<div class="t10messages"><div class="t10success" 
style="display: block;"></div></div>');
$('.t10success')
.append(success_message)
.slideDown('slow');

The best thing is either to open the template and have a look at the structure of the success message part or to use firebug and inspect the HTML structure on your page.

Enjoy.

I don't know what I would do without LinkedIn.

Before this amazing website came along, I was pretty sure that my software skills could be described as:

1. Oracle PL/SQL: excellent
2. SQL: just OK
3. Database Design: well, sort of
4. HTML: dangerous
5. XML: really dangerous
6. Java: pitiful
7. Everything else: nothing going on

See, I am probably the most narrowly specialized high-tech "expert". I know PL/SQL and that's really about it.

At least, that's what I thought I knew. According to my endorsements on LinkedIn, however, well....I will let them speak for themselves:



Now all LinkedIn has to provide is a tool that takes these endorsements and constructs an iResume.

This simple example is showing how to use a simple checkbox column in a tabular form to enable/disable or check/uncheck another checkbox column.

If you have seen a colored scripts like a fish from “Session Snapper v.4″ by Tanel Poder or OraLatencyMap by Luca Canali, you may be also want to colorize your scripts.
I’ve created the script for this purposes with predefined substitution variables.
Just download colors.sql and use it like that:

@colors.sql;
prompt ::: &_C_RED ***  TEST PASSED  *** &_C_RESET :::
prompt ::: &_C_RED *** &_C_BLINK TEST PASSED &_C_BLINK_OFF *** &_C_RESET :::

You’l get something like this:

Spoiler:: List of variables SelectShow

Description Variable _C_RESET Reset formatting – Turn off all attributes _C_BOLD

_C_BOLD_OFF Set bright mode on/off _C_UNDERLINE

_C_UNDERLINE_OFF Set underline mode on/off _C_BLINK

_C_BLINK_OFF Set blink mode on/off _C_REVERSE

_C_REVERSE_OFF Exchange foreground and background colors _C_HIDE

_C_HIDE_OFF Hide text (foreground color would be the same as background) _C_BLACK

_C_RED

_C_GREEN

_C_YELLOW

_C_BLUE

_C_MAGENTA

_C_CYAN

_C_WHITE

_C_DEFAULT Font colors _CB_BLACK

_CB_RED

_CB_GREEN

_CB_YELLOW

_CB_BLUE

_CB_MAGENTA

_CB_CYAN

_CB_WHITE

_CB_DEFAULT Background colors

In addition, i want to show simple example of printing histograms.
We can simple print histogram with query:
Spoiler:: Сolorless histogram SelectShow

-- loading colors variables:
@inc/colors;
-- set max length of bar:
def _max_length=80;
-- columns formatting:
col bar format a&_max_length;
-- clear screen:
prompt &_CLS
with t as (-- it's just a test values for example:
            select level id
                 , round(dbms_random.value(1,100)) val 
            from dual 
            connect by level<=10
          )
select t.* 
      -- bar length is just " (value / max_value) * max_length" in symbols:
      ,floor( val * &_max_length / max(val)over() 
            ) as bar_length
      -- generating of bar:
      ,lpad( chr(176)
            ,ceil(val * &_max_length / max(val)over())
            ,chr(192)
           ) as bar
from t;

And now we can colorize it:
Spoiler:: Colorized script SelectShow

-- loading colors variables:
@inc/colors;
-- set max length of bar:
def _max_length=100;
-- column formatting
col bar format a&_max_length;
-- clear screen:
prompt &_CLS

-- test query which prints histogram(or may be simply bars?):
with t as (-- it's just a test values for example:
            select level id
                 , round(dbms_random.value(1,100)) val 
            from dual 
            connect by level<=10
          )
select 
       id
      ,val
      , case 
           when pct >= 0.9 then '&_C_RED' 
           when pct <= 0.4 then '&_C_GREEN'
           else '&_C_YELLOW'
        end 
        -- string generation:
      ||lpad( chr(192)
             ,ceil(pct * &_max_length)-9 -- color - 5 chars and reset - 4
             ,chr(192)
            )
      ||'&_C_RESET'
       as bar
from (
     select 
        t.*
       ,val / max(val)over() as pct -- as a percentage of max value:
     from t
     ) t2
/

This entry might seem a bit trivial, but from experience I know that sometime new features that are added to the product are not detected by developers who just continue working in the "traditional way". Well here is a quick update on such a feature - PPR:

Way back in 2009 I did a blog entry that showed how to implement partial page rendering (PPR) in ADF by setting the partialTrigger attribute of a field to depend on another.

Somewhere along the way* ADF got more advanced, and today there's a simpler way to do this without the need to define the partialTrigger property for your calculated field. Instead you just define dependency between fields in the model layer (ADF BC) and your View layer automatically handles the update to the screen. This is driven by the default use of the "ppr" mode for the ChangeEventPolicy of iterators in your page's binding layer.

Here is a quick demo that shows you how to define a calculated field that depends on the values of two other fields, and have it automatically display the value when the other fields are set.

For the record here is the bit of groovy code used in the calculated field:

if (CommissionPct != null)
{return Salary * (1+CommissionPct);}
else
return Salary

* - I'm not exactly sure in which version of JDeveloper this became the default behavior, but I just looked in 11.1.1.7 and the default changeEventPolicy is not PPR for a page - but it seems like you can change it to ppr to get it working.

My good friend, Marcelle Kratochvil, is the Oracle ACE Director for (as she put it to me) "all data/any data." She's been working with Oracle since V4 and specializes in Oracle Multimedia. She has been a beta tester for this product since Oracle8i and runs a SIG on multimedia and unstructured data. Check out her blog at: http://eternal-donut.blogspot.com.au.
Marcelle is a great speaker and able to communicate clearly how to work with complex data in Oracle. I first met and worked with Marcelle when she flew up from Australia to speak at my first Oracle PL/SQL Programming conference in November 2005). And now she's written her first book!Managing Multimedia and Unstructured Data in the Oracle Database has just been released by PackT and covers everything a person will need to know to get them working in multimedia. And Marcelle should know; as CTO of Piction, she works with customers all around the world including major museums and universities. She performs database administration on multi-terabyte Unix and Windows environments as well as doing serious development work with PL/SQL. She has designed and built a multimedia search engine, e-commerce system, security system, reporting engine, shipping system and her own XML parser and language specifically to handle the workflow needs of managing multimedia.
The book, according to Marcelle, provides an introduction to multimedia, how to search on it, how to manage it and how integrate it into your current environment. With lots of PL/SQL examples and schema setup scripts, Marcelle promises that this book will get you loading and using unstructured data in no time at all.
Best of all (to me), Marcelle is a strong advocate of PL/SQL and recommends it for anyone to use for building web based applications and for working with multimedia. My kind of Oracle technologist. J
To encourage us to check out her book, Marcelle is offering a chance to win a copy. All you have to do is choose from the most relevant section below, and send your answer to the question to Marcelle at marcelle@xor.com.au. Marcelle tells me that "The best, most creative and accurate answer will win a copy of the book." You must submit your answer by 31 May, 2013.
1. Open Category (open to everyone): Name three key advantages for storing multimedia in a database.

2. Museum/Gallery/Print Media (open to anyone in these industries): What is the biggest issue you face when digitizing and managing any multimedia in your organization?

3. Oracle (open to any Oracle employee):  Name five Oracle products that are tightly integrated with Securefiles and Oracle Multimedia.

 4. Oracle ACE Program (open to any Oracle ACE or ACE Director):  Which Oracle development tool or product is best designed for working with unstructured data (including multimedia)?

Yesterday an interesting question regarding tabular forms, collections and instant updates was asked in the Oracle APEX Forum. This example in my Demo Application shows how you can create a tabular form based on a collection and update this collection instantly. The whole example consists of three main parts:

1. save changes instantly
2. add rows and
3. delete rows

The whole code and the steps required to get it working are explained in the Code section.

Enjoy.

I know this is nothing new but I had a hard time to find out how to get the SQL of the currently viewed Interactive Report. In one of my projects I needed the exact query including the filtered values and sorting  in order to save the data into a PL/SQL collection and process it further. I tried using the existing IR Application Views but those do not provide all the information I needed. I stumbled upon this documentation page

http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_ir.htm#BABEFDJE

but was confused by the statement for getting the IR Query:

DECLARE
   l_report  apex_ir.t_report;
   l_query   varchar2(32767);
BEGIN     
    l_report := APEX_IR.GET_REPORT (
                    p_page_id   => 1,
                    p_region_id => 2505704029884282,
                    p_report_id => 880629800374638220);
    l_query := l_report.sql_query;
    for i in 1..l_report.binds.count
    loop
        dbms_output.put_line(i||'. '||
                             l_report.binds(i).name||
                             '='||l_report.binds(i).value);
    end loop;
END; 

If you run this statement, you will receive a concatenated string of binds used in for the filtering and the corresponding values and not the actual query (it is just not printed out). In addition to that, you need to combine this statement with the one for getting the last viewed report id:

DECLARE
    l_report_id number;
BEGIN     
    l_report_id := APEX_IR.GET_LAST_VIEWED_REPORT_ID (
        p_page_id   => 1,
        p_region_id => 2505704029884282);
END;

After talking to Patrick Wolf I realized that this statement delivers almost everything you need in order to get the complete query. I combined the two statements and created a function which you can use to get a query for any of your interactive reports including replaced binds. The function code is:

CREATE OR REPLACE FUNCTION get_report_sql (
   p_app_id     IN   NUMBER,
   p_page_id    IN   NUMBER,
   p_all_cols   IN   BOOLEAN DEFAULT TRUE
)
   RETURN VARCHAR2
IS
   v_report_id   NUMBER;
   v_region_id   NUMBER;
   v_report      apex_ir.t_report;
   v_query       VARCHAR2 (32767);
   v_column      VARCHAR2 (4000);
   v_position    NUMBER;
BEGIN
   SELECT region_id
     INTO v_region_id
     FROM apex_application_page_regions
    WHERE application_id = p_app_id
      AND page_id = p_page_id
      AND source_type = 'Interactive Report';

   v_report_id :=
      apex_ir.get_last_viewed_report_id (p_page_id        => p_page_id,
                                         p_region_id      => v_region_id
                                        );
   v_report :=
      apex_ir.get_report (p_page_id        => p_page_id,
                          p_region_id      => v_region_id,
                          p_report_id      => v_report_id
                         );
   v_query := v_report.sql_query;

   FOR i IN 1 .. v_report.binds.COUNT
   LOOP
      v_query :=
         REPLACE (v_query,
                  ':' || v_report.binds (i).NAME,
                  '''' || v_report.binds (i).VALUE || ''''
                 );
   END LOOP;

   IF p_all_cols
   THEN
      FOR c IN (SELECT   *
                    FROM apex_application_page_ir_col
                   WHERE application_id = p_app_id AND page_id = p_page_id
                ORDER BY display_order)
      LOOP
         v_column := v_column || ', ' || c.column_alias;
      END LOOP;

      v_column := LTRIM (v_column, ', ');
      v_position := INSTR (v_query, '(');
      v_query := SUBSTR (v_query, v_position);
      v_query := 'SELECT ' || v_column || ' FROM ' || v_query;
   END IF;

   RETURN v_query;
EXCEPTION
   WHEN OTHERS
   THEN
      v_query := SQLERRM;
      RETURN v_query;
END get_report_sql;

You can call this function in your application or in a PL/SQL package run from an application session like this:

DECLARE
   v_sql   VARCHAR2 (4000);
BEGIN
   v_sql := get_report_sql (:app_id, :app_page_id, FALSE);
   HTP.prn (v_sql);
END;

Setting the parameter

p_all_cols 

to TRUE would export all columns used in the IR SQL.

Enjoy.

I posted before on how to do code level debugging in your ADF Mobile application, but sometimes debugging is an overhead and you would rather just put out some log messages that will allow you to track what's going on where.

ADF Mobile has built in support for a logging framework and it is documented in this chapter in the ADF Mobile Developer Guide.

You can use a line of code like this in your code:

Logger.getLogger(Utility.APP_LOGNAME).logp(Level.INFO, this.getClass().getName(), "Shay","We invoked the button");

Then don't forget to set the right level of logging (and possibly the log message format) in the logging.properties file under your META-INF directory.

The logging chapter in the doc, doesn't mention where to actually see the messages being logged.

One utility that you can use to see your log messages comes with the Android SDK - look into the tools directory there and you'll find the ddms.bat file - run it and you'll be able to see the log messages from your application.

On the side of that utility you can also define filters to just show you the messages you are interested in.

Here is a quick demo showing how this all works together:

By the way - a comment I got pointed out that ddms is old school and you should be using the new monitor.bat at the same locaiton. This will basically work just the same and will look like this:

 More tips on debugging and logging in this blog entry by Joe

A new component that showed up in the JDeveloper 11.1.1.7 release is the af:listView component. This component will become more and more popular as more people target tablet devices with ADF Faces UI. The component allows you to create a scrollable list from a collection of data, and it also does fetching with ranges so you don't get too much network traffic. If you ever used a contacts list on a smart phone you'll recognize the list view source of inspiration - check out the runtime demo of the component here.

The component was actually backported into 11.1.1.7 from the 12c version - and while in the 12c version of JDeveloper there is better design time support for adding and binding a listview to a page, in the current release the work will mostly be manual.

However, for the lazy developer there are some shortcuts you can take to create the list component faster.

Here is a short video that shows you how to leverage an existing table component on your page to make the creation of the list component easier and with more functionality.

<span id="XinhaEditingPostion"></span>

Craig O'Neill is the Founder and CTO of CaoSys and a recent Formspider customer. This is the feedback he left on our Q&A site.

The dial gauge is a very visual way to show data in an application - and it has been there in Oracle ADF Mobile since 11.1.2. However in that release you could only use a range of 0-100 - well now you can do better with the new ADF Mobile version.

But there is one little trick to how this works compared to the way it works in the regular web ADF Faces gauge component, and if you don't notice it you might think you are still stuck in the 0-100 days - The trick is the new background property.

If you are working in the property inspector you can right-click in the property to see explanation and the available values.

Or if you work in the code editor just use the code insight to choose the value you want.

For defining your own range you'll want to use the costum options.

Then you can get something that looks like these:

Also note that you can control the indicator type with the indicator property.

<dvtm:dialGauge background="circleAntiqueCustom" indicator="needleLight" value="7000" minValue="4000" maxValue="9000"/>

Happy visualization.

Back in mid-March, I wrote:

A week ago, I played a game of Scabble with Veva and scored 32 points by placing the word "SEEN" on the board. Now, every letter in "SEEN" is worth 1 point, making it very difficult to get to 32 points. But I attached "SEEN" to a word on the board (of course!) and I got my 32 points by using a total of 8 letters, two of which were worth 3 points and the other 6 were worth 1 point each.

[Note: I must note a correction. I actually wrote earlier that I had scored 36 points, but I now realize that was an exaggeration, an honest mistake. Honest! It was only 32 points. I would be mortally embarrassed, but please know that when my wife and I play Scrabble, we don't keep score.]

I thought I'd see if anyone could figure out how to do it. It clearly wasn't a very compelling challenge, since no one answered. OK, so now I will reveal the secret!


On my previous turn, I had put down a seven letter word as follows:

That was a whole lot of points, right there, of course. But you expect that with a seven letter word.

On my next turn, however, I put down "SEEN" and now I expect you can figure out how I did it....

Haven't shaved for two weeks. Decided to give my skin a break, and I also wondered: what do I really look like? Shaving my head is, I have come to accept, the "main vain" thing I do in my life (well, that and painting flamboyan flowers on my toenails).

How much gray and white is there? Answer: Lots.

How much is left on top? Answer: Not very much.

Back in April 2010, I started up the PL/SQL Challenge (www.plsqlchallenge.com) to provide quizzes on PL/SQL, SQL, logic and Oracle Application Express.

Hundreds of people take these quizzes each day. Since that time, over 670,000 answers have been submitted by thousands of Oracle technologists.

But you can't please everyone. I received this outraged message yesterday regarding the PL/SQL Challenge.:

This was the utmost bad joke on me. Would you be so kind to make clear your statement? My experience on the site was out of the limits of the common sense. No question but a popup nightmare required. Kindly please do something. This site is BAD, BAD, BAD. The worst I have ever seen in overall behavior!

I try not to take such criticism personally, though this did seem a bit harsh.

Well, I asked for clarification. I suggested that maybe the problem lies somewhere in his computer or network.

:-)

Let's see what he says, if he replies.

External libraries are typically used to extend APEX so you (and the APEX development team) don't need to build certain features from scratch. 
One of the advantages of applying the latest patches is that the external libraries that come with APEX carry an update too. Most known examples of external libraries are AnyChart (chart engine in APEX) and the CKEditor (Rich Text Editor), but there are some other nice libraries that come with APEX too.
Here's an overview of the libraries that you will find in the images folder:

Some are declaratively integrated in APEX for example respond-js is part of the Blue Responsive theme, some others are not, for example twitter-bootstrap and another part is used in some sample or packaged apps, for example jquery-flot.

Having those libraries already there, means you can more easily integrate those in your own apps yourself too. Remember, if you want to go the extra mile it's worthwhile to create an APEX plugin so it's easier to reuse.

If you want to know more about those libraries, you find the links below:

Library(latest) version in APEXLatest version of library960.gs120203130207bgiframe2.1.12.1.2ckeditor3.6.44.1.1codemirror0.663.12ios-inspired-theme121106121106jquery1.7.11.9.1 / 2.0.0 (no support IE6/7/8)jquery-autocomplete1.1deprecated - part of jQuery UIjquery-colorpicker1.41.4jquery-flot0.70.8jquery-jstree0.9.9a2pre 1.0 fix 2jquery-mobile1.2.11.3.1jquery-qtip22.0-6.26.20112.0.1jquery-quicksand1.2.21.3jquery-ui1.8.221.10.3jquery-validate1.71.11.1modernizr2.5.32.6.2respond-js1.1.01.1.0selectivizr1.0.21.0.2twitter-bootstrap2.2.12.3.1

Many of the libraries come out with newer versions more frequently than there are APEX patches.
So note that APEX is not always including the latest version of the library. 

The below content is based on a David Peake's presentation at OOW 2012 and APEX World 2013.

As the Oracle Database 12c is not out yet at the time of writing, note that things might be different in the final product.

The Oracle Database 12c is a major release in Oracle history, as it contains a complete redesign of the underlying architecture. Two things will come back a lot when you read about Oracle DB 12c; the Container Database (CDB) and the Pluggable Database (PDB).

The below image shows how it works; you have a container database which the memory and processes are attached at, and then you can have multiple other databases that you can just plugin.


You find more information about this architecture here.

So what does Oracle DB 12c mean for APEX?



Oracle DB 12c comes with APEX 4.2.0. You can either install APEX in the CDB or in the PDB. APEX 4.2.0 is the minimum version you can install in Oracle DB 12c.
The advantage of running APEX in the CDB is that when you upgrade APEX, all database plugged into that CDB will have the latest version of APEX.
The advantage of installing APEX in the PDB is that you can run different versions of APEX in the different databases. If APEX is installed in the CDB, you can't remove APEX from CDB$Root anymore.
But one of the first things I would do is either upgrade APEX 4.2.0 to 4.2.2 or install APEX 4.2.2 from scratch depending which option you went with.

When you upgrade from DB 11g with APEX to 12c PDB here's a screenshot of the steps to take:


When you install APEX in the Oracle database 12c, some new options will become available in APEX, build on top of some new Oracle database 12c features.

The 12c database parameter - max_string_size will allow you to upgrade your VARCHAR2 from 4k (standard) to 32K (extended).
If you look in your APEX installation you will find /core/collection_member_resize.sql. Running that script will change the APEX collection VARCHAR2 columns from 4K to 32K.

In 12c there's also a new column type that is basically an "auto numbering field", something you would typically do with a sequence and trigger. In 12c the column type is called "Identity column" and it can have following values: ALWAYS - which means a Sequence number is always used, DEFAULT set value, DEFAULT ON NULL use seq nr if null.
When you run APEX in a 12c database, SQL Workshop will allow to specify an identity column in the create table wizards. Also the Create Form/Report wizard will create the correct item type (display only).

Another new column type in 12c database is the "Invisible Column": SELECT * from
will not display invisible columns. INSERT into will not insert into invisible columns. To insert into the invisible column you must explicitly set it.
In APEX - SQL Workshop - Object browser; the column will not show up.

Data Redaction, to mask your application data dynamically, is also a new feature in Oracle 12c. Before you had to setup VPD policies and do FGAC (Fine Grained Access Control), now it becomes more native in 12c. APEX will just show you what you are allowed to see following your policy. For example depending your policy you will see a credit card number like 4541 **** **** **** or null. You can read more about data redaction and masking in a blog post of Lewis Cunningham.

The warm days are coming... time for some APEX Ice Cream! 
People in my close environment, they know I can't resist ice cream and chocolate! So what I did was I bought a real ice cream machine, so I could make my ice cream myself :-)

The different ways and flavours you can make your ice cream is unlimited ... anyway, this is how it looks like when you put your cream into the ice machine and wait 30 minutes.

So, is this blog post about making ice cream? Not really... I just wanted to bring under your attention that under the lead of Lewis Cunningham some Oracle ACE and ACE Directors created a real cookbook (for food)!
Lewis came up with the idea and did the hard work of putting everything together. There's a nice video of Lewis explaining why he wanted to do it. In 2008, it was actually Lewis who put forward my name to become an Oracle ACE Director which I will be forever thankful. When he raised the idea about the cookbook, I didn't have to think long to put the recipe in to make one of my favourite desserts; home made ice cream with chocolate sauce, I call it "APEX Ice Cream". You can read in the recipe why :-)
I embedded the cookbook from Scribd below:
ACE Cookbook by Lewis Cunningham

If you want to make your ice cream responsive...

Here's how a version of a desktop version of APEX Ice Cream looks like:


And here's the smarth phone version, I didn't remove any content as you can see, it's just a bit closer together... APEX Ice Cream is why to good to show less on a smaller plate ;-)


Enjoy!

The new Oracle JDeveloper 11.1.2.4 just went out with a bunch of new features for ADF Mobile developers. Read more about it here - or watch this video.

One small feature that somehow got left out from the above two links is that there is a new UI component offered in ADF Mobile now - Star Rating.

The official tag name is dvtm:ratingGauge - and you can find it in the DVT Mobile AMX component palette under the Gauge section.

You can configure how many stars you want to show for your rating in the data section of the property inspector, and you can even specify to advance in half steps.

The component supports multiple shapes that you can choose from - star, diamond, circle, rectangle

You can also specify a different shape to be displayed for the unselected spots.

The code for the above 3 components is:
      <dvtm:ratingGauge id="ratingGauge1" value="2.5" maxValue="3" shape="circle"/>
      <dvtm:ratingGauge id="ratingGauge2" unselectedShape="dot" inputIncrement="half"/>
      <dvtm:ratingGauge id="ratingGauge3" shape="diamond"/>

After reading Michael Hichwa's blog post about APEX 4.2.2 where he mentions the patch set includes a major upgrade of all 18 productivity and all 16 database sample applications, I decided to install all of them again and check them out.



Those apps are a great way to see how to do certain things and see working applications behind the scenes. The packaged applications you first have to unlock before you can see the pages and source.

As APEX is meta-data driven you can also query for specific information.

I was for example interested in knowing which apps where using the Responsive Design Theme (Theme 25). Following query shows you per application which theme is used.
As you can see the sample apps are using Blue Responsive (theme 25) and most of the packaged apps are using the Cloud Apps theme. As those also have a mobile version, you can see the JQuery Mobile Smartphone theme is used there too.

select a.application_name, a.alias, a.compatibility_mode, a.pages, a.installation_scripts,
       listagg(t.theme_number, ',') within group (order by t.theme_number) as theme_numbers,
       listagg(t.theme_name, ',') within group (order by t.theme_name) as theme_names
  from apex_applications a, apex_application_themes t
 where a.application_id = t.application_id
group by a.application_name, a.alias, a.compatibility_mode, a.pages, a.installation_scripts   
order by 1

Here's the result:


The sample and packaged apps are not only nice examples (and useful apps), but they also contain some plugins that are not on the official Oracle Plugins page.

There are 21 plugins used in the sample and packaged apps. But two I believe are the same (CSS Bar Chart vs CSS Bar Charts and Slider vs APEX Slider), so that brings the total to 19 plugins to checkout!
And there are some really nice ones, like the Gantt Chart (completely in css, so works on the iPad) :


and Flot Pie Chart (build in JavaScript and CSS) :


Running following query shows you all the plugins in your workspace and in which application it was used:

select p.plugin_type, p.display_name, p.name,
       count(a.application_name) as nbr_app_using_plugin,
       listagg(a.application_name, ',') within group (order by a.application_name) as applications
from apex_appl_plugins p, apex_applications a
where p.application_id = a.application_id
group by p.plugin_type, p.display_name, p.name
order by 1,2

Here's the result:


I would definitely recommend having a look at the apps, the APEX team did a nice job on those.