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.

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;