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.