1.5.1 Take a Creative, Even Radical Approach

We all tend to fall into ruts, in almost every aspect of our lives. People are creatures of habit: you learn to write code in one way; you come to assume certain limitations about a product; you turn aside possible solutions without serious examination because you just know it can't be done. Developers become downright prejudiced about their own tools, and often not in positive ways. "It can't run any faster than that; it's a pig." "I can't make it work the way the user wants; that'll have to wait for the next version." "If I were using X or Y or Z product, it would be a breeze. But with this stuff, everything is a struggle."

Sadly (or is it happily?), the reality is that your program could almost always run a little faster. The screen could function just the way the user wants it to. Although each product has its limitations, strengths, and weaknesses, you should never have to wait for the next version. Isn't it so much more satisfying to be able to tell your therapist that you tackled the problem head-on, accepted no excuses, and created a solution?

How do you do this? Break out of the confines of your hardened views and take a fresh look at the world (or maybe just your cubicle). Reassess the programming habits you've developed, particularly regarding fourth-generation language (4GL) development with the Oracle tools. Be creative -- step away from the traditional methods, from the often limited and mechanical approaches constantly reinforced in our places of business.

Try something new: experiment with what may seem to be a radical departure from the norm. You will be surprised at how much you will learn, how you will grow as a programmer and problem-solver. Over the years, I have surprised myself over and over with what is really achievable when I stopped saying "You can't do that!" and instead simply nodded quietly and murmured "Now, if I do it this way..."

1.5.2 Get Ready to Establish New Habits

PL/SQL was initially a way to increase the flexibility of the ANSI-standard SQL, and soon a multi-purpose "Swiss Army Knife" for many of Oracle's tools. Suddenly, developers found they could plop function calls, IF-THEN-ELSE clauses, loops, and even GOTOs right into the midst of their otherwise pristinely declarative screen modules and batch database processing routines.

Did the appearance of PL/SQL instantly transform Oracle-based applications into shining examples for programmers of all faiths? Hardly. Sure, 4GLS are lots more productive than the older software technology: now you can dig yourself into a pretty deep hole with a 4GL much more efficiently than was ever possible with good old FORTRAN.

In fact, for a number of reasons, the level of sophistication of programming in PL/SQL has proven very uneven. While many SQL*Forms developers came out of a 3GL programming environment with a strong history in structured programming and strict guidelines, these principles have been largely forgotten or considered inapplicable in the new 4GL world of SQL and SQL*Forms. What does "structured code" mean when a screen is not composed of lines of code in a file, but rather as a series of pictures and boxes of attributes in the Designer? How do you flowchart a SQL statement?

Many of us left our good programming manners behind when we sauntered into the world of SQL*Forms. It's been hard to return to those habits, especially given some of the limitations of PL/SQL Version 1. On the other hand, many Oracle developers are not seasoned programmers, but relative newcomers who may have little or no formal training in computer sciences and programming. They might, for example, have started out as end users who needed some ad hoc queries and ended up building forms.

The first release of PL/SQL (and the later versions, for that matter) was not intended to be a comprehensive procedural language in the same league as, say, C or COBOL. Officially, it existed only to provide some programming constructs around the SQL language to facilitate batch database procedures. PL/SQL did not interact with the operating system, had no debugger whatsoever, and didn't support the normal 3GL concepts of link libraries and modularized code. As soon as Oracle made PL/SQL available in SQL*Forms Version 3, however, thousands of Oracle developers moved quickly to put it to work in their forms. Suddenly they could code all (well, almost all) the fancy gizmos their users wanted.[2]

[2] And they could do so without committing the most unnatural acts (for example, user exits to provide pop-up windows or SQL*Forms Version 2.3 triggers that called themselves recursively and were impossible to debug).

Damn the torpedoes and full speed ahead! But what about standards? What about modularization? What about reusable code? Such concerns were often ignored as the volume of PL/SQL programming exploded throughout the Oracle community. In their press to meet management expectations of extraordinary productivity, developers did what they needed to do in what little time they had. And few of us had time to take training in PL/SQL, even when it was offered. Few of us had time to think about whether what we wrote could be maintained or enhanced easily. Instead we just coded. And coded. And coded.

When it came time to debug a PL/SQL module, we discovered that there wasn't any debugger in PL/SQL at all, and precious little to work with in SQL*Forms and SQL*Plus. Programmers with backgrounds in established languages like COBOL or FORTRAN or C shook their heads and did what they could. The many people introduced to software development through Oracle software didn't know what they were missing. They just knew that it was very difficult to identify and then repair problems in their code.

PL/SQL has come a long way from its first hesitant offering for the RDBMS in 1990. Developers who have worked with the language since its first release must make sure to adapt to the changing features and potential of PL/SQL.

1.5.3 Assume that PL/SQL Has What You Need

Programmers who are new to PL/SQL often make the mistake of starting their coding efforts before they are sufficiently familiar with everything the language has to offer. I have seen and heard of many instances where a developer spends valuable time writing procedures or functions that duplicate built-in functionality provided by PL/SQL.

Please don't write a function that looks through each character in a string until it finds a match and then returns the index of that match in the string. The INSTR function does this for you. Please don't write a function to convert your string from uppercase to lowercase by performing ASCII code-table shifting. Use the LOWER function instead.

With the PL/SQL of the 1990s, you also have to keep in mind much more than these basic functions. Each new release of the database and the tools include packages that stretch the boundaries of the PL/SQL language itself. These packages extend PL/SQL by providing additional datatypes, functions, and procedures to handle more specialized situations. You can use DBMS_ JOB to schedule processes from the database. You can use DBMS_PIPE to communicate information between different Oracle sessions. The ideas and the list of prebuilt code goes on and on. Take some time to stroll through Part 3, Built-In Functions , and Appendix C, and get familiar with all the features that are built into the PL/SQL language.

1.5.4 Share Your Ideas

Oracle Corporation, along with its flavor of SQL and the PL/SQL language, has been around for close to 15 years. They have listened to user requests, kept up with the standards committees, and generally sought to create a very robust environment for developers and users. As I've said, there is a very good chance that what you need is already available in the language. If so, use it. If not, build it yourself in the most general and reusable way possible. Then share it. Share your ideas and your creations with others in your company, your Oracle User Group, even the worldwide Oracle community through the International Oracle User's Group and User's Week convention.