Showing posts from 2018

Reduce the volume of PL/SQL code you write with these tips

I'm not known for being concise. I'm best known in the world of Oracle Database for my "magnum opus" Oracle PL/SQL Programming, which checks in at 1340 pages (the index alone is 50 pages long).

But I've picked up a few tips along the way for writing PL/SQL code that is, well, at least not as long, as verbose, as it could have been. And certainly shorter than my books. :-)

You probably have some ideas of your own; please offer them in comments and I will add them to the post.

Qualified Expressions (new to 18c)

In the bad old days before Oracle Database 18c was released (and is now available for free in its "XE" form), if you wanted to initialize an associative array with values, you had to do in the executable section as follows:

DECLARE TYPE ints_t IS TABLE OF INTEGER INDEX BY PLS_INTEGER; l_ints ints_t; BEGIN l_ints (1) := 55; l_ints (2) := 555; l_ints (3) := 5555; FOR indx IN 1 .. l_ints.COUNT …

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today:
In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer

Quick Answer
Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption.Test to see if that's fast enough (likely will be for many cases).If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory. Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification.Don't put anything in the collection you don't need. [from Giulio Dottorini]Remember: each session that runs this code will use that amount of memory.Background

When you use BULK COLLECT, you retrieve more than row with each fetch, reducing context switchi…

Why won't MULTISET work for me?

I recently got an email from an Oracle Database developer who was trying to get the MULTISET operator to work in his code.

He'd created nested tables of records and found that MULTISET UNION would work but MULTISET EXCEPT would not.

When he ran his code he got this error:

PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL"
I will confess that it took me longer than I'd like to admit (but I just did!) to get to the heart of his problem, so I figure others might get similarly befuddled. Time for a blog post!

Let's explore some of the nuances behind musing MULTISET, centered around this important statement from the documentation:
Two objects of nonscalar type are comparable if they are of the same named type and there is a one-to-one correspondence between their elements. In addition, nested tables of user-defined object types, even if their elements are comparable, must have MAP methods defined on them to be used in equality or IN conditi…

An Introduction to PL/SQL

Just getting started with PL/SQL? You will find PL/SQL to be a very readable and accessible programming language. You'll be productive in a very short amount of time!

I offer this post as a quick way to access a number of resources that will provide a nicely-paced introduction to this powerful database programming language. Of course, it helps a lot to know SQL, too, so check out the Other Useful Links at the bottom of the post.

I wrote a series of "PL/SQL 101" articles for Oracle Magazine several years ago. Here's a convenient index to all those articles:

1. Building with Blocks - an overview of PL/SQL, followed by coverage of some fundamentals
2. Controlling the Flow of Execution - conditional statements and loops
3. Working with Strings
4. Working with Numbers
5. Working with Dates
6. Error Management
7. Working with Records
8. Working with Collections
9. Bulk Processing with BULK COLLECT and FORALL
10. The Data Dictionary: Make Views Work for You
11. Wrap Your …

High Performance PL/SQL

PL/SQL is a key enabling technology in Oracle Database. You should make sure that you are aware of and take advantage appropriately key features of the PL/SQL language focused on performance.

I offer a short list of those features below, along with links to related resources. Most of this is also capture in this slide deck:

Key Performance Features

All of these are covered in the slide deck above; the links will take you to the documentation on these features. Click here for the overall section in the doc on performance and optimization.
Bulk processing with FORALL and BULK COLLECTThe function result cacheOptimized execution of user-defined functions from SQLThe NOCOPY HintAutomatic compiler optimization But Don't Forget About SQL Optimization

Chances are you could take full advantage of all the features listed above and more in PL/SQL, and still end up with a slow application. That's because at the heart of every application built on Oracle Database are your SQL statements, and …

Packaged Cursors Equal Global Cursors

Connor McDonald offered a handy reminder of the ability to declare cursors at the package level in his Public / private cursors blog post.

I offer this follow-on to make sure you are aware of the use cases for doing this, and also the different behavior you will see for package-level cursors.

Generally, first of all, let's remind ourselves of the difference between items declared at the package level ("global") and those declared within subprograms of the package ("local").
Items declared locally are automatically cleaned up ("closed" and memory released) when the block terminates.Items declared globally are kept "open" (memory allocated, state preserved) until the session terminates. Here's are two LiveSQL scripts that demonstrates the difference for a numeric and string variable (not that the types matter).

Global vs Local variable (community contribution)
Global (Package-level) vs Local (Declared in block) Variables (mine)

But this princ…

Declarative PL/SQL

SQL is a set-oriented, declarative language. A language or statement is declarative if it describes the computation to be performed (or in SQL, the set of data to be retrieved/modified) without specifying how to compute it.

PL/SQL is a procedural language, tightly integrated with SQL, and intended primarily to allow us to build powerful, secure APIs to underlying data (via SQL).

If I try hard, I can maximize the procedural side of PL/SQL and minimize the declarative aspect of SQL (primarily by ignoring or discounting the set-orientation of SQL). That's generally a bad idea. Instead, we Oracle Database developers make the most of the many powerful features of SQL (think: analytic functions, pattern matching, joins, etc.), and minimize processing in PL/SQL.

What we should also do, though, is recognize the make the most of the declarative features of PL/SQL. There are two big reasons to do this:

1. When you don't tell the PL/SQL compiler how to do things, the optimizer has more …

All About PL/SQL Compilation Settings

A recent Twitter thread delved into the topic of the best way to enable PL/SQL warnings for program units, including this recommendation from Bryn Llewellyn, Distinguished Product Manager for PL/SQL:

which then led to Bryn volunteering me to delve into the details of PL/SQL compiler settings in an AskTOM PL/SQL Office Hours session. 

Which I will do. But I figured I could start right off by writing this post. So let's explore how to set and modify PL/SQL compiler settings.

First, you might wonder what those settings are or could be. The best way to check is by examining the USER_PLSQL_OBJECT_SETTINGS view (and of course the ALL* version to examine attributes of code you do not own but can execute):

The values that are stored for a PL/SQL unit are set every time it is compiled—in response to "create", "create or replace", "alter", invoking a utility like Utl_Recomp, or implicitly as a side effect of trying to execute an invalid PL/SQL unit.

A spec…

How to debug your dynamic SQL code

Got this plea for help via our AskTOM PL/SQL Office Hours program:

Dear Experts, I have below written below code: ---------------------------------------------- Declare v_Table all_tables.TABLE_NAME%type; v_Mnt varchar2(2):='08'; Type Cur_type is Ref Cursor; C Cur_type; Begin v_Table:='ddi_ticket_10_1018'; Open C for 'SELECT * from bill.'||v_Table||v_Mnt||'Where called_nbr=123'; End; ------------------------------------------------------------------- When executing this code, I face this Error message. ORA-00933-SQL Command not properly ended ORA-06512: At Line 9. Please check the above code and modify for syntax correction
I could, at a glance, pretty well guess what the problem is.

Can you?

I am not trying to boast. I just encourage you to not read further and instead examine the code. What could be causing his problem?


Code You Should Never See in PL/SQL

If you ever run across any of the following, apply the suggested cleanup, or contact the owner of the code, or run for the hills.

And I am pretty sure many of my readers will have suggestions for other code that should never appear in your PL/SQL programs. Let me know in the comments and I will add them to the post (giving me all the credit of course - no, just joking! YOUR NAME will be bright lights. :-) ).

Here's the list of things to avoid:

DON'T set default values of variables to NULL.DON'T select from dual for....just about anything.DON'T declare the FOR loop iterator.DON'T trap NO_DATA_FOUND for inserts, updates and deletes.DON'T execute the same DML statement inside a loop.DON'T hide errors.DON'T re-raise an error without logging first. See below for the gory details.

Set default value to NULL

Whenever you declare a variable it is assigned a default value of NULL. So you should not explicitly provide NULL as a default value. It won't do any ha…