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