Showing posts with label APEX. Show all posts
Showing posts with label APEX. Show all posts

Friday, 15 August 2025

Oracle APEX: "Number Field" Issue for Large numbers



The Issue

Recently I bumped into an issue with Oracle APEX page item with "Number field" type. 

It simply does not work if the page item contains large number as a value, such as workspace ID or a primary key value generated by SYS_GUID (related to Quick SQL tables and triggers generation).

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_GUID.html

https://docs.oracle.com/en/database/oracle/apex/23.1/aeutl/configuring-quick-sql-settings.html

So what is the issue?

During the item validation APEX displays the invalid number error EVEN if the number IS valid. 

Like on the screenshot below, where I entered a workspace ID and tried to submit the page.


BTW - I'm using the latest APEX version 24.2 at the moment 

Also, when I manually updated the value in the database table using SQLCl client and opened the page I also had an issue. Page was not loaded correctly.

Why is this issue happening?

I noticed a marker of JavaScript error in the developer bar and investigated an error:


It seems that the number is too big for JavaScript to handle?!

I checked the documentation and the largest number value that JavaScript can handle is 9007199254740991 and my Workspace ID value 43001042883076577 is much larger than that.

During the page load while the large number value was already in the table (updated via SQLCl client) I also got a JavaScript error.

When this issue started to happen?

Originally, I developed an application in APEX version 19.2 and there I had no issues. Large numbers were handled flawlessly and I was able to enter a workspace ID and submit the page. Data validation also worked well in case that I haven't entered a valid number.

But then I installed the application on another environment with latest APEX 24.2 and got this issue.

I was curious when this issue started to happen... somewhere between versions 19.2 and 24.2. 

So I started digging and figured out that APEX 21.1 introduced some number item enhancements like on-the-fly formatting and client side validation. You may check the following URL and the official release notes document (page 17):

https://www.oracle.com/a/otn/docs/apex-211-new-features.pdf

The client side implementation required JavaScript to be used and JavaScript has this number size limitation I already mentioned in previous chapter. 

What to do with this issue - a solution

Well, the simplest solution is to use an ordinary text item field instead of number item field.

Regarding a data validation, it can be done on a server-side as a ordinary APEX validation after the submit and before processing. There is a validation type "Item is numeric" and You may apply it on ordinary text field. Like this:




Pros - You may enter the large number and also validate an entered value 
Cons - There is no on-the-fly number formatting.

So, if You can survive without  the on-the-fly number formatting then this is a way to go. Otherwise You need to implement a solution by Yourself. (JavaScript supports big integer values) or search for an appropriate plugin. Maybe You can find something on apex.world website.

Monday, 22 July 2024

Oracle APEX - an Override for the BUG 36847892



The Oracle APEX team received an report and evidenced a BUG no 36847892 named "PACKAGE COMPILE ERROR FROM SUPPORTING OBJECT AND SQL WORKSHOP IF TABLE TYPE DECLARED"

The problem is that You gen an error when You try to compile the package which contains a table type definition.

For example:


Hopefully, this BUG should be fixed soon.


But meanwhile there is an override for this BUG.

Simply create another type of type RECORD at the beginning of the code and it should compile without an error. No need to use this type later in the code... just let it sit there for the time being 😸

For example like this:






Monday, 1 July 2024

Oracle APEX - open a Page in another Application from navigation Menu

Today I developed an application in Oracle APEX for our customer and bumped into an interesting issue.

I wanted to create a navigation menu item to open a page from another application. But it seems that it was not supported out-of-the-box!

APEX provided me with only 2 options to chose - either a page from the current application or a custom URL. There was no option to open a page from another application, which is an ordinary functionality in other parts of an APEX (button, link column in IR...).


So, I needed to make an override of this limitation.

The approach was pretty simple but effective:

  • First I created an application item to store an URL of a target page in another application
  • Then I created an application computation to compute the URL using APEX_PAGE API
  • Then I selected an URL as a target type 
  • And finally I used a calculated application item's value in a form of substitute variable as a target URL value

An example.

Created an application item to store a target URL:


Created an application calculation to calculate an URL:


Set up a menu list entry attributes and use an application item's value as a substitute string:



And it works like a charm!

Tuesday, 9 April 2024

Oracle APEX ZIP API - an Upgrade which is not officially documented

Recently I had a request from a customer to unzip files in the database. Of course, I used one of the most popular Oracle APEX APIs - APEX_ZIP - which is implemented in the APEX long time ago... I think in version 4 or 5.

At the moment I'm writing this blog I've been using the latest Oracle APEX 23.2. and I noticed some new functionality in this API... which is not yet officially documented in the Oracle API documentation.

https://docs.oracle.com/en/database/oracle/apex/23.2/aeapi/APEX_ZIP.html#GUID-270BFF3A-5FB1-4089-894E-978608F9BD87

So, what's new?

It goes for a new function named "get_dir_entries" and a new function "get_file_content" adapted for a new dir_entries approach.

Warning! Old function get_files is getting deprecated! This is stated in the package comments:

In which APEX version is this new functionality implemented?

I checked all APEX versions going from 23.2 toward previous versions and figured out that those new functionalities are implemented in version 21.2

Version 21.1:

Version 21.2:


But in the official documentation there is no mention of those new functions:


New approach vs old approach - benchmark

In the package comment it is stated that this new function is much faster and much more efficient.

So I tested performances on extracting 10112 files from a ZIP archive. There are 2 steps in the process: 

  1. get a list of all files from the ZIP file with get_dir_entries or ger_files function
  2. extract all files from the ZIP file with get_file_content function

Results are:

  • new approach with get_dir_entries took cca 30 seconds to finish
  • old approach with get_files - after more than 1 hour I stopped the script execution

Looking in details for a new approach:

  • the first step of preparing a file list took most of the time... around 29 seconds
  • to extract 10112 files - it took only a second.

Benchmark details (dbms output):


The code I used for testing:

DECLARE
    l_zip blob;
    l_files apex_zip.t_dir_entries;
    l_file blob;
    l_counter pls_integer := 1;
    l_index varchar2(32767);
    
    PROCEDURE p_out (p_text varchar2) IS
    BEGIN
        dbms_output.put_line(to_char(systimestamp, 'hh24:mi:ssXff') || ' - ' || p_text);
    END p_out;
    
BEGIN
    SELECT blob_content 
    INTO l_zip
    FROM import_zip_xml 
    WHERE id = 2;
    
    dbms_output.put_line('ZIP file size: ' || dbms_lob.getLength(l_zip) );
    p_out('Getting dir entries list...');
    
    l_files := apex_zip.get_dir_entries (
        p_zipped_blob => l_zip,
        p_only_files => true
    );
    
    p_out('Total files in the list: ' || l_files.count);
    p_out('Start extracting files...');

    --unzip files
    l_index := l_files.first;
    
    LOOP
        EXIT WHEN l_index is null;
        
        --p_out('Processing file ' || to_char(l_counter, 'fm00000') || ' ' || l_index || ': ');
        
        l_file := apex_zip.get_file_content (
            p_zipped_blob => l_zip,
            p_dir_entry => l_files( l_index ) 
        );
        
        l_index := l_files.next(l_index);
        l_counter := l_counter + 1;
    END LOOP;
    p_out('Finish.');
END;


One more detail I noticed - a function get_dir_entries is returning not only a collection of filenames found in the ZIP file but for every file it returns an uncompressed file size too.

This is the record type and the collection it returns:


A Conclusion

If You have a newer version of APEX, >= 21.2, this new approach is definitely worth using.

Performances are blazing fast.

Plus, there are additional metadata provided for files, like an uncompressed file size for example.

Friday, 22 March 2024

Oracle APEX - export and import XLIFF files for multiple pages

So... we are developing an Oracle APEX multi language application for a customer and recently they decided to deal with problematic translations on multiple pages for multiple languages through the application. It goes for approximately 300 pages and 2 languages (Dutch and French).

And they requested us to provide them separate XLIFF files for every single page and language - about 600 files - it was easier for them to translate this way. 

After the translation is done we should import all files back into the application translations repository.


While dealing with this task I figured out it was not so easy to accomplish, because the APEX App Builder does not fully support this functionality out-of-the-box. Therefore I have written a utility package, which can be downloaded from my GitHub repository

https://github.com/zorantica/db_apex_utils


App Builder built-in functionality for XLIFF export/import

Oracle APEX provides a functionality to export XLIFF files directly from App Builder... 









but it goes either for a complete application in one file OR a single page.













There is no option to select multiple pages and languages, as was the requirement. 😓

For importing XLIFF files there is a similar problem. Files need to be open one by one - there is no bulk upload and import.












Plus, there is no automatic language detection after the files upload regardless of the fact that the target language is contained in the XLIFF file:












My Solution

Since the APEX version 23.1 APEX_LANG API provides a function to generate a XLIFF file for a single page and language combination:








I wrote a PL/SQL code, which loops through all requested pages and languages, gets XLIFF files via APEX_LANG API and stores them as a single ZIP file. And this part of the requirement was fulfilled.


Regarding the import, there is a procedure in APEX_LANG API, which can be used to import / apply the XLIFF file:








So again, I wrote a PL/SQL code, which gets a ZIP file containing all XLIFF files, unzips XLIFF files, loops through all XLIFF files, for each file it determines the target language and applies files via APEX_LANG API. And this part of the requirement was also fulfilled.


At the end I wrapped all the code in the single package and added some additional functionalities like splitting XLIFF files in separate folders named by page groups, conducting seed and publish during the import...

And as I already mentioned, this package can be freely downloaded from the GitHib

https://github.com/zorantica/db_apex_utils

The example of the usage is 

DECLARE
    l_zip blob;
    
BEGIN
    --create a ZIP file
    l_zip := apex_lang_utils.get_xliff_per_page (
        p_app_id => 123,
        p_pages => '1,2,3',  --comma separated list of pages
        p_languages => 'nl-be,fr-be',  
--comma separated list of languages
        p_folder_per_group_yn => 'Y',  --separate files per folders
        p_only_modified_elements_yn => 'N'
    );
    
    --store the ZIP file into TEST table
    DELETE test;
    INSERT INTO test (id, blob_doc)
    VALUES (1, l_zip);
    
    COMMIT;
END;

 

Tuesday, 27 February 2024

Oracle APEX - DML on Collections

Introduction

APEX Collections are introduced in the Oracle APEX since... forever.

They provide a really nice replacement for Global Temporary Tables and can be very useful in a wide range of scenarios and use cases.

Data stored in APEX collections can be read via APEX_COLLECTIONS view and can be manipulated via APEX_COLLECTION API. 

But unfortunately Oracle APEX doesn't provide a functionality to execute DML statements on APEX collections and this is something I've been missing since... forever 😊

This means You can not execute the following statement successfully:

UPDATE apex_collections
SET 
    c002 = c001,
    n002 = n002 * 2,
    d001 = trunc(sysdate + n001)
WHERE 
    collection_name = 'MY_COLL'
AND n001 > 50

You are going to get an error ORA-01031: insufficient privileges. 

In order to modify data in the collection You need to write a PL/SQL code instead, like this:

BEGIN
    FOR t IN (
        SELECT 
            seq_id,
            n001,
            c001,
            n002
        FROM apex_collections
        WHERE 
            collection_name = 'MY_COLL'
        AND n001 > 50
    ) LOOP
        apex_collection.update_member (
            p_collection_name => 'MY_COLL',
            p_seq => t.seq_id,
            p_c002 => t.c001,
            p_n002 => t.n002 * 2,
            p_d001 => trunc(sysdate + t.n001)
        );
    END LOOP;
END;


But fortunately, I found a way on how to execute DML statements on APEX collections!

Solution scripts and examples can be found in the GIT Hub repository here:

https://github.com/zorantica/db_apex_utils


The Idea behind the Solution

The idea behind the solution is to create a local updateable view in the target schema named "apex_collections_dml", on which You can execute DML statements.

This view contains an "instead of" trigger, which is triggered by DML operations and manages data in APEX collections via APEX_COLLECTION API.

The view is based on a pipelined function named "apex_collections_dml_pkg.get_coll_data", which simply returns data from APEX_COLLECTIONS view. The select statement / view source is 

SELECT * 
FROM 
    table (apex_collections_dml_pkg.get_coll_data)


This way a following UPDATE statement can be executed without ORA errors:

UPDATE apex_collections_dml
SET 
    c002 = c001,
    n002 = n002 * 2,
    d001 = trunc(sysdate + n001)
WHERE 
    collection_name = 'MY_COLL'
AND n001 > 50


Why this Approach with the pipelined Function?

Well... the first idea was to create an instead of trigger directly on the APEX_COLLECTIONS view. But I have no privileges to do so. Plus, this also means messing up with APEX objects, which is never a good option. Plus, if You are on the cloud You have no access to APEX objects.

Second idea was to create a local view APEX_COLLECTIONS_DML, which reads data directly from APEX_COLLECTIONS view, and to create an instead of trigger on the view. But the problem was that the database is checking privileges on underlying objects BEFORE executing the trigger and therefore I ended up with "ORA-01031: insufficient privileges" error.

In order to avoid this ORA error I needed to somehow separate APEX_COLLECTIONS view object from my local DML view... and pipelined function was the solution I was looking for.


INSERT statement vs apex_collection.create_collection_from_query

The collection can be created in both ways and the SELECT statement, which prepares data is the same.

But... the SELECT statement used with the procedure apex_collection.create_collection_from_query is executed and validated during the runtime. And if the data structure used in the SELECT statement changes (for example some table is dropped or some columns are renamed) You are going to get errors during the runtime only.

On the other hand INSERT statement is validated during the development and compilation phase and program units with INSERT statement are going to be invalidated... as a good sign that something is not right...


Performance Concerns

I noticed no significant performance issues with this approach on the range of few hundreds or thousands of collection members.


Monday, 19 February 2024

Oracle APEX - beware of the "Quick Edit" button while editing the code in a popup editor!

I bumped into a problem with Oracle APEX App Builder while editing the page and been able to reproduce it on apex.oracle.com (current APEX version 23.2.4).

So, what happened was - the JavaScript code I was editing in the JavaScript popup editor overwrote item's label! This happened when I used the "Quick Edit" button in the running page and selected a page item with the editor still open.

Popup editors in the Page Designer are modal and this should prevent user from selecting another object while they are open... but with Quick Edit button this can be achieved and can lead to a problem if changes in the editor are accepted.

Other editors like PL/SQL editor or CSS editor behave in the similar way, but they are overwriting another item attributes... not necessarily the label. For example PL/SQL editor is overwriting text item's subtype, CSS editor is overwriting Template Options, File URL editor is is overwriting text item's Column attribute... HTML editor is overwriting the text item's label, similar to the JavaScript editor.

Beside those editors I mentioned, other editors might have the same behavior but I haven't tested them all.

Friday, 13 October 2023

Oracle APEX - export multi language application components with APEX_EXPORT API

If You want to export multi language application components with APEX_EXPORT API (function apex_export.get_application) it is pretty tricky to get them exported for other languages.

Parameter p_with_translations is not working. Scripts will always be generated for main language.

But hopefully there is a way to get component scripts for translated applications.

In my testing case I have the main application in English language (app ID 124) and 2 translations to Dutch (app ID 999) and French (app ID 998). See pic below.


To get the component scripts for page 1 and Yes/No LOV (shared component) from main application ID 124 in English language the syntax is 

    lrFiles := apex_export.get_application(
        p_application_id => 124,
        p_split => false,
        p_with_translations => true,
        p_components => apex_t_varchar2( 'PAGE:1', 'LOV:39437346231509935' )
    );

But to get those scripts for translated application ID 999 in Dutch language the syntax is 

    lrFiles := apex_export.get_application(
        p_application_id => 999,
        p_split => false,
        p_with_translations => true,
        p_components => apex_t_varchar2( 'PAGE:1.999', 'LOV:39437346231509935.999' )

    );

So, the main component ID stays the same but it is followed by dot plus application ID.
Generated script:


Saturday, 23 April 2022

Oracle APEX_DICTIONARY and DBA_DICTIONARY views

Oracle APEX is providing a dictionary view called APEX_DICTIONARY which is returning names and comments for all other dictionary vies.

For example:

SELECT *
FROM apex_dictionary
;

A result:


So, rows with column ID 0 contain a view name and other rows with numbers >= 1 contain column names. 

Sunday, 4 April 2021

Oracle APEX - export static application files as installation scripts

If You want to export static application files as components from APEX GUI... well, it is not working.

Static application files are provided in component type list... but if You select them and click on GO... a list is empty and You can not select any static file to export:


Also, static application files can not be found in APEX_APPL_EXPORT_COMPS view. If You execute following script for application which contains static application files to get all component types... there are no static app files.

SELECT DISTINCT type_name
FROM apex_appl_export_comps
WHERE application_id = 400
;




Fortunately, there is a way to get those script files using PLSQL and APEX_EXPORT package.

Let's look at following example - application 400 and static file "js/page6100.js". 
First You need to locate exact component file name for desired file with PLSQL code:

DECLARE

    l_files apex_t_export_files;

BEGIN
    --get files
    l_files := APEX_EXPORT.get_application(
        p_application_id => 400,
        p_split => true,
        p_with_translations => true,
        p_with_comments => true
    );

    FOR t IN 1 .. l_files.count LOOP
        dbms_output.put_line(l_files(t).name);
    END LOOP;

END;


Result:

Then You can add IF statement in PLSQL code and get script content for page "js/page6100.js" component:

DECLARE
    l_files apex_t_export_files;

BEGIN
    --get files
    l_files := APEX_EXPORT.get_application(
        p_application_id => 400,
        p_split => true,
        p_with_translations => true,
        p_with_comments => true
    );

    FOR t IN 1 .. l_files.count LOOP
        --dbms_output.put_line(l_files(t).name);
        
        if l_files(t).name = 'f400/application/shared_components/files/js_page6100_js.sql' then
            dbms_output.put_line(l_files(t).contents);
        end if; 
    END LOOP;
    
END;

Result:

In this example I used DBMS_OUTPUT package to get script files but You can also insert them in some table / CLOB column.

Generated script contains only procedure call to crate a file (wwv_flow_api.create_app_static_file). So, if You need to remove existing file first then You should modify this script manually and add wwv_flow_api.remove_app_static_file procedure call.

Monday, 26 October 2020

Oracle APEX selected components export from API (APEX_EXPORT)

 Oracle APEX 20.1 introduced new functionality for application components export. Function APEX_EXPORT.get_application got a new parameter named P_COMPONENTS.

So, with this parameter it is possible to make a list of components for which a scripts will be returned. No need to export and split full application.


But beware!

P_SPLIT parameter (boolean value) is still functional and it has a great influence on function result!


If value of P_SPLIT parameter is FALSE, function will return one script for all components from P_COMPONENTS parameter.


If value is TRUE it will return multiple scripts. 

For every component 2 scripts will be created:

  • delete component script
  • create component script
Plus, additional scripts will be generated:
  • set environment (as first script in collection)
  • end environment (as second last script in collection)
  • main install script which executes all generated scripts (as last script in collection)

So, do not make a mistake like me - to set a P_SPLIT parameter to TRUE and expect one script per component to be generated (for example 3 scripts for 3 pages). There are only two scenarios - one script for everything OR multiple scripts (environment, delete components, create components, finish install, main install script).

Monday, 12 October 2020

Use SELECT statement with inline PLSQL in APEX

If someone wants to use a SELECT statement which contains inline PLSQL as region source in APEX it will not work out-of-the-box.

Example of such a statement is:

WITH
    FUNCTION f_2x(p_number number) RETURN number IS
    BEGIN
        RETURN p_number * 2;
    END;
SELECT 
    level as c_level,
    f_2x(level) as c_x2
FROM dual
CONNECT BY level < 10
;

APEX validates it as a valid statement BUT when page is run it returns an error "Unsupported use of WITH clause":



There are 2 possible solutions:

  1. to create a view and then use it in region source (preferably)
  2. to use a WITH_PLSQL hint
Hint should be set as region property "Optimizer hint":


but once set... SELECT statement can not be validate any more!


Maybe this will be solved once in future versions of APEX... but until then I suggest to create and use database view for such a scenarios.


Database view creation example:

CREATE OR REPLACE VIEW v_tica_test AS
WITH
    FUNCTION f_2x(p_number number) RETURN number IS
    BEGIN
        RETURN p_number * 2;
    END;
SELECT 
    level as c_level,
    f_2x(level) as c_x2
FROM dual
CONNECT BY level < 10
;
/

or with outside wrapper (example of HINT usage):

CREATE OR REPLACE VIEW v_example AS
SELECT /*+ WITH_PLSQL */
    v.*
FROM 
    (
    WITH
        FUNCTION f_2x(p_number number) RETURN number IS
        BEGIN
            RETURN p_number * 2;
        END;
    SELECT 
        level,
        f_2x(level) as x2_value
    FROM dual
    CONNECT BY level < 10
    ) v
;
/

Sunday, 23 June 2019

APEX Page Processing when buttons have similar names

When a button on page has a name which is contained in another button's name (for example button "SAVE" and another button "SAVE_AND_CONFIRM") be careful with defining Server-side condition for page process, computations or branches when Type is "Request is contained in Value".

If Value of Server-side condition is "SAVE_AND_CONFIRM" (so we want for example to execute page process when button "SAVE_AND_CONFIRM" is pressed) process will be executed also when button "SAVE" is pressed. I presume that this is happening because APEX is using LIKE operator to check if request is contained in value... and in our example SAVE is contained in both button names/requests.

My quick (and maybe dirty solution) was to rename button "SAVE_AND_CONFIRM" to "SV_AND_CONFIRM".

Wednesday, 20 March 2019

Oracle APEX - be careful not to duplicate APEX_ITEM ID for different items

When page gets complicated and there are a lot of regions, it is easy to forget which items created with APEX_ITEM package have which ID.

This IDs is later used in page processes and are referenced as APEX_APPLICATION.G_Fxx collections (where xx is ID defined in APEX_ITEM funtions call).


For example, if we have two SELECT statements as source for two different regions on page

SELECT
    level as nivo,
    apex_item.checkbox(1, level) as nivo_input
FROM dual
CONNECT BY level <= 10;

SELECT
    level + 30 as nivo,
    apex_item.text(1, level + 30) as nivo_input
FROM dual
CONNECT BY level <= 10;



There will be no error when page is generated and shown. Items are working as they are supposed to. You can mark checkboxes from first region and enter/alter values in second region.


BUT!!!!


If we have after submit process which loops through collection APEX_APPLICATION.G_F01 and do some work... we encounter a problem. LOOP takes data from both items and do the operation in process (deletes rows).

Example of process:

APEX_DEBUG.ENABLE;

FOR t IN 1 .. apex_application.g_f01.count LOOP
    apex_debug.message('how many: ' || apex_application.g_f01.count);
END LOOP;



Debug after processing:


We can see that process combined values from 2 items.

Using TABLE operator with apex_application.g_fxx collections

From Oracle version 12.1 it is possible to use TABLE operator with APEX apex_application.g_fxx collections to get Your data directly into SELECT statement as table.

Example:

  • In interactive or classic report define one field as checkbox (APEX_ITEM package)
  • Create new process which executes after submit
  • Inside PLSQL code You can use following SELECT to get checked values


SELECT column_value as vl
FROM table(apex_application.g_f01)
WHERE column_value is not null

Examples and detailed explanation:

https://roelhartman.blogspot.com/2018/02/apexapplicationgf0x-array-processing-in.html



Thursday, 14 March 2019

Oracle APEX - Conditions for Classic Report Gallery Row Template

When editing Classic Report template of style Gallery it is possible to define 4 different row templates, which are later used to render a page region correctly.




Interesting is that APEX will choose first one which meet desired conditions, similar to CASE or IF statement.

So, in upper example first template will be chosen when value exists for both CARD_LINK and CARD_LINK_TARGET. Every card in gallery will have link with target set.

Second template will be chosen only when CARD_LINK value exists. No target in link will be set.

Third template will be chosen when conditions for first two are not met.


It is possible to quickly edit template when You have it used on page. Find it on Page Shared Components:


On right side there is Edit Component Button: