Loading...

Follow Steven Feuerstein on Oracle PL/SQL on Feedspot

Continue with Google
Continue with Facebook
or

Valid
The bulk processing features of PL/SQL (BULK COLLECT and FORALL) are key tools for improving performance of programs that currently rely on row-by-row processing, an example of which is shown below.

Use this blog post to quickly get to some of the best resources on bulk processing - from articles to quizzes to workouts to tutorials.
LiveSQL TutorialI offer a 19-module tutorial on all things bulk processing here. I complement the explanations with lots of code to run and explore, along with:
  • Fill in the Blanks: partially-written code that you need to finish up, that reinforces the content of that module
  • Exercises: You do all the coding to solve the stated requirement (be on the lookout for copy/paste opportunities from the module to speed things up).
Oracle-BASE ContentYou can always depend on Tim Hall to offer comprehensive coverage of SQL and PL/SQL features, with straightforward, easy-to-run code snippets to drive the points home. You'll find his coverage of bulk processing here.
Oracle DocumentationThe Bulk SQL and Bulk Binding section of the PL/SQL Users Guide is packed full of syntax, links to related content, and tips on how to best take advantage of FORALL and BULK COLLECT.
Oracle Dev Gym WorkoutsThe Oracle Dev Gym offers multiple choices quizzes, workouts and classes on a wide variety of Oracle Database topics. Find below a set of four workouts (three featuring content by Tim Hall) on FORALL and BULK COLLECT.

BULK COLLECT by Tim Hall

Tim explores the BULK COLLECT feature of PL/SQL, which allows you to retrieve multiple rows with a single fetch. Note that Tim's article also covers FORALL, which is for multi-row, non-query DML (inserts, updates, deletes) and will be explored in a separate workout. After you read his article and check out the documentation, it's time to take four quizzes written by your truly to test your knowledge of this feature.

FORALL - Basic Concepts by Tim Hall

Tim offers a comprehensive review of bulk processing in PL/SQL; this workout focuses in on FORALL, covering the basic concepts behind this powerful performance enhancer. We complement Tim's article with a link to documentation and FORALL quizzes from the Dev Gym library.

FORALL and SAVE EXCEPTIONS by Tim Hall

Tim provides a comprehensive review of bulk processing in PL/SQL in this workout's leading exercise. Drill down to the SAVE EXCEPTIONS section of Tim's article to explore how to handle exceptions that may be raised when FORALL executes. Check out the documentation for more details. Then finish up with quizzes from your truly on SAVE EXCEPTIONS. Go beyond FORALL basics with this workout!

An Hour (more or less) of Bulk Processing Quizzes

Ten quizzes on FORALL and BULK COLLECT, ranging in difficulty from beginner to intermediate.
Other Blog Posts and ArticlesMy article in Oracle Magazine: Bulk Processing with BULK COLLECT and FORALL

Blog post: A checklist for Bulk Processing Conversions in PL/SQL
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Huh, what?

Make sure my code fails to compile?

Why would I want to do that.

Well, suppose that you had a compute-intensive procedure that ran every hour and benefited greatly from full PL/SQL compiler optimization (level set to 3, to take advantage of subprogram inlining and everything else it does).

Next, suppose that somehow as the procedure (newly enhanced, fully tested) was being deployed to production, the optimization level was mistakenly set to 0 or 1. This would cause severe performance problems.

So in that case, wouldn't it be nice if you could build a "circuit breaker" into that procedure so that the compiler says "No go" even if the code itself compiles just fine?

I think it would be nice - and you can accomplish precisely that with the error directive of the conditional compilation feature of PL/SQL.

First, here's the code that demonstrates precisely the scenario outlined above.
CREATE OR REPLACE PROCEDURE compute_intensive
AUTHID DEFINER
IS
BEGIN
$IF $$plsql_optimize_level < 3
$THEN
$ERROR 'compute_intensive must be compiled with maximum optimization!' $END
$END

/* All the intensive code here! */
NULL;
END compute_intensive;
I check the system-defined inquiry directive, $$plsql_optimize_level, to see what the current optimization level is. If less than 3, the PL/SQL compiler encounters the $error directive.

At this point, the compiler rejects the procedure with this error:
PLS-00179: $ERROR: compute_intensive must be compiled with maximum optimization!
Notice that the string after the $error directive becomes the compilation error message the developer will see.

You can try this yourself with my LiveSQL script.

You might also use $error to mark "not done" parts in your code. It's a lot more effective than a comment like this:
/*TODO Finish section */
and it guarantees that partially written code will never make it into production, no matter how distracted you are. Here's an example. On line 24, I've got to deal with my ELSE condition, but no time right now! So I quick1y drop in a $error snippet I've created, and add the appropriate message. Notice that I include two other system defined directives, $$plsql_unit and $$plsql_line.
FUNCTION list_to_collection (
string_in IN VARCHAR2
, delimiter_in IN VARCHAR2 DEFAULT ','
)
RETURN DBMS_SQL.varchar2a
IS
l_next_location PLS_INTEGER := 1;
l_start_location PLS_INTEGER := 1;
l_return DBMS_SQL.varchar2a;
BEGIN
IF string_in IS NOT NULL
THEN
WHILE ( l_next_location > 0 )
LOOP
-- Find the next delimiter
l_next_location :=
NVL (INSTR ( string_in, delimiter_in, l_start_location ), 0);

IF l_next_location = 0
THEN
-- No more delimiters, go to end of string
l_return ( l_return.COUNT + 1 ) :=
SUBSTR ( string_in, l_start_location );
ELSE
$ERROR
'list_to_collection INCOMPLETE!
Finish extraction of next item from list.
Go to ' || $$PLSQL_UNIT || ' at line ' || $$PLSQL_LINE
$END
END IF;
l_start_location := l_next_location + 1;
END LOOP;
END IF;
RETURN l_return;
END list_to_collection;
When I try to compile the code, I see this error:
PLS-00179: $ERROR: list_to_collection INCOMPLETE! 
Finish extraction of next item from list.
Go to LIST_TO_COLLECTION at line 28
Those are two ideas I've come up with for $error. I bet you will come up with more of your own. When you do, please let us know by adding a comment to this post!

Conditional Compilation Series
1. An introduction to conditional compilation
2. Viewing conditionally compiled code: what will be run?
3. Writing code to support multiple versions of Oracle Database
4. Setting and using your own conditional compilation flags
5. How to make sure your code FAILS to compile
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an insert, delete or update. Without RETURNING you would have to run a SELECT statement after the DML statement is completed, in order to obtain the values of the changed columns. So RETURNING helps avoid another roundtrip to the database, another context switch in a PL/SQL block.

The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INTO form.

You can also call aggregate functions in the RETURNING clause to obtain sums, counts and so on of columns in multiple rows changed by the DML statement.

Finally, you can also use RETURNING with EXECUTE IMMEDIATE (for dynamically constructed and executed SQL statements).

Run this LiveSQL script to see all of the statements shown below "in action."

First, I will create a table to use in my scripts:
CREATE TABLE parts ( 
part_number INTEGER
, part_name VARCHAR2 (100))
/

BEGIN
INSERT INTO parts VALUES (1, 'Mouse');
INSERT INTO parts VALUES (100, 'Keyboard');
INSERT INTO parts VALUES (500, 'Monitor');
COMMIT;
END;
/
Which rows did I update? (the wrong way)The code below issues the update and then in a separate SQL statement retrieves the part number of the row that was just modified - but only by reproducing the logic ("partname = UPPER (partname)") in the WHERE clause.

This means that I have introduced repetition in my code, and also inefficiency (an extra context switch). This is logically equivalent to using the RETURNING clause, but definitely inferior to RETURNING.

And keep in mind that if you use a SELECT after your DML statement to determine if the correct changes were made, you need to be very careful about how you specify the WHERE clause of your query to be sure that you identify the same rows that were (possibly) changed.
DECLARE 
l_num PLS_INTEGER;
BEGIN
UPDATE parts
SET part_name = UPPER (part_name)
WHERE part_name LIKE 'K%';

SELECT part_number
INTO l_num
FROM parts
WHERE part_name = UPPER (part_name);

DBMS_OUTPUT.put_line (l_num);
END;

/
Which rows did I update? (the right way)Don't do an unnecessary SELECT simply to see/verify the impact of a non-query DML statement! Just add RETURNING to the statement and get information back from that single context switch between PL/SQL and SQL. Note that this RETURNING INTO only works because the WHERE clause identifies a single row for changing. If more than one row is or may be changed, you will need to also use BULK COLLECT (see later example).
DECLARE 
l_num PLS_INTEGER;
BEGIN
UPDATE parts
SET part_name = UPPER (part_name)
WHERE part_name LIKE 'K%'
RETURNING part_number
INTO l_num;

DBMS_OUTPUT.put_line (l_num);
END;
Use RETURNING with BULK COLLECT INTO when changing multiple rowsIf your non-query DML statement changes (or might change) more than one row, you will want to add BULK COLLECT to your RETURNING INTO clause and populate an array with information from each changed row.
DECLARE 
l_part_numbers DBMS_SQL.number_table;
BEGIN
UPDATE parts
SET part_name = part_name || '1'
RETURNING part_number
BULK COLLECT INTO l_part_numbers;

FOR indx IN 1 .. l_part_numbers.COUNT
LOOP
DBMS_OUTPUT.put_line (l_part_numbers (indx));
END LOOP;
END;
Return an entire row? Not with ROW keyword. You can "UPDATE table_name SET ROW =" to perform a record-level update, but you cannot use the ROW keyword in that same way in a RETURNING clause.
DECLARE 
l_part parts%ROWTYPE;
BEGIN
UPDATE parts
SET part_number = -1 * part_number, part_name = UPPER (part_name)
WHERE part_number = 1
RETURNING ROW
INTO l_part;

DBMS_OUTPUT.put_line (l_part.part_name);
END;
Populate record in RETURNING with list of columnsSorry, but you must list each column, with compatible number and type to the fields of the "receiving" record.
DECLARE 
l_part parts%ROWTYPE;
BEGIN
UPDATE parts
SET part_number = -1 * part_number, part_name = UPPER (part_name)
WHERE part_number = 1
RETURNING part_number, part_name
INTO l_part;

DBMS_OUTPUT.put_line (l_part.part_name);
END;
OK, let's create another table for some other examples.
CREATE TABLE employees ( 
employee_id INTEGER
, last_name VARCHAR2 (100)
, salary NUMBER)
/

BEGIN
INSERT INTO employees VALUES (100, 'Gutseriev', 1000);
INSERT INTO employees VALUES (200, 'Ellison', 2000);
INSERT INTO employees VALUES (400, 'Gates', 3000);
INSERT INTO employees VALUES (500, 'Buffet', 4000);
INSERT INTO employees VALUES (600, 'Slim', 5000);
INSERT INTO employees VALUES (700, 'Arnault', 6000);
COMMIT;
END;
/
Need aggregate information about impact of DML?Sure, you could execute ANOTHER SQL statement to retrieve that information, using group functions. As in:
DECLARE 
l_total INTEGER;
BEGIN
UPDATE employees
SET salary = salary * 2
WHERE INSTR (last_name, 'e') > 0;

SELECT SUM (salary)
INTO l_total
FROM employees
WHERE INSTR (last_name, 'e') > 0;

DBMS_OUTPUT.put_line (l_total);
END;
Or you could perform a computation in PL/SQL. Use RETURNING to get back all the modified salaries. Then iterate through them, summing up the total along the way. Hmmm. That's a lot of code to write to do a SUM operation.
DECLARE 
l_salaries DBMS_SQL.number_table;
l_total INTEGER := 0;
BEGIN
UPDATE employees
SET salary = salary * 2
WHERE INSTR (last_name, 'e') > 0
RETURNING salary
BULK COLLECT INTO l_salaries;

FOR indx IN 1 .. l_salaries.COUNT
LOOP
l_total := l_total + l_salaries (indx);
END LOOP;

DBMS_OUTPUT.put_line (l_total);
END;
What you should do instead is call the aggregate function right inside the RETURNING clause!

Yes! You can call SUM, COUNT, etc. directly in the RETURNING clause and thereby perform analytics before you return the data back to your PL/SQL block. Very cool.
DECLARE    l_total   INTEGER; 
BEGIN
UPDATE employees
SET salary = salary * 2
WHERE INSTR (last_name, 'e') > 0
RETURNING SUM (salary)
INTO l_total;

DBMS_OUTPUT.put_line (l_total);
END;
Use RETURNING with EXECUTE IMMEDIATEYou can also take advantage of the RETURNING clause when executing a dynamic SQL statement!
DECLARE  
l_part_number parts.part_number%TYPE;
BEGIN
EXECUTE IMMEDIATE
q'[UPDATE parts
SET part_name = part_name || '1'
WHERE part_number = 100
RETURNING part_number INTO :one_pn]'
RETURNING INTO l_part_number;

DBMS_OUTPUT.put_line (l_part_number);
END;
RETURNING Multiple Rows in EXECUTE IMMEDIATE In this variation you see how to use RETURNING with a dynamic SQL statement that modifies more than one row.
DECLARE  
l_part_numbers DBMS_SQL.number_table;
BEGIN
EXECUTE IMMEDIATE
q'[UPDATE parts
SET part_name = part_name || '1'
RETURNING part_number INTO :pn_list]'
RETURNING BULK COLLECT INTO l_part_numbers;

FOR indx IN 1 .. l_part_numbers.COUNT
LOOP
DBMS_OUTPUT.put_line (l_part_numbers (indx));
END LOOP;
END;
Resources The RETURNING INTO Clause (doc)

DML Returning INTO Clause (Oracle-Base)
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
This question hit my Twitter feed yesterday:
When you enable all warnings, have you ever seen a "PLW-06006-- uncalled procedure removed" (lots of them), when they surely are called?
Now that, I must admit, has to be a little bit concerning. You write code, you know it is going to, or should be, executed, and yet the PL/SQL compiler tells you it's been removed?

OK, OK, calm down. Everything is just fine.

Here's the explanation:
  • The optimizer performed an inlining optimization, so all the code for that procedure (or function) was moved to where it is invoked.
  • The "original" nested or private subprogram that you wrote (and, don't worry, is still and always will be in the source code of your program unit) is, truth be told, never going to be called. 
  • So then the compiler removed it (did not include it in the compiled code - which is not PL/SQL code any longer).
Let's take a look at some code, and what happens when we run it (you can see this for yourselves via my LiveSQL script):
ALTER SESSION SET plsql_optimize_level = 3
/

Statement processed

ALTER SESSION SET plsql_warnings='enable:all'
/

Statement processed

CREATE OR REPLACE PROCEDURE show_inlining
AUTHID DEFINER
IS
FUNCTION f1 (p NUMBER)
RETURN PLS_INTEGER
IS
BEGIN
RETURN p * 10;
END;

FUNCTION f2 (p BOOLEAN)
RETURN PLS_INTEGER
IS
BEGIN
RETURN CASE WHEN p THEN 10 ELSE 100 END;
END;

FUNCTION f3 (p PLS_INTEGER)
RETURN PLS_INTEGER
IS
BEGIN
RETURN p * 10;
END;
BEGIN
DBMS_OUTPUT.put_line ('f1 called: ' || f1 (1));

PRAGMA INLINE (f2, 'YES');
DBMS_OUTPUT.put_line ('f2 called: ' || TO_CHAR (f2 (TRUE) + f2 (FALSE)));

PRAGMA INLINE (f3, 'NO');
DBMS_OUTPUT.put_line ('f3 called: ' || f3 (55));
END;
/

Warning: PROCEDURE SHOW_INLINING
Warning: PROCEDURE SHOW_INLINING
Line/Col: 4/4 PLW-06027: procedure "F1" is removed after inlining
Line/Col: 10/4 PLW-06027: procedure "F2" is removed after inlining
Line/Col: 22/4 PLW-06005: inlining of call of procedure 'F1' was done
Line/Col: 25/4 PLW-06005: inlining of call of procedure 'F2' was done
Line/Col: 25/4 PLW-06004: inlining of call of procedure 'F2' requested
Line/Col: 25/4 PLW-06005: inlining of call of procedure 'F2' was done
Line/Col: 25/52 PLW-06004: inlining of call of procedure 'F2' requested
Line/Col: 28/4 PLW-06008: call of procedure 'F3' will not be inlined

BEGIN
show_inlining;
END;
/

f1 called: 10
f2 called: 110
f3 called: 550
As you can see, the warnings feedback ("PLW" stands for PL/SQL Warning) tells the story: procedures are removed after inlining.

Though I suppose we could be a little more explicit - and reassuring - and say:
PLW-06027: procedure "F1" is removed after inlining - but just from the compiled code, not from the source code of your program unit!

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
This post is the fourth in my series on conditional compilation. You will find links to the entire series at the bottom.

In this post, I explore how to set and use conditional compilation flags (also known as inquiry directives and referred to below as ccflags) used in $IF statements, and control which code will be included or excluded when compilation occurs.

In theory, you don't need ccflags at all. You could just create a package with static constants, like DBMS_DB_VERSION, and then reference those constants in $IF statements. That makes sense when many different compilation units (packages, procedures, triggers, functions, object types) need to be consistently controlled by the same settings. With the package approach, when you change a value for the constant, the dependent program units will be invalidated, and upon recompilation, will be compiled with the new values.

If, on the other hand, you want to add conditional compilation logic to a single unit, or a handful, then you might find a package dedicated to this purpose to be a bit too much. For this situation, you might consider using an inquiry directive instead. You know you're looking at a ccflag or inquiry directive, when you see an identifier prefixed by "$$".

An inquiry directive gets its value from the compilation environment, in three different ways:
  1. from a PL/SQL compilation parameter
  2. from a predefined ccflag
  3. from a user-defined ccflag
Compilation ParametersPL/SQL offers the following compilation parameters as inquiry directives:

$$PLSCOPE_SETTINGS - the current settings for PL/Scope for a program unit

$$PLSQL_CCFLAGS - the current settings for user-defined ccflags for a program unit

$$PLSQL_CODE_TYPE - the type of code, NATIVE or INTERPRETED

$$PLSQL_OPTIMIZE_LEVEL - the optimization level used to compile the program unit

$$PLSQL_WARNINGS - compile-time warnings setting  for the program unit

$$NLS_LENGTH_SEMANTICS - NLS length semantics for the program unit

Here's a procedure you can use to display the current values for these parameters in your session (available for download in this LiveSQL script):
BEGIN
DBMS_OUTPUT.PUT_LINE('$$PLSCOPE_SETTINGS = ' || $$PLSCOPE_SETTINGS);
DBMS_OUTPUT.PUT_LINE('$$PLSQL_CCFLAGS = ' || $$PLSQL_CCFLAGS);
DBMS_OUTPUT.PUT_LINE('$$PLSQL_CODE_TYPE = ' || $$PLSQL_CODE_TYPE);
DBMS_OUTPUT.PUT_LINE('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
DBMS_OUTPUT.PUT_LINE('$$PLSQL_WARNINGS = ' || $$PLSQL_WARNINGS);
DBMS_OUTPUT.PUT_LINE('$$NLS_LENGTH_SEMANTICS = ' || $$NLS_LENGTH_SEMANTICS);
END;
This same information is stored persistently in the database for every program unit and is available through the USER/ALL_PLSQL_OBJECT_SETTINGS view, as in:
SELECT *
FROM all_plsql_object_settings
WHERE name = 'PROGRAM_NAME'
Predefined CCFlagsAs of Oracle Database 19c, the ccflags automatically defined for any program unit are:

$$PLSQL_LINE - A PLS_INTEGER literal whose value is the number of the source line on which the directive appears in the current PL/SQL unit.

$$PLSQL_UNIT - A VARCHAR2 literal that contains the name of the current PL/SQL unit. If the current PL/SQL unit is an anonymous block, then $$PLSQL_UNIT contains a NULL value.

$$PLSQL_UNIT_OWNER - A VARCHAR2 literal that contains the name of the owner of the current PL/SQL unit. If the current PL/SQL unit is an anonymous block, then $$PLSQL_UNIT_OWNER contains a NULL value.

$$PLSQL_UNIT_TYPE - A VARCHAR2 literal that contains the type of the current PL/SQL unit—ANONYMOUS BLOCK, FUNCTION, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, or TYPE BODY. Inside an anonymous block or non-DML trigger, $$PLSQL_UNIT_TYPE has the value ANONYMOUS BLOCK.

You might be disappointed, however, in how you can use these flags. Selective directives ($IF) must contain only static expressions, which means they are resolved at compilation time, which means in the world of Oracle Database, that you can only work with integer and Boolean values.

So this code will compile:
PROCEDURE test_cc
IS
BEGIN
$IF $$PLSQL_UNIT IS NULL $THEN
-- Include this line
$END
NULL;
END;
but this gives me a headache:
PROCEDURE test_cc
IS
BEGIN
$IF $$PLSQL_UNIT = 'TEST_CC' $THEN
-- Include this line
$END
NULL;
END;

PLS-00174: a static boolean expression must be used
Consequently, these predefined flags are mostly used for tracing and logging purposes, as in:
CREATE OR REPLACE PROCEDURE using_predefined_ccflags
AUTHID DEFINER
IS
myvar INTEGER := 100;
BEGIN
DBMS_OUTPUT.put_line (
'On line ' || $$plsql_line || ' value of myar is ' || myvar);
RAISE PROGRAM_ERROR;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Failure in program unit ' ||
$$plsql_unit_owner || '.' ||
$$plsql_unit_type || '.' ||
$$plsql_unit);
END;
/

BEGIN
using_predefined_ccflags;
END;
/

Procedure created.
Statement processed.
On line 7 value of myar is 100
Failure in program unit SCOTT.PROCEDURE.USING_PREDEFINED_CCFLAGS
User-defined CCFLagsUser-defined ccflags are defined by setting the PLSQL_CCFlags parameter before your program unit is compiled. You can set ccflags in this way at the session or program unit level. Here are some examples:
ALTER SESSION SET plsql_ccflags = 'Flag1:10, Flag2:true'
/

ALTER PROCEDURE myproc
COMPILE SET PLSQL_CCFlags = 'Flag1:10, Flag2:true' REUSE SETTINGS
/
Since these are user-defined, you can use them for, well, just about anything. You can turn on and off tracing, or the amount of tracing. You can include or exclude chunks of code depending on features your users have paid for. You can expose otherwise private packaged subprograms in the package specification so you can test them directly, and then make sure they are hidden in production.

Here's an example of that use case (also in LiveSQL):
ALTER SESSION SET PLSQL_CCFLAGS = 'show_private_joke_programs:TRUE'
/

CREATE OR REPLACE PACKAGE sense_of_humor
IS
PROCEDURE calc_how_funny (
joke_in IN VARCHAR2
, funny_rating_out OUT NUMBER
, appropriate_age_out OUT NUMBER
);
$IF $$show_private_joke_programs $THEN
FUNCTION humor_level ( joke_in IN VARCHAR2 )
RETURN NUMBER;

FUNCTION maturity_level ( joke_in IN VARCHAR2 )
RETURN NUMBER;
$END
END;
/

CREATE OR REPLACE PACKAGE BODY sense_of_humor
IS
FUNCTION humor_level ( joke_in IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
-- Some really interesting code here...
RETURN NULL;
END humor_level;

FUNCTION maturity_level ( joke_in IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
-- Some really interesting code here...
RETURN NULL;
END maturity_level;

PROCEDURE calc_how_funny (
joke_in IN VARCHAR2
, funny_rating_out OUT NUMBER
, appropriate_age_out OUT NUMBER
)
IS
BEGIN
funny_rating_out := humor_level ( joke_in );
appropriate_age_out := maturity_level ( joke_in );
END calc_how_funny;
END;
/

Tips for Using CCFlagsDon't Assume Anything
By which I mean: don't assume that user-defined ccflags have been set before compilation for use in production.

If a ccflag has not been defined with an ALTER command, it is evaluated to NULL.

So the behavior of your code in production should be, whenever possible, determined by the value of all of your ccflags set to NULL. That way, if for any reason the code is compiled without the proper ALTER statement run, you will not be dealing with a big mess.

Keep CCFlag Settings with Code
One way to avoid the problem described above is to keep all the ccflag settings needed for a particular program unit in the file for that unit itself. Then, whenever you need to compile that program unit, the ccflags are set first, and then the compilation occurs.

Check for Undefined CCFlags

Use the compile-time warnings feature of PL/SQL to reveal any ccflags that have not been set at the time of compilation. Here's an example:
ALTER SESSION SET plsql_warnings = 'ENABLE:ALL'
/

CREATE OR REPLACE PACKAGE sense_of_humor AUTHID DEFINER
IS
PROCEDURE calc_how_funny (
joke_in IN VARCHAR2
, funny_rating_out OUT NUMBER
, appropriate_age_out OUT NUMBER
);
$IF $$show_private_joke_programs $THEN
FUNCTION humor_level ( joke_in IN VARCHAR2 )
RETURN NUMBER;
$END
END;
/

PLW-06003: unknown inquiry directive '$$SHOW_PRIVATE_JOKE_PROGRAMS'
Conditional Compilation Series1. An introduction to conditional compilation
2. Viewing conditionally compiled code: what will be run?
3. Writing code to support multiple versions of Oracle Database
4. Setting and using your own conditional compilation flags
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

3rd in a series on conditional compilation. See end of post for links to all posts in the series.

Do you write code that must run on more than one version of Oracle Database? This is almost always the case for suppliers of "off the shelf" applications. And when confronted with this reality, most developers choose between these two options:

Use only those features available in all versions ("lowest common denominator" or LCD programming).
or
Maintain separate copies of the code for each supported version, so you can take advantage of new features in later versions of the database ("sure to create a mess" or SCAM programming).

And let's face it, both have some serious drawbacks.

The LCD approach ensures that your code will compile on all supported versions. But you will sacrifice the ability to take advantage of new features in the later versions. That can be a high price to pay.

The SCAM approach, well, "sure to create a mess" says it all. What's the chance that you will be able to keep 2, 3, or 4 copies of the same code up to date with all bug fixes, enhancements, comments, etc., along with the special-purpose code written to leverage features in that specific version?

Fortunately, there is a third and better way: use conditional compilation so that you can write and maintain your code in a single file / program unit, but still take maximum advantage of each version's cool new features.

Actually, you use conditional compilation and the DBMS_DB_VERSION package. Let's check them out!

Here's the entire code for the DBMS_DB_VERSION package for Oracle Database 12c:
PACKAGE DBMS_DB_VERSION IS
VERSION CONSTANT PLS_INTEGER := 12; -- RDBMS version number
RELEASE CONSTANT PLS_INTEGER := 2; -- RDBMS release number
ver_le_9_1 CONSTANT BOOLEAN := FALSE;
ver_le_9_2 CONSTANT BOOLEAN := FALSE;
ver_le_9 CONSTANT BOOLEAN := FALSE;
ver_le_10_1 CONSTANT BOOLEAN := FALSE;
ver_le_10_2 CONSTANT BOOLEAN := FALSE;
ver_le_10 CONSTANT BOOLEAN := FALSE;
ver_le_11_1 CONSTANT BOOLEAN := FALSE;
ver_le_11_2 CONSTANT BOOLEAN := FALSE;
ver_le_11 CONSTANT BOOLEAN := FALSE;
ver_le_12_1 CONSTANT BOOLEAN := FALSE;
ver_le_12_2 CONSTANT BOOLEAN := TRUE;
ver_le_12 CONSTANT BOOLEAN := TRUE;
END DBMS_DB_VERSION;
The package contains two "absolute" constants: the version and release numbers. it then contains a set of "relative" constants, basically telling you, true or false, if the current version is less than or equal to the version specified by the constant name.

If I was a betting man, I'd bet a whole lot of money than you could figure out what this package looks like in Oracle Database 18c and 9c. If not, run this query:
  SELECT LPAD (line, 2, '0') || ' - ' || text
FROM all_source
WHERE owner = 'SYS' AND name = 'DBMS_DB_VERSION'
ORDER BY line
One Program for Multiple Versions
The DBMS_DB_VERSION package makes it really easy to ensure that each installation of your code takes full advantage of the latest and greatest features.

Consider the package body below (full code available on LiveSQL). Starting with Oracle Database 10g Release 2, you can use INDICES OF with FORALL to handle spare bind arrays elegantly. Prior to that, you would have to "densify" the collection and get rid of any gaps.

A simple application of the $IF statement along with a reference to the appropriate DBMS_DB_VERSION constant, and job done.
CREATE OR REPLACE PACKAGE BODY pkg
IS
PROCEDURE insert_rows ( rows_in IN ibt )
IS
BEGIN
$IF DBMS_DB_VERSION.VER_LE_10_1
$THEN
/* Remove gaps in the collection */
DECLARE
l_dense t;
l_index PLS_INTEGER := rows_in.FIRST;
BEGIN
WHILE (l_index IS NOT NULL)
LOOP
l_dense (l_dense.COUNT + 1) := rows_in (l_index);
l_index := rows_in.NEXT (l_index);
END LOOP;

FORALL indx IN l_dense.FIRST .. l_dense.LAST
INSERT INTO t VALUES l_dense (indx);
END;
$ELSE
/* Use the very cool INDICES OF feature to skip over gaps. */
FORALL indx IN INDICES OF rows_in
INSERT INTO t VALUES rows_in (indx);
$END
END insert_rows;
END;
Not Just for Oracle Versions
You can use this same technique to manage deployments of different versions of your code (different for different versions of your application or different for different customers). You can create your own variation on DBMS_DB_VERSION, or use your own flags, and use it in exactly the same way in your code base.

You will just need to make that your version package is always available, or your flags are always set so that you end up with the right code for the right customer.

In addition, you can only use constants with Boolean or integer values in your $IF conditions., and those conditions must be static expressions, meaning their values do not vary when initialized in the package.

Perhaps an example would help here. :-)

Suppose you wanted to use conditional compilation to automatically enable or disable features in the application depending on the customer.

You might create code like this:
CREATE OR REPLACE FUNCTION eligible_for_use (type_in       IN VARCHAR2,
customer_in IN VARCHAR2)
RETURN BOOLEAN
AUTHID DEFINER
IS
BEGIN
RETURN type_in = 'TURBO' AND customer_in = 'ACME Inc';
END;
/

CREATE OR REPLACE PACKAGE include_features
AUTHID DEFINER
IS
include_turbo CONSTANT BOOLEAN := eligible_for_use ('TURBO', USER);
END;
/

CREATE OR REPLACE PROCEDURE go_turbo
AUTHID DEFINER
IS
BEGIN
$IF include_features.include_turbo
$THEN
DBMS_OUTPUT.put_line ('all systems go');
$ELSE
NULL;
$END
END;
/
So far, so good. But when I try to apply it, I see this error:
CREATE OR REPLACE PROCEDURE go_turbo
AUTHID DEFINER
IS
BEGIN
$IF include_features.include_turbo
$THEN
DBMS_OUTPUT.put_line ('all systems go');
$ELSE
NULL;
$END
END;
/

PLS-00174: a static boolean expression must be used
The value of the include_turbo constant is not set until the package is initialized, and that's too late when it comes to conditional compilation.

So if you want to take this approach, you will need to generate (or hand-code, but that seems like a stretch) the (in this case) include_features package so that each customer receives its own version of the package, as in:
CREATE OR REPLACE PACKAGE include_features
AUTHID DEFINER
IS
/* Generated 2019-04-06 13:44:50 for ACME Inc - Customer ID 147509 */
include_turbo CONSTANT BOOLEAN := TRUE;
END;
/
and then when go_turbo and other program units are compiled at the customer site, the correct features will be made available.
Tips for Doing It Right
Suppose I want the following procedure to compile and run on versions 12.2, 18.x and 19.x. Will it work as desired?
CREATE OR REPLACE PROCEDURE lots_of_versions
AUTHID DEFINER
IS
BEGIN
$IF dbms_db_versions.ver_le_19 AND NOT dbms_db_versions.ver_le_18
$THEN
DBMS_OUTPUT.put_line ('Having fun on 19c!');
$ELSIF dbms_db_versions.ver_le_18 AND NOT dbms_db_versions.ver_le_12
$THEN
DBMS_OUTPUT.put_line ('Good to go on 18.x');
$ELSIF NOT dbms_db_versions.ver_le_12_1
$THEN
DBMS_OUTPUT.put_line ('Good to go on 12.2');
$ELSE
raise_application_error (-20000, 'Not supported');
$END
END;
/
No - when I try to compile this on 12.2, 18.1 and 18.2, it will fail because those 19c-related constants are not defined in the earlier versions of the DBMS_DB_VERSION package.

This approach will work much better:
CREATE OR REPLACE PROCEDURE lots_of_versions
AUTHID DEFINER
IS
BEGIN
$IF dbms_db_versions.ver_le_12_2
$THEN
raise_application_error (-20000, '12.1 is not supported');
$ELSIF dbms_db_versions.ver_le_18_1
$THEN
DBMS_OUTPUT.put_line ('Good to go on 12.2');
$ELSIF dbms_db_versions.ver_le_19_1
$THEN
DBMS_OUTPUT.put_line ('Good to go on 18.x');
$ELSE
DBMS_OUTPUT.put_line ('Having fun on 19c!');
$END
END;
/
In other words, go from lowest version to highest version in any $IF statements to ensure that the referenced constant will always be defined (or never reached).
Conditional Compilation Series
1. An introduction to conditional compilation
2. Viewing conditionally compiled code: what will be run?
3. Writing code to support multiple versions of Oracle Database
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Waaaaay back in 2010, on April 8 to be specific, I started a website called the PL/SQL Challenge. It featured a daily PL/SQL quiz (yes, that's right - a new quiz every weekday!) and gave Oracle Database developers a way to both deepen and demonstrate their expertise. Players were ranked and competed for top honors in our annual championships.

Not quite as waaaaay back, in 2014, I rejoined Oracle Corporation after 22 years away (from the company, not from the technology). The PL/SQL Challenge came with me, and a year later we rebranded it as the Oracle Dev Gym.

Today, we offer quizzes on SQL, PL/SQL, database design, logic, Java and Application Express. We've added workouts and classes.

Yesterday we celebrated the ninth anniversary of the Dev Gym / PL/SQL Challenge. And my oh my but Oracle Database developers have been busy!


Here are some stats from those nine years:
  • Almost 35,000 developers and DBAs have taken quizzes on the site, a total of 1.27M answers submitted.
  • They spent a total of over 118 YEARS of their cumulative time taking quizzes, workouts and classes.
  • Their average grade on quizzes is 75% (we have a lot of tough quizzes!).
  • Roughly 3,500 developers have been active on the site in the last three months.
  • We've had almost 35,000 sign-ups for our Dev Gym classes (the most popular being Chris Saxon's Databases for Developers classes).
  • Individual workouts have also been very popular, with just under 33,000 taken.
The Dev Gym has become an essential part of the learning experience for thousands of developers. They've discovered new features of the database, learned from other developers, and even written their own quizzes for everyone to play.

If you haven't yet tried the Dev Gym, come check it out! Take a quiz, sign up for a class (they are on-demand, take them at your own pace) or take a workout. 


Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
In the previous (first) post in my series on conditional compilation, I covered use cases and presented some simple examples.

In this post, I show you how you can confirm what code is actually going to be executed after compilation. Without conditional compilation, this is of course a silly exercise. The code that is executed is the same as the code you see in your editor.

But with conditional compilation, the code that is compiled and therefore runs could depend on any of the following:
  • The version of the database in which it is compiled
  • The values of user-defined conditional compilation flags
  • The values of pre-defined (system) conditional compilation flags, like ##plsq1_optimize_level
It can be a little bit nerve-wracking for a developer to not be entirely sure what is going to execute, so we provide the DBMS_PREPROCESSOR package, with its two subprograms:
  • print_post_processed_source - display the post-processed code on your screen
  • get_post_processed_source - return the post-processed code as an array.
If you are wondering why the name of the package contains "preprocessor" but its only subprograms contain "post_processed"....well, what's life without a mystery or two? :-)

The "print" procedure has three overloadings:

1. Prints post-processed source text of a stored PL/SQL unit:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (
   object_type    IN VARCHAR2,
   schema_name    IN VARCHAR2,
   object_name    IN VARCHAR2);

2. Prints post-processed source text of a compilation unit:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (
   source IN VARCHAR2);
 
3. Prints post-processed source text of an INDEX-BY table containing the source text of the compilation unit:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (
   source IN source_lines_t);

Let's try it out. My optimization level is set to the default: 2. I execute these statements:
CREATE OR REPLACE PROCEDURE post_processed
IS
BEGIN
$IF $$plsql_optimize_level = 1
$THEN
-- Slow and problematic
NULL;
$ELSE
-- Fast and modern and easy
NULL;
$END
END post_processed;
/

BEGIN
DBMS_PREPROCESSOR.print_post_processed_source (
'PROCEDURE',
SYS_CONTEXT ('userenv', 'current_schema'),
'POST_PROCESSED');
END;
/
and I see this output:
PROCEDURE post_processed
IS
BEGIN





-- Fast and modern and easy
NULL;

END post_processed;
I then set the optimization level to 1 for this procedure as I recompile it:
ALTER PROCEDURE post_processed COMPILE plsql_optimize_level=1
/
The output then changes as follows:
BEGIN
DBMS_PREPROCESSOR.print_post_processed_source (
'PROCEDURE',
SYS_CONTEXT ('userenv', 'current_schema'),
'POST_PROCESSED');
END;
/

PROCEDURE post_processed
IS
BEGIN


-- Slow and problematic
NULL;




END post_processed;
Take a close and careful look at these two sets of output. Notice how carefully the white space (vertical - lines, and horizontal - spaces) is preserved. This is critical.

This is the code that will be executed. So PL/SQL needs to make sure that if an exception is raised and error stack or back trace is displayed/logged, the line and column numbers in those messages reflect what you see in your original source code, namely:
CREATE OR REPLACE PROCEDURE post_processed
IS
BEGIN
$IF $$plsql_optimize_level = 1
$THEN
-- Slow and problematic
NULL;
$ELSE
-- Fast and modern and easy
NULL;
$END
END post_processed;
I hope you can see that is, in fact, the case.

I could show you examples of calling the other overloadings of the print procedure, but I think you get the idea. Here's essentially the same behavior as the print procedure, but using the get function instead - and encapsulate into my very own procedure, adding line numbers:
CREATE PROCEDURE show_code_for (tp IN VARCHAR2, nm IN VARCHAR2)
IS
l_postproc_code DBMS_PREPROCESSOR.source_lines_t;
l_row PLS_INTEGER;
BEGIN
l_postproc_code :=
DBMS_PREPROCESSOR.get_post_processed_source (
tp,
SYS_CONTEXT ('userenv', 'current_schema'),
nm);
l_row := l_postproc_code.FIRST;

WHILE (l_row IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (
LPAD (l_row, 3)
|| ' - '
|| RTRIM (l_postproc_code (l_row), CHR (10)));
l_row := l_postproc_code.NEXT (l_row);
END LOOP;
END;
/
and I put it to use:
BEGIN
show_code_for ('PROCEDURE', 'POST_PROCESSED');
END;
/

1 - PROCEDURE post_processed
2 - IS
3 - BEGIN
4 -
5 -
6 - -- Slow and problematic
7 - NULL;
8 -
9 -
10 -
11 -
12 - END post_processed;
I hope you find this helpful.

Resources

Comprehensive white paper: a great starting place - and required reading - for anyone planning on using conditional compilation in production code

Conditional compilation scripts on LiveSQL

Tim Hall (Oracle-BASE) coverage of conditional compilation

Conditional compilation documentation

My Oracle Magazine article on this topic

Conditional Compilation Series

1. An introduction to conditional compilation
2. Viewing conditionally compiled code: what will be run?
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This question was submitted as a comment in one of my videos today:
Do we have to include an exception section for each individual subprogram or can we have a single handler for all subprograms?
The quick answer is: if you want an exception raised in a procedure or function defined in a package, you need to add an exception to that subprogram.

I can certainly see why this question would come up. A package body can have its own exception handler. Here's an example:

CREATE OR REPLACE PACKAGE pkg
AUTHID DEFINER
IS
PROCEDURE proc;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg
IS
PROCEDURE proc
IS
BEGIN
RAISE NO_DATA_FOUND;
END;
BEGIN
NULL;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Was proc executed?');
END;
/

And it kinda, sorta looks like if I execute the following block, I will see "Was proc execute?" on my screen.

BEGIN
pkg.proc;
END;
/

But I would be wrong. Instead, I will see:

ORA-01403: no data found
ORA-06512: at "QDB_PROD.PKG", line 6

But, but, but....what's going on here? I explicitly handle NO_DATA_FOUND right there at the bottom of the package.

What's going on is that the exception handler only looks like it is a handler for the entire package. In actually, it can only possibly handle exceptions raised by the executable code between the BEGIN and EXCEPTION keywords underneath the proc procedure.

This is called the initialization section of the package.  It is designed - you guessed it - initialize the state of the package (set values, perform QA checks, etc.). It runs once per session to initialize the package.

HOWEVER: in stateless environments like websites, this code may well execute each time a user references a package element (runs a subprogram, gets the value of a variable). So these days, it would be rare to find an initialization section in a package, and probably something to generally avoid.

The bottom line when it comes to exception handling for subprograms in a package is simple: you must include an exception section in each of those subprograms. The code that is executed in each of those subprograms could be shared. And should be. You should use a generic error logging API like the open source Logger, so that everyone handles, logs and re-raises exceptions in the same way.
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
DatelineDB: April 1st 2019

The European Union turned heads today with a surprise announcement:
"Starting 1 January 2020, all business logic in applications must be made available via code stored inside the database. While we recommend that you use Oracle Database and PL/SQL, that will not be required."
This position was apparently taken after close review of the groundbreaking research conducted by Toon Koppelaars of Oracle Corporation, in which he showed that by putting business logic in the database, the overall work - and therefore energy consumption - of the application is reduced, sometimes by as much as 235%. While improving the overall performance of the application by 500%.

A close confidant of the President of the European Union told DatelineDB that the EU would soon adopt a resolution stating that we are now in a climate emergency and every effort must be made in every aspect of human activity to slow down the warming of our planet.

"So the decision to require business logic in the database was basically a no-brainer. A win-win for the customer and the planet."

There are rumors that Java developers all over the world are seeking therapy to deal with their years of falsely implanted memories that made them think the database should be used as nothing but a bit bucket.

And in an unprecedented show of unity, all the JavaScript developers in the world announced that they would henceforth only write code in the dark web, because they really don't like databases. And they are building a new framework: darkDB.js

"Don't worry about that," Brendan Each told DatelineDB. "For a whole boatload of JavaScript programmers that just means they are going to run their editors in dark mode. Shhhhhhh. Don't tell them that's not the dark web."

Larry Ellison was not available for comment.

But Prime Minister May of the United Kingdom did further shock all concerned by issuing her own statement:
"Now that the EU has shown such great wisdom and concern for life on this planet, I have instructed my ministers to halt all work on Brexit and instead participate fully in this critical EU initiative, titled For All a Beautiful Database."
If anyone has any questions about putting their business logic in the database, Toon Koppelaars will be available live to answer your questions on May 21.
Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview