Enhancements for PL/SQL development


Hello, and welcome once again to our quarterly KeepTool newsletter, Keeping in Touch. In this issue, we’ll be highlighting a few of the new features that were introduced with Version 12.1.1, which was released in September 2016.

KeepTool 12.1.1 is entirely compatible with Windows 10 Anniversary Edition.

Support for NLS_LENGTH_SEMANTICS on the PL/SQL Page

A number of these enhancements support the display of NLS_LENGTH_SEMANTICS parameter settings, which affects how PL/SQL objects and types are compiled: i.e. whether they have implied byte or character semantics.

Here’s a short example. Suppose the user KTDBA creates the following package:

1-packagae-source-code

In the PL/SQL overview, we can see that this package uses character semantics, but others do not:

2-pl-sql-overview

Suppose that the package owner KTDBA wants to make this packaged constant GLOBAL_KEY available to all users. It was compiled with CHAR semantics through the use of an ALTER SESSION command—which means that 13 characters of storage have been reserved by defining GLOBAL_KEY as VARCHAR2(13).

This string consists of a variety of “European” characters chosen just for demonstration purposes.

Now, suppose that another user, HR1, logs on and creates a procedure SHOW_GLOBAL_KEY using BYTE semantics because by default, the database instance uses BYTE semantics. The procedure attempts to show some information about the database characterset and the length of the local variable L_GLOBAL_KEY, which is defined with a length of 13:

3-procedure-source-code

Upon execution, we get the following error:

4-call-interface-error

Execution has failed at line 16 of the source code, which attempted to assign the 13-character string defined in KTDBA’s package to the local variable, which has a length of 13 bytes.

Simply by issuing

ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;

and recompiling the procedure without any source code changes, everything should turn out fine:

5-output-window

And indeed it is!

The length in bytes is 21 because each of the 6 accented characters takes up 2 bytes in the AL32UTF8 characterset and the euro symbol takes up 3 bytes. The remaining 6 characters take one byte each.

This shows that when sharing compiled PL/SQL code among various users, it is critical to pay attention to the NLS_LENGTH_SEMANTICS that were in effect at the time of compilation. Otherwise, errors or misunderstandings could result.

Hora shows which semantics are in effect on the PL/SQL page for all PL/SQL objects, and on the TYPES page.

6-types

Thus, in the above example, it is clear that PHONE_LIST_TYP is a varying array of elements 25 bytes in length.

Furthermore, in the Reverse DDL tool, the following statement is now generated near the beginning of the script, based on the NLS_LENGTH_SEMANTICS setting in effect for the session:

7-reverseddl

 

Enhanced display of PL/SQL with errors

In the previous version of Hora, we introduced square red bullets to pinpoint lines with PL/SQL errors. This makes it fairly easy to find errors in the part of PL/SQL source code that currently fits the editor window. Now we have an easy way to find the offending line if this is not the case, as in the example below, where the error is on line 198:

1-procedure-code-error

Simply by dragging the vertical scroll bar down until the vertical gray “view” marker aligns with the red “error line” marker, it becomes easy to see locate the offending statement.

1-procedure-code-error-2

Both of these markers are available for all PL/SQL objects, including triggers.

Increased ease of navigation within package bodies

Hora Version 12.1.1 has introduced a new method of navigating within package bodies. For example, when you look at a package body with dozens of functions, like this one belonging to SYS:

navigation

… within the list of functions to the left of the source code, you can navigate to any one of them using Ctrl + Click, just as if you were following a hyperlink in a web browser.

Hora 12.1.1 now also distinguishes between public and private methods within a package body. Note that TOCHAR is a local function (nested within FORMAT_CELL), and it is therefore preceded by a gray bullet, unlike the public functions that are declared in the package specification, which have a blue bullet.

This version of KeepTool comes with a few other enhancements, which are described in the Release Notes in the help files, and on the KeepTool website at https://keeptool.com/release-notes/.

Here we have chosen a few topics that all revolve around PL/SQL code. We hope that you will explore them, and that you will find that they make your life easier. Let us know!

In the next edition of the newsletter, slated for January 2017, we may highlight some existing KeepTool capabilities that may not be well known, but that you may want to take advantage of. See you then!