I recently received this question from Syed:

"How does Oracle store the data for associative arrays and nested tables internally? Which type of collection is better for processing a huge volume of data without putting back into the database?"

First, regarding the question of "internal" storage: I have no idea how Oracle manages these structures. I expect that the PL/SQL development team uses some kind of linked list implementation. I also suppose that I could ask Bryn Llewellyn (the PL/SQL Product Manager) about this, but I am certain his answers will be:

...

On 15 July 2010, 32 developers participated in the first-ever PL/SQL Challenge championship playoff. This playoff (consisting of ten questions to be answered in 15 minutes) was the culmination of a three-month competition in which PL/SQL developers from around the world tested their knowledge of PL/SQL against one another through daily quizzes.

Steven explains the difference between RAISE_APPLICATION_ERROR and RAISE and why you would use one vs. the other.

I recently needed to move the contents of clobs and collections to and from files, and also write simple reporting routines. So I ended up creating the following and uploading them to my demo.zip download:

export_clob.sql - move the contents of a clob to the specified file or to system output, if no file is specified.    file_to_clob.sql - load the contents...

Way back when Oracle10g was released, Oracle added compile-time warnings to the PL/SQL compiler.

When you enabled warnings in your session (or for a specific program), then at the time of compilation, Oracle will check to see if it can identify ways in which you might be able to improve the quality or performance of your code.

You can read more about the compile time warnings feature here.

...

I recently received an email from Syed asking this very question as follows:

"Could you please let me know why DDL statements are not allowed [natively] in PLSQL? I read somewhere that the reason is that DDL statements will cause objects to be invalidated and then require recompilation. As all objects are already in a compiled [VALID] state, compiling again will be an overhead. But when we execute DDL statements [with...

I recently received this question from a PL/SQL developer:

"What is the maximum size of a PL/SQL procedure?"

The answer is interesting: there is not a maximum size, per se. Instead, the limitation has to do with the maximum number of DIANA nodes your program generates in the compilation process.

DIANA is an intermediate language produced and used by the compiler. DIANA standards for “Descriptive Intermediate Attributed Notation for Ada" - and reminds us that PL/SQL is based on Ada, a programming language originally developed for the U.S. Department of Defense.

...

I received yesterday this email from a PL/SQL developer:



I am stumped. My PL/SQL code does not seem to be handling dates properly. Here is a block of code I extracted from my actual program to demonstrate the problem:

DECLARE   global_end_date   DATE := '04-May-2010';   global_beg_date   DATE := '01-May-2010';BEGIN   IF TRUNC (SYSDATE) > TO_DATE (global_end_date, 'DD-MON-YYYY')   THEN      DBMS_OUTPUT.put_line...

The PL/SQL Standards page of PL/SQL Obsession now offers a third choice for pre-defined standards for PL/SQL development.

 

This document has been fine-tuned by Bill Coulam (www.dbartisans.com) as part of his effort to construct a development framework for PL/SQL...

I've just returned from Collaborate 10, the international Oracle conference organized through a collaboration (hence the name!) between IOUG, Quest (the user group) and OAUG.

It was held at the Mandalay Bay Convention Center in Las Vegas. Now, I must admit, I really don't like Las Vegas (the pretend parts) very much at all. The real parts, like the Red Rock Canyon National Conservation Area,...

"Invoker rights, what's that?" you may be asking.

That wouldn't surprise me, greatly, though one might consider it a bit odd because the invoker rights feature of PL/SQL was added in Oracle8i - many years ago!

So I will first offer a brief overview of invoker rights, why you'd use it, how you use it. Then I will introduce one of the major challenges with invoker rights. Finally, I offer a utility that can help...

I have certainly learned a lot over the years from other authors, and I thought I would share with you my fairly eclectic collection of favorite books (those that have informed my programming, in any case).

The PL/SQL Challenge is a daily quiz that culminates every three months in a championship tournament to determine the most knowledgeable PL/SQL developers in the world. You could win cash, O'Reilly Media books and other goodies - but only if you play!

We all know that hard-coding is a bad thing in software. But most developers think of hard-coding simply as typing a literal value into your program whenever you need it.

You can now download version 1.9 of Code Tester. This release features the following enhancements:

 

Object type support   You can now test directly the methods of object types, test the contents of object type instances for equality and inequality, and specify the values of attributes through a grid.

 

Default value support   You now...

Steven discusses generic retrieval/change challenges when it comes to PL/SQL records.

FORALL was introduced into PL/SQL in Version 8i. It is a fantastic feature; you should use it in place of all loops that contain DML statements performing row-by-row processing. You will generally see performance improvements of at least an order of magnitude. 

If you are not familiar with FORALL, make it a priority to get familiar. You can start with my brand new 5th edition of Oracle PL/SQL Programming,...

I recently published the following puzzle in the ToadWorld newsletter:

Which of the following queries return the names of programs (without duplication) defined in the currently connected schema whose source contains a call to DBMS_OUTPUT.PUT_LINE (assume that this program name does not appear inside comments)?

A.   SELECT name FROM USER_DEPENDENCIES     WHERE referenced_name = 'DBMS_OUTPUT.PUT_LINE' B.  ...

I was recently in Germany, and did an interview with Erik Franz of database|pro. One of the questions he asked me was: "Which programming language would you recommend a college student learn to give them the strongest chance at a good job upon graduation?" I would have loved to be able to answer: "Learn PL/SQL!

As I write this, I am flying back to Chicago now, 9 PM Oct 21, from Mexico City, after a very nice two day visit.

 

Quest Mexico asked me to come down to help celebrate the release of Toad Version 10. I said "OK."

 

And, wow, they did a really fantastic job of making me feel appreciated. Besides...

Steven discusses how LTRIM works and an alternative solution for a user's problem.

A little known, but very handy feature of PL/SQL is the ability to apply set operators, like union, intersect and minus, to nested tables.

Check out this video. IBM claims to now provide native support for PL/SQL programs, including support for many built-in packages, collections, etc. Wow! Lots more details here, but you will find below some



...

When the going gets tough and budgets get tight, money for training is often cut first. You still, however, need to learn how to best leverage the PL/SQL language and write high quality code. To help you, Quest is offering a three-part, webcast training series that I will be teaching. Each session consists of two hours of in-depth coverage of a specific functionality area or best practice.

 

The cost is far less...

What a big relief....I was in the beta program, checking out the new features of PL/SQL in this latest release of the Oracle database. That's quite a privilege, but also a burden; Oracle is very severe about not wanting us to talk about upcoming features before they officially release the product to their adoring public.

 

That's tough on me, because I have a hard time keeping my mouth shut about anything, especially...

[ It is very likely that if you are reading this you are a developer, but this is really geared towards software managers. So feel free to copy and paste into a Word doc and pass it along to your manager or team lead! ]

 

I very often give trainings and presentations on the topic of PL/SQL best practices. I offer lots of ideas for writing code that is easier to read and maintain, runs more efficiently, can be...

I just fixed a bug in Quest Code Tester for Oracle, and I thought I would share my experience with you.

The bug was reported by our most excellent QA tester, Danny Pham. He is not only good at going through the defined test cases to verify correct behavior. He also takes devilish delight in trying all sorts of "crazy" stuff to see what happens.

So Danny encountered this error after he set up a test case with three outcomes, all built on cursor variable expression tests (something that is very unlikely for a customer to have done):

...

I spend way too much of my life on airplanes zipping around to different places, doing presentations and trainings on PL/SQL. In the process, I meet many PL/SQL developers – and I continue to be surprised and disappointed at how many developers do not know about or at least do not seem to take advantage of very powerful and useful features of PL/SQL. So I have to decided to list below all those elements of PL/SQL that I consider...

Steven proposes a solution to a customer's question about using a 2 dimensional array in PL/SQL.

I was on the phone with a Quest Code Tester user the other day and he asked me if I was working on the next edition of my original "magnum opus", Oracle PL/SQL Programming, which would include Oracle11g features.

 

I cannot describe to you how good it felt to say: "No, I'm not working on it. I'm finished working on it."

 

In fact, just...

I am often asked about the naming conventions and coding standards that I use. My answer is usually a combination of muttering and vague statements and hand-waving.

 

That's because I have a confession: I don't have a single naming conventions and coding standards document that I use. Why not? Because I am a software developer! That is, I feel (well, I am) very busy, overwhelmed by deadlines. I don't feel like...

Every month, a Toad World newsletter goes out to thousands of Toad users and it includes a monthly puzzle that I write. Last month's puzzle went like this:

Which of the following blocks does not contain an infinite loop? A. DECLARE    l_line VARCHAR2(32767);    l_file UTL_FILE.file_type :=       UTL_FILE.fopen ('C:\temp', 'my_file.txt', 'R'); BEGIN    LOOP       UTL_FILE.get_line (l_file, l_line);    END LOOP;...

It's truly one of the oddities of the PL/SQL language that it does not offer a delimited string parsing program. The closest we can get is DBMS_UTILITY.COMMA_TO_TABLE, and that is sadly deficient (it only parses comma-delimited strings and each item between the commas must be a valid PL/SQL identifier).

 

So I built one myself (the parse package) and put it in the demo.zip file...

Developers are hard people to satisfy. Oracle gives us this incredibly robust, powerful and easy to use database programming language – and all we can do is complain about what it doesn't do for us.

Well, that's reality for you: PL/SQL is powerful and robust and easy to use, but also very narrowly focused. So if you want it to do something outside of its area of expertise, sometimes you have to jump through a few more hoops than you'd like.

...

In part 1 of this series, I reviewed the automated refactoring features in SQL Developer. In part 2, I checked out PL/SQL Developer's refactoring features. Now, it's time for Toad.

 

Differently from both PL/SQL Developer and SQL Developer, Toad does not have...

In part 1 of this series, I reviewed the automated refactoring features in SQL Developer. Now I will check out PL/SQL Developer (version 7.1.5)'s refactoring features.

 

To access these features, I highlight some code in a procedure edit window and then choose Refactoring from the right click menu. I see these options:



 

I...

 

 

 

I have long been attracted to the idea and process of refactoring (explained below). It is now very exciting to see that automated refactoring features are working their way into tools for PL/SQL development. I will analyze these features over the next few entries in my ToadWorld blog, starting with SQL Developer and then moving on to PL/SQL Developer, and finishing up with SQL Navigator and Toad.

...

As I travel certain parts of the globe doing presentations on PL/SQL, I meet many extremely talented PL/SQL programmers – people who meet the most complex challenges you can imagine with a deft combination of intense creativity, hard work, and of course Oracle PL/SQL.

Oracle Magazine names only one as the PL/SQL Developer of the Year at each Oracle Open World, and this year the award was given to Alex De Vergori of Betfair.  Oracle Magazine writes:

...

Part 2: The Game of Mastermind

 

Software development is one heck of a serious job. It turns out, however, that there are several games you can play to improve the quality of code you write. This is the second of two blog entries that introduce you to two of my favorite brain development and training games: Set and Mastermind.

...

Part 1:  The Game of Set

 

Software development is one heck of a serious job. It turns out, however, that there are several games you can play to improve the quality of code you write. This is the first of two blog entries that introduce you to two of my favorite brain development and training games: Set and Mastermind.

 

Play either (preferably both) of these games, and you will write better software.

...

One of the highlights of Oracle Open World 2008 for me was the presentation by Bryn Llewellyn (PL/SQL Product Manager) on “Doing SQL in PL/SQL.”

 

Bryn surely has the most thorough and clear understanding of the PL/SQL language of anyone I have met (definitely including me).

 

His talk was detailed and precise (and maybe just a little bit overwhelming. He needed twice the time allotted) on this most important topic.

...

I have generally recommended in the past that whenever you are writing code to iterate through the elements of a collection, you should use a while loop, combined with the FIRST-NEXT or LAST-PRIOR collection methods.  

The key advantage of this approach is that the code will not raise a NO_DATA_FOUND exception if your collection is sparse (there is an index value between FIRST and LAST that is not defined). And if your collection is empty, the loop will not execute at all, whereas with a for loop, an empty collection could cause a VALUE_ERROR exception if you are not careful....

I have, for the past several years, focused heavily on designing and building an automated code testing tool for PL/SQL: Quest Code Tester for Oracle. One feature that is often requested as a part of code testing is analysis of code coverage, answering questions like:

When I run my program do I use 50% of the code? 75% of the code?   Are there chunks...

Hey folks,   For anyone attending Oracle Open World this year, here is my schedule of presentations:

Session ID: S300184 Session Title: Weird PL/SQL Track: Oracle Develop: Database Room: Golden Gate C3 Date: 2008-09-21 Start Time: 15:45

 

Session ID: S300183 Session Title: Break Your Addiction to SQL! Track: Oracle Develop: Database Room: Salon 02 Date: 2008-09-22 Start Time: 13:00  

...

You learn something new every day, right? Well, I certainly do (more or less). Even about PL/SQL, about which I am sure many people think I already know everything. Far from it.  

In fact, I learned just last week from the PL/SQL Product Manager, Bryn Llewellyn, that his recommendation regarding cursor FOR loops and bulk collect is different from mine – and for a very good reason.  

Several years ago, he informed...

Oracle doesn't make it terribly easy to run operating system commands from within a PL/SQL block. I suppose that's understandable, given that PL/SQL is an embedded database-oriented language. Still, developers do ask me on a regular basis about how they can do this.

As I understand it, there are basically three ways to do this:

Invoke a Java method from within a PL/SQL wrapper   Call a C program...

As many of my readers likely know by now, I have been working for the past several years on the Quest Code Tester development effort.  

Code Tester is the most powerful PL/SQL test automation tool available. You describe the expected behavior of your programs and Code Tester generates your test code, which can then be run from the UI or via a script. With Code Tester, you can build comprehensive regression tests and...

A few months ago, I posted on this blog an explanation of how to use Toad's Code Templates to standardize development and improve productivity. I included an XML document that contains over 20 templates that I thought you might find useful. I also asked my readers to produce XML transformations so that the XML document could be "output" in the format that Toad recognizes (and SQL Navigator as well).

 

I am happy...

  Collaborate08, annual conference of the International Oracle User Group, and several other national and international user groups, will be held in Denver this year, from April 13th to the 16th. I am going to present three papers, including (for the first time) Weird PL/SQL. I thought you might enjoy reading about some of the weirdnesses of PL/SQL in my ToadWorld blog, so here's an excerpt from the beginning of my whitepaper:

...