Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. 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.

Friday, 20 June 2025

Oracle Database - Conditional Compiling based on the Table/View Accessibility

The Scenario

Recently I've got a request to write a PL/SQL function and include it in a package, which counts a number of active sessions in the database. The following code was working well.

CREATE OR REPLACE PACKAGE BODY tica_demo AS

FUNCTION count_active_sessions RETURN number IS
    l_count number;
BEGIN
    SELECT count(*)
    INTO l_count
    FROM v$session
    WHERE status = 'ACTIVE';

    RETURN l_count;
END count_active_sessions;

END tica_demo;
/

But then additional request came and this was a problematic one. 

"This package is going to be a part of the product, which we are going to install on various environments. And on some environments a user/schema, where the package is installed does not have access to V$SESSION view. 
But nevertheless we want this package to be compiled and valid after the installation.
And if the user/schema, where is the package installed, does not have the access to the V$SESSION view we want the function to return a value -1".

What happened. They tried to install / compile the package on one environment and got an error ORA-00942: table or view does not exist. Like on the pic below.

And this is where a conditional compiling of PL/SQL code comes handy. In next 2 chapters I'm going to present You a Conditional Compiling Solution and, as alternative, a dynamic SQL solution.

A Conditional Compiling Solution

Conditional compiling is providing an option to include or exclude a piece of code from Your program units during the compilation, based on the Boolean condition. With directives like $IF boolean condition $THEN ... $ELSE ... $END You can control it. Read more on this topic here:

https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/conditional-compilation1.html#GUID-75F8A716-B2BE-494F-B150-1B4AB802DF25

And my (optimistic) idea for the solution was: 

  • to create a function named OBJECT_ACCESSIBLE returning Boolean value, which is checking if the current user has the access to the certain object like table, view, package
  • to implement conditional compiling in my package by using OBJECT_ACCESSIBLE function as a boolean condition; like this
    $IF object_accessible('v$session') $THEN ... $ELSE 

OBJECT_ACCESSIBLE function:

CREATE OR REPLACE FUNCTION object_accessible (
    p_name varchar2,
    p_type varchar2 --TABLE, VIEW, PLSQL, SEQUENCE, TYPE
) RETURN boolean IS

    l_schema VARCHAR2(4000);
    l_part1 VARCHAR2(4000);
    l_part2 VARCHAR2(4000);
    l_dblink VARCHAR2(4000);
    l_part1_type NUMBER;
    l_object_number NUMBER;

    l_no_object exception;
    PRAGMA EXCEPTION_INIT(l_no_object, -6564);

BEGIN
    dbms_utility.name_resolve (
        name => p_name,
        context =>
            CASE p_type
                WHEN 'TABLE' THEN 0
                WHEN 'VIEW' THEN 0
                WHEN 'PLSQL' THEN 1
                WHEN 'SEQUENCE' THEN 2
                WHEN 'TYPE' THEN 7
            END,
        schema => l_schema,
        part1 => l_part1,
        part2 => l_part2,
        dblink => l_dblink,
        part1_type => l_part1_type,
        object_number => l_object_number
    );

    RETURN true;

EXCEPTION WHEN l_no_object THEN
    RETURN false;

END object_accessible;
/

If the user / schema, where the package is installed, has proper SELECT privileges on the view V$SESSION then include the code which counts a number of sessions and returns it's number. Otherwise exclude this piece of code and include RETURN -1. Simple as that.

But I've got a following error when I implemented this function in the conditional compiling
PLS-00174: a static boolean expression must be used

Seems that boolean conditions used in conditional compiling are pretty limited and You can not use functions or other more complex conditions, only static boolean expressions like comparing 2 numbers.

But then I got an idea to introduce Inquiry Directives in the solution. Inquiry Directives resemble to boolean variables, whose true or false values are stored in Your current database session and they can be used as static boolean expressions.

And it worked! So:

  • First, I created a procedure, which sets a true/false value to a named Inquiry Directive based on the object accessibility, like v$session view in my example
  • Second, I executed this procedure before the main package is compiled; the procedure sets the appropriate true/false value to Inquiry Directive named "v$sessionAccessible"
  • Third, I used Inquiry Directive "v$sessionAccessible" in the conditional compiling within the package body source and compiled the package

First - The procedure code:

CREATE OR REPLACE PROCEDURE set_obj_exists_cnd_cmp_flag (
    p_inquiry_directive varchar2,
    p_object varchar2,
    p_type varchar2
) IS
BEGIN
    EXECUTE IMMEDIATE
        'ALTER SESSION SET PLSQL_CCFLAGS = ''' ||
        p_inquiry_directive || ':' ||
        CASE WHEN object_accessible(p_object, p_type) THEN 'TRUE' ELSE 'FALSE' END ||
        ''''
    ;
END set_obj_exists_cnd_cmp_flag;
/

Second - the code to execute a procedure before the package compilation:

BEGIN
    set_obj_exists_cnd_cmp_flag (
        p_inquiry_directive => 'v$sessionAccessible',
        p_object => 'V$SESSION',
        p_type => 'VIEW'
    );
END;
/

Third - The package source with the conditional compiling included:

CREATE OR REPLACE PACKAGE BODY tica_demo AS

FUNCTION count_active_sessions RETURN number IS
    l_count number;
BEGIN
    $IF $$v$sessionAccessible $THEN
        SELECT count(*)
        INTO l_count
        FROM v$session
        WHERE status = 'ACTIVE';

        RETURN l_count;
    $ELSE
        RETURN -1;
    $END

END count_active_sessions;

END tica_demo;
/

Using DBMS_PREPROCESSOR package You may check how the source code of the package should look based on the conditional compiling. Execute the following code and the source is going to be printed as DBMS output.

BEGIN
  DBMS_PREPROCESSOR.print_post_processed_source (
    object_type => 'PACKAGE BODY',
    schema_name => 'ZORANDBA',
    object_name => 'TICA_DEMO');
END;
/

In case that the user has no access to v$session view the package body source is going to look like this (notice empty space where lines of code are skipped):

And this approach can be used for other checks and boolean conditions too, not only the object accessibility. 

Some pros and cons of this solution:

Pros:

  • Compiled code is natively compiled and executed and not wrapped in dynamic SQL or treated in some other unusual way.
  • You may use any kind of conditions and not only static boolean expressions for conditional compiling
  • It is much easy to handle a longer code with conditional compiling than with dynamic SQL

Cons:

  • If the PL/SQL code is re-compiled without Inquiry Directive values properly set, the resulted compiled code might not execute as expected. In our example the function might return -1 even if the user has access to the v$session view.

Alternative - a Dynamic SQL Solution

Dynamic SQL is constructing and executing SQL and PL/SQL statements during the runtime. This means that even if the database user/schema, where the PL/SQL code is compiled, does not have access to certain object (like in my scenario V$SESSION view), the code is going to be compiled and valid.

Actual errors are going to happen during the runtime.

The following package code can be compiled and be valid even if the user does not have a select grant to the V$SESSION view, and the request from the scenario was fulfilled:

CREATE OR REPLACE PACKAGE BODY tica_demo AS

FUNCTION count_active_sessions RETURN number IS
    l_count number;

    l_no_grant exception;
    PRAGMA EXCEPTION_INIT (l_no_grant, -00942);

BEGIN
    EXECUTE IMMEDIATE q'[
        SELECT count(*)
        FROM v$session
        WHERE status = 'ACTIVE'
    ]'
    INTO l_count;

    RETURN l_count;

EXCEPTION WHEN l_no_grant THEN
    RETURN -1;

END count_active_sessions;

END tica_demo;
/

Some pros and cons of this solution:

Pros:

  • A pretty simple solution, which does not require any additional pre-compiling or program logic.

Cons: 

  • This implementation can be especially tricky and complex if longer SQL or PL/SQL code is in play. I had an example of another package, where we needed to handle around 2500 lines of code.
  • It is a dynamic SQL and not a natively compiled and executed SQL or PL/SQL, which might have an impact on performances.
  • Dynamic SQL is not sensitive to DDL changes and the package is not going to become invalid if for example some tables, which are used in SELECT statement are dropped. The undesired behavior or error is going to occur only during the runtime. As consequence, it might happen that the this is noticed on the very production and not caught and handled during the development.

Monday, 20 January 2025

Oracle Database - Foreign Key Constraints and mutating Triggers

An Issue

If You have foreign keys in Your database (and chances are huge, if You're using a relational database like Oracle properly 😎) which have ON DELETE CASCADE or ON DELETE SET NULL set, be aware that cascade deletion or records or setting a null value is also activating table triggers on child tables.
And this can cause a famous mutation ORA-04091 error if a parent table is referenced in child table triggers!

More about this error:
https://docs.oracle.com/en/error-help/db/ora-04091/?r=23ai

But why is that so?
I mean, according to the documentation, the mutation occurs when the table iteslf is referenced in the trigger and the DML transaction is still in progress... so the data in the table is not in the final state and the table is "mutating".

And in the scenario I mentioned a parent table is referenced in the trigger, not the child table.

The explanation is the following - because of the foreign key constraint and the cascade delete, the data in both parent and child tables is changing within the same transaction and not only the data in the child table where the trigger is located.
Consequently, both tables are still mutating and the error occurs.

An Example

There are 3 tables joined with foreign keys:

  • financial periods DEMO_FIN_PERIODS
  • invoices DEMO_FIN_INVOICES
  • invoice lines DEMO_FIN_INVOICE_LINES

There is a trigger on DEMO_FIN_INVOICE_LINES table, which prevents altering or deleting invoice lines if the finance period is locked.

Scripts to create tables:

CREATE TABLE DEMO_FIN_PERIODS (
  period_id number not null primary key,
  year_month varchar2(7),
  lock_period_yn varchar2(1)
);

CREATE TABLE DEMO_FIN_INVOICES (
  invoice_id number not null primary key,
  invoice_date date,
  invoice_location varchar2(100),
  period_id number,
  constraint demo_fin_invoice_period_fk FOREIGN KEY (period_id) REFERENCES demo_fin_periods(period_id)
);

CREATE TABLE DEMO_FIN_INVOICE_LINES (
  invoice_line_id number not null primary key,
  invoice_id number not null,
  item varchar2(100),
  price number(10, 2),
  constraint demo_fin_invoice_lines_fk FOREIGN KEY (invoice_id)
     REFERENCES demo_fin_invoices(invoice_id)
     ON DELETE CASCADE
);

Populate data in tables:

Insert into DEMO_FIN_PERIODS
  (PERIOD_ID, YEAR_MONTH, LOCK_PERIOD_YN)
Values
  (1, '202401', 'Y');

Insert into DEMO_FIN_INVOICES
  (INVOICE_ID, INVOICE_DATE, INVOICE_LOCATION, PERIOD_ID)
Values
  (1, TO_DATE('22.12.2024', 'DD.MM.YYYY'), 'Ljubljana', 1);

Insert into DEMO_FIN_INVOICE_LINES
  (INVOICE_LINE_ID, INVOICE_ID, ITEM, PRICE)
Values
  (1, 1, 'bread', 2.5);
Insert into DEMO_FIN_INVOICE_LINES
  (INVOICE_LINE_ID, INVOICE_ID, ITEM, PRICE)
Values
  (2, 1, 'milk', 1.2);

Create lock trigger on invoice lines:

CREATE OR REPLACE TRIGGER demo_fin_invoice_lin_trg_lock
BEFORE INSERT OR UPDATE OR DELETE ON demo_fin_invoice_lines
FOR EACH ROW
DECLARE
  l_locked_yn demo_fin_periods.lock_period_yn%TYPE;
BEGIN
  SELECT dfp.lock_period_yn
  INTO l_locked_yn
  FROM
    demo_fin_invoices i --referencing invoices table!!!
    JOIN demo_fin_periods dfp ON i.period_id = dfp.period_id
  WHERE i.invoice_id = nvl(:new.invoice_id, :old.invoice_id);

  if l_locked_yn = 'Y' then
    RAISE_APPLICATION_ERROR(-20001, 'Period is locked!');
  end if;
END;

An Error

If we try to delete an invoice for a locked financial period we expect a custom error "Period is locked!" to be produced from a trigger.

DELETE FROM demo_fin_invoices
WHERE invoice_id = 1;

But instead of a custom error, a mutating trigger error occurs and it is mentioning DEMO_FIN_INVOICES table, not DEMO_FIN_INVOICE_LINES table:

[Error] Execution (87: 13): ORA-04091: table HR.DEMO_FIN_INVOICES is mutating, trigger/function may not see it
ORA-06512: at "HR.DEMO_FIN_INVOICE_LIN_TRG_LOCK", line 4
ORA-04088: error during execution of trigger 'HR.DEMO_FIN_INVOICE_LIN_TRG_LOCK'

Solutions for this scenario

Separate transactions

Delete child records from a table DEMO_FIN_INVOICE_LINES first. Then, if child records are successfully deleted, delete the parent record from a table DEMO_FIN_INVOICES.

DELETE FROM demo_fin_invoice_lines
WHERE invoice_id = 1;

DELETE FROM demo_fin_invoices
WHERE invoice_id = 1;

In our scenario a custom error is going to be risen from a trigger when the first delete statement is executed, as expected.

[Error] Execution (90: 13): ORA-20001: Period is locked!
ORA-06512: at "HR.DEMO_FIN_INVOICE_LIN_TRG_LOCK", line 16
ORA-04088: error during execution of trigger 'HR.DEMO_FIN_INVOICE_LIN_TRG_LOCK'

Pros: Simple solution and it requires no changes in triggers, no additional storage objects and has no significant impact on performances.
Cons: 2 separate transactions required.

Read data from a parent table before the tranasaction

This is a classic approach for solving a mutating trigger issue.
Data is read from a mutating table into a temporaty storage like global temporary table, PL/SQL collection, APEX collection... and it is used in the trigger from temporary storage instead.

Let's create a global temporary table for storing data of invoice ID and locked period.

CREATE GLOBAL TEMPORARY TABLE demo_fin_invoices_lock_gtt (
  invoice_id number,
  lock_period_yn varchar2(1)
) ON COMMIT DELETE ROWS;

INSTEAD OF triggers are brilliant in solving mutating table issues.
Let's populate the global temporary table before the delete statement.
Then let's use this data to check if the period is locked.

CREATE OR REPLACE TRIGGER demo_fin_invoice_lin_trg_lock
FOR INSERT OR UPDATE OR DELETE ON demo_fin_invoice_lines
COMPOUND TRIGGER

BEFORE STATEMENT IS
BEGIN
  INSERT INTO demo_fin_invoices_lock_gtt (invoice_id, lock_period_yn)
  SELECT
    i.invoice_id,
    dfp.lock_period_yn
  FROM
    demo_fin_invoices i
    JOIN demo_fin_periods dfp ON i.period_id = dfp.period_id
  ;
END before statement;

BEFORE EACH ROW IS
  l_locked_yn varchar2(1);
BEGIN
  SELECT lock_period_yn
  INTO l_locked_yn
  FROM demo_fin_invoices_lock_gtt
  WHERE invoice_id = nvl(:new.invoice_id, :old.invoice_id);

  if l_locked_yn = 'Y' then
    RAISE_APPLICATION_ERROR(-20001, 'Period is locked!');
  end if;

END BEFORE EACH ROW;

END;

Pros: Data deletion is executed as a single transaction.
Cons: It requires global temporary table to be created + reading data into a global temporary table slows down data deletion.

Wednesday, 25 September 2024

Oracle Database - case sensitive Columns/Components in PL/SQL Collections, Records and Objects



Introduction

Regarding table and column names, Oracle is by default case insensitive. This means the table column can be named address or ADDRESS or AdDrEsS... it does not matter - the database converts all those lower/mixed case combinations into the upper case.

An example:

CREATE TABLE case_sensitive_test (
    id number primary key not null,
    first_name varchar2(100),
    Last_name varchar2(100),
    AdDrEsS varchar2(1000)
);

The table definition shows that that last name and address columns are in the upper case regardless of our DDL script case.

SELECT statements are also by default case insensitive. All names are converted into an uppercase. For example:


On the other hand, if names are quoted with double quotes they are treated as case sensitive, for example:

CREATE TABLE case_sensitive_test (
    id number primary key not null,
    first_name varchar2(100),
    "Last name" varchar2(100),
    "AdDrEsS" varchar2(1000)
);

Also in this scenario a space character can be used... which is not the case without double quotes.

Later on, double quotes must be used in SELECT or DML statements... otherwise an "Invalid identifier" exception occurs because without double quotes oracle converts names in the upper case.

PL/SQL Collections

If a collection in PL/SQL is based on a table, view, select statement, record... which is using case sensitive columns/components, double quotes must be also used when addressing such components later on in the code.

It looks a little bit silly to use double quotes right after the dot... but... the working example shows it all:

DECLARE
    CURSOR c_data IS
        SELECT
            id,
            first_NAME,
            "Last name",
            "AdDrEsS"
        FROM
            case_sensitive_test
    ;

    TYPE t_data IS TABLE OF c_data%ROWTYPE;
    l_data t_data;

BEGIN
    OPEN c_data;
    FETCH c_data BULK COLLECT INTO l_data;
    CLOSE c_data;

    dbms_output.put_line( l_data(1).first_name );
    dbms_output.put_line( l_data(1).FIRST_NAME );
    dbms_output.put_line( l_data(1)."Last name" );
    dbms_output.put_line( l_data(1)."AdDrEsS" );
END;

Otherwise, without double quotes a "component must be declared" exception occurs, because the database is treating the component name it in the uppercase:



PL/SQL Records

Same story. If the component in the record is defined with double quotes (case sensitive) it must be addressed in the same way later on in the code. Otherwise an exception occurs.

An example:

DECLARE

    TYPE r_data IS RECORD (
        id number,
        first_name varchar2(100),
        "Last name" varchar2(100),
        "AdDrEsS" varchar2(1000)
    );

    l_data r_data;

BEGIN
    dbms_output.put_line( l_data.first_name );
    dbms_output.put_line( l_data.FIRST_NAME );
    dbms_output.put_line( l_data."Last name" );
    dbms_output.put_line( l_data."AdDrEsS" );
END;


Object types and PL/SQL

Same story. If the component in the object type is defined with double quotes (case sensitive) and the variable/collection is based on this object type it must be addressed in the same way later on in the code. Otherwise an exception occurs.

An example:

CREATE OR REPLACE TYPE t_case_sensitive_test AS OBJECT (
    id number,
    first_name varchar2(100),
    "Last name" varchar2(100),
    "AdDrEsS" varchar2(1000)
);


DECLARE

    l_data t_case_sensitive_test;

BEGIN
    dbms_output.put_line( l_data.first_name );
    dbms_output.put_line( l_data.FIRST_NAME );
    dbms_output.put_line( l_data."Last name" );
    dbms_output.put_line( l_data."AdDrEsS" );
END;

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:






Oracle Database - an Issue with wrapped Packages


Today I've been investigating an issue with wrapped packages, which refused to compile on my target database.

The error was PLS-00753: malformed or corrupted wrapped unit

But there was no reason for that. Only few packages were problematic while others compiled without an error.


I tried to wrap packages in 2 ways:

  • from the database using dbms_ddl.wrap function
  • from the Linux OS command line (available on https://www.apexofficeprint.com/wrap/)

But the result was the same. An error. 


But at the end I figured out what the problem is and there is a way how to override it. 😎

And it seems that this is a database issue. My target database is the OCI database version

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 
Version 19.24.0.1.0

The last line of wrapped code is completely filled and it seems that the database is unable to unwrap it correctly... like it can not determine where the code ends?!











But if I add ";" character at the end of the code... or split the last line in two lines... then it can be compiled without problem! See pics below:














































The solution with splitting the last line into two lines solves an issue in Oracle APEX 24.1 too.






So, for a permanent solution simply add one or two empty lines of code to Your source code before wrapping. This will extend the wrapped code into a new line.


















Produced wrapped source looks like this and it can be compiled on the target database without any manual alterations:





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.

Wednesday, 27 March 2024

Oracle Database - When is the data in the user_errors view populated

So, today I've been playing with visible and invisible columns in Oracle database tables and noticed one interesting fact related to USER_ERRORS view data population.

The data is populated AFTER the first explicit or implicit compilation of the invalid object.


The example

I created a table named TICA_TEST

CREATE TABLE tica_test (
    id number primary key not null,
    c1 varchar2(100), 
    c2 varchar2(100), 
    c3 varchar2(100), 
    c4 varchar2(100), 
    c5 varchar2(100) 
);

Also created a procedure P_TICA_TEST, which is reading data from the tica_test table:

CREATE OR REPLACE PROCEDURE p_tica_test IS
    
    l_id number;
    l_c1 varchar2(100);
    l_c2 varchar2(100);
    l_c3 varchar2(100);
    l_c4 varchar2(100);
    l_c5 varchar2(100);

BEGIN
    SELECT *
    INTO l_id, l_c1, l_c2, l_c3, l_c4, l_c5
    FROM tica_test
    WHERE rownum = 1
    ;

END;

If I drop the column named C5 or make it invisible I expect that the procedure gets invalidated and that errors are stated in the view USER_ERRORS... 

But expectations are reality are not always aligned 😁


The column is dropped:

ALTER TABLE tica_test MODIFY (c5 invisible);

The status of the procedure is INVALID, as expected :

SELECT status
FROM user_objects
WHERE object_name = 'P_TICA_TEST';



But the USER_ERRORS view does not return any data?!

SELECT *
FROM user_errors
WHERE name = 'P_TICA_TEST';








And only when I compile the procedure (explicit compile) or execute the procedure (implicit compile) errors are shown and the view is populated:

ALTER PROCEDURE p_tica_test COMPILE;

or

BEGIN
    p_tica_test;
END;







Data from the view:










The conclusion

Do not be surprised if the object is invalid and there are no errors stated. 

Maybe indeed there are no errors... and the object is just marked as invalid... but the errors are going to be revealed only after the object compilation.

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.

Monday, 11 December 2023

Deferrable constraint in Oracle database - be aware of them!

Recently I bumped into an error during Oracle APEX application import and I started digging in the depths of the APEX trying to figure out what the problem was. The error message stated that one foreign key constraint failed and I hoped to find a procedure with DML statement, where this error happened.

The error back trace was pretty long... and it ended at the COMMIT statement 😐At the COMMIT statement? And not at the very DML operation? 😮 I was puzzled... staring blankly at the screen... checking three times if I traced the error correctly... but I did. 😵

And suddenly I remembered that there is a parameter related to table constraints, the parameter I never used in my whole life, which determines if the constraint is validated at the moment of DML statement execution OR later on during the transaction ending COMMIT statement. This parameter is called "deferrable". And indeed, it was a case here. In the problematic APEX table all constraints were initially deferrable.

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:


Friday, 15 September 2023

Where to download Oracle eCertificate and Badge for the passed exam?

So, You've just passed an Oracle exam, feeling happy and full of energy 💪 and now You want to download Your badge and eCertificate.

But where to find it? 😕

Maybe I'm a little confused guy... but honestly, it took me some time to click through the CertView portal to find the correct spot.

Tuesday, 23 August 2022

Oracle MERGE INTO statement behavior in concurrent sessions

A question - if we have 2 concurrent sessions executing exactly the same MERGE INTO statement on a table... what will happen? Are we going to get one row inserted, two rows inserted, one inserted and one updated... an exception... what will happen?

Well... let's see in following experiment 😏

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).