Schlagwort: apex

Oracle Apex 23.1 Template Component

Bug or Feature ?

Should work in Cards Region.. but..

Info from Oracle:

Currently,  the Template Components cannot be used inside a Cards region. The reason is that Template Components are rendered on the server and Cards on the client.

We hope to lift this restriction in a future release, we’d like to push the Template Components to the client when needed. We must make sure the functionality is equal, currently the templates behave slightly different.

You can use the {with/} and {apply/} syntax in cards but this is undocumented. You first need to call apex.util.defineTemplates:


    name: „MY_TEMPLATE „,

    template: „#VAL#“



    VAL:=Hello World

{apply MY_TEMPLATE /}`);

Nice to know now.. i will try this solution..


Requirement: Every table of any schema should be editable.

  • Choose a schema
  • Choose a table
  • Select any columns
  • Generate an editing mask

So, I would say it’s something dynamic, but it works… see for yourself:

Imagine a scenario where you have a database with multiple schemas, each containing various tables and columns. Now, let’s say you have a requirement where you need to allow users to edit the data in any table of any schema. This can be quite a challenging task, considering the dynamic nature of the database structure. However, with the right approach, it is definitely achievable.

To begin with, the first step is to select the desired schema. This can be done by providing a dropdown or a list of available schemas for the user to choose from. Once a schema is selected, the next step is to choose a specific table within that schema. Again, you can provide a dropdown or a list of tables available in the selected schema for the user to pick from.

Now comes the interesting part. The user should be able to select any columns they want to edit within the chosen table. This can be done by displaying all the columns of the selected table and allowing the user to check or uncheck the ones they wish to include in the editing process. This flexibility gives the user full control over the data they want to modify.

Once the user has made their column selections, it’s time to generate an editing mask. The editing mask is a dynamic interactive grid that displays the selected columns as editable fields. It provides a convenient way for the user to modify the data within those columns. The mask should reflect the data type and constraints of each column, ensuring that the modifications are valid and consistent with the database schema.

By following these steps, you can create a dynamic solution that allows users to edit data in any table of any schema. This kind of flexibility empowers users to manage and manipulate their data efficiently, making your database application more user-friendly and versatile.


Oracle APEX App Multi-Tenant Capable

How do I turn a single-user app into a multi-tenant app?



Tom, I want to use your app, but only with my data!


There are a few ways to achieve this (here only 2):

  1. Clone the app in a workspace and mount it on its own Oracle schema, create all the necessary objects (which you can now define right inside Oracle Apex with the install scripts).

    not all sideeffects:
  • Update of the app means more complex app lifecycle management.
  • Maybe other processes have to be adapted which also write to the new database or schema.
  • There might be common data that should be used by all

2. Don’t clone the app and change the data model and customize the APP

not all side effects:

  • This means you have to filter in the Apex App depending on APP_USER or client of a user.
  • Depending on how the data is connected to Regions, IG, IR etc., you might have to make an adjustment in each page.

to point 2:

Here we can proceed very effectively because we can use the option of Oracle VPD-Virtual Private Database. We do not need to customize a single view or Apex SQL + PL/SQL query and that is really a huge advantage.

What was to be done?

In my case I had to extend the APP_USER in the relevant tables (of course not in attached views), I call the column MANDANT:


Next I had to tell the asynchronous load process to assign the data to the user. That means the DML (Insert , Update, Delete) in this table had to be extended by the column MANDANT. It would also have been possible to use a DML trigger that could assign the data correctly. But this was not an „easier“ option for me so I decided to customize the loading process.

As soon as the data of the other client was in the database, I saw my data aggregated with his in the Apex APP. For example, in this specific case, my car drove 133km instead of 100km that day.

Now the Oracle VPD comes into play:

First, create a function that allows to manage more complex criteria in one place: Here I restrict that the check only takes place if the session is in the context of the Apex application 100.

The APP_USER is my client. Oracle Apex automatically sets the APP_USER to the session context after login. Nice…

create or replace function get_mandant_criteria 
 object_schema IN VARCHAR2, 
 object_name VARCHAR2
return varchar2 DETERMINISTIC as
l_criteria varchar2(4000);
  -- Only apply VPD to specific APEX applications
  if sys_context('APEX$SESSION', 'APP_ID') in (100)
   l_criteria:=q'#(MANDANT=sys_context('APEX$SESSION', 'APP_USER'))#';
  end if;

  return l_criteria;
end get_mandant_criteria;

The function must of course also be used in the VPD therefore.
Using Oracle Virtual Private Database to Control Data Access

    ,object_name=>'CARLOG_DATA' -- für jede Tabelle anlegen wo gebr.

And already APEX App 100 is filtered to APP_USER client from login time.


On the subject of multi-tenant and more complex security concepts such as row-level and column-level security, we recommend our SCURTY!

© 2023 Sphinx Blog

Theme by Anders NorenUp ↑