Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

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.

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 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:





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;

 

Wednesday, 14 February 2024

Oracle "Instead of" triggers - beware of privileges and grants!

Recently I bumped into a problem while dealing with "instead of" triggers in the Oracle database.


The scenario is the following.

In my main schema (let's call it SC1) I created a view named DEMO_STATES_V based on the table DEMO_STATES located in another schema (let's call it SC2).

CREATE OR REPLACE VIEW my_demo_states AS
SELECT st, state_name
FROM sc2.demo_states;

The user SC1 has SELECT privilege granted on the table SC2.DEMO_STATES.

I created an "instead of" trigger on the view DEMO_STATES_V to handle DML operations. 
Currently there is only a dbms output but it is enough to present the problem.

CREATE OR REPLACE TRIGGER my_demo_states_trg
INSTEAD OF INSERT OR UPDATE OR DELETE 
ON my_demo_states
FOR EACH ROW
BEGIN
    dbms_output.put_line('The trigger executes!');
END;
/

I tried to insert the record into the view and I'm expecting to get the dbms output written.

INSERT INTO my_demo_states (st, state_name)
VALUES ('SI', 'Slovenia');

But...

Instead of the trigger execution and dbms output produced I got an error!

ORA-01031: insufficient privileges
Seems that the database checked insert privileges BEFORE executing the trigger!
And while user SC1 does not have the insert privilege granted... an error occurred.

Honestly, I haven't expected that. I mean, if I have the "instead of" trigger created, why would database check any DML privileges on the underlying table? The "instead of" trigger is going to handle eventual DML operations... and grants should be checked for the PL/SQL code in the trigger body.


Another but... there is an override. 💪

If You modify the SELECT statement for the view object and implement UNION ALL from DUAL with "1=2" condition... so that actually no record is added to the original result set from the first part of the select statement... then everything works! For example:

CREATE OR REPLACE VIEW my_demo_states AS
SELECT st, state_name
FROM dome_ms1.demo_states
UNION ALL
SELECT null, null
FROM dual
WHERE 1=2;

No privileges are checked and the trigger executes flawlessly!


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.

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.