Tuesday, December 20, 2016

PL/SQL Brain Teaser: When is NO_DATA_FOUND not?

Here goes:

I execute this statement:

CREATE OR REPLACE FUNCTION ndf
   RETURN NUMBER
IS
BEGIN
   RAISE NO_DATA_FOUND;
END;
/

We all know what that function is going to do, right? #Fail, as one might say on Twitter.

So the brain teaser is: 

In the block below, replace <statement> with a single statement that does not contain any exception handling, but does call the NDF function, so that after the block executes, "NDF? What NDF?" is displayed on the screen.


DECLARE
   n   NUMBER;
BEGIN
   <statement>
   DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');
END;
/

I will wait a bit to post my answer. I encourage you to post yours as a comment to this blog post.

Wait....
Wait....
Wait for it....

OK!

After a couple of days of collecting responses on this post and also in LinkedIn, it's time to publish the answer:

You can replace <statement> with any DML statement that executes the function, and the failure of the function with an unhandled NO_DATA_FOUND exception will not cause the SQL statement to terminate with said exception.

Instead, the SQL engine swallows up that exception and simply returns NULL to the statement.

Why, you might wonder, would the SQL engine do this?

NO_DATA_FOUND is, on the one hand, an exception like any other. And on the other hand, it is different, in that the lack of data often does not indicate any kind of actual error, but simply a data condition. And so it was decided that when a function executed within a SQL statement fails with an unhandled NO_DATA_FOUND, that NULL would simply be returned.

You might not like that answer or decision, but there it is.

Now, there is another way to both invoke the NDF function in a single statement and not have the exception terminate the block, as Edwin points out in the comments: Call the function inside a COALESCE function call.

COALESCE offers the very cool feature of not evaluating an expression in its least until it needs to (in contrast, for example, to NVL, which always evaluates the second argument, even if the first argument is not NULL.

Update 12-21: Jeff Kemp (@jeffreykemp) notes on LinkedIn that since the text in the brain teaser says "call the NDF function", COALESCE is not a valid answer, since you never call the function. Strictly speaking he is correct.

Friday, December 16, 2016

Do Comments Throw Off Error Backtrace? No!


Just received this feedback on an Oracle Magazine article:
I’ve just started using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and it’s a great utility when the code contains no comments, but unless I’m missing something it’s not terribly useful with regard to pinpointing the exact line number when the code contains comments.
Now, I must confess that I am tempted to respond with such questions along these lines:
Did you try it out? That's the best way to learn!
But I am always looking for another reason to post on my blog and add to the LiveSQL repository. So that's what I am doing! :-)

The answer is quite straightforward:

Comments do not interfere with "pinpointing the exact line number" on which the error was raised.

But I bet you want proof, so let's get down to business.

I create a procedure with comments and a RAISE statement:

CREATE OR REPLACE PROCEDURE comments_throw_off_line# 
IS 
/* 
Here's my header 
 
Blah blah blah 
*/ 
BEGIN 
   DBMS_OUTPUT.put_line ('wow!'); 
   /* 
   And here's another comment because I really love 
   to fill my code with meaningless comments! 
   */ 
   RAISE PROGRAM_ERROR; /* This is line 14 */ 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace); 
END;

I run the procedure and see this output:

BEGIN 
   comments_throw_off_line#; 
END;

wow!
ORA-06512: at "COMMENTS_THROW_OFF_LINE#", line 14

So, there's your answer.

Next question? :-)

Wednesday, December 14, 2016

Some Beginner Tips for Working with JSON - From a Beginner

We are enhancing the Oracle Dev Gym to automatically execute code you type as a solution to a problem, and validate that it works. We are doing this via REST calls to LiveSQL. It's very cool and a lot of fun to put together. Hopefully it will be way more fun for you to see if you can solve our challenges.

Anyway, LiveSQL is going to pass back results via a JSON document, which means that yes, finally, I am going to start working with JSON and learning about Oracle Database JSON functionality.

Yes, I am a total novice at this. Which, I imagine, is the case for many other PL/SQL developers. So I thought I would immediately share some early lessons learned. That way maybe you will save yourself the 15 minutes I wasted sorting this out.

First, though, if you haven't done anything with JSON yet, start by looking over these:

JSON in Oracle Database
JSON Support in Oracle Database 12c Release 1 (12.1.0.2)

Plus, all the code shown below can be seen, and run, in LiveSQL.

OK. So first I created a table to hold my JSON documents:

CREATE TABLE json_data (my_doc CLOB)
/

Then I insert a row of data. JSON! (or so I thought)

BEGIN
   INSERT INTO json_data
        VALUES (q'^{
          "without_spaces" : "OK",
          "with spaces not a good idea" : "Not OK",
          "carriage returns definitely bad idea" : 
      "BEGIN 
          DBMS_OUTPUT.PUT_LINE ('Hello JSON!'); 
       END;"}^');

   COMMIT;
END;
/

Use the JSON_VALUE function to get the value for a given property name.

SELECT json_value (j.my_doc, '$.without_spaces') r
  FROM json_data j
/

OK

I can also enclose the property name inside double quotes:

SELECT json_value (j.my_doc, '$."without_spaces"') r
  FROM json_data j
/

OK

Now let's the value for a property name that contains spaces:

SELECT json_value (j.my_doc, '$.with spaces not a good idea') r
  FROM json_data j
/

ORA-40442: JSON path expression syntax error ('$.with spaces not a good idea')

Ugh. OK, so if I am going to put spaces in my names I must enclose that name within double quotes in my call to JSON_VALUE:

SELECT json_value (j.my_doc, '$."with spaces not a good idea"') r
  FROM json_data j
/

Not OK

Fine. Now let's get that PL/SQL block back from my table - notice, I remembered to include the double quotes:


SELECT NVL (json_value (j.my_doc
                      , '$."carriage returns definitely bad idea"'), 'NULL') r
  FROM json_data j
/

NULL

Nothing there! Nothing there? But there is something there. What's going on?

So now I waste time trying to figure it out - but you get to save yourself the time by reading my blog! The problem is that you cannot have carriage returns or newline characters in your JSON values. In other words, that "JSON document" I inserted isn't even valid JSON. And I didn't realize it, because I was lazy and did not tell Oracle Database that the my_doc column contains JSON.

Let's do that:

DELETE FROM json_data
/

ALTER TABLE json_data
ADD CONSTRAINT json_data_json_chk
CHECK (my_doc IS json) enable
/

And  now when I try to insert this value....kaboom!

BEGIN
   INSERT INTO json_data
        VALUES (q'^{
          "without_spaces" : "OK",
          "with spaces not a good idea" : "Not OK",
          "carriage returns definitely bad idea" : 
      "BEGIN 
          DBMS_OUTPUT.PUT_LINE ('Hello JSON!'); 
       END;"}^');

   COMMIT;
END;
/

ORA-02290: check constraint (JSON_DATA_JSON_CHK) violated ORA-06512: at line 2

OK. So I replace the newline characters with "\n", a standard replacement string to indicate a new line. In the code below you see "\\" because the first \ escapes the next one.

BEGIN
   INSERT INTO json_data
        VALUES (q'^{
          "without_spaces" : "OK",
          "with spaces not a good idea" : "Not OK",
          "carriage returns definitely bad idea" : 
      "BEGIN\\nDBMS_OUTPUT.PUT_LINE ('Hello JSON!');\\nEND;"}^');

   COMMIT;
END;
/

And now....no problems!

SELECT json_value (j.my_doc, '$."carriage returns definitely bad idea"') r
  FROM json_data j
/

BEGIN\nDBMS_OUTPUT.PUT_LINE ('Hello JSON!');\nEND;

And so to conclude:
  1. When stuffing JSON into a table's column, make sure you use the IS JSON check constraint to ensure valid JSON.
  2. Don't put spaces in your property names. Use underscores or camelCase.
  3. Keep newline characters out of your JSON documents.

Tuesday, December 13, 2016

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x the lowest salary in the company. Your salary will be automatically set to the maximum allowed, if this rule is broken.
So just check to see if that rule is violated, right? Easy enough in PL/SQL, right in a database trigger (see links at bottom of post for discussions on whether or not you should put logic like this in your database triggers):

CREATE OR REPLACE TRIGGER equitable_salary_trg 
   AFTER INSERT OR UPDATE 
   ON employees 
   FOR EACH ROW 
DECLARE 
   l_max_allowed   employees.salary%TYPE; 
BEGIN 
   SELECT MIN (salary) * 25 
     INTO l_max_allowed 
     FROM employees; 
 
   IF l_max_allowed < :NEW.salary 
   THEN 
      UPDATE employees 
         SET salary = l_max_allowed 
       WHERE employee_id = :NEW.employee_id; 
   END IF; 
END equitable_salary_trg; 

Well....maybe not. I execute the following block:

BEGIN 
   UPDATE employees 
      SET salary = 100000 
    WHERE last_name = 'King'; 
END; 

and I see this error:

ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "EQUITABLE_SALARY_TRG", line 4

OK, we get that, right? I am both selecting from and trying to update the EMPLOYEES table in a row-level trigger. That's the no-no.

Getting Around ORA-04091 with PL/SQL Packages

The solution, conceptually, is simple enough. If I can do task X in the row level trigger, save whatever information I need to perform X on that row in a to-do list (a collection, perhaps?). Then define an AFTER STATEMENT trigger that goes through the to-do list, and executes the desired logic for each row.

The traditional (now, out-of-date) solution is to define a package that contains a collection defined at the package level. Package-level variables have session scope. So I can add information to the collection within the row-level trigger, and it will still be there when I bubble up to the statement-level trigger.

Here's my package specification:

CREATE OR REPLACE PACKAGE equitable_salaries_pkg 
IS 
   PROCEDURE initialize; 
 
   PROCEDURE add_employee_info ( 
      employee_id_in IN employees.employee_id%TYPE 
    , salary_in IN employees.salary%TYPE 
   ); 
 
   PROCEDURE make_equitable; 
END equitable_salaries_pkg; 

Huh. I don't see any collection there. Right. You shouldn't. If you put the collection in the specification, it can be modified by any schema with EXECUTE authority on the package, in whatever way anyone wants to mess with that collection. Well, that's no good. So I "hide" the list in the body and "expose" it through the procedures in the spec.

CREATE OR REPLACE PACKAGE BODY equitable_salaries_pkg   
IS   
   TYPE id_salary_rt IS RECORD (   
      employee_id   employees.employee_id%TYPE   
    , salary        employees.salary%TYPE   
   );   
   
   TYPE g_emp_info_t IS TABLE OF id_salary_rt   
      INDEX BY PLS_INTEGER;   
   
   g_emp_info                 g_emp_info_t;   
   g_corrections_in_process   BOOLEAN      := FALSE;   
   
   PROCEDURE initialize   
   IS   
   BEGIN   
      g_emp_info.DELETE;   
   END initialize;   
   
   PROCEDURE finished_corrections   
   IS   
   BEGIN   
      g_corrections_in_process := FALSE;   
   END finished_corrections;   
   
   PROCEDURE starting_corrections   
   IS   
   BEGIN   
      g_corrections_in_process := TRUE;   
   END starting_corrections;   
   
   FUNCTION corrections_in_process   
      RETURN BOOLEAN   
   IS   
   BEGIN   
      RETURN g_corrections_in_process;   
   END corrections_in_process;   
   
   PROCEDURE add_employee_info (   
      employee_id_in IN employees.employee_id%TYPE   
    , salary_in IN employees.salary%TYPE   
   )   
   IS   
      l_index   PLS_INTEGER := g_emp_info.COUNT + 1;   
   BEGIN   
      IF NOT corrections_in_process   
      THEN   
         g_emp_info (l_index).employee_id := employee_id_in;   
         g_emp_info (l_index).salary := salary_in;  
      END IF;   
   END add_employee_info;   
   
   PROCEDURE make_equitable   
   IS   
      l_max_allowed   employees.salary%TYPE;   
      l_index         PLS_INTEGER;   
   BEGIN   
      IF NOT corrections_in_process   
      THEN   
         starting_corrections;   
   
         SELECT MIN (salary) * 25   
           INTO l_max_allowed   
           FROM employees;      
   
         WHILE (g_emp_info.COUNT > 0)   
         LOOP   
            l_index := g_emp_info.FIRST; 
   
            IF l_max_allowed < g_emp_info (l_index).salary   
            THEN   
               UPDATE employees   
                  SET salary = l_max_allowed   
                WHERE employee_id = g_emp_info (l_index).employee_id;   
            END IF;   
   
            g_emp_info.DELETE (g_emp_info.FIRST);   
         END LOOP;   
   
         finished_corrections;   
      END IF;   
   END make_equitable;   
END equitable_salaries_pkg;

See? Aren't you glad I wrote that, so you didn't have to? :-) Well, it gets better - as in lots of that code is unnecessary. But before I get to that, let's finish up the old-style approach. We need to rebuild the triggers!

1. Before getting started, make sure no one is going to muck with those rows. And make sure the package-based collection is empty.


CREATE OR REPLACE TRIGGER equitable_salaries_bstrg 
   before INSERT OR UPDATE  
   ON employees 
BEGIN 
   LOCK TABLE employees IN EXCLUSIVE MODE; 
   equitable_salaries_pkg.initialize; 
END; 

2. For each insert or update to employees, add the necessary information to the to-do list.

CREATE OR REPLACE TRIGGER equitable_salaries_rtrg  
   AFTER INSERT OR UPDATE OF salary  
   ON employees  
   FOR EACH ROW  
BEGIN  
   equitable_salaries_pkg.add_employee_info (:NEW.employee_id, :NEW.salary);  
END; 

3. Create a statement-level trigger to apply the rule.

CREATE OR REPLACE TRIGGER equitable_salaries_astrg  
   AFTER INSERT OR UPDATE   
   ON employees  
BEGIN  
   equitable_salaries_pkg.make_equitable;  
END; 

And now the update statement will work without raising any ORA-04091 errors!

BEGIN  
   UPDATE employees  
      SET salary = 100000  
    WHERE last_name = 'King';  
  
   ROLLBACK;  
END; 

add_employee_info: 100-100000
add_employee_info: 156-100000
make_equitable max allowed 52500
make_equitable emp id and salary: 100-100000

Yep. That's a lot of code to write and deal with to get around this problem. So several years back, the PL/SQL team decided to make things easier for their users with....

The Compound DML Trigger

Straight from the doccompound DML trigger created on a table or editioning view can fire at multiple timing points. Each timing point section has its own executable part and optional exception-handling part, but all of these parts can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error. Two common uses of compound triggers are: (1) To accumulate rows destined for a second table so that you can periodically bulk-insert them; (2) To avoid the mutating-table error (ORA-04091).

The compound trigger more allows you to define variables which persist through the execution of the steps defined in the compound trigger. And that's the aspect of this feature that makes things so much easier when it comes to mutable table errors.

Using this feature, I can combine all the different trigger events and code, plus they share scope like the subprograms of a package body. So I declare a variable in the compound trigger and reference it in both trigger events. Take a look:

CREATE OR REPLACE TRIGGER equitable_salary_trg    
FOR UPDATE OR INSERT ON employees    
COMPOUND TRIGGER     
   TYPE id_salary_rt IS RECORD (    
      employee_id   employees.employee_id%TYPE    
    , salary        employees.salary%TYPE    
   );    
    
   TYPE row_level_info_t IS TABLE OF id_salary_rt  INDEX BY PLS_INTEGER;    
    
   g_row_level_info   row_level_info_t;    
    
   AFTER EACH ROW IS    
   BEGIN  
      g_row_level_info (g_row_level_info.COUNT + 1).employee_id :=    
           :NEW.employee_id;    
      g_row_level_info (g_row_level_info.COUNT).salary := :NEW.salary;
   END AFTER EACH ROW;    
    
   AFTER STATEMENT IS    
      l_max_allowed   employees.salary%TYPE;    
   BEGIN      
      SELECT MIN (salary) * 25    
        INTO l_max_allowed    
        FROM employees;     
       
      FOR indx IN 1 .. g_row_level_info.COUNT    
      LOOP                                      
         IF l_max_allowed < g_row_level_info (indx).salary    
         THEN    
            UPDATE employees    
               SET salary = l_max_allowed    
             WHERE employee_id = g_row_level_info (indx).employee_id;    
         END IF;    
      END LOOP;    
   END AFTER STATEMENT;    
END equitable_salary_trg; 

Much simpler  - all relevant code in one place.

More reliable - you don't have to worry about managing the session-persistent collection.

Less code - always a nice thing, as long as the "less code" is also understandable and easy to maintain.

You might also find these resources helpful:

ORACLE-BASE: Trigger Enhancements in Oracle Database 11g Release 1
ORACLE-BASE: Should you use triggers at all? (Facts, Thoughts and Opinions)
Toon Koopelars: Triggers Considered Harmful, Considered Harmful

Tuesday, December 6, 2016

PL/SQL 201: Ensuring backward compatibility in your PL/SQL code

Unlike other code in the stack, database-level programs such as those built in PL/SQL, tend to have a very long half-life. In part, that's because companies are far less likely to change their database technology than user interface language.

And that means that it is especially important for Oracle Database programmers to prioritizing writing maintainable, well-documented programs. It is also critical that as we make changes to our code base, we don't break programs that have been working for years.

In other words, whenever possible, enhanced code should be backward compatible with earlier versions of code. That's called backward compatibility. In this post, I will explore features of PL/SQL that make it easy to ensure your latest and greatest code has maximum compatibility with "the past."
If a tree falls in a forest and no one is around to hear it, does it make a sound?
You've heard that before, right? Well, how about this one:
If you write a program and no one ever uses it, did you really write that program?
I ask myself this a lot. I've written hundreds of "demo" programs over the years, for trainings, for play, for libraries. But, hey, maybe nobody ever used them or ran them. Did I ever really write it? Oh, sure I did! Did it really matter? Maybe just to me.

Anyway, chances are that you don't write a program unless it is going to be used. And as soon as "someone" uses your code (it could even by you, with a new packaged procedure calling an existing subprogram), you've implicitly signed a contract with that person (or other program unit), stating:
I will do everything in my power to ensure that as I make changes in the future, I will honor how the program works for you now.
So let's see how we can keep our promises, by taking a look at what I just had to do today to my code.

Most of the production PL/SQL code I've written since 2010 is for the PL/SQL Challenge, a website offering weekly quizzes on SQL, PL/SQL and more (try it out!). The backend code uses the "qdb" prefix for "Quiz DataBase". And I created a package to manage player information.

You can play weekly quizzes competitively or just for fun. So I created the set_competitive_status procedure to toggle between them. In other words, the first time it is called for my user ID, it sets up to compete (my answers are ranked). The next it is called, my status is set to "for fun only."

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
END;

And for six years it worked just fine. In other words, it is used, both in the Oracle Application Express "front-end" and in other packages. Here's an example from the backend:

PROCEDURE qdb_process_answers
   AUTHID DEFINER
IS
BEGIN
   qdb_player_mgr.set_competitive_status (
      user_id_in          => 1506,
      competition_id_in   => 15709);
END;

And from APEX:


We are now, though, working on a new "skin" for the PL/SQL Challenge: the Oracle Dev Gym. This site has a different flow when taking a quiz, and I needed to be able to force the competitive status to "for fun only" - even if it was the first time, even if it was already set that way. In other words, my "toggle" procedure should not toggle.

First, let's see what happens if I push ahead somewhat blindly, paying no attention to the past. "Hey, no problem, I will just add a parameter!"

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in           IN INTEGER,
      competition_id_in    IN INTEGER,
      force_non_compete_in IN BOOLEAN);
END;

I compile the package with the modified specification. No errors! Exciting! Even before I get to changing the implementation of this enhancement, though, I notice that the QDB_PROCESS_ANSWERS procedure has an INVALID status. That makes sense - I changed a program unit (QDB_PLAYER_MGR) on which the procedure relies.

No worries. I will just recompile the procedure. But when I do, I see this error:

PLS-00306: wrong number or types of arguments in call to 'SET_COMPETITIVE_STATUS' 

Um, still no worries? Well....I guess not. I will just change QDB_PROCESS_ANSWERS to pass a Boolean value for the third argument:

PROCEDURE qdb_process_answers AUTHID DEFINER
IS
BEGIN
   qdb_player_mgr.set_competitive_status (
      user_id_in           => 1506,
      competition_id_in    => 15709,
      force_non_compete_in => FALSE);
END;

Now it compiles without errors. Problem solved.

No! No! No! That is almost certainly the wrong thing to do. Why?

1. This procedure does not need the "force non compete" functionality. It's fine the way it is.

2. What if there are hundreds of invocations of this procedure in my code base? Am I going to search them all out (which I can do quite nicely with PL/Scope) and change each one, even though they do not need the new functionality?

3. Even worse, am I going to deal with the outraged howls of other developers on my team whose code now will not compile?

So what can I do instead?

1. Provide a default value for the new parameter: if it is a trailing IN parameter, then I can assign a default value, and all existing invocations of that subprogram will recompile successfully:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in           IN INTEGER,
      competition_id_in    IN INTEGER,
      force_non_compete_in IN BOOLEAN DEFAULT FALSE);
END;

2. Create an overloading of that procedure (same name, different parameter list):

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER,
      force_non_compete_in IN BOOLEAN);
END;

Now, when QDB_PROCESS_ANSWERS is recompiled, the PL/SQL compiler will automatically determine which of the SET_COMPETITIVE_STATUS subprograms "match" the invocation in the procedure.

3. Create an entirely new procedure for this new requirement, as in:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE turn_off_ranking (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
END;

So which should you do? It depends (when doesn't it "depend"? :-) ).

You will not be able to simply add a parameter with a default value if the new parameter is an OUT or IN OUT parameter.(only IN parameters can have default values). In this case, you will need an overloading. For example:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER,
      new_status_out OUT VARCHAR2);
END;

If adding that single new IN parameter actually means making a whole lot of changes in the existing procedure, it will likely make the most sense to create a new subprogram dedicated to that strikingly different algorithm.

To Conclude (and offer some additional advice)

1. When changing the header of a subprogram already in use, honor the current signature, and do everything you can to avoid having to change existing invocations to match the new signature. Use default parameter values or overloading. Which, by the way, is another reminder of a baseline best practice for PL/SQL: put all your procedures and functions in packages. Do not create schema-level procedures and functions. You can't overload at the schema level.

2. Determine whether the changes made to the implementation of the subprogram (its executable section) may affect the way the subprogram is currently used. For example, if it is a function that is executed from within a SQL statement, then you could cause all sorts of problems by inserting a SQL statement into the function, especially non-query DML (inserts, updates, deletes).

3. If a subprogram that is currently in use is being replaced entirely by a new, shinier, better version, mark the original subprogram as deprecated with the DEPRECATE pragma (new to 12.2).