Steven Feuerstein's Blog

Oracle Open World Presentations

Steven Feuerstein — Tue, 22 Jul 2008 15:41:00 GMT

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

Session ID: S300185
Session Title: Why You Should Care About Oracle 11g PL/SQL Now
Track: Oracle Develop: Database
Room: Salon 02
Date: 2008-09-23
Start Time: 11:30

Hope to see you there!

Always Bulk Collect

Steven Feuerstein — Mon, 23 Jun 2008 14:34:00 GMT

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 me (and provided a script to verify the statement: 10g_optimize_cfl.sql in the demo.zip) that in Oracle10g, the compiler now optimizes every cursor FOR loop to execute at a level of performance similar to BULK COLLECT.

This is a wonderful and non-trivial optimization. Rather than have to go through a manual rewrite of all cursor FOR loops, you can leave them in place and reap the benefits of the BULK COLLECT (sort of).

Well, that's not quite true – and it's even less true than I thought.

First of all, if your cursor FOR loop contains any DML statements, you will still want to explicitly convert to BULK COLLECT. The reason is that while the optimizer will improve the performance of the query step, it will not optimize the DML statements to execute like FORALLs (the bulk processing analogue for updates, inserts and deletes).

So if you have code that looks like (cfl_to_bulk_0.sql in demo.zip):

PROCEDURE upd_for_dept (
   dept_in     IN   employees.department_id%TYPE
, newsal_in   IN   employees.salary%TYPE
)
IS
   CURSOR emp_cur
   IS
      SELECT employee_id, salary, hire_date
        FROM employees
       WHERE department_id = dept_in;
BEGIN
   FOR rec IN emp_cur
   LOOP
      BEGIN
         INSERT INTO employee_history
                     (employee_id, salary, hire_date
                     )
              VALUES (rec.employee_id, rec.salary, rec.hire_date
                     );

         adjust_compensation (rec.employee_id, rec.salary);

         UPDATE employees
            SET salary = newsal_in
          WHERE employee_id = rec.employee_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            log_error;
      END;
   END LOOP;
END upd_for_dept;

You will need to change the cursor FOR loop to BULK COLLECT so that you can fill up collections with data from the query, which can then be used in the FORALL statement. You will, sadly (due to increased complexity and program length) end up with something like this (cfl_to_bulk_5.sql in demo.zip):

PROCEDURE upd_for_dept (
   dept_in     IN   employees.department_id%TYPE
, newsal_in   IN   employees.salary%TYPE
)
IS
   bulk_errors    EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

   TYPE employee_tt IS TABLE OF employees.employee_id%TYPE
      INDEX BY BINARY_INTEGER;

   employee_ids   employee_tt;

   TYPE salary_tt IS TABLE OF employees.salary%TYPE
     INDEX BY BINARY_INTEGER;

   salaries       salary_tt;

   TYPE hire_date_tt IS TABLE OF employees.hire_date%TYPE
      INDEX BY BINARY_INTEGER;

   hire_dates     hire_date_tt;

   CURSOR employees_cur
   IS
      SELECT     employee_id, salary, hire_date
            FROM employees
           WHERE department_id = dept_in
      FOR UPDATE;

   PROCEDURE fetch_data_quickly (
      limit_in           IN       PLS_INTEGER
    , employee_ids_out   OUT      employee_tt
    , salaries_out       OUT      salary_tt
    , hire_dates_out     OUT      hire_date_tt
   )
   IS
   BEGIN
      FETCH employees_cur
      BULK COLLECT INTO employee_ids_out, salaries_out, hire_dates_out LIMIT limit_in;
   END fetch_data_quickly;

   PROCEDURE adj_comp_for_arrays (
      employee_ids_io   IN OUT   employee_tt
    , salaries_io       IN OUT   salary_tt
   )
   IS
   BEGIN
      FOR indx IN 1 .. employee_ids_io.COUNT
      LOOP
         adjust_compensation (employee_ids_io (indx), salaries_io (indx));
      END LOOP;
   END adj_comp_for_arrays;

   PROCEDURE insert_history
   IS
   BEGIN
      FORALL indx IN employee_ids.FIRST .. employee_ids.LAST SAVE EXCEPTIONS
         INSERT INTO employee_history
                     (employee_id, salary, hire_date
                     )
              VALUES (employee_ids (indx), salaries (indx), hire_dates (indx)
                     );
   EXCEPTION
      WHEN bulk_errors
      THEN
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            -- Log the error
            log_error
                     (   'Unable to insert history row for employee '
                      || employee_ids
                                     (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
                    , SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                     );
            /*
            Communicate this failure to the update phase:
            Delete this row so that the update will not take place.
            */
            employee_ids.DELETE (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX);
         END LOOP;
   END insert_history;

   PROCEDURE update_employee
   IS
   BEGIN
      /*
        Use Oracle10g INDICES OF to avoid errors
        from a sparsely-populated employee_ids collection.
      */
      FORALL indx IN INDICES OF employee_ids SAVE EXCEPTIONS
         UPDATE employees
            SET salary = newsal_in
              , hire_date = hire_dates (indx)
          WHERE employee_id = employee_ids (indx);
   EXCEPTION
      WHEN bulk_errors
      THEN
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            log_error
                     (   'Unable to update salary for employee '
                      || employee_ids
                                     (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
                    , SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                     );
         END LOOP;
   END update_employee;
BEGIN
   OPEN employees_cur;

   LOOP
      fetch_data_quickly (100, employee_ids, salaries, hire_dates);
      EXIT WHEN employee_ids.COUNT = 0;
      insert_history;
      adj_comp_for_arrays (employee_ids, salaries);
      update_employee;
   END LOOP;
END upd_for_dept;

Sorry, I know that I should probably explain this code but to be brutally honest my dad had heart bypass surgery and I am currently at the hospital and so you get the abbreviated version ....

Anyway, that is one scenario where you definitely need to convert from the cursor FOR loop.

But Bryn, to my surprise, recommends that you always convert explicitly to BULK COLLECT. Why is that? For performance reasons. Apparently, the cursor FOR loop optimization makes the code run faster, but not as quickly as BULK COLLECT.

In my tests (which you can reproduce in cfl_vs_bulkcollect.sql), I found that BULK COLLECT ran 33% faster than the cursor FOR loop:

Cursor For Loop Elapsed: 8.12 seconds.
Bulk Collect Elapsed: 5.46 seconds.

So if you really want the best performance, take the time and make the effort to switch over to BULK COLLECT.

How to Run an OS Command from PL/SQL

Steven Feuerstein — Wed, 04 Jun 2008 14:11:00 GMT

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 an external procedure from within PL/SQL.
Use the new DBMS_SCHEDULER package.

I will soon publish a DBMS_SCHEDULER solution (written by Bryn Llewellyn, PL/SQL Product Manager) on my OTN Best Practices column. In the meantime, you will find below a quick review of the steps needed to do this in Java and C.

Executing Host Command with Java

With the Java approach, you will take these steps:

Find the Java class that implements host command execution.
Build a class that invokes that host command method. Let's call it HostCommand.
Build a PL/SQL program that calls a method in HostCommand to run your command.
Acquire the privileges needed to execute host commands via Java in the database.

Let's go through each of these steps.

1. Find the Java class that implements host command execution.

Objects of the java.lang.Runtime class include an exec method that will execute a host command.

2. Build a class that invokes that host command method.

Let's call it HostCommand.

Here's code to create a new Java class in the database to invoke this command for the Windows XP operating system:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "UTLcmd"
AS import java.lang.Runtime;
public class execHostCommand
{ 
  public static void execute (String command) 
    throws java.io.IOException
  {
   String osName = System.getProperty("os.name");
   if(osName.equals("Windows XP"))
       command = "cmd /c " + command;
   Runtime rt = java.lang.Runtime.getRuntime();
   rt.exec(command);
  }
}
/

You can easily modify the execute method to support other operating systems based on the value of osName.

3. Build a PL/SQL program that calls a method in HostCommand to run your command.

CREATE OR REPLACE PACKAGE host_command IS
  PROCEDURE execute (cmd IN VARCHAR2) AS LANGUAGE JAVA NAME
           'execHostCommand.execute(java.lang.String)';
END;
/

Notice that I "map" the VARCHAR2 datatype to the java.lang.String class in my call to the new Java method.

4. Acquire the privileges needed to execute host commands via Java in the database.

You need special privileges to execute host commands from within the database through Java. Otherwise when you try to execute your command you will see an error like this (the error message will vary depending on what you are trying to do):

One way to obtain these privileges is to have the JAVASYSPRIV role granted to your schema. This role contains all the privileges you need (and more).

For a more nuanced approach to granting the required privileges, you can also use the Java security API available in the Oracle database. For example, if I want to delete a file using a host command (perhaps it is not accessible through UTL_FILE), I will need to grant the following privileges to the schema in which the command is executed, such as HR:

BEGIN
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.io.FilePermission'
                             , '<>'
                             , 'execute'
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'writeFileDescriptor'
                             , ''
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'readFileDescriptor'
                             , ''
                              );
END;
/

Executing Host Command with C

To use C, you must define an external procedure and then invoke it within your PL/SQL block. It is not possible within this article to cover completely all the steps and issues involved in setting up such an external procedure. I will, instead, cover the highlights. For the full details, read Chapter 27 of Oracle PL/SQL Programming, 4th edition, in which my co-author Bill Pribyl thoroughly explains external procedures.

As with Java, you will need help from your database administrator to supply the privileges needed to execute your host command in C.

Here are the steps to follow with C:

Find (or build) the C program that implements host command execution.
Save the C source to a file and generate a shared library for it.
Save the library file where Oracle can find it.
Define a library inside Oracle that is associated with the shared library on disk.
Create a PL/SQL wrapper for the C function.

Let's go through each of these steps.

1. Find (or build) the C program that implements host command execution.

The C system function executes an operating system command. So I build a simple C function, extprocsh(), that accepts a string and passes it to the system function for execution:

int extprocsh(char *cmd)
{
   return system(cmd);
}

The function returns the result code as provided by system, a function normally found in the C runtime library (libc) on Unix, or in msvcrt.dll on Microsoft platforms.

2. Save the C source to a file and generate a shared library for it.

I save the source code in a file named extprocsh.c. I then use the GNU C compiler to generate a shared library. On a 64-bit Solaris machine running GCC 3.4.2 and Oracle Database 10g Release 2, the following compiler command worked to create a shared library:

gcc -m64 extprocsh.c -fPIC -G -o extprocsh.so

Similarly, on Microsoft Windows XP Pro running GCC 3.2.3 from Minimal GNU for Windows (MinGW), also with Oracle Database 10g Release 2, this works:

c:\MinGW\bin\gcc extprocsh.c -shared -o extprocsh.dll

3. Save the library file where Oracle can find it.

These commands generate a shared library file, extprocsh.so or extprocsh.dll. Now I need to put the library file somewhere that Oracle can find it. The default locations for Windows and Unix respectively are:

$ORACLE_HOME/bin
$ORACLE_HOME/lib

If you want to use a non-default location, you will need to edit the listener configuration file and supply path value(s) for the ENVS="EXTPROC_DLLS...".

4. Define a library inside Oracle that is associated with the shared library on disk.

After copying the file and/or making adjustments to the listener, you will then define a "library" inside Oracle to point to the DLL. For example:

CREATE OR REPLACE LIBRARY extprocshell_lib 
   AS '/u01/app/oracle/local/lib/extprocsh.so';   -- Unix
     
CREATE OR REPLACE LIBRARY extprocshell_lib
   AS 'c:\oracle\local\lib\extprocsh.dll';      -- Microsoft

Note: performing this step requires Oracle's CREATE LIBRARY privilege.

5. Create a PL/SQL wrapper for the C function.

Now I can create a PL/SQL call specification which uses the newly created library:

CREATE OR REPLACE FUNCTION exec_host_command (cmd IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   LANGUAGE C
   LIBRARY extprocshell_lib
   NAME "extprocsh"
   PARAMETERS (cmd STRING, RETURN INT);

Then, assuming that your DBA has set up the system environment to support external procedures, the exec_host_command function can now be called anywhere you can invoke a PL/SQL function.

Note that these operating system commands will execute with the same privileges as the Oracle Net listener that spawns the extproc process.

Testing and refreshing data from production

Steven Feuerstein — Thu, 17 Apr 2008 16:21:00 GMT

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 even implement the Test Driven Development methodology.

In the process of talking about Code Tester with many developers, I have come across a belief regarding code testing and the refreshing of data from production tables that I think actually reflects a misunderstanding about both how to use Code Tester specifically and, more generally, how to test our code.

Here's the way this belief was expressed by a customer recently:

"We have a development environment - let's call it TEST. Inside this environment is the supporting schema / repository for Code Tester, as well as the test definitions and generated test code."

"Once a week, we refresh the TEST environment from the production environment, PROD. PROD doesn't contain a Code Tester repository, so after I refresh, I lose my test repository and code."

Clearly, this developer can do an export of the Code Tester schema and then import it after refresh, but he was concerned about having to add any overhead for the DBA to this refresh process.

Actually, I think the problem goes much deeper than that: if you refresh your test tables with production data on a regular basis, you will find it very difficult indeed to create stable regression tests that can be run against your code.

Why do I say that? Because when you refresh data from production, you change the "inputs" to your programs (contents of the tables) and therefore you will almost certainly have to change the expected results for your tests.

In fact, I think that when it comes to functional testing of your programs (does it meet user requirements?), you should not be refreshing your test tables from production. To understand why I would say this, let's talk about....

How we make sure our programs work

There are as many kinds of tests as there are definitions of what it means for a program to "work." We need to make sure, for example, that our programs meet functional requirements (they are correct) and also that they run quickly enough to avoid user frustration. The programs need to scale up for many users and lots of data, etc.

Quest Code Tester is designed specifically to help you implement tests on functional requirements; in essence, to verify that your program is correct.

To do this, I will almost always want to compare the actual results of running my program with the expected results or control data.

For example, if table XYZ contains a certain set of rows, then after running the program, table ABC should be changed in a specific way. Or the program is a function such that when I pass "ABC" for an IN argument, the function returns 100, and so on.

In general, there is no room for ambiguity here. Either the program works as expected or it does not.

Now, if I want to build regression tests and automate the process of testing my program, I need to able to tell Code Tester that for a given set of inputs, I expect the associated outcomes. And – this is the key thing to realize – those inputs can't keep changing on me. Every time the inputs change, I would need to change the expected outcomes.

Constantly updating one's test code might make sense if you are perform manual tests from hand-coded scripts. But if you want to build comprehensive, serious regression tests, then you need a stable, consistent environment from which to run those tests.

Every time you refresh data from production, you change the values in your table and thus you cannot reliably execute your regression test.

But don't we need real production data to really test?

Is this a big problem? Does this mean that we can't or shouldn't build static, repeatable regression tests for our code? After all (so the thinking goes), we need to test our code against production data to make sure that code handles "real world" situations.

Clearly, our code does need to work properly with production data.

This does not mean, however, that you need to constantly change the data for your functional tests as production data changes. It does mean that the data used in functional tests should represent the variety of data found in production.

In other words, the data in your test tables must be sufficiently varied to allow you to exercise the program to verify all requirements. It doesn't really matter so much that the data is precisely the same as that found in production.

And, again, if you keep changing the test data, you must also change your test definition.

OK, but don’t we need to test against production volume?

There's another problem with basing functionality testing around production data: the data volumes are generally too large, increasing the time it takes to complete the tests.

You certainly do need to make sure your code executes with production volumes of data. But that sort of stress testing should be done independently of your functionality testing. With functionality testing, you want your tests to run as quickly as possible, for these reasons:

You will have lots of (dozens, perhaps hundreds) of separate test cases to run; if each test takes five minutes due to data volume, the test cycle will take an enormous amount of time.
Ideally, you run your tests after each change you make to your program. That way, you can immediately determine if you have introduced any bugs. But if running those tests takes an hour, you will test less frequently and you will get less "return" on your investment of creating your tests.

Conclusion: Segregate your functional test environment

The most important thing to verify about your program is that it is correct: it meets user requirements.

The best way to do this is to build a regression test that you can run after any change to the program, to ensure that it has no bugs.

A regression test should not have to be changed as long as the program itself has not changed. It should work from a consistent set of "inputs" (values for IN arguments, contents of any tables queried by programs, etc.) that do not change. [Of course, you may need to make some changes along the way as program requirements change, as you add more test cases, etc. That is different, however, from daily or weekly refreshes.]

In addition, you want regression tests to run as efficiently as possible. These tests focus on program functionality, not performance. So you want the minimum volume of data in test tables that allow you to cover your requirements. Use different tests to verify adequate performance.

Consequently, when it comes to functionality testing, you should avoid refreshing your test tables from production. Instead, invest the time upfront to come up with setup scripts to populate tables with data that fully exercises your code. Include those setup scripts in your Code Tester test definitions and then you have an independent, consistent test environment.

Auto-formatting of templates for Toad

Steven Feuerstein — Thu, 10 Apr 2008 16:14:00 GMT

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 to say that Sean Gilbert has done this for Toad 8 and is working on a transformation for Toad 9.

For more information, check out https://getonthetrail.blogsite.org/Blog/post/2008/04/Transform-Custom-TOAD-XML-Template-with-XSLT.aspx.

Thanks, Sean!

Weird PL/SQL

Steven Feuerstein — Mon, 17 Mar 2008 19:57:00 GMT

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 16^th. 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:

Introduction

You probably think that PL/SQL is rather ordinary programming language. Well, it truly is a very powerful and straightforward language, but there are some features "less traveled" which can seem nothing less than weird. This presentation explores some of stranger nooks and crannies of the PL/SQL language, perhaps in the process making them a little bit less weird to the everyday programmer.

All the code I reference in this paper is available at my PL/SQL Obsession site: www.ToadWorld.com/SF. Just click on the "Trainings" link and then click on the "demo.zip" link.

Disclosure: I will poke fun at Oracle, the PL/SQL language, and implicitly the folks who are responsible for building and enhancing PL/SQL. Please know that it is all done in a spirit of deep appreciation for what PL/SQL (and its developers) has done for me and so many others around the world. But, hey, there's always room for improvement and you've got to keep a sense of humor about all this stuff!

Error Codes: Negative or Positive? Make up your mind, Oracle!

I really would have thought that Oracle would have sorted this out by now. Here's the issue:

When you see this string:

ORA-01855

do you interpret the "-" character as a hyphen or a negative sign?

I always considered it to be a negative sign, and that just about every single error code in the world of Oracle is negative. The only exceptions are 1 (user-defined exception) and 100 ("No data found" – which is another little weirdness in PL/SQL. The "No data found" exception has two error codes: 100 and -1403).

Yet sometimes Oracle treats the error codes as positive numbers, most notably when using the SAVE EXCEPTIONS clause of FORALL. When you include SAVE EXCEPTIONS, Oracle "saves up" any exceptions it encounters as it executes all the DML statements specified by the bind array. Then if at least one error occurred, it raises the ORA-24381 exception (Argh! Sometimes I worry about all the little bits of information that are stuck in my head!). It also populates the SQL%BULK_EXCEPTIONS pseudo-collection with all the exceptions that were raised.

Here's an example of a program that uses this feature:

/* bulkexc.sql */
DECLARE
   bulk_errors          EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

   TYPE namelist_t IS TABLE OF VARCHAR2 (1000);

   enames_with_errors   namelist_t
      := namelist_t ('ABC'
                   , 'DEF'
                   , NULL
                   , 'LITTLE'
                   , RPAD ('BIGBIGGERBIGGEST', 250, 'ABC')
                   , 'SMITHIE'
                    );
BEGIN
   FORALL indx IN enames_with_errors.FIRST .. enames_with_errors.LAST SAVE EXCEPTIONS
      UPDATE employees
         SET last_name = enames_with_errors (indx);
   ROLLBACK;
EXCEPTION
   WHEN bulk_errors
   THEN
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.put_line (   'Error '
                               || indx
                               || ' occurred on index '
                               || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
                               || ' with error code '
                               || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                              );
      END LOOP;

      ROLLBACK;
END;
/

And this is what I see when I run the program:

Error 1 occurred on index 3 with error code 1407
Error 2 occurred on index 5 with error code 12899

Hmmm. Oracle returns the error code as a positive number! So if I want to use SQLERRM to look up the error message for that code, I have to multiply it by -1:

FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
   DBMS_OUTPUT.put_line
                      (   'Error '
                       || indx
                       || ' occurred on index '
                       || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
                       || ' with error '
                       || SQLERRM
                                ( -1
                                 * SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                                )
                      );
END LOOP;

And then I see this output:

Error 1 occurred on index 3 with error ORA-01407: cannot update () to NULL
Error 2 occurred on index 5 with error ORA-12899: value too large for column

It would be nice if Larry would decide once and for all if error codes are positive or negative, and then lay down the law.

SQLERRM and DBMS_OUTPUT.PUT_LINE

Speaking of SQLERRM....let's talk about everybody's favorite program: DBMS_OUTPUT.PUT_LINE. For many, many years – all the way up to the release of Oracle Database 10g Release 2, if you tried to display a string of more than 255 characters with DBMS_OUTPUT.PUT_LINE, that built-in would raise an exception. This caused no end of teeth-gnashing and anguished moans from developers over the year. Fortunately, that restriction was lifted in Oracle Database 10g Release 2 – you can now display up to 32K characters.

But there is still a problem with SQLERRM. We all know about SQLERRM. You call it to return the error message for the current error (obtained by calling SQLCODE). Did you know, however, that Oracle recommends that you not use this function,. and instead call DBMS_UTILITY.FORMAT_ERROR_STACK?

Why would that be? The problem is that SQLERRM may truncate your error message. In earlier versions of Oracle, truncated occurred at 255 characters. Now, it is 512. DBMS_UTILITY.FORMAT_ERROR_STACK, on the other hand, returns strings of up to 2000 bytes.

I can just picture some developer years and years ago, confronted with a nasty problem:

He has found that SQLERRM can return error messages that get quite long, depending on application-specific information, like the names of identifiers. And when you try to display that string with DBMS_OUTPUT.PUT_LINE, an exception is raised.

What's a developer to do?

1. Fix DBMS_OUTPUT.PUT_LINE so it displays longer strings.

or

2. Truncate your error message.

Well, obviously, the solution is to truncate the error message!

Isn't that weird?

I am so glad the PL/SQL team finally got around to fixing DBMS_OUTPUT.PUT_LINE! And according to the PL/SQL development manager at the time, all they had to was change one number!

So...there's a little bit of weirdness in PL/SQL...more to come at Collaborate08 and this blog.

Just how popular is PL/SQL?

Steven Feuerstein — Mon, 04 Feb 2008 18:56:00 GMT

Just how popular is PL/SQL?

And how many PL/SQL developers are there "out there"?

These are surprisingly hard questions to answer. Officially and even unofficially, Oracle Corporation's point people on PL/SQL do not have any idea (or refuse to say) how many PL/SQL developers there are.

I take the approach of doing some rough extrapolations from numbers of books I and other PL/SQL developers have sold, and I conclude that there are perhaps a couple million PL/SQL developers, all told.

That is, however, very unscientific.

There are some websites, however, that offer an analysis of relative popularity of languages, usually based on data they have retrieved from various search sites.

TIOBE

TIOBE compiles a list of relative popularity of programming languages at http://www.tiobe.com/index.htm?tiobe_index.

Here is the list as of January 2008:

Ok, fine, so PL/SQL is not the most popular language. We all know that. What I was very pleased to see is that PL/SQL had increased in popularity over the last year. Based on my experience in India, I can see why; most of the increase probably came from there alone!

Here is some information from the TIOBE website about how they calculate their ratings:

The ratings are calculated by counting hits of the most popular search engines. The search query that is used is: +" programming"
The search query is executed for the regular Google, Google Blogs, MSN, Yahoo!, and YouTube web search for the last 12 months. The web site Alexa.com has been used to determine the most popular search engines.
The number of hits determine the ratings of a language. The counted hits are normalized for each search engine for the first 50 languages. In other words, the first 50 languages together have a score of 100%.

Let's define "hits50(SE)" as the sum of the number of hits for the first 50 languages for search engine SE and "hits(PL,SE)" as the number of hits for programming language PL for search engine SE, then the formal definition of the ratings becomes

((hits(PL,SE1)/hits50(SE1) + ... + hits(PL,SEn)/hits50(SEn))/n
 where n is the number of search engines used.

LangPop

The nice thing about the TIOBE site is that they rank PL/SQL explicitly. All the other language popularity pages I could find simply list "SQL," which I took to mean "SQL and all procedural language extensions to SQL."

The http://www.langpop.com/ site shows the following graph:

O'Reilly Media Popularity by Book Sales

O'Reilly Media, publisher of all my books on PL/SQL, has a research arm ("O'Reilly Radar") that projects programming language trends based on book sales. Again, there is just a single entry for SQL:

http://radar.oreilly.com/archives/2005/04/book_sales_as_a.html

Functions should return data only through the RETURN clause

Steven Feuerstein — Mon, 07 Jan 2008 21:46:00 GMT

You will find below an excerpt from my latest publication: the 2^nd edition of Oracle PL/SQL Best Practices. This edition is a complete rewrite of the 1^st edition. I decided that since software is still, for the most part, written by humans, I would create a cast of characters who write the software I reference and critique in this book.

They work for a company named My Flimsy Excuse, Inc (MFE) and their web site is myflimsyexcuse.com. The mission statement of My Flimsy Excuse is simple and powerful:

Provide a wide array of excuses (flimsy and otherwise) to people in need of a way to explain away questionable behavior.

The MFE founders love technology and don't want to have any flimsy excuses for failure, so they have chosen Oracle as their database technology. They have several different development teams working on various aspects of the web-deployed business plan. The team, which has graciously volunteered to share their experiences with my readers, is made up of the following individuals:

Sunita

The team leader, a very smart woman with a fast, razor-sharp mind. She is always busy, always constructive, and somewhat intimidating. She was a programmer in years past, mostly trained in Fortran and then some C+. She isn't sure if that makes her a better manager or a bigger danger to the team, but she still likes to get her hands "dirty," now and then, writing code.

Delaware

A classic anarchistic, anti-standards, big ego kind of programmer. He has a hard time learning from anyone else, and writes code that is hard to understand....but he is very productive and very bright. If you need a job done overnight (literally), Delaware is the guy to do it. He claims to read up on all the latest features of PL/SQL, but he generally programs in a rut – relying on techniques learned years past in Oracle7. He keeps his thinning hair neatly trimmed, with just a hint of a comb-over, and favors three-piece suits from the Men's Wearhouse.

Lizbeth

The anchor of the team. She used to write in Cobol and is continually shocked at the lack of strong process in the PL/SQL world. She can't understand why programmers today make fun of the Cobol programmers of the past. Didn't they write the software that made the first phase of the Information Revolution a broad success? Lizbeth is methodical and careful, but not, on the whole, the best problem solver—she too easily falls into the trap of seeing things only from her own perspective.

Jasper

The junior member of the team. He is new to PL/SQL and new to MFE. Jasper is eager to learn from anyone and everyone and has nice thick skin, but he is not very creative (not ready to take risks in his code). He always wears jeans, preferably of the distressed variety, complemented by polo shirts with the logos of animals on them. Lizbeth thinks of him as the son she never had, and Delaware treats him like the mascot of the team.

And here is my recommended best practice and explanation for this first blog entry of the new year:

Functions should return data only through the RETURN clause.

Problem: Jasper returns data in a very confusing manner.

Jasper needs a program to retrieve several pieces of information about an excuse: the title, the author and the word count. In just a few moments he builds the following function:

FUNCTION excuse_title (
   excuse_id_in IN mfe_excuse.isbn%TYPE
 , author_out OUT mfe_excuse.author%TYPE
 , word_count_out OUT mfe_excuse.word_count%TYPE)
RETURN mfe_excuse.title%TYPE
IS BEGIN 
   ... implementation unimportant! ... 
END excuse_title;

And then he puts this function to use as follows:

PROCEDURE process_excuse (excuse_id_in IN mfe_excuse.isbn%TYPE)
IS
 l_title mfe_excuse.title%TYPE;
 l_author mfe_excuse.author%TYPE;
 l_word_count mfe_excuse.word_count%TYPE;
BEGIN
   l_title := excuse_title (l_id, l_author, l_word_count);
   ...

Jasper is proud that he took the time to put this retrieval logic into its own program. Now it can be used in many places in the application. He shows everyone excuse_title at the weekly code review session. Imagine his dismay when Delaware snorts:

Humph. Well, Jasper, it's absolutely peachy keen that you wrote a reusable function. But that is not a program I would ever want to use. The name says you are giving me a title, and you are, but then you are also passing back all that other stuff. It's self-contradictory and I have to declare a bunch of individual variables to use the darned thing!

Jasper pouts; he was so looking forward to a pat or two on the back. Sunita gives Delaware a dirty look and shakes her head. "Jasper," she says, drawing his attention away from Delaware's gloomy outlook, "hiding the lookup was an excellent move. You just need to take a step or two further, and make sure there are no mixed messages. Let's redesign excuse_title together."

Solution: Return multiple values through a single, composite structure or with a procedure.

Here are the steps that Sunita takes to revamp the lookup function and make it more useful:

Make sure the name of the program reflects what it does: In this case, the program doesn't return just the title; it returns several pieces of information about an excuse. Let's call it the "excuse_info" function.
Pass everything in the RETURN clause: Rather than returning one value through the RETURN clause and another through the parameter list, let's pass everything back in the RETURN clause, using a composite structure—in this case, a record.

Here, then, is the new header of the program:

FUNCTION excuse_info (id_in IN mfe_excuse.id%TYPE)
   RETURN mfe_excuse%ROWTYPE
And here is the revised usage of this function:
PROCEDURE process_excuse (excuse_id_in IN mfe_excuse.isbn%TYPE)
IS
   l_excuse mfe_excuse%ROWTYPE;
BEGIN
   l_excuse := excuse_info (l_id);

Now the code is leaner and cleaner. Everything that is returned by the function is deposited into a single record. And if you are concerned about returning all the data in a table's row, when you need only a small subset of its columns, you can always create your own user-defined record, as shown here:

PACKAGE excuse_pkg
IS
   TYPE key_info IS RECORD (
      title mfe_excuse.title%TYPE
    , author mfe_excuse.author%TYPE
    , word_count mfe_excuse.word_count%TYPE
   );
 
   FUNCTION excuse_info (id_in IN mfe_excuse.id%TYPE)
      RETURN key_info;
END excuse_pkg;

To sum up: the whole point of a function is to return a value (whether it’s a single, scalar value or a composite, such as a record or a collection). If you also return data back through the parameter list with OUT or IN OUT arguments, the purpose and usage of the function will be obscured.

If you need to return multiple pieces of information, take one of the following approaches:

Return a record or collection of values

Make sure to publish the structure of your record or collection (the TYPE statement) in a package specification so that developers can understand and use the function more easily.

Break up a single function into multiple functions, all returning scalar values

With this approach, you can call the functions from within SQL statements.

Change a function into a procedure

Unless you need to call a function to return this information, just change it to a procedure returning multiple pieces of information through the OUT arguments in the parameter list

If you follow these guidelines, your subprograms will be more likely to be used and reused, because they will be defined in ways that make them easy to understand and apply in your own code.

Your function may also then be callable from within a SQL statement, which encourages even wider use of this program. Note, though, that there are restrictions on function calls from SQL. You may not call a function with an OUT argument from within SQL, You also may not call a function that returns a record (the datatypes of all parameters must be SQL-compatible).

Single quotes and CLOBs in 10.2.0.3

Steven Feuerstein — Thu, 20 Dec 2007 00:41:00 GMT

I usually write about best practice principles and general issues.

Today I thought I would share a bit of my pain with you on a very specific topic: single quotes and CLOBs, and specifically on Oracle 10.2.0.3.

I figure that I lost an hour or two of my life to this one, and would like to pass on the warning to you all.

In Quest Code Tester, we store in put and expected values that you provide in your test definition as CLOBs. We maintain "internal" and "external" values – the external value is a string that is ready to be concatenated directly into the generated test code.

Thus, if the input value is one single quote, we save it in the external_value column as

''''

That is, four single quotes – which evaluate to a single quote. Got it so far, right?

When we generate test code, those four single quotes need to be treated as NULL, so we wrote this function to tell us if a CLOB is really to be considered as NULL:

And it worked just fine in all sorts of situations, but then we got a support issue logged by a user. He was getting the wrong test results: Quest Code Tester was showing that he had provided a NULL value when he had instead entered the single quote character!

What the heck was going on?

Well, it took quite a while to narrow things down. The error did not occur on Oracle9i, Oracle11g, on Oracle 10g 10.2.0.1. It only seems to happen on 10.2.0.3.

And after a while of testing and experimenting, I discovered that on this version of Oracle, you get some very strange behavior when performing a test like:

value_in = ''''''

It's easy to see; you can try it yourself. Copy and paste the following block of code into Toad and run it:

You should see this output:

FOUR QUOTES
FOUR QUOTES

And that is wrong, so wrong. It looks like it doesn't matter how many single quote characters you put inside two single quotes; that equality check will always return TRUE.

Ugh.

So I built the following workaround:

And that seems to work reasonably well – though I would much rather NOT have to do this.

Now you know and hopefully you will never run into this issue – but if you do, you have been warned!

Error Management functions and features for PL/SQL Developers

Steven Feuerstein — Wed, 05 Dec 2007 03:41:00 GMT

Winter is descending on Chicago; we had our first snow (flurries, really) on Thanksgiving Day, and the sun is weak. For many, this is a depressing time – and I mean that the lack of that and cold actually does make people feel depressed. Perhaps that is why I found myself thinking negatively – that is, about exceptions in PL/SQL.

So I thought I would share with you some of the features and functions available in PL/SQL, especially those introduced in recent versions of Oracle, which will help you trap and log error information.

For each of the topics below, I describe them briefly and then point to files in my demo.zip archive that illustrate the technique, and also the hyperlink to Oracle documentation on the topic.

You can download my demo.zip archive by clicking here.

Enjoy! And remember: never be sad about Oracle exceptions. Just make sure you have a powerful, general utility for raising, handling and logging those errors. If you don't already have such a thing, then please do download and try the freeware Quest Error Manager. I wrote it myself – just for you!

Save DML errors to a log table instead of raising an exception (Oracle10g)
As soon as the SQL engine raises an exception from processing a DML statement, your executable section terminates. You can, of course, trap the exception, log it, and then keep going, but once an exception is raised, performance degrades terribly.

Use the DBMS_ERRLOG package to define an error log table for your own table. Then when you write your DML statement, include the LOG ERRORS clause. With this approach, Oracle will save DML errors to a log table instead of raising an exception; your program will finish more quickly and then afterwards, you can query the information from the log table and either log the errors in your own system, try to recover or display error information.

My demo.zip files of relevance
dbms_errlog*.*
forall_with_dbms_errlog.sql

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_errlog.htm#sthref2860

Save exceptions till the end of your FORALL execution
FORALL is an absolutely, deliriously wonderful enhancement to DML execution introduced in Oracle8i. With FORALL, you can execute multiple DML statements in "bulk," which means they run in a fraction of the time it would take if they ran them on a row by row basis.

As with LOG ERRORS above, you can tell FORALL to continue past any exceptions encountered as it runs each DML operation by adding the SAVE EXCEPTIONS clause. Then if one or more exceptions were encountered, when FORALL is done, Oracle will raise the ORA-24381 error and also populates a "pseudo-collection" called SQL%BULK_EXCEPTIONS with the error code and the index in the collection that raised the error.

My demo.zip files of relevance
cfl_to_bulk*.sql
bulkexc.sql

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2201

Find the line number on which the error was raised (Oracle10g)
Call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to retrieve a string that shows the stack of calls that trace back to the line number and program name from which the most recent error was raised.

My demo.zip files of relevance
backtrace*.sql

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9387

Get the error message
What? You thought you should call the SQLERRM function? No way! Oracle recommends that you not use this function, because it might truncate your error message. Instead, call the DBMS_UTILITY.FORMAT_ERROR_STACK function and it will return the full error message (and sometimes even a stack!).

My demo.zip files of relevance
Sorry, none! It's such an easy, little thing to use...

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9392