Monday, July 24, 2017

Document deprecated program units with new pragma (12.2)


Software is constantly evolving: bugs fixed, new features added, and better ways to do things discovered and implemented.

A great example of this dynamic from PL/SQL itself is the UTL_CALL_STACK package. This package was first introduced in Oracle Database 12c Release 1, and it improves upon the functionality already provided by the following functions in the DMBS_UTILITY package: FORMAT_CALL_STACK, FORMAT_ERROR_STACK, and FORMAT_ERROR_BACKTRACE.

The same thing happens in PL/SQL code that is developed by customers. The now-outdated subprograms (or other elements) of one's API cannot be removed immediately; that would break existing code. But everyone would like to make sure that any new code uses the new API.

The new DEPRECATE pragma (compiler directive) in Oracle Database 12.2 will help you accomplish this transition in a smooth, error-free fashion. It provides a formal way to communicate information about deprecated elements [my change ok? SF YES] with a power that ordinary external documentation cannot convey.

You can apply this new pragma to a whole unit, to a subprogram within a unit, at any level of nesting, to any definition, and so on. When a unit is compiled that makes a reference to a deprecated element, a warning is displayed (when you have compile time warnings enabled).

Let's take a look at some examples.

1. Deprecate an entire package.

CREATE PACKAGE pkg AUTHID DEFINER 
AS
   PRAGMA DEPRECATE(pkg);

   PROCEDURE proc;
   FUNCTION func RETURN NUMBER;
END;

2. Deprecate a subprogram in a package. Note the comment added to the pragma. This text will be displayed along with the warning/error information.

CREATE PACKAGE pkg AUTHID DEFINER
AS
  PROCEDURE proc;
  PRAGMA DEPRECATE (
    proc,
    ’pkg.proc deprecated. Use pkg.new_proc instead.’);

  PROCEDURE new_proc;
END;

Let's try using that deprecated procedure, with warnings enabled.

ALTER SESSION SET plsql_warnings = 'enable:all'
/

CREATE OR REPLACE PROCEDURE use_deprecated
   AUTHID DEFINER
IS
BEGIN
   pkg.proc;
END;
/

Procedure USE_DEPRECATED compiled with warnings

PLW-06020: reference to a deprecated entity: PROC declared in unit PKG[4,14]. pkg.proc deprecated. 
   Use pkg.new_proc instead.

Deprecation Warnings

PL/SQL in Oracle Database 12.2 has four new warnings to help you utilize the DEPRECATE pragma:

6019. The entity was deprecated and could be removed in a future release. Do not use the deprecated entity.

6020. The referenced entity was deprecated and could be removed in a future release. Do not use the deprecated entity. Follow the specific instructions in the warning if any are given.

6021. Misplaced pragma. The DEPRECATE pragma should follow immediately after the declaration of the entity that is being deprecated. Place the pragma immediately after the declaration of the entity that is being deprecated.

6022. This entity cannot be deprecated. Deprecation only applies to entities that may be declared in a package or type specification as well as to top-level procedure and function definitions. Remove the pragma.

Try it on LiveSQL!

I know, I know. You'd love to start exploring this and other new features in 12.2, but you don't yet have 12.2 installed at work. No problem!

LiveSQL offers free, 24x7 access to a 12.2 schema. You can execute SQL and PL/SQL code to your heart's content.

You can also run scripts that others, including myself, have uploaded to LiveSQL.

Here's one on the deprecate pragma. Enjoy!

Thursday, July 13, 2017

COUNT Method Works Like COUNT in SQL

You are writing PL/SQL code to provide secure, high performance access to your data and implement business rules. [reference: Why Use PL/SQL?]

Right? Good.

And you use collections (associative arrays, nested tables, arrays) because they offer all sorts of great functionality. [reference: Collections in PL/SQL YouTube playlist]

Right? Good.

So here's a quick reminder about COUNT, one of many methods available for collections (others include DELETE, FIRST, LAST, NEXT, PRIOR, TRIM, EXTEND):

It works pretty much like COUNT in SQL.

If the collection is empty, COUNT returns 0, not NULL.

If you try to "read" an element at an undefined index value, Oracle Database raises NO_DATA_FOUND. Just like a SELECT INTO that identifies no rows.

If you check to see if a collection is empty with a call to COUNT, it doesn't raise NO_DATA_FOUND.

To verify what I've said, and to have a bit of fun while doing it, you can take a quiz on this topic at the Oracle Dev Gym.


Thursday, July 6, 2017

Three tips for getting started right with Oracle Database development

By "Oracle Database development", I mean, more or less, writing SQL and PL/SQL. I assume in this post that you have access to Oracle Database (which you can get via Cloud services, Docker, GitHub and OTN).

A. Use a powerful IDE, designed with database programming in mind.

There are lots of editors out there, and many IDEs that work with Oracle Database. Sure, you could use Notepad, but OMG the productivity loss. You could also use a popular editor like Sublime, and then it get it working with Oracle.

I suggest, however, that you download and install Oracle's own own, free, powerful IDE: SQL Developer.

If you like to complement your graphical IDE with a command line tool (or OMG if you actually prefer a command line tool to a graphical interface), you should also check out the relatively new and generating-lots-of-excitement SQLcl.

B. Enable compile-time warnings and PL/Scope.

The database has tons of useful functionality burned right into it, ready for you to use. For example, when PL/SQL program units are compiled, Oracle can give you feedback (aka, "compile-time warnings) to improve the quality and performance of your code.

In addition, PL/Scope - when enabled - will gather information about your identifiers and (in 12.2) SQL statements. This will allow you to do some very impressive impact analysis of your code.

Most developers are not aware of these features and so leave them turned off. Here's my suggestion for SQL Developer users:

Open up Preferences, type "compile" in the search field. Then change your settings to match these:


In other words:

1. Enable all warnings. 

This way, whenever you compile a program unit, Oracle will give you advice about ways to improve your code.

2. Treat all "severe" warnings as compile-time errors

If the PL/SQL team thinks these warnings are critical in some way, then I want to make my production code is free of such warnings. By setting this caregory to ERROR, I ensure that the code will not compile unless it is "clean". 

3. Tweak your optimization level up to 3 (all the good stuff plus subprogram inlining).

And even more important, take whatever steps are appropriate in your development environment to ensure that production code is compiled at this level of optimization as well. Check out this guidance from the PL/SQL dev team for more details.

4. Turn on PL/Scope.

You can then execute queries against your code to get information regarding naming conventions, sub-optimal code, and opportunities for performance improvements. 

Resources to help you with PL/Scope may be found on LiveSQL and GitHub.
Important Note: These are settings for use in DEVELOPMENT - and they will be applied to all connections made in SQL Developer. When you deploy to production, you should use a script that explicitly sets values for warnings, optimization level (still 3) and PL/Scope (off).
C. Decide RIGHT NOW on logging and instrumentation.

Before you start writing you next program, accept this reality: your code will be full of bugs. You will need to trace execution as well as log those bugs, in order to get your code ready for production and then keep it running smoothly in production.

You need a logging utility for this, and I suggest you use the open-source, widely-used Logger utility available from GitHub.



Wednesday, July 5, 2017

What happens when a package fails to initialize? New behavior as of 12.1!

The best way to build applications on top of Oracle Database is to build lots of APIs (application programmatic interfaces) to your data with PL/SQL packages.

And that means you end up with lots of packages in your application. That's just great!

Now, when a user selects a feature of your application that in turn references an element in a package (invokes a procedure or function, or reads the value of a constant), that package must be instantiated and initialized for that user's session. As described in the documentation:
When a session references a package item, Oracle Database instantiates the package for that session. Every session that references a package has its own instantiation of that package. 
When Oracle Database instantiates a package, it initializes it. Initialization includes whichever of the following are applicable:
  • Assigning initial values to public constants
  • Assigning initial values to public variables whose declarations specify them
  • Executing the initialization part of the package body
Ah, but what happens when any of these steps fail? That, dear reader, is the focus of this post.

Suppose I have a procedure that raises an exception when executed:

CREATE OR REPLACE PROCEDURE always_fails
IS
BEGIN
   RAISE PROGRAM_ERROR;
END;
/

Then no matter how many times I try to run this procedure, it terminates with that same exception:

BEGIN
   always_fails;
END;
/

ORA-06501: PL/SQL: program error
ORA-06512: at "STEVEN.ALWAYS_FAILS", line 4

BEGIN
   always_fails;
END;
/

ORA-06501: PL/SQL: program error
ORA-06512: at "STEVEN.ALWAYS_FAILS", line 4

At which point you must now be saying: "Well, duh, Steven. Of course you are going to see the same exception each time you try to run the procedure."

Exactly. Just so. OK, now let's try it again, with the following package. When the package is initialized, it assigns (or tries to assign) a value of "Lu" to g_name. But that assignment fails, since "Lu" is too big to fit into a VARCHAR2(1) variable.

Thus, the PL/SQL engine raises the VALUE_ERROR exception (ORA-06502).

CREATE OR REPLACE PACKAGE valerr
IS
   FUNCTION little_name RETURN VARCHAR2;
END valerr;
/

Package compiled

CREATE OR REPLACE PACKAGE BODY valerr
IS
   g_name   VARCHAR2 (1) := 'Lu';

   FUNCTION little_name RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_name;
   END little_name;
BEGIN
   DBMS_OUTPUT.put_line ('Before I show you the name...');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'Trapped the error: ' || DBMS_UTILITY.format_error_stack ());
END valerr;
/

Package body compiled


So what happens when I try to execute the little_name function, after compiling the package?

I see an unhandled exception:

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name);
END;
/

ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
ORA-06512: at "VALERR", line 3

Before going any further, let's make sure you understand why the exception went unhandled. After all, the package body has a "catch-all" exception handler:

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'Trapped the error: ' || DBMS_UTILITY.format_error_stack ());
END valerr;

So why did the exception go unhandled? Because the error occurred in the "declaration section" of the package (not within the initialization section of the package or the executable section of a subprogram of the package). Exception sections only handle errors raised in the executable section of code (see my video for more details).

OK, so now we know:
  1. The package failed to finish initializing.
  2. An exception raised when assigning a default value to a package-level variable or constant cannot be handled within the package.
So if I try to execute this function again, I will see the same error, right? Well, maybe - depending on your version of Oracle Database. Prior to 12.1, you will see this:

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name);
END;
/

Name = 

No exception. Instead, the valerr.little_name function returns a NULL value. Huh?

Yes, I know. That seems counter-intuitive, but here's the thing: prior to Oracle Database 12c Release 1, even if a package failed to initialize, it would be marked as initialized in that session. And any variables or constants that had already successfully been assigned a value would have those values. Which can make it tough to track down the error.

But as of 12.1, when a package fails to initialize, then that package is marked as uninitialized. And any subsequent effort to use that package will grow the same exception. So in 12.1 (and 12.2 and 18.1 and....) you will see:

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name);
END;
/

ORA-06502: PL/SQL: numeric or value error: character string buffer too small  
ORA-06512: at line 2

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name);
END;
/

ORA-06502: PL/SQL: numeric or value error: character string buffer too small  
ORA-06512: at line 2

Conclusion

I expect you will all agree that the 12.1 behavior is preferred to the earlier "Oh, that package has a problem? Not to worry!" approach.

And ideally this change would not result in changed behavior for your application.

As in: hopefully, your testing is good enough so that you would have noticed a package initialization failure.

Finally, if you'd like to test your knowledge on this topic, try our Oracle Dev Gym quiz.