I visited the Czech Republic in February to do a training for Oracle and another for the IT department of a financial institution based in Brno (second largest city after Prague). I thought you might enjoy reading this....

 

You have been developing software since 1980. What were the conditions like for programming back them? Did it take longer time to write code? How have things changed over the last 30 years?

...

For the past several years, I have given my Coding Therapy talk to Oracle technologists, offering insights through:

Dream therapy Shock therapy Game therapy Couples therapy It is, of course, entirely tongue-in-cheek, but also (I hope) helpful to programmers, in giving them a different perspective on some of the challenges they face in their work.

These talks have usually been very well-received,...

Problem: The exception section of a block can only trap errors raised in the executable section!

In one important respect, writing software is no different from driving a car. You can be the best driver in the world, but if the other driver is awful, you can still have a big problem on your hands. So you drive defensively; you stay alert to the possibility that another driver might make a poor decision or be careless. You protect yourself as best you can.

It’s the same with software. You believe that you are a fine programmer (and I have no reason to doubt that!), and that your programs work well. But have you guarded your programs against people who might use them carelessly or in ignorance? Probably not.

Problem: Jasper queries the same, unchanging data over and over and over again.  What's the solution?

Sure, you should certainly read my blog - and encourage everyone you know to do the same.

But there are lots of other fantastic Oracle technologists out there, and several write blogs on Toad World.



So today I would like to start the New Year by drawing your attention to the excellent writing of Dan Clamage. I've known Dan for years, since 1997 in fact (just searched my email history). He's reviewed...

Problem: One person's clarity is another person's bewilderment.

Problem: You have lots of dynamic SQL updates, with different values each time.

Solution: Bind, don't concatenate, to optimize performance and simplify dynamic string construction.

Or are they just sociable? Social Media has taken the world by storm, primarily through Facebook, Twitter, Yelp, Foursquare, LinkedIn, etc. And now everyone expects to have access to a network of "friends" wherever they are, through whichever device they are using. This raises an interesting question for those us in the technology world and vendors providing tools in which technologists "live": Does social media/networking...

We software developers are a very privileged bunch. We don’t have to work in dangerous environments, and our jobs aren’t physically taxing (though carpal tunnel syndrome is always a threat). We are paid to think about things, and then to write down our thoughts in the form of code. This code is then used and maintained by others, sometimes for decades.

Given this situation, I believe we all have a responsibility to write code that can be easily understood and maintained (and, c’mon, let’s admit our secret desires, admired) by the developers who follow in our footsteps. Look at this way: if you have a child, she might grow up to be a programmer. She might even put in time at one of your previous employers. She might actually end up having to maintain code that you wrote.

The choice is yours: avoid mortifying embarrassment either by never putting your name in your code, or by writing code that you would be proud to show to, share with, and inflict on your own flesh and blood.

I write a PL/SQL 101 column for Oracle Magazine and the March/April 2012 issue focused on Error Management. I received the following letter from a reader; we couldn't fit the whole thing in Oracle Magazine's Q&A section, so I decided to publish it here.



Hello Steven, I've very much...

OOW is the big (enormous, really) blast of a conference/party that Oracle hosts each year in San Francisco. I've been attending these conferences since the late 1980s, and each year it seems to get bigger. Fortunately, Oracle offers a "conference within a conference": Oracle Develop, for those of us developing applications with Oracle technology. I usually spend almost all my time at the Oracle Develop hotel, so even though...

Consider the following block:

 

DECLARE

   l_date   DATE;

BEGIN

   l_date := '15-FEB-2011';

END;

 

I declare a date variable and then assign a string to it. Will this block raise an error? Hard to say. It depends on the NLS settings for the default date format – for the database as a whole or for my session. If this string doesn’t match the default, then Oracle will raise...

Oracle PL/SQL provides the RAISE_APPLICATION_ERROR built-in procedure (actually defined in the DBMS_STANDARD package) so that we can communicate application-specific error messages back to our users. When you call this procedure, you provide a negative number between -20999 and -20000, as well as your error message.

Here's an example of calling RAISE_APPLICATION_ERROR from inside a trigger, ensuring that employees...

The "new" debugging architecture (Java Debug Wire Protocol-based) that was introduced in Oracle Database 9i Release 2 was a major improvement on the previous one (DBMS_DEBUG), especially because it made it possible to inspect the values of a wider range of data types. However, it's still not possible to inspect the values of all data types, as the JDWP-based debugging architecture has the following limitations on inspection...

Oracle added the CONTINUE statement in Oracle Database 11g to simplify the logic you would have to write when you need to terminate execution of code within the body of a loop.

CONTINUE is especially helpful when you are executing nested loops and need to skip the remainder of the inner loop's body and continue on to the next iteration of the outer loop. Sure, you can achieve the same effect with IF statements, but...

The SUBTYPE statement is one of my favorite features of the PL/SQL language; it allows you to create “aliases” for existing types of information, in effect creating your own specially-named datatypes. Use SUBTYPE when you want to standardize on a set of named datatypes that aren’t anchorable back to the database. You can then anchor to those new datatypes, instead, and achieve the same, desired goal: if a change must be made, or takes place, to a datatype, you will have to make that change in only one place.

TIOBE Software (www.tiobe.com) publishes a Programming Community Index that rates the popularity of various programming languages and shows trends in usage

Nancy recently wrote to me with the following question:

Steven, how does one avoid hard codes in views?  I have a very complex view that wants specific data from a multiple of tables - so there are multiple in ('xx','xyz','abc'....) and more.  I know it is a maintenance headache when hard codes are introduced, but not sure how to avoid them - short of creating specific tables that hold the values I want - which then...

I received this request from a reader in July:

"Could you please explain why Oracle recommends that we not use OUT/IN-OUT parameters in functions?"

Actually, it's not just Oracle. Most "gurus" in the software world make the same suggestion. There are two key reasons for the recommendation that you should only return data through the RETURN clause of a function, not the parameter list. I sum up these answers here,...

Oracle introduced a significant enhancement to PL/SQL’s SQL-related capabilities with the FORALL statement and BULK COLLECT clause for queries. Together, these are referred to as bulk processing statements for PL/SQL. Why, you might wonder, would this be necessary? We all know that PL/SQL is tightly integrated with the underlying SQL engine in the Oracle database. PL/SQL is the database programming language of choice for Oracle—even though you can now use Java inside the database as well.

But this tight integration does not mean that there is no overhead associated with running SQL from a PL/SQL program. When the PL/SQL runtime engine processes a block of code, it executes the procedural statements within its own engine, but passes the SQL statements on to the SQL engine. The SQL layer executes the SQL statements and then returns information to the PL/SQL engine, if necessary.

This transfer of control between the PL/SQL and SQL engines is called a context switch. Each time a switch occurs, there is additional overhead. There are a number of scenarios in which many switches occur and performance degrades. As you can see, PL/SQL and SQL might be tightly integrated on the syntactic level, but "under the covers" the integration is not as tight as it could be.

With FORALL and BULK COLLECT, however, you can fine-tune the way these two engines communicate, effectively telling the PL/SQL engine to compress multiple context switches into a single switch, thereby improving the performance of your applications. 

A PL/SQL developer (let's call him Dan) sent me this request last week:



=============================================================

Would you please settle a minor argument for me? We're trying to institute coding standards at my company.

 

I've been pushing for developers to put explicit declarations of private functions and procedures in their packages. My co-workers argue back that...

I have for many years now promoted "Write tiny little chunks of code" as one of the most impactful and important steps we can all take to improve the readability and maintainability of code. The basic idea is that you should not let your executable sections get to be more than 50 or 60 lines of code in length. You should, in other words, be able to at a single glance take in the full algorithm/logical flow of the subprogram....

The ecosystem of Oracle technology is vast, ever-changing and full of surprises. These surprises are sometimes referred to as "bugs", "undocumented features" and just plain "What? Are you kidding me?"

Michael Rosenblum of Dulcian, and co-author of Oracle PL/SQL for Dummies (I know, I know, sounds a bit silly - but it is an excellent "intro" book for beginner PL/SQL developers), recently asked me about some...

I discovered something curious today. Check out the following block; it runs without any compilation or runtime error:

DECLARE   l_char   CHAR (1);   CURSOR x   IS      SELECT dummy INTO l_char FROM DUAL;BEGIN   OPEN x;   FETCH x INTO l_char;   CLOSE x;END;  

Can anyone see why I find it odd and curious? I have sent a note to the PL/SQL Product Manager asking what he thinks. What do you think?

 

...

About six years ago, I had Lasik surgery on my eyes. Wow, that was fantastic! I'd been wearing glasses since I was 8 years old. To able to walk outside in the rain and see clearly, to be able to see in the shower - well, I was overjoyed with the results.

Unfortunately, since then I have come to realize and accept that Lasik is not a panacea for all eye problems (not that I ever really thought that was true). In the...

The Quest Code Tester for Oracle development team is making more and more extensive use of the XMLTYPE datatype in the Oracle database. XML documents are a perfect fit for several requirements of Code Tester, in particular storing values for nested structures like collections of records, which in turn contain collections as fields.

After adding an XMLTYPE column to our table, we then updated our table API packages to...

I recently received this email:

Hi Steven, I am looking for tools to measure code quality and via which you can drill  down to the problem areas and for tools to refactor PL/SQL. Recently we insourced a team that is doing maintenance on an Oracle product. We are an Agile company and are now in the process of transforming the team to a Scrum team. The other teams develop Java desktop applications and have all the means to support Agile development. As a Scrum master of the insourced team I want to stimulate the team to explore ways to improve PL/SQL code quality, small steps at a time.  An important aspect of Scrum is continuous improvement. One of the bigger problems I see is code duplication....

 

I love to hear from PL/SQL developers, and I love to help them out, but sometimes I am just too busy to respond quickly or in time. But sometimes, best of all, a developer asks me for help, and then solves his or her problem without my help.   

That happened last week, and the result is a utility that I thought I would share with you. I received this email from Bryan Nyman (bnyman1@yahoo.com):

" apologize...

I received this request from Jayme last week:

"Hello Steven. In Transact-SQL I was able to have a block of code say if today was a  Monday give me Friday's date, else give me yesterday.  I have no idea how to do this in PL/SQL and you said if you spend more than 30 minutes on a problem to get a second set of eyes to look at it...will you be my second set of eyes?"

We needed logic similar to this for the PL/SQL...

I have written a lot of PL/SQL code over the years, and I've collected the programs and scripts I reference in my training materials in the demo.zip file, which you can download from PL/SQL Obsession.

Sometimes when I present one of the packages I have written, I joke that:

"I had fun writing this package, but I wonder if anyone will ever use it. And you must be familiar with that 'Zen of Programming' riddle: If you write a program and no one ever uses it, did you really write it?"

...

One of the most persistent problems I hear about from PL/SQL developers is that of receiving an "insufficient privileges" error when trying to execute a SQL statement in a stored program unit. I received an email last week with another variation of this problem, this time involving DBMS_ERRLOG.

Steven addresses a question from a user on how to set up a magic kingdom package using a magic_values table.

Conditional compilation is a feature of PL/SQL introduced in Oracle Database 10g that allows you to specify "meta" commands in PL/SQL that tell the compiler to include/exclude certain lines of code for compilation - and even raise a compilation error.

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...

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.

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!

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...

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 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....

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...