Steven Feuerstein's Blog

Should you use an associative array or a nested table?

Steven Feuerstein — Tue, 17 Aug 2010 14:33:00 GMT

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:

Why should you care?
We don't give out information like this.

And I agree with him: why should we care? We just need to know that we can use the data structures with confidence (no or minimal bugs, good performance). Why get caught up with "internal" details that can change from version to version anyway?

Now, on to the question of which is better.

If my memory serves me, back in the early days of the introduction of nested tables and varrays (Oracle8), the performance and memory management of associative arrays (the "original" collection type, introduced in Oracle7) was better than that for nested tables.

These days (Oracle10g Release 2 and higher), I believe that you will see comparable levels of performance and memory usage, regardless of the type of collection you use.

I don't have lots of time to build scripts that exercise lots of test cases, but I did put together a script to compare the amount of memory and time required to load over 1.2M rows into both an associative array and nested table.

The code is shown below, and relies on several packages you will find in my demo.zip file. The results of one row is as follows:

SQL> @c:\work\demo-seminar\aa_vs_nt
Connected.
Associative memory
1225043
- Elapsed CPU : 3.02 seconds.
Change in UGA memory: 65512 (Current = 373096)
Change in PGA memory: 485490688 (Current = 486150372)
 
PL/SQL procedure successfully completed.
 
Connected.
Nested table
1225043
- Elapsed CPU : 2.82 seconds.
Change in UGA memory: 65512 (Current = 373096)
Change in PGA memory: 485490688 (Current = 486150372)

The bottom line? You will not see any discernable difference between the two.

So when you are trying to decide which of the collection types you should use, base the decision on functionality, not performance.

With associative arrays, you get:

Ability to index by strings
Use of negative index values and a larger range of index values than with a nested table
Sparse collections (nested tables can be sparse, but they are more naturally used with densely-filled lists of information).

With nested tables, you get:

Use of MULTISET operators to perform set-level operations, just like in SQL
Ability to query from nested tables using the TABLE operator

The script (in demo.zip - aa_vs_nt.sql)

connecthr/hr
 
setserveroutputon
 
DECLARE
   TYPEemployees_aatISTABLEOFemployees%ROWTYPE
                            INDEXBYPLS_INTEGER;
 
   l_aat   employees_aat;
 
   TYPEemployees_ntISTABLEOFemployees%ROWTYPE;
 
   l_nt    employees_nt;
 
   PROCEDUREstart_test(tVARCHAR2)
   IS
   BEGIN
      DBMS_OUTPUT.put_line(t);
      DBMS_SESSION.free_unused_user_memory;
      plsql_memory.start_analysis;
      sf_timer.start_timer;
   END;
BEGIN
   start_test('Associative memory');
 
   SELECTe1.*
     BULKCOLLECTINTOl_aat
     FROMemployees,employees,employeese1;
 
   DBMS_OUTPUT.put_line(l_aat.COUNT);
   sf_timer.show_elapsed_time;
   plsql_memory.show_memory_usage;
   l_aat.delete;
END;
/
 
connecthr/hr
 
setserveroutputon
 
DECLARE
   TYPEemployees_aatISTABLEOFemployees%ROWTYPE
                            INDEXBYPLS_INTEGER;
 
   l_aat   employees_aat;
 
   TYPEemployees_ntISTABLEOFemployees%ROWTYPE;
 
   l_nt    employees_nt;
 
   PROCEDUREstart_test(tVARCHAR2)
   IS
   BEGIN
      DBMS_OUTPUT.put_line(t);
      DBMS_SESSION.free_unused_user_memory;
      plsql_memory.start_analysis;
      sf_timer.start_timer;
   END;
BEGIN
   start_test('Nested table');
 
   SELECTe1.*
     BULKCOLLECTINTOl_nt
     FROMemployees,employees,employeese1;
 
   DBMS_OUTPUT.put_line(l_nt.COUNT);
   sf_timer.show_elapsed_time;
   plsql_memory.show_memory_usage;
END;
/

More ...

Tags: oracle,plsql,nested table

Category: PL/SQL Obsession

PL/SQL Developers win over US$2000 in prizes at PL/SQL Challenge playoff

Steven Feuerstein — Tue, 20 Jul 2010 13:51:00 GMT

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.

Category: PL/SQL Obsession

RAISE vs RAISE_APPLICATION_ERROR?

Steven Feuerstein — Wed, 14 Jul 2010 20:02:00 GMT

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

Tags: plsql,oracle

Category: PL/SQL Obsession

New Utilities in demo.zip

Steven Feuerstein — Mon, 28 Jun 2010 13:18:00 GMT

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 of a file into a clob
collection_to_file.sql - writes the contents of a collection declared on the DBMS_SQL.varchar2a type to the specified file.
display_clob_by_cr.sql - writes the contents of a clob to system output, using a line feed (CHR(10)) as the delimiter. [Note: original publication of blog entry said "carriage return"; I changed it to "line feed"]
display_header.sql - displays the specified string as a "header", which means that it looks like this:

===========================================================
Written by Steven Feuerstein
===========================================================
You can specify the length of the header, the border character, and more.

I hope you find these utilities useful. As always, it is your responsibility to ensure that these programs meet your requirements and have no bugs.

If you do find a bug or a way to improve any of these programs, please don't hesitate to let me know.

More ...

Tags: plsql,oracle

Category: PL/SQL Obsession

Handy new warning in Oracle 11.2

Steven Feuerstein — Fri, 11 Jun 2010 20:18:00 GMT

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.

You can see a list of all compile time warnings available here.

When this feature was first made available, I was both excited and disappointed.

Excited, because I could now easily determine if, for example, my program unit had any "dead code" - code that would never execute, no matter what (that's warning PLW-06002).

Disappointed, because Oracle did not implement warnings for some of the most common and irritating coding mistakes, such as:

SQL> CREATE OR REPLACE PROCEDURE my_prod

  2        AUTHID CURRENT_USER

  3   IS

  4        l_string VARCHAR2(1) := 'abc';

  5   BEGIN

  6     l_string := 'abc';

  7   END;

  8   /

In this program, I try to assign a value that is too big for the variable, which will cause Oracle to raise a runtime VALUE_ERROR exception. But I want to hear about this problem at compile-time, not run-time.

Well, in 11.2, code like that found in lines 4 and 6 are now flagged as problematic, as you can see when I show the errors after I attempt to compile this procedure:

SQL> sho err

Errors for PROCEDURE MY_PROC

LINE/COL ERROR

-------- -----------------------------------------------------------------

4/4      PLW-06017: an operation will raise an exception

6/4      PLW-06017: an operation will raise an exception

So that is very, very nice….but it would have been so much nicer if the warning message could have been a bit more specific. Here's the explanation for this warning:

"The compiler has determined that some operation near this location is certain to raise an exception during execution. This may be a programming error; consider rewriting the code. If the intention is to raise an exception, use an explicit RAISE statement."

Well, that covers a lot of territory.

Another problem with this warning is that it only works for VARCHAR2; you will not see this warning if you write a line of code like:

l_string CHAR(1) := 'abc';

Still, it is a nice enhancement and I am very glad that Oracle did this for us. Thanks, PL/SQL development team!

More ...

Tags: plsql,oracle,11g

Category: PL/SQL Obsession

Why can't I execute DDL natively in PL/SQL

Steven Feuerstein — Tue, 01 Jun 2010 19:29:00 GMT

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 EXECUTE IMMEDIATE] objects will still be invalidated and require additional recompilation - the same drawback. So why not allow it in as static SQL?"

There are generally two classes of questions that I prefer to not answer:

Why did Oracle do X that way?
Why doesn't Oracle let us do X?

I don't like speaking for Oracle, nor do I like speculating about why Oracle decides to do or not do something (or do it in a particular way).

So I suggested to Syed that he contact the Oracle PL/SQL Product Manager, Bryn Llewellyn. Hey, it's Bryn's job to represent Oracle to the PL/SQL developer community (among other responsibilities, though none more important. ).

Syed did just that, and Bryn gave me approval to publish his reply. Here it is:

From the Keyboard of Bryn Llewellyn:

The answer is obvious, and not what you think.

First off, it helps to remember that at run time, all SQL issued by a PL/SQL program is "dynamic" in that the sense that the text of the SQL statement is handed over, at run time, from the PL/SQL subsystem to the SQL subsystem for execution and the SQL subsystem processes it in the same way. This is the case even for PL/SQL's embedded SQL (I don't like to call it "static" because of the point I just made). For embedded SQL, whatever parsing of the SQL statement that the PL/SQL subsystem did at PL/SQL compile time (in order to work out how to do the binds and defines, to establish dependencies, and so on -- see below) is re-done at run time.

PL/SQL's embedded SQL has these big advantages:

(1) The text is checked at compile time for correct syntax and (unhelpfully for invoker's rights programs) for correct semantics. Following on from this, dependencies are set up to the referenced objects (but for IR units they might not be helpful).

(2) When the statement includes local PL/SQL variables (which select, insert, update, delete, and merge statements, a.k.a. DML statements, typically do), these are converted to placeholders in the SQL that will be used at run time and appropriate binding code is included in your executable PL/SQL program. Appropriate code is also generated to handle values returned by select statements, and by change-statements that use returning. Embedded SQL makes all this highly usable.

(3) The statement execution code that's generated implements a special soft-parse avoidance scheme. You can think of the benefit as rather like that by setting Session_Cached_Cursors to a non-zero value for an ordinary OCI program -- only quite a lot better.

Non-DML statements cannot benefit from these advantages. Notice that ready-to-go statements that are not select, insert, update, delete, or merge are never cached in the shared pool. Further, especially because the names of the target objects are typically not known until run time, and because only DML statements allow binding, these non-DML statements are almost always composed programmatically -- ideally with the help of the DBMS_Assert functions. In other words, you never see an into or using clause when execute immediate is used for anything except DML.

This means that there would be no real benefit if a scheme were invented to allow non-DML statements to be written in PL/SQL using a new kind of embedded SQL.

More ...

Tags: plsql,oracle,ddl

Category: PL/SQL Obsession

How big can my program get?

Steven Feuerstein — Mon, 10 May 2010 13:09:00 GMT

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.

DIANA code is in the form of a tree structure, and nodes in the tree correspond to tokens in your programs, which include identifiers, keywords and operators.

So here's the actual limitation regarding "size" (quoting now from the Oracle documentation): "a package spec, object type spec, standalone subprogram, or anonymous block is limited to 67108864 (2**26) DIANA nodes....[which] allows for ~6,000,000 lines of code."

In other words, you really should not have to worry about "running out of space" when building your PL/SQL program units.

You will run up against maintainability issues way before you run into PL/SQL limitations. If your program unit is getting big enough that you are even wondering if it is too big, you should break it up into smaller units, with different names and distinct purposes.

More ...

Tags: plsql,oracle

Category: PL/SQL Obsession

Little things can get you

Steven Feuerstein — Wed, 05 May 2010 13:17:00 GMT

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 (TRUNC (SYSDATE));
      DBMS_OUTPUT.put_line (TO_DATE (global_end_date, 'DD-MON-YYYY'));
      DBMS_OUTPUT.put_line ('> global_end_date');
   ELSIF TRUNC (SYSDATE) < TO_DATE (global_beg_date, 'DD-MON-YYYY')
   THEN
      DBMS_OUTPUT.put_line ('sysdate < beg_date');
   ELSE
      DBMS_OUTPUT.put_line ('Neither');
   END IF;
END;

My problem is that when I run this code (on 4 May 2010), it shows me "> global_end_date".

How can that be, when it is "obvious" that:

TRUNC (SYSDATE) > TO_DATE (global_end_date, 'DD-MON-YYYY')

Being the nice guy that I am, I grabbed the code from the email, pasted it into Toad and ran it myself. She was right! What the heck is going on here?

Perhaps some of you have seen the issue already; it actually took me a few minutes of playing with the code.

If you have not yet seen it, DO NOT READ ANY FURTHER IN THIS BLOG.

Instead, take a few moments to carefully read the code and make sure that it makes sense to you.

Did you read the code? Do you now see the problem?

The developer is calling TO_DATE on a date. So what is actually happening is that the date value in global_end_date is being converted implicitly to a string, and then converted explicitly back to a date with TO_DATE.

In fact, there is no reason to call TO_DATE (or TO_CHAR). Just perform a direct comparison:

TRUNC (SYSDATE) > global_end_date

If you hadn't yet caught the problem, you are probably now slapping yourself on the forehead and exclaiming "Duh! How could I miss something so obvious?"

How can we miss, over and over again, such obvious problems in our code?

Human psychology, is the answer.

There are, I believe, several factors that make it hard to see issues in our programs, including:

We are inclined to believe that what we have written is correct or likely to be correct. So when we look at our code, we are not critical enough of what we are seeing.
We think we know what we are reading (after all, we wrote it), so we skim rather than read closely. This is true not just for reading code, but reading text as well.
We are too far "inside" our programs and our bugs to take a clear, unbiased look at our code.

How can we avoid overlooking problems in our code?

Ask someone else to look at the code. And ask for help sooner rather than later. I know, I know, you hate to ask for help. But it's OK to admit that you are having trouble; the person you ask for help will feel very good about being able to help you. And they will have fresh eyes, making it easier to identify the source of a problem.
Develop a habit of reading, truly reading, your code. All too often, we type a bunch of statements, get it to compile and then "try it." "Let's just see what happens," is a common programmer refrain. Trying something is very different from either actually testing it or using symbolic logic to step through your code (with your eyes, not the runtime PL/SQL engine) and verify the algorithm.

My sense is that relatively few developers spend the requisite time to simply sit and read their code. They instead rely on tracing, debugging, trying. A de-emphasis on reading/verifying logic results in a poorly-trained eye and the likelihood that you will "skip over" bugs and other kinds of weaknesses in your programs.

So next time you are truly puzzled by what your program is doing, the next time you say to yourself "That's impossible!", recognize that it is not only possible, but happening, and go back to the code and read it line by line with care and objectivity.

More ...

Tags: plsql

Category: PL/SQL Obsession

New standards document available

Steven Feuerstein — Mon, 26 Apr 2010 19:49:00 GMT

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 applications. You don't have to use his framework, however, to benefit from his standards. Many thanks to Bill for sharing his experience and efforts with us!

More ...

Tags: plsql,standards,oracle

Category: PL/SQL Obsession

Collaborate 10 - A report from the casinos

Steven Feuerstein — Thu, 22 Apr 2010 14:44:00 GMT

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, are beautiful and I very much enjoy. Forests of slot machines surrounded by posters hawking various forms of excess? They just don't do much for me.

Fortunately, I was there to "work" (as if you can really call what I and most programmers do work). So I could bury my curmudgeonly inclinations under a burning obsession with the PL/SQL language.

I did a couple of presentations, including my first ever PL/SQL quiz. As you may know, I recently started the PL/SQL Challenge, a daily PL/SQL quiz that culminates in a quarterly championship, with a grand prize of $1000 USD (with other prizes along the way). Needless to say, if you haven't already started playing the Challenge, get over there fast, register, and start taking the quiz!

Reading documentation and books are fine, but I think that taking a quiz, getting the competitive juices flowing, is a great way to focus our brains and learn more effectively. So I have decided to start offering PL/SQL quizzes as technical sessions at conferences.

On Monday afternoon, over 150 developers filled the Palm C room to take the quiz. In addition, my session was streamed out over the Internet, so hopefully hundreds more also played. I presented ten multiple choice questions (which I will not repeat here! You may, after all, attend such a quiz in the future.) We then went through the answers.

I was very impressed with how many people knew about AUTHID CURRENT_USER (invoker rights). And I found that the number of people aware of and using collections (with all their related benefits, like FORALL and BULK COLLECT) seems to be growing. Excllent!

Many people came up afterwards to tell me they enjoyed taking the quiz. I heard later from another attendee that her co-worker, who had attended one of my trainings last year, was very excited. He says he got the quiz 100% right. That is impressive.

I also learned about ways to improve the quiz:

It would be nice to provide answer forms, so you can easily mark your choices and compare them to my answers later.
You need to have hard-copies of any questions with more than a few lines of code. It is very hard to stare at a screen and absorb the details of a block of code.
I need to have example scripts pre-written to verify all answers, clearly and quickly. Can't afford to waste time fumbling around typing up blocks of code on the fly!

I will be able to apply all these lessons almost immediately. On 29 June 2010, at ODTUG's Kaleidoscope conference, ODTUG and the PL/SQL Challenge will co-sponsor a special evening event, "PL/SQL Developer, Quiz Thyself," that will feature another PL/SQL quiz, this time with a first place cash prize of $500, and many other fantastic prizes. This will be a monitored quiz - we will grade your answers, rather than rely on the honor system (hey, there's money involved!). It will, therefore, be a great opportunity to demonstrate your deep knowledge of PL/SQL.

But to win, you must attend Kaleidoscope and come to the quiz event. I hope to see you there, and best of luck in both the PL/SQL Challenge and the Kaleidoscope quiz.

My second presentation on error management also went well. Another full room, lots of good questions, not enough time to get through all the material.

Here are the main points from that presentation:

When an exception is raised in the declaration section, it always propagates unhandled to the outer block.
Call DBMS_UTILITY.FORMAT_CALL_STACK whenever you log or trace. It answers the question "How did I get here?"
Call DBMS_UTILITY.FORMAT_ERROR_STACK instead of SQLERRM, to avoid possible truncation of error message text.
Call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE whenever you log an error. It answers the question "On what line of code was my error raised?" (RDBMS version 10.2 and higher)
All developers should use a shared utility to raise, handle, log and communicate errors. If you don't already have one in place, check out the Quest Error Manager.

In between my presentations, I took advantage of the Luxor Hotel's very nice fitness center. I also spent a most enjoyable evening at a party sponsored by Oracle Magazine. They reserved the whole Red Square restaurant to celebrate a thorough re-design of the magazine. They even had new photos taken of all their columnists. Oh, and Oracle has penned an agreement with Marvel Comics to use the Iron Man character (and upcoming sequel movie) to promote Oracle technology. So...look for references to Oracle in Iron Man 2.

From what I could tell, the Collaborate conference bounced back very nicely from a big drop in attendance experienced in 2009 (when everyone was worried that the fall of capitalism would throw us into a deep depression). There seemed to be a whole lot of people hurrying from session to casino to restaurant to session to pool to....doing whatever it is people do in Vegas that "stays in Vegas."

More ...

Tags: plsql

Category: PL/SQL Obsession

Making sure invoker rights is defined properly

Steven Feuerstein — Tue, 13 Apr 2010 12:54:00 GMT

"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 you overcome that challenge.

When you create a PL/SQL program unit, you can include an optional AUTHID clause. There are two forms of this clause:

AUTHID DEFINER
AUTHID CURRENT_USER

AUTHID DEFINER is the default. So these two procedures specify "definer rights"

PROCEDURE show_emp_count AUTHID DEFINER
PROCEDURE show_emp_count

And this version specifies "invoker rights":

PROCEDURE show_emp_count AUTHID CURRENT_USER

So that's the syntax. What does it do for you?

In many application environments, the code is owned by one schema (let's call it CODE_SCHEMA), and then privileges are granted to execute that code to other schemas (USER_SCHEMA).

When a program unit is created in CODE_SCHEMA with definer rights, then when a user connected to USER_SCHEMA runs that program, it runs under the privileges of CODE_SCHEMA.

When a program unit is created in CODE_SCHEMA with invoker rights, then when a user connected to USER_SCHEMA runs that program, it runs under the privileges of USER_SCHEMA. And all such privileges must be directly granted (no role-based privileges).

In other words, at the time an invoker rights program is executed, Oracle resolves all references to SQL-related database objects (tables and views, for the most part) according to the current user's privileges, and not the privileges of the owner of the program. And role-based privileges are used by Oracle with invoker rights programs.

I will step through a simple demonstration below. You are, however, probably wondering what invoker rights is good for. Invoker rights comes in very handy whenever you have a program that needs to work differently (with different tables) depending on who is running the code.

Consider Quest Code Tester for Oracle. This automated testing tool for PL/SQL is built around a test repository: 30+ tables and associated code. Usually, this repository is installed in a central schema, and then access to the repository is allowed through synonyms.

As a use of Quest Code Tester, I connect to my own development schema, and then call Code Tester programs to define and run my tests.

If Quest Code Tester relied on definer rights, then the test repository schema would need to have the authority to execute code in all the development schemas, and probably have directly granted privileges on underlying tables as well. That is a very risky test repository and most DBAs will not create such a powerful schema.

Instead, many of the packages in Quest Code Tester are defined as invoker rights. So when the developer asks Code Tester to run a test of the program in her schema, no special privileges are needed in the test repository schema. And Code Tester is not able to do anything to application data that the developer's code does not specifically allow.
In essence, with invoker rights, Oracle "reflects" back into the invoking schema to resolve references as you see below:

Now let's walk through a simple demonstration. I will first create a table in SCOTT with two rows:

CONNECT scott/tiger

CREATE TABLE authid_demo (n NUMBER)
/

BEGIN
   INSERT INTO authid_demo
   VALUES (1);

   INSERT INTO authid_demo
   VALUES (2);

   COMMIT;
END;
/

I will create the "same" table in HR, but with no rows:

CONNECT hr/hr

CREATE TABLE authid_demo (n NUMBER)
/

Then in SCOTT, I create three procedures, each of which do mostly the same thing: show the number of rows in the authid_demo table.

CREATE OR REPLACE PROCEDURE proc1 AUTHID CURRENT_USER
IS
   num   PLS_INTEGER;
BEGIN
   SELECT COUNT (*) INTO num FROM authid_demo;
   DBMS_OUTPUT.put_line ('proc 1 invoker authid_demo count = ' || num);
END;
/

CREATE OR REPLACE PROCEDURE proc2 AUTHID DEFINER
IS
   num   PLS_INTEGER;
BEGIN
   SELECT COUNT (*) INTO num FROM authid_demo;
   DBMS_OUTPUT.put_line ('proc 2 definer authid_demo count = ' || num);
   proc1;
END;
/

CREATE OR REPLACE PROCEDURE proc3 AUTHID CURRENT_USER
IS
   num   PLS_INTEGER;
BEGIN
   SELECT COUNT (*) INTO num FROM authid_demo;
   DBMS_OUTPUT.put_line ('proc 3 invoker authid_demo count = ' || num);
   proc1;
   proc2;
END;
/

Note that we have these two sequence of calls:

PROC3 -> PROC1
PROC3 -> PROC2 -> PROC1

and that PROC3 and PROC1 are invoker rights, while PROC2 is definer rights.

I then allow HR to run all this code and access the underlying table in SCOTT:

GRANT EXECUTE ON proc1 TO hr
/

GRANT EXECUTE ON proc2 TO hr
/

GRANT EXECUTE ON proc3 TO hr
/

GRANT SELECT ON scott.authid_demo TO hr
/

Finally, I run PROC3 - from HR:

BEGIN
   scott.proc3;
END;
/

Here's the output I see:

Line 1: proc 3 invoker authid_demo count = 0
Line 2: proc 1 invoker authid_demo count = 0
Line 3: proc 2 definer authid_demo count = 2
Line 4: proc 1 invoker authid_demo count = 2

Lines 1 and 2 demonstrate invoker rights at work: even though HR called SCOTT's PROC3 and PROC1 programs, the count of rows in authid_demo is retuned as 0, not 2.

Line 3 demonstrates definer rights: it shows the number of rows in SCOTT's authid_demo table, even though the program was called by HR.

But line 4 - now that is puzzling, isn't it? Notice that I got an answer of 2, not 0, for the count in the authid_demo table. Yet I called the same program (PROC1) that two lines earlier showed 2.

So: same code executes, but returns different answer. Very troubling, wouldn't you say?

Here's the problem: if at any point along the execution call stack (in this case, PROC3 -> PROC2 -> PROC1), a definer rights program is executed, then from that point on down the stack the "current user" is resolved by Oracle to be the owner of that definer rights program and not the currently connected user.

When PROC3 called PROC1, that was invoker rights calling invoker rights, and so the current user was HR and the procedure showed 0 rows.

When PROC3 called PROC2, Oracle reset "current user" to SCOTT. Then when PROC2 called PROC1, Oracle resolved the reference to the authid_demo table using SCOTT, and theprocedure therefore showed 2 rows.

This change in expected behavior can cause many problems in your application, ranging from raising "table or view does not exist" and "insufficient privileges" errors to returning or changing the wrong rows of data.

Assuming this is not the behavior you want, you need to make sure that all the program units executed in your call stack are all definer rights or all invoker rights, or at least that once you call a definer rights program, you only call other definer rights programs from that point on.

Fine advice (at least I think so), but how do you apply it? Call stacks in real applications can be very deep, easily a dozen or more entries. Plus, how can you even get hold of the call stack to analyze it?

First, you can call the DBMS_UTILITY.FORMAT_CALL_STACK at any point in your code, and Oracle will return a string that contains the call stack. Here's an example of the formatted call stack Oracle returns, taken from the Quest Code Tester log:

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
3B6C8C64         1  anonymous block
3FA473E8      1012  package body QCTO183.QU_RUNTIME
3FA473E8      1042  package body QCTO183.QU_RUNTIME
3FA32EF8      3120  package body QCTO183.QU_ALL_OBJECTS
3FA32EF8      3138  package body QCTO183.QU_ALL_OBJECTS
3FA12F2C      2613  package body QCTO183.QU_GENERATE
3FA12F2C      3269  package body QCTO183.QU_GENERATE
3FA12F2C      3341  package body QCTO183.QU_GENERATE
3FA12F2C      3601  package body QCTO183.QU_GENERATE
3FA12F2C      6192  package body QCTO183.QU_GENERATE
3FA12F2C      8835  package body QCTO183.QU_GENERATE
3FA12F2C      9138  package body QCTO183.QU_GENERATE
3FA12F2C      9196  package body QCTO183.QU_GENERATE
3FA12F2C      9487  package body QCTO183.QU_GENERATE
4DA12F23      9737  package body QCTO183.QU_HARNESS
3BC12F2C      9800  package body QCTO183.QU_TEST
3BC12F2C     10130  package body QCTO183.QU_TEST
3B6F83F8         4  anonymous block

So to follow my own advice, I would now have to open each of these package specifications and check the AUTHID setting. That's rather tedious.

Perhaps there is another way. Consider the ALL_PROCEDURES data dictionary view:

Notice the AUTHID column. You can look up the AUTHID setting for your program unit from this view. So it seems as though you could write a program to parse the call stack and then look up the AUTHID setting for each program unit in the stack, and make sure there are no problems with a switch from invoker rights to definer rights, and back, in that stack.

Yes, you could do that. But likely you want, because you have a real job and a real life and very little time to write such programs.

Fortunately, I don't have a "real job" (well, believe you me, they keep me very busy here at Quest, but they also more or less let me do what I want. J ) and I sure don't have a real life. My office is at home, so the boundaries between work (PL/SQL) and non-work crumbled years ago.

Well, enough about me and my life. The bottom line is that I have created a package, called authid_analysis, available in authid_analysis.pkg file of my demo.zip, that will perform precisely this analysis on your behalf.

Here's the header of the package:

CREATE OR REPLACE PACKAGE authid_analysis
IS
   FUNCTION authid_setting (program_owner_in IN VARCHAR2
                          , program_name_in  IN VARCHAR2
                           )
      RETURN VARCHAR2;

   FUNCTION is_current_user (program_owner_in IN VARCHAR2
                           , program_name_in  IN VARCHAR2
                            )
      RETURN BOOLEAN;

   FUNCTION is_definer (program_owner_in IN VARCHAR2
                      , program_name_in  IN VARCHAR2
                       )
      RETURN BOOLEAN;

   PROCEDURE analyze_callstack (callstack_in IN VARCHAR2 DEFAULT NULL);
END authid_analysis;

You can retrieve the AUTHID setting of a program from ALL_PROCEDURES and find out if a program is invoker rights (is_current_user) or definer rights (is_definer).

Finally, call the analyze_callstack procedure to identify possible problems, as described above. If you do not pass the call stack, analyze_callstack will call the FORMAT_CALL_STACK function for you.

You can easily integrate this package into your error logging and tracing routine, making it easy to identify possible program areas in your code. I plan to add this to Quest Error Manager as soon as I have a moment.

The authid_analysis.tst scritp will allow you run a quick test of this utility. Here's what I see:

CREATE OR REPLACE PROCEDURE proc1
   AUTHID CURRENT_USER
IS
BEGIN
   authid_analysis.analyze_callstack;
END;
/

CREATE OR REPLACE PROCEDURE proc2
IS
BEGIN
   proc1;
END;
/

CREATE OR REPLACE PROCEDURE proc3
   AUTHID CURRENT_USER
IS
BEGIN
   proc2;
END;
/

BEGIN
   proc3;
END;
/
> DEFINER      package body HR.AUTHID_ANALYSIS called by...
> CURRENT_USER procedure HR.PROC1 called by...
> DEFINER      procedure HR.PROC2 called by...
> CURRENT_USER procedure HR.PROC3 called by...
>              anonymous block

As you can see, I have a switch from invoker rights to definer rights in the call to PROC2 and that will likely cause problems.

I hope you find this utility useful.

More ...

Tags: plsql,oracle,invoker rights

Category: PL/SQL Obsession

Books That Taught Me Lots

Steven Feuerstein — Mon, 29 Mar 2010 13:01:00 GMT

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

Beta Testing Starts for the PL/SQL Challenge

Steven Feuerstein — Thu, 18 Mar 2010 15:14:00 GMT

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!

Category: PL/SQL Obsession

Say goodbye to hard-coding!

Steven Feuerstein — Mon, 01 Mar 2010 16:26:00 GMT

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.

Tags: plsql,package,oracle

Category: PL/SQL Obsession

Code Tester Version 1.9 Now Available!

Steven Feuerstein — Thu, 18 Feb 2010 14:15:00 GMT

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 request that the default of a parameter be used when testing your program. The generated test code will not pass a value for this argument.

Improved code coverage support You can now specify your code coverage target as a percentage, both as a preference (global target) and for specific program units. Actual code coverage % and target % values are now shown in Test Editor and code coverage reports. These reports have also been expanded.

Specifying order of execution of test suites You can now specify the order of execution of tests in a suite through the Dashboard (right-click on suite name, choose "Specify order of tests in suite").

Random test execution You can request through the Test Editor that unit tests and test cases within a unit test be executed in random order.

We have also cleaned up various parts of the user interface, most notably the Preferences Window, which has finally caught up with the rest of the tool (greatly expanded preferences, such as for export/import, as well as removal of old, unused preferences). All preferences are also now stored in their own preferences ini file, so that you can copy it to the workstations of other Code Tester users and easily apply a standard set of preferences to a development team.

If you have not yet checked out Code Tester, I urge you to do so. There is no better way to build and run regression tests that can finally break out of the vicious cycle of....

"I don't have enough time to do it right because I am too busy fixing bugs from the last release."

Download Quest Code Tester for Oracle v1.9

More ...

Tags: code tester

Category: Code Tester for Oracle

Retrieving values from a record dynamically

Steven Feuerstein — Tue, 09 Feb 2010 19:19:00 GMT

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

Tags: plsql

Category: PL/SQL Obsession

Binding sparse arrays in a FORALL statement

Steven Feuerstein — Mon, 23 Nov 2009 15:44:00 GMT

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 5^th edition of Oracle PL/SQL Programming, complemented by the Oracle documentation. Follow up with a perusal of my 21^st Century PL/SQL course materials.

I assume a working knowledge of FORALL for the remainder of this post.

The typical (and, in Oracle8i and Oracle9i, the only) way to construct a FORALL statement is to use a header that is very similar to a numeric FOR loop. Here is an example:

DECLARE
   TYPE namelist_t IS TABLE OF employees.first_name%TYPE;

   l_enames   namelist_t := namelist_t ('ABC', 'DEF', 'SMITHIE');
BEGIN
   FORALL indx IN 1 .. l_enames.COUNT
      UPDATE employees
         SET first_name = l_enames (indx);
END;

More generally, the FORALL header looks like this:

FORALL integer_index IN low_value .. high_value
where integer_index is an implicitly declared integer iterator, low_value is the low end of the integer range and high_value is the high end. Each integer between low and high must reference a defined index value in any of the collections that are bound into the FORALL's DML statement (in the above case, there is just one: l_enames).

In other words, the binding array must be densely filled between the low and high values.

If the collection is sparse and the FORALL statement tries to read an element at an undefined element, it will raise an exception as shown below:

Notice that it does not raise NO_DATA_FOUND. I point this out because it seems like Oracle goes out of its way to raise this exception whenever the error bears even the remotest resemblance to "I tried to get something and it wasn't there" (SELECT INTO, read an element in a collection at an undefined index value, read past the end of a file).

Notice also that even if I include the SAVE EXCEPTIONS clause in my FORALL statement, this exception is not "saved" to the SQL%BULK_EXCEPTIONS pseudo-collection. That's because this is a "meta-error" for the FORALL statement. The exception is not raised by the SQL engine as it performs the DML statement. It is, instead, raised by the PL/SQL engine before the SQL statements are passed along to the SQL engine.

So what's a programmer to do if your binding array is not densely-filled?

In Oracle10g Release 2, Oracle added support for two new ways to construct the FORALL header: INDICES OF and VALUES OF. With either of these, your FORALL header no longer looks like a numeric FOR loop. Instead you specify that you want the FORALL statement to reference only those elements in the binding array whose index value is defined in the INDICES OF array.

The simplest form uses the same collection as the binding and INDICES OF arrays, as in:

   FORALL indx IN INDICES OF l_enames
      UPDATE employees SET first_name = l_enames (indx);

Now, an update statement will be sent to the PL/SQL engine only for this index values that are defined in the collection.

But the collection referenced in the INDICES OF clause need not be the same as the binding array. Here is an example of using INDICES OF with a distinct collection:

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   l_employees          employee_aat;

   TYPE boolean_aat IS TABLE OF BOOLEAN
      INDEX BY PLS_INTEGER;

   l_employee_indices   boolean_aat;
BEGIN
   l_employees (1) := 137;
   l_employees (100) := 126;
   l_employees (500) := 147;
   
   l_employee_indices (1) := FALSE;
   l_employee_indices (500) := TRUE;
   l_employee_indices (799) := NULL;

   FORALL l_index IN INDICES OF l_employee_indices 
                  BETWEEN 1 AND 500
      UPDATE employees
         SET salary = 10000
       WHERE employee_id = l_employees (l_index);
END;

Updates will be run only for employee IDs 137 and 147.

The VALUES_OF clause offers an additional layer of "indirection." With VALUES_OF, the FORALL statement executes a DML statement for each index value in the binding array that is an element in (not an index value in) the VALUES OF collection.

Here is an example:

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   l_employees          employee_aat;

   TYPE indices_aat IS TABLE OF PLS_INTEGER
      INDEX BY PLS_INTEGER;

   l_employee_indices   indices_aat;
BEGIN
   l_employees (-77) := 134;
   l_employees (13067) := 123;
   l_employees (99999999) := 147;
   l_employees (1070) := 429;
   
   l_employee_indices (100) := -77;
   l_employee_indices (200) := 13067;
   l_employee_indices (300) := 1070;
   
   FORALL l_index IN VALUES OF l_employee_indices
      UPDATE employees
         SET salary = 10000
       WHERE employee_id = l_employees (l_index);
   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;

In this block of code, an UPDATE is executed for employee IDs134, 123 and 429.

So if you are using FORALL but running into situations where your binding array may not be sequentially filled, give INDICES OF or VALUES_OF a try. It might simplify your life and code dramatically.

More ...

Tags: plsql,oracle,forall

Category: PL/SQL Obsession

The Subtleties of Programming

Steven Feuerstein — Mon, 16 Nov 2009 15:54:00 GMT

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.   SELECT name from ALL_SOURCE
WHERE owner = USER
AND name = 'DBMS_OUTPUT.PUT_LINE'

C.   SELECT DISTINCT name from ALL_SOURCE
    WHERE INSTR (text, 'DBMS_OUTPUT.PUT_LINE') > 0

D.   SELECT DISTINCT name from USER_SOURCE
WHERE INSTR (UPPER (text), 'DBMS_OUTPUT.PUT_LINE') > 0

I believed that (D) was correct. Certainly, A, B and C do not do the trick (click here to see my explanation). But I recently received the following email from Wim de Lange of the Netherlands:

"None of the answers below is correct. (D) could be the solution, but DBMS_OUTPUT.PUT_LINE could be spelled as DBMS_OUTPUT . PUT_LINE (spaces around the dot) and that code will still compile and run. So the spaces are destroying the solution here."

Wim is absolutely correct. You could put spaces between the package name, dot and subprogram name, and the PL/SQL compiler will have no trouble with that code.

Thanks, Wim, for your very close reading of my puzzle, and for reminding me that programming can be a very subtle and nuanced craft. Oh, and also that it is so difficult to come up with all the test cases for a program.

And just to show you that I am not the least bit upset about someone finding an error in my code and writing, Wim is hereby declared another winner of that contest and will receive a Toad World t-shirt in addition to the randomly selected four winners.

More ...

Tags: plsql,oracle

Category: PL/SQL Obsession

The Most Important Language to Learn

Steven Feuerstein — Mon, 02 Nov 2009 13:54:00 GMT

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!

Category: PL/SQL Obsession

An Amazing Visit to Mexico City

Steven Feuerstein — Fri, 23 Oct 2009 12:38:00 GMT

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 putting me up in a fabulous, old but very excellent hotel, El Camino Real Polenco, and making sure I enjoyed outstanding meals of (surprise) Mexican cuisine, they pulled together an enormous gathering of over 250 Oracle technologists for an evening presentation at the beautiful http://www.haciendadelosmorales.com/index.php by Claudia Fernandez, Quest product manager supreme, to talk about Toad 10, and me to talk about, well, pretty much whatever I felt like talking about, which turned out to be the function result cache feature of Oracle11g.

Quest Mexico also ordered 100 of my Oracle PL/SQL Best Practices books to give away to the first 100 people who arrived on the 20th. That turned out to be a fine motivator. Developers started arriving at 2 PM or so for a 5 PM start time. Unfortunately, due to problems with Customs in Mexico, we didn't have those orange books. Fortunately, we had a nice big pile of a book that Claudia co-authored, Database Benchmarking. So attendees received those. And then they lined up for autographs. As I signed books, I got to meet a lot of very enthusiastic PL/SQL developers - several of which said that they benefited greatly from my writing. How satisfying!

On the wall behind our signing table was a banner with a reproduction of my head that must have been five feet high. I kid you not - in fact, you can see it here. Fortunately, I am a humble fellow, so I only posed in front of the banner for a few photos.

Many attendees also asked to have their photo taken with me. And Ricardo of Quest was kind enough to use my camera to also capture those moments. You can view all those photos here.

I must tell you that years ago, when people first asked for autographs, asked to have their picture taken with me, I was a little bit embarrassed by the whole situation. C'mon, I just wrote a few (well, 10) books about this one language, PL/SQL. Why are they getting so excited? They are being weird, ridiculous. But I eventually came to realize that it really doesn't matter what I think. What matters is that for whatever reason (I have some idea of why this happened, but it is still a bit of a mystery to me) my books, my writings, my trainings have had a substantial impact on the lives of these developers. My ideas and my ways of expressing them have improved their lives.

Now, that realization is/was a continually humbling experience. Now, I am honored that anyone would care to have me sign their book or share a photo with them. And I have decided to start celebrating this honor and, at least this is my intention, honor my readers by posting on Flickr the photos we took together (and others from the event in La Hacienda Morales in Mexico City).

If anyone sees their photo on this page and does not want it to be available publicly, please let me know immediately and I will remove it. My apologies in advance for any liberties taken.

Many thanks to Diana, Belen, Daniel, Pablo, Ricardo and others at Quest Mexico for organizing a fantastic set of events (beyond just the public program), media interviews and overall stay. I look forward to a triumphant return, or perhaps a visit to Monterey.

Once again, I find myself marveling at my good fortune and the fine life I have. I only wish I was able to spend time with all my PL/SQL friends around the world and not be away from wife, Veva, and my boys. That is a substantial price to pay. Sigh....

Finally, I very much enjoyed having an opportunity to resurrect my rusty (not) command of the Spanish language. I am not sure whether it is a blessing or a curse, but they tell me that I am able to pronounce words in Spanish very well (roll those r's, put the accent on the correct syllable, etc.). This leads native speakers to believe that I can speak and understand much more than I actually can. So I start out strong, but immediately stumble over a serious lack of vocabulary, and very poor comprehension (I can talk better than I can understand - big surprise, I spend so much of my life talking at others).

More ...

Resolving an LTRIM mystery

Steven Feuerstein — Thu, 15 Oct 2009 13:42:00 GMT

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

Tags: plsql,oracle,ltrim

Category: PL/SQL Obsession

Compare Nested Tables of Non-Scalar Datatypes

Steven Feuerstein — Mon, 12 Oct 2009 14:28:00 GMT

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.

Tags: plsql,set operator,nested table,oracle

Category: PL/SQL Obsession

PL/SQL on DB2 9.7 ?!

Steven Feuerstein — Wed, 16 Sep 2009 13:36:00 GMT

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

I think this is wonderful news for the PL/SQL community. Not only can I rebrand all of my books for DB2 (as in "DB2 PL/SQL Programming" - wow!), but PL/SQL developers should have growing opportunities for jobs.

More ...

Tags: plsql,db2,oracle

Category: PL/SQL Obsession

Online Training for the New Economy

Steven Feuerstein — Tue, 08 Sep 2009 18:23:00 GMT

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 than would be for an onsite or public training - and I don't have to travel (and neither do you)!

More information about session logistics and registration are available through these links below. For much more detail on each session, please check out the descriptions at the end of this posting:

Webcast Details

Session 1: "Turbo-charge PL/SQL Execution with Bulk Processing"
Thursday, October 22, 2009 at 2 p.m. ET
Session 2: "Writing Maintainable PL/SQL Code"
Thursday, November 12, 2009, at 2 p.m. ET
Session 3: "Error Management Features of Oracle PL/SQL"
Thursday December 10, 2009, at 2:00 p.m. ET

I hope that you can take advantage of this opportunity. Click on the link below to:

Get Details and Register

Optimizing PL/SQL Execution with Bulk Processing

This session focuses on how to turbo-charge the performance of multi-row SQL statements executed from within a PL/SQL using FORALL and BULK COLLECT. These features have been around since Oracle8i, but many developers still don't use them or only scratch the surface of what is possible.

The Bulk Processing webinar will talk about why these features were needed and how they work. Steven will start with simple examples and syntax and drill down into all nuances, including the LIMIT clause for BULK COLLECT and the SAVE EXCEPTIONS, INDICES OF and VALUES OF clauses for FORALL.

After attending this webinar, you will be able to both create new programs built around these features and also redesign existing programs safely to improve performance.

Writing Maintainable PL/SQL Code

Software managers (and, therefore, their developers) are very focused on delivering applications "on time" (that is, to meet the current production deadline). Sure, that's important. But as (or maybe even more) important is to build applications so that they can be maintained and enhanced without consuming all the resources of the development team (thereby interfering with new development).

This webinar offers a wide-ranging set of techniques to make your code easy to understand and maintain over time. These techniques include: the use of subtypes and local modules; how to activate and check compliance with standards; and encapsulation (information hiding).

After attending this webinar, you will be able to write code that minimizes the amount of development resources needed to maintain that code.

Error Management Features of Oracle PL/SQL

Consistent, robust error management is a critical feature of any successful application. Developers need to know all that is possible in PL/SQL regarding the raising, handling and logging of errors, and that standardize the way in which those tasks are performed.

This webinar takes you beyond the basics of exception handling in PL/SQL to explore the wide range of specialized error management features in Oracle, plus recommendations for best practices for consistent, robust application construction.

We will cover FORALL's SAVE EXCEPTIONS, DML error logging with the DBMS_ERRLOG package, the AFTERSERVERERROR trigger, the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, and more. In addition, we will talk about hiding error management functionality behind a PL/SQL packaged API and then deploying it as a set of services for application developers.

After attending this webinar, you will be able to fully leverage PL/SQL error management features and follow best practices for consistent error management across your entire development team.

More ...

Category: PL/SQL Obsession

Oracle Database 11g Release 2 Has Arrived!

Steven Feuerstein — Fri, 04 Sep 2009 22:25:00 GMT

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 wondrous new features in PL/SQL. So what I usually do is say something like this:

"It is possible, just possible that in the next release of Oracle you will be able to...."

And I leave it up to my audience to draw their own conclusions.

No longer. I can now disclose to you the new features for PL/SQL in Oracle Database 11g Release 2. Drum roll, please!

So what's new? Hmmm. Not much, in PL/SQL specifically.

Probably the most important thing to me won't mean much to most of you, because it is an improvement over an 11.1 feature – and who's upgraded to 11g already? Not many.

Still, I will tell you about it: when working with the Function Result Cache, you no longer need to include the RELIES_ON clause. Oracle will automatically determine what tables the cache is reliant on. When and if any changes are committed to that table, all dependent caches will be invalidated.

It's a nice improvement and it also means that for most of you, RELIES_ON will be completely ignored. Lucky you! This sort of "instant deprecation" reminds me of the RESTRICT_REFERENCES pragma that was required back in Oracle7 or 8 to be able to execute user-defined functions inside SQL statements. Now that was an enormous pain in the neck and in Oracle8i, it was no longer required.

Besides the enhancement to the Function Result Cache, we have the following changes in and additions to PL/SQL (I encourage you to download the 11.2 documentation library, even if you have no plans to upgrade to 11.2 anytime soon. Then you can review the PL/SQL Users Guide yourself!):

Surely, you are thinking, 11.2 consists of more than this. Certainly it does. The major new feature in 11.2 is "edition-based redefinition," which (according to the doc) " enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time."

This is REALLY BIG NEWS. You can now "hot patch" your application code, which means that Oracle is finally and truly a 24x7 database platform. This feature will have a big impact on application architects and some impact on PL/SQL developers, but there is actually very little change in the PL/SQL language itself.

And now for the biggest news of all: now that Oracle has officially released 11.2, O'Reilly can publish the 5^th edition of Oracle PL/SQL Programming. This edition has lots of good stuff in it, but most important are:

Optimize PL/SQL performance with the aid of a brand-new chapter in the fifth edition;
Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, sequences in PL/SQL expressions, supertype invocation from subtypes, and enhancements to native compilation, triggers, and dynamic SQL;
Use new Oracle Database 11g tools and techniques such as PL/Scope, the PL/SQL hierarchical profiler, and the SecureFiles technology for large objects.

[ By the way, I am considering making it possible to order a signed copy of OPP5 through my own website. If you are interested, please drop me a note. ]

More ...

Tags: plsql,oracle,11g

Category: PL/SQL Obsession

Making Change Happen

Steven Feuerstein — Sun, 23 Aug 2009 00:18:00 GMT

[ 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 tested with less difficulty, and utilize more fully the best features of PL/SQL.

And all too often, the response I get to these ideas are: "They all sound great, but I know that when I go back to work, I'll just fall back into the same way I've been doing things. And even if I change, the rest of the group will still be stuck in the old ruts."

Well, that's depressing, isn't it?

When I was in Singapore last week, I gave one of those "Here's the right way to do everything" talks to a group of developers at a major financial services firm. Then I met with the Deputy Director of IT and he presented me that same challenge:

"How can I get my people to change the way they do things?"

So I decided to try to come up with an answer. Here's what I suggested to him:

Champions

Research Time

Team-building Reviews

Incentives

Selective Enforcement

Perhaps I should go into a bit more detail....

First of all, it is important to recognize that the biggest challenge to bringing about change is not making sure that developers know about the new features and the right way to do things. The technology is actually the easy part. The hard part is to change behavior. We are all creatures of habit, and we like our comfort zones. They're so comfortable.

Champions

For any new product (we were talking about Quest Code Tester, but it applies to anything) or major new feature, you should assign a person on your team to be the "champion" of that technology. They should become expert in the technology, be enthusiastic about what it can do for the team, and they should be ready, willing and able to mentor others.

Without a strong proponent of a new direction, you won't be able to build the momentum to ensure a solid, consistent, team-wide implementation. With a champion in place, everyone on the team knows who to go to when they get stuck. And since the role is well-defined and "public knowledge," there is no reluctance to approach the champion for help.

Research Time

I continue to be shocked at how many developers still do not seem to know about and use many important features added to PL/SQL in Oracle8i. Yes, Oracle8i. Things like: invoker rights, autonomous transactions, FORALL and BULK COLLECT. And then of course, there are the many new features of 9i and 10g, such as string-indexed collections, MULTISET operators, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, and more.

It's easy to understand why so many developers remain ignorant about such things and consequently under-utilize the language: they don't have the time (or feel like they have the freedom) to explore PL/SQL.

Managers usually ask just one or two questions of their developers: "When you will be done?" and "Why is your code so buggy?" Neither of those questions are conducive to expanding one's knowledge of a programming language.

I strongly urge managers and team leads to ask each developer to come up with a research plan: spend 4 hours every two weeks (5% of your time - more is better, but I don't want to sound crazy) exploring technologies that could be helpful to the project.

Insist that the developer not work directly on application code, but also that they put together a proposal for precisely what they want to study and why. They will feel great about the opportunity, and both the team and application will benefit in short order.

Team-building Reviews

Everyone knows that code review is a good idea. I would even go so far as to say that no critical code should go into production until at least one other human being (besides the one who wrote it) looks it over and makes sure it isn't totally crazy (that is, unreadable spaghetti code). Often the form of such a review is "one on one" - the team lead looks over the good and gives private feedback to the developer.

That's not what I'm talking about here (though it's a fine thing to do!). Rather (or in addition), I propose that once a month, the entire team gets together to critique, as group, a piece of code. The aim of the review is not so much to find flaws in code, but to share knowledge about the application and elevate the whole team's awareness of how best to use PL/SQL.

Whoever is the senior developer on the team should take the lead; you can't expect junior programmers to be willing to subject themselves to potential humiliation (at least not until they see there is nothing to be afraid of). The best developer should set an example: everyone's code can be improved; it's OK to make mistakes; we can all learn from one another.

Incentives

What's the best way to motivate a developer: carrot (incentive) or stick (punishment)? We all know the answer, and not just for developers: we aren't motivated to change for the better with a threat of punishment (though we may still change because we have no choice). I think the world would be a far better place if without coercion.

And certainly when it comes to getting developers to create higher quality software, well, it's really important to make that they want to do. So....let's use incentives!

Managers should identify ways to measure objectively progress in important areas, and then give out rewards (both recognition/status and goodies, like gift certificates to restaurants, Amazon.com, etc.) to those who are high achievers in those areas.

Here are some ideas for possible metrics:

Reusable code: we don't want to reinvent the wheel, but sometimes it's hard to find and reuse code. Perhaps prizes will help. You can run searches through the code base to find all external (not in the same package) program calls, for one example. And I suggest you give a reward for (a) the person writes the most reusable code - usually a senior developer, and (b) the person who uses the most reusable code. That way even a junior developer has a chance to win and is very much incented to learn from the use the work of others.
Naming standards: we usually have some set of naming standards, but how do you check to see if people are following them? Consider installing Oracle Database 11g on a workstation, load up your application code, and use PL/Scope to analyzer variable declarations. For an example of a query that demonstrates this powerful feature, check out the AMIS blog, and search for "PL/Scope".
Unit tests (especially automated tests): I would give out a really substantial reward for a developer who does the most automated testing of their code, building and putting in place regression tests. This will have the biggest impact on the quality and success of your applications, so get people motivated to do it! It will greatly help to provide a tool to make it easy(ier) to automate the process. Check out Quest Code Tester for Oracle, utPLSQL, dbFit, PLUTO or PL/Unit.

Selective Enforcement

No doubt about it: carrots are the way to go. But that doesn't mean that you (a manager or team lead) shouldn't be ready to bring out the stick now and then.

Well, that's probably a bad way to put it. I am not making an argument for punishing developers if they don't do things a certain way. I do believe, however, that a manager should in at least some cases, insist and enforce certain critical changes in behavior. Some things are just too important to leave up to developer preference and hopes for good behavior.

The following areas come to mind:

Testing (yes, that again): let's face it. Our applications have too many bugs in them. That's not only a big drag for our users, but it makes our live miserable, too. Rather than focus on building new applications or adding interesting functionality, we spend way too much of our time putting out fires. We are stuck in a vicious cycle: no time to write better code, because we have too many bugs to fix. The only way out is better and more automated testing. Managers should find tools to help make this a reality (see list in previous section) and they should insist that developers write repeatable, automated tests.
Version control: it seems obvious, but I still run into development shops that do not have VC systems in place. Sometimes I even encounter developers who do not maintain their source code in files, but simply edit and compile right out of the database. Totally insane! That's a sure recipe for losing work and creating terrible frustration on your team.

I could list other ideas, but I don't want to end this note with too much of heavy hand. And managers should definitely not go overboard with putting lots of new requirements in place at once. Phase them in, and make sure that you provide support (tools, champions, training time) to ensure success.

No Guarantees But It Won't Hurt to Try!

Maybe none of these ideas will make a difference in your team, but surely it's worth trying some new directions to see what if anything will stick.

I have no doubt that developers will be excited to see their manager trying new approaches, especially ones that don't simply mean more work and longer hours.

More ...

Watch Out for Data Caching!

Steven Feuerstein — Mon, 03 Aug 2009 18:31:00 GMT

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):

    PL/SQL: numeric or value error: NULL index table key value
  ORA-06512: at "QCTO.QU_TEMPLATE_XP", line 1523
  ORA-06512: at "QCTO.QU_TEMPLATE_XP", line 1570
  ORA-06512: at "QCTO.QU_GENERATE", line 6062
  ORA-06512: at "QCTO.QU_GENERATE", line 6138

I did some tracing, digging around, etc. and I concluded that this is very quirky behavior, likely specific to this unlikely scenario, and I was just about to write him an email suggesting that this one will not be easy to fix, is not a high priority bug to fix (we are in final QA testing - only able to fix "P0" bugs - the product is considered broken if a P0 is not addressed), and we should leave it for next version.

But then I thought: "Aw, heck, let's try to reproduce the problem with a simpler, more likely scenario." And, lo and behold, and much to my stunned disappointment, it was very easy to replicate the error with a much more likely scenario.

That left me with no choice: MUST FIX BUG. And it was very odd behavior, too. The error occurred when I first defined the test and any time I tried to run it in that session. But when I exported and imported the test definition, the bug went away, and I could run the test just fine.

After a little bit more analysis, I also found that if I closed Code Tester and reopened it, the bug would disappear. How could this be? What was different?

Well, any time a bug's behavior changes with a new connection, you should think about what was cached in the session, either by Oracle or by the application. That led me to remember that I had implemented some caching of key data in the Code Tester backend to improve performance when generating and running tests.

I cache the data when validating the test definition, and then reuse that same cached data when generating the test code and finally when running the test (at least in those situations where I need to generate some "just in time" code elements).

Caching is fine and wonderful - especially Oracle 11g's new function result cache, which is a thing of great beauty - but you have to make sure that the underlying data hasn't changed!

So maybe that was the problem...something had changed. But why would it change? So I opened up one of my program files and did some searching for DML operations. And I found a point in the validation phase when I go back and make a change to the underlying data (actually, I knew about this step and had studied it, but hadn't connected it to the caching issue).

Now, you could argue (and I did so to myself) that validation is not the place for data changing. You could also argue that there is no time to refactor this convoluted piece of code. You could, finally, argue that you should put it on your to do list to get back to this piece of code and clean it up.

I put the following on my to do list: "Get back to this piece of code and clean it up."

In the meantime, however, I put in a call to invalidate the cache and force new retrieval of data, immediately after the change I made to the qu_attributes table.

And then the bug disappeared. Ah...so nice.

Lessons learned (or of which I was reminded):

Don't be hasty in drawing conclusions about the causes of bugs, especially if your conclusion seems anything like a rationalization for not doing something you know you should do.
Be very, very, very careful when caching data. It's a powerful performance optimization technique, but it can lead to dirty data and bugs that are hard to identify and fix.

More ...

Tags: code tester,plsql

Category: Code Tester for Oracle

Category: PL/SQL Obsession

Must Know Features of PL/SQL

Steven Feuerstein — Wed, 22 Jul 2009 14:50:00 GMT

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 to be "must know." In other words, if you are not familiar with the following features, and not utilizing them, then your PL/SQL programs likely leave lots to be desired from the standpoints of efficiency and maintainability.

In the table below, you will find on the left the PL/SQL keywords that identify the feature and on the right a brief description and link to the Oracle documentation on this feature. Of course, you can also download my trainings at PL/SQL Obsession and learn about these features – oh, and don't forget to buy my books. J

Collections	Associative arrays, nested tables, varrays: if these terms mean little to you, then you are missing out on some of the most important functionality in PL/SQL. The collection is PL/SQL's version of an array, and they can help you in so many different ways. >More info
FORALL	Bulk processing of DML statements. This is the most important new feature in PL/SQL since Oracle8i was released. Anytime you execute an insert, update, delete or merge inside a loop of some sort (multi-row DML), you should convert this looping operation to a FORALL statement. You will see an amazing improvement in performance...and you need to use collections to leverage FORALL. Don't forget to also check out these nuances of FORALL: SAVE EXCEPTIONS, INDICES OF, VALUES OF. >More Info
BULK COLLECT	Bulk retrieval of data. Second only to FORALL in importance, use BULK COLLECT with both implicit and explicit cursors to drastically speed up the performance of data retrieval. Don't forget to use the LIMIT clause to manage the amount of PGA memory consumed by your program. As with FORALL, you need to use collections to leverage BULK COLLECT. >More Info
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE	Answers the question "On what line number was the most recent exception raised?" Available in Oracle Database 10g Release 2 and above, this function should be called whenever you trap and log an error. >More Info
DBMS_UTILITY.FORMAT_CALL_STACK	Answers the question "How did I get here?" In other words, it shows you the execution call stack (A called B called C). Very useful information when logging errors or tracing application execution. >More Info
DBMS_UTILITY.FORMAT_ERROR_STACK	Returns the current Oracle error message (sometimes as a single string, and sometimes as a stack of messages, if the exception has been re-raised one or more times). Oracle recommends you use this instead of SQLERRM, which may truncate your error message. >More Info
PRAGMA AUTONOMOUS_TRANSACTION;	This pragma defines your procedure or function as an autonomous transaction, which means that any DML operations performed within this subprogram are saved or rolled back without affecting any other uncommitted changes in your session. Very handy when logging errors or tracing, also useful in a variety of other circumstances. >More Info
AUTHID CURRENT_USER	When you include this clause in the header of a program unit, then when the program is run, the privileges of the current user or invoker of the program are used to resolve references to table and view names. This is called invoker rights and is in contrast to definer rights, the default (or AUTHID DEFINER), in which the privileges of the owner of the program determine at compile time how references to database objects are resolved. Invoker rights comes in very handy when you need the same code to work against different tables, depending on who is calling the program. >More Info
SUBTYPE	The SUBTYPE keyword allows you to define your own, application-specific datatypes. It is a trivial feature to learn and use, but a very powerful one from the standpoint of writing maintainable code. Use packaged-based subtypes to hide the actual datatype definition, such as VARCHAR2(500). That way, if you ever need to change the datatype (for example, to make the maximum size of the string larger), you can change it one place and all the variables that are declared using the subtype will use this new definition after recompilation. >More Info
RESULT_CACHE	New to Oracle Database 11g, you can specify that a function is a result cache. This means that Oracle will cache the inputs and return values of the function in a new SGA cache, and share this data across all sessions in the instance. It can provide a significant boost in query performance for any tables that are queried more frequently than they are updated. Sure, you are not using 11g yet, but you will. And you can get ready to take advantage of the result cache by hiding your queries inside functions now. >More Info
DBMS_ERRLOG	Introduced in Oracle Database 10g Release 2, this package and the associated LOG ERRORS clause, allows you to suppress the raising of exceptions in for DML statements, instead writing error information to a log table. This feature is very handy when performing large numbers of DML operations (ie, batch loads) from within PL/SQL and you need to continue past any exceptions raised. You can also use this within a FORALL statement. >More Info

More ...

Tags: plsql,oracle,collection,forall,bulk collect,pragma

Category: PL/SQL Obsession

Avoiding repetitive coding for repetitive column structures

Steven Feuerstein — Wed, 15 Jul 2009 13:10:00 GMT

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

Tags: plsql,array,oracle

Category: PL/SQL Obsession

Oracle PL/SQL Programming 5th Edition - "Early Warning"

Steven Feuerstein — Wed, 10 Jun 2009 18:19:00 GMT

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 last week, my editor (Debby Russell) sent all the chapters to the production department, so that they could begin the long process of converting Word docs into an actual, publishable book.

This fifth edition will cover all features of Oracle11g, through Release 2 - which means it will not be published until the fall, probably at the same time as Oracle Open World (week of October 12), or whenever Oracle formally announces availability of Release 2.

We have also added a single, very extensive chapter on optimizing performance of PL/SQL code.

You can see more information about the book and even pre-order the book at:

http://oreilly.com/catalog/9780596514464/#top

But you may want to hold off a little bit on that pre-order. I am considering offering the ability to order the book directly from me, and have it signed whatever personalized message you request. So....you will hopefully see an update about that soon!

More ...

Naming Conventions and Coding Standards, the Feuerstein Way

Steven Feuerstein — Thu, 28 May 2009 17:32:00 GMT

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 I have the time to stop and write down the few rules that I might actually follow more of less consistently. And it seems that a part of me is somewhat resistant to being accountable to a standard. Sound familiar?

Sigh...unfortunately (no, make that very fortunately), lots of other PL/SQL developers look to me for advice about how to "do things right." Ah, the pressure, the pressure!

Ah, the hypocrisy. That's what it really comes down to: I am a big hypocrite. I routinely violate just about every best practice I push on others. I sound high and mighty, but when I write code, I feel the same pull to do the quick and dirty.

So I've decided to take a few moments out of my various plane rides (I am now - when I started this document, anyway - flying from Manchester, UK, to Sweden to do a presentation to the Stockholm OUG, then a couple of days of customer visits in Stockholm and Milan, then two days of training for Oracle in Helsinki) to jot down what have become my de facto coding standards.

You see, I do have standards, and I even follow them, more or less. I just don't like to bother writing them down.

So that's what (finally) you'll find here. It's a combination of the naming conventions I follow, certain naming conventions with which I disagree, and a variety of other suggestions. I hope you find it useful. I'm not going to make much of an effort to justify my approach or explain away the inconsistencies. I'll just show you what I do and you can decide for yourself if you'd like to use my path as a model for your own journey.

I also hereby declare this to be a work in progress. So...

If you have disagreements with my approach, let me know. Perhaps I will agree with you, and change my document (you will get the credit).
If you have a suggestion for something to add to the document, send it to me. If I like it, I will put it into the document (and give you credit).
If you would like to offer your own set of naming conventions and coding standards to the world of PL/SQL developers, I will add it to the webpage.

The URL for my standards document is: www.ToadWorld.com/SF/standards

More ...

Tags: plsql,naming standards

Category: PL/SQL Obsession

A Classic Error in My Monthly Puzzle!

Steven Feuerstein — Mon, 27 Apr 2009 15:47:00 GMT

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;
END;

B.
BEGIN
   LOOP
      DBMS_OUTPUT.PUT_LINE ('abc');
   END LOOP;
END;

C.
DECLARE
   l_keep_going BOOLEAN;
BEGIN
   WHILE (l_keep_going)
   LOOP
      DBMS_OUTPUT.PUT_LINE ('abc');
      l_keep_going := SYSDATE IS NOT NULL;
   END LOOP;
END;

D.
BEGIN
   <>
   DBMS_OUTPUT.PUT_LINE ('abc');
   GOTO start_of_block;
END;

I told my friends at Toad World that the answer was (A), because UTL_FILE.GET_LINE raises the NO_DATA_FOUND exception when you read past the end of the file. Since files cannot be of infinite size, you will eventually read to the end of the file and then the loop will terminate.

And I wiped my hands of that puzzle. Unfortunately, it came back to haunt me in the form of a message from the Toad World editor-in-chief:

"Steven, normally I don't worry when I get just a few responses to the puzzler with answers different from what you’ve told us is the correct one, but this month I’m wondering if we missed something because we’ve had close to 50% of the respondents picking the wrong answer (C), and explaining their choice as follows: The value of l_keep_going is not initialized to TRUE and is therefore unknown. It will not enter the loop and therefore the loop is not infinite."

Oh my, oh my. They are so right. And I was so sloppy. And I was so over-confident, that I did not ask anyone with PL/SQL experience to proof my Q&A.

Let's take a closer look at choice C:

DECLARE
   l_keep_going BOOLEAN;
BEGIN
   WHILE (l_keep_going)
   LOOP
      DBMS_OUTPUT.PUT_LINE ('abc');
      l_keep_going := SYSDATE IS NOT NULL;
   END LOOP;
END;

I declare a variable that I will use to determine termination of the loop, but I neglected to provide a non-null default value. As a result, the WHILE condition fails immediately, the loop body never executes even once – and certainly not an infinite number of times!

This kind of bug often manifests in this form as well:

DECLARE
   l_counter PLS_INTEGER;
BEGIN
   LOOP
      do_something;
      l_counter := l_counter + 1;
      EXIT WHEN l_counter = 100;
   END LOOP;
END;

Again, I have failed to initialize my counter (likely it would be set to 1) and in this case the result is an infinite loop.

Hey, that must have been what I meant to use as choice C! Yeah, right.

So the lessons to be learned are:

Anyone can and everyone does make mistakes. Including the self-named PL/SQL Evangelist.
Code review is a critical component of high quality development. All critical code (and what isn't?) should be looked at by another human being before it goes "live."

More ...

Tags: plsql,while loop

Category: PL/SQL Obsession

An interview with Steven Feuerstein

Steven Feuerstein — Thu, 23 Apr 2009 01:06:00 GMT

In case you simply can't get enough of Steven Feuerstein (no, I don't generally talk about my self in the third person or in the royal "we"), I invite you to check out this interview. I had an awful lot of fun answering the questions, and you might be entertained reading them.

More ...

Category: PL/SQL Obsession

A handy string parsing utility - extended

Steven Feuerstein — Mon, 13 Apr 2009 16:57:00 GMT

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 that accompanies my training materials. You will find it in the parse.pkg file. For the past few years it has consisted of a single function that accepts a delimited list and returns a collection of the items in the list.

On a flight from London Heathrow to Istanbul in April 2009, though, I decided to make this package a bit more useful. I added two overloadings of the string_to_list function so that you can now easily parse (1) a string that contains multiple lists and (2) a string that contains multiple lists, each of which has a name.

So I have updated the demo.zip download to include this new version, and I thought I would bring it to your attention. The demo.zip contains about 2000 files and there is no index. So it's just a tad bit difficult to find the useful programs within it.

I have also created a Quest Code Tester for Oracle test definition to verify the behavior of these functions. If you would like to check that out, install Quest Code Tester 1.8.3 or higher (available as a beta here until mid-May 2009), and then import the Q#PARSE.qut file (also in demo.zip).

Here are examples of using the three different overloadings of parse.string_to_list:

DECLARE
   l_list   parse.items_tt;
BEGIN
   l_list:=parse.string_to_list('a,b,c,d',',');
END;
/

DECLARE
   l_list   parse.nested_items_tt;
BEGIN
   l_list:=parse.string_to_list('a,b,c,d|1,2,3,4','|',',');
END;
/

DECLARE
   l_list   parse.named_nested_items_tt;
BEGIN
   l_list:=parse.string_to_list('a,b,c,d|1,2,3,4','|',',');
END;
/

The third overloading, which returns a collection of type parse.named_nested_items_tt, also offers a demonstration of the elegance and usefulness of multi-level and string-indexed collections.

I hope you find the parse package useful.

And I hope that someday we can happily deprecate it, for the very good reason that PL/SQL finally offers one of its own.

More ...

Tags: plsql,string parser

Category: Code Tester for Oracle

Category: PL/SQL Obsession

Run an OS command from within PL/SQL

Steven Feuerstein — Fri, 27 Mar 2009 13:14:00 GMT

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.

A good example of this is figuring out how to run an OS command from a PL/SQL block. There is not, unfortunately, a native "execute host command" statement within PL/SQL . Instead, Oracle offers (at least) three ways to do this:

Use the DBMS_SCHEDULER package
Run a Java method to execute your command.
Run a C program to execute your command.

DBMS_SCHEDULER offers the simplest and most intuitive solution, especially from the standpoint of DBA and system administration tasks. I talk about this solution on the OTN Best Practice page:

http://apex.oracle.com/pls/otn/f?p=2853:4:1544539351000407::NO::P4_QA_ID:16282

I will, in this Toad World blog, offer brief guidance on the other two options. It is very unlikely that you will be able to rely solely on the content below to implement either approach. Instead, I suggest you use this as a starting point and then from there visit the detailed Oracle documentation and/or the full chapter coverage of both these techniques in my Oracle PL/SQL Programming.

Executing an OS 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.

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.
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.
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.
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 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 an OS 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, check out Oracle documentation at:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_extern_proc.htm#ADFNS010

and also read Chapter 27 of Oracle PL/SQL Programming, 4^th edition, in which my co-author Bill Pribyl thoroughly explains external procedures.

As with Java, you will need help 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.

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

More ...

Tags: plsql,os command

Category: PL/SQL Obsession

Automatic refactoring in PL/SQL tools – Part 3

Steven Feuerstein — Mon, 09 Mar 2009 15:18:00 GMT

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 a separate Refactoring right click menu. Instead, the main (and most important) refactoring is explicitly visible from the right click menu in the editor: Extract Procedure.

Let's dive right in and explore this feature. When I select Extract procedure...., I see the following window:

When I choose the default (and check the "With replace option" – which I would argue should be checked by default), Toad then opens another tab in my editor and fills it with this:

It did not automatically compile the procedure for me, which I actually like. I'd rather be responsible for the act of compiling - and perhaps over-writing an existing procedure of the same name, which in fact would have occurred here, as you can see from my list of existing procedures:

The extraction was smart; I ended up with a procedure that has two formal parameters, ensuring that it is completely independent of the original context (the use_cursor procedure). That original procedure is also changed appropriately:

If I choose the "local procedure" option, then Toad correctly creates a local subprogram for me:

Conclusion: a very handy and competent refactoring. It performs correctly under all tested circumstances (PL/SQL Developer had a number of bugs, which I have been told is fixed in a later release than I used). My suggestions for improvement? Let me specify that I want the stored program to be not schema-level, but inside a package. And if I could choose an existing package to put it into, well, that would be fantastic! I could cut down on the number of independent stored program units (I try to avoid schema-level functions and procedures whenever possible), and increase the likelihood of code re-use.

That is the only truly automatic refactoring feature in Toad, at least from my perspective. But for the sake of performing a true apples-to-apples comparison, you may recall that SQL Developer offers these options on its refactoring menu:

I don't really consider case conversion to be refactoring, but in case you do, you can certainly do that in Toad, either under the Edit menu in Toad or on Editor toolbar (or you can simply use the keyboard shortcuts):

Toad doesn't offer any kind of rename feature, as does SQL Developer and PL/SQL Developer, but both of those implementations were a bit hobbled in how they worked. I doubt they are used much on a day to day basis.

Overall Conclusion

All three tools examined (Toad, SQL Developer and PL/SQL Developer) offer competent Extract Procedure functionality, though at least in the versions I used, Toad had the fewest drawbacks in its implementation. Beyond that, Toad has fewer explicit refactoring features than PL/SQL Developer. My feeling, however, is that outside of Extract Procedure, the refactorings are minor and of limited importance.

I hope that with the introduction of PL/Scope and ALL_IDENTIFIERS in Oracle11g, we will see a major step forward by all IDE vendors as regards refactoring and code analysis.

More ...

Tags: plsql,refactoring

Category: Toad for Oracle

Category: PL/SQL Obsession

Automatic refactoring in PL/SQL tools – Part 2

Steven Feuerstein — Tue, 06 Jan 2009 16:30:00 GMT

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 explore each of these below.

Rename item

If I highlight the name of identifier in its declaration section, the Rename item option is enabled:

I specified "abc" as the new name for this variable and PL/SQL Developer correctly substituted all instances of that identifier:

What if I have another variable with the same name in a different scope? PL/SQL Developer does a nice job of recognizing that it should not change it:

Sadly, it's not yet working at genius level. If I include in the subprogram a reference to the outer l_cursor variable, it does not change that name:

Conclusion: Works quite well, though it would be nice if I could highlight an identifier name anywhere in the program and have it do a rename (not only in the declaration statement for that variable). Could be a bit smarter, but for most "normal" scenarios, it should be fine.

Extract procedure

This is one of the most important refactorings: take logic out of your executable section and "hide" it inside a local procedure – or in a schema-level procedure. So I highlighted a line of code and choose this option. I was then asked to provide the new procedure name:

After providing that name and pressing OK, I see the following:

Well...that didn't go very well. Notice that it grabbed a part of the next line (DBMS_SQL.PARSE), even though I didn't highlight it. Ugh. OK, I will do it again, with just the code from that single open_cursor line. The result looks much better; notice that the tool correctly extracted all local variables and arguments referenced in the code and now passes them in as arguments:

There is one big problem, however: the code will not compile, because the subprogram was inserted before the l_cursor declaration and that is invalid:

Here's a nice touch, though: if your highlighted code references local variable that are used only in that highlighted code, PL/SQL Developer will move the declaration for that variable out of the main program and into the local module. In other words, from this:

to:

Conclusion: Disappointing overall. It's got the right idea and has been pretty-well thought through, but has at least two fairly serious flaws. Also, SQL Developer offers the option to define locally or store as a separate subprogram, which PL/SQL Dev does not.

Extract local constant

Here's what the help doc says about this one:

"If a certain expression should be converted to a local constant, you can select it and provide the constant name. A local constant will be created within the current subprogram, of a type that is determined from the expression. All occurrences of the expression in the current subprogram will be replaced by the constant name."

All righty – let's try it out. I will create a constant for the call to the open_cursor function:

And the result:

PL/SQL Dev did a good job of not including the ; from the highlighted text in the value assigned to the constant. It correctly replaced the call to the function with the named constant. It did not, however, come up with the right datatype for the constant. Instead, it seems to have simply added up the number of characters in the selected text. That's just weird!

Next I will highlight the local variable and change it to a constant:

The result is not great:

It seems to me that it should have left the original declaration of l_cursor alone. The result is code that will not compile.

Conclusion: marginally useful refactoring.

Extract global constant

PL/SQL Dev help tells us the following about this feature:

"If a certain expression should be converted to a global constant, you can select it and provide the constant name. A global constant will be created within the current package, of a type that is determined from the expression. All occurrences of the expression in the current package will be replaced by the constant name."

So what does this do? Well, if I use it inside my procedure, it acts the same as with a local constant extraction. I would have thought it should prompt me for the package name and either move the variable to that package or create a new package with that name.

Next I tried in a package:

and it did the right thing, sort of:

It still does not work properly with datatypes, though. It just defines them as VARCHAR2 all the time.

Conclusion: same as for local constant....minimally useful.

Swap assignment

A very minor "refactoring". Use it to change an assignment of this form:

a := b;

b := a;

It's fairly smart. You don't have to highlight the entire assignment statement, just a part of it, and PL/SQL Dev will move everything on the right to the left.

Conclusion: not really a refactoring, but it could be a useful accelerator on occasion.

Replace assignment with initialization

The help doc says:

"If a local variable assignment is purely for initialization, you can move it to the declaration of the variable. You can right-click on a statement or select multiple statements."

So I started with this code:

When I clicked on the variable name, the identifiers lit up:

I then chose my refactoring option

and ended up with this:

That's nice...though I have one concern and one desire:

Concern: the change is valid, but it could cause problems. When an exception raised in the declaration section, it cannot be handled within that block's exception section. The exception always propagates unhandled to the outer block. So making this change could affect program behavior.
Desire: This would be even nicer if it was combined with the option to move the assignment to an initialization procedure not simply to the declaration statement.

Conclusion: mildly useful, but sometimes not desireable due to the way exception propagation works in PL/SQL.

Overall Conclusion

Refactoring in PL/SQL Developer is minimal, but useful. It's done in a fairly smart way. Now if they would only give me a key stroke to pop up the refactoring menu, rather than having to make my way down through two levels of right click menu options.

More ...

Tags: plsql,refactoring

Category: PL/SQL Obsession

Automatic refactoring in PL/SQL tools – Part 1

Steven Feuerstein — Tue, 09 Dec 2008 19:23:00 GMT

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.

Oracle recently released Version 1.5.1 of SQL Developer, its free development tool for PL/SQL developers. I have been watching SQL Developer since Oracle first brought it into the world, seeing as (a) I am obsessed with PL/SQL; and (b) Oracle has had a checkered history when it comes to producing decent development tools for us PL/SQL developers; and (c) I work for Quest Software and of course we are keen to see how SQL Developer stacks up with Toad and SQL Navigator.

My overall assessment of SQL Developer has been: it's a fine, clean, light development environment – with lots of room for growth and catching up. That is, for a free product that is relatively new, it is a nice effort. Not too surprising, since it leverages the JDeveloper framework, into which Oracle has invested lots and lots of resources.

Would I use it instead of Toad or SQL Navigator or a number of the other tools that have been out there for awhile? Not unless my budget consisted of $0 for PL/SQL development tools. As with any piece of software, it will take time for SQL Developer to mature and fill out its feature set.

The SQL Developer team is certainly not standing still, however, and with Version 1.5, they have added several new features. One feature that caught my eye in particular was support for automated refactoring.

" Refactoring? What's that?" some of you may be wondering. You can see lots of information at http://www.refactoring.com/, but briefly (and from that site):

" Refactoring is a disciplined technique for restructuring an existing body of code, altering its internal structure without changing its external behavior. Its heart is a series of small behavior preserving transformations. Each transformation (called a 'refactoring') does little, but a sequence of transformations can produce a significant restructuring. Since each refactoring is small, it's less likely to go wrong. The system is also kept fully working after each small refactoring, reducing the chances that a system can get seriously broken during the restructuring."

The bottom line is that it is a highly structured methodology for going through clearly defined steps (each ideally verified by a pre-defined regression test) to make your code more maintainable.

Automated refactoring is a process that applies well-established and accepted refactorings automatically to your code. IDEs for object oriented languages have offered automated refactorings, albeit very simple ones, for a while. Now, SQL Developer offers this capability.

Exciting! So I decided to take a look. After installing SQL Developer 1.5, I opened up a program definition in an editor window, highlighted some code and checked out the right click menu. There it is!

Interesting....but what are they? I dipped into Help just to see what I could find about these and found....very little. Just a window on Extract Procedure. So let's start with that first. SQL Developer tells me:

"This dialog box is displayed if you are editing a procedure, select one or more PL/SQL statements, right-click, and select Refactoring, then Extract Procedure. The selected statements are encapsulated into the procedure to be created.

"Defined Locally: For a standalone procedure, defines the newly refactored code in the definition section of the original procedure.

"Stored: For a standalone procedure, defines the newly refactored code in a new standalone procedure.

"Name: Name of the procedure to encapsulate the selected statements. For a packaged procedure, the newly extracted procedure text is placed immediately after the current procedure."

Now, I really like this. I do. I believe that encapsulation or information hiding is one of the most important factors in writing maintainable code. And I believe in particular that we should make extensive use of locally-declared modules to do this.

So I highlight the line of code I want to encapsulate (the open cursor statement). I then, for starters, ask to create it as a schema-level procedure:

Type in the name, press OK, and I see this:

So far...OK, but just OK. Notice that it does not recognize that my line of code references a parameter from the main program. It would seem to me that the refactoring process should automatically create an argument for this procedure. This code isn't even valid. But I press Yes, and the box goes away, fine, but then I see that the original code is still present in my procedure:

That's no good – I wanted to replace it with the new stored procedure. I check the browser and find that this procedure has not been created in my schema:

Now maybe that's because there was an error in the code (un-declared referenced to security_level_in), but I didn't get any notification. OK, I will try it again with code that will compile, but still I see nothing. Well, I have no idea what is going on here, but really what I want to do is create a local module, so let's try that.

I highlight the code, choose my refactoring option, choose Local:

I type in the name of my new local module and press OK. I then see this:

Ugh. That's no good. It's supposed to be a local module. SQL Developer has placed this code above and outside of the use_cursor procedure. I then have to move it inside to make it work.

Bottom line, SQL Developer developers: nice idea, but your implementation leaves much to be desired. I don't see how I can use this to help me develop more modular, maintainable code any more easily than if I cut and paste myself.

Let's check out the other refactoring options. "Rename Local Variable..."

Hey, I like the sound of that! All too often we come up initially (in the heat of development) with bad variable names like "d" or "l_integer." Yuch! So the ability to be able to provide a new name and have the product sweep through my code and replace all instances...well, that would be pretty darned cool. Let's give it a try; in the program below I reference l_cursor four times:

There's no help for this feature, so I figure I will highlight the variable name and then specify a new one that indicates that this is a dynamic cursor:

Press OK, and it worked very nicely:

Excellent! Now I will add a reference to a package variable with the same name:

and try to do another renaming. This last addition should not be changed, as it is not the local variable. OK, I highlight the variable inside the code and try to change it, but I get this message:

Hmmm. I guess I need to highlight the declaration itself, though that shouldn't be necessary. OK, I do that, ask to rename variables to abc and sadly I see this:

So the rename feature is nice enough, but if it is not scope-aware (that is, if it cannot distinguish between locals, globals and even program names – yes, in another test, it even replaced the name of a packaged program that happened to have the same name as the local variable), then it really isn't doing anything better than a search and replace.

It also lets me change the variable name to something that is not a valid identifier, such as 123. If this feature is really directed at changing variable names, it should be aware of the rules and at a minimum put invalid names inside double quotes.

Finally, I tried to change a local variable's name from abc to something with about 100 characters in it and got this message:

Not sure what this means, but it certainly isn't helpful.

So I have to rate this feature a marketing-inflated misnomer. Sorry, SQL Developer developers!

And the third refactoring:

This lets you toggle between different case formats. It seems to work well enough. Honestly, however, I believe that it is not appropriate to include something like this under the Refactoring banner. It is a simple key driven formatting feature.

In conclusion, I am very pleased that the SQL Developer has introduced automated refactoring to the PL/SQL community. This is a concept and capability which is badly needed. The implementation in Version 1.5, however, leaves much to be desired.

Next/soon, I will take a look at PL/SQL Developer's refactoring capabilities.

More ...

Tags: plsql,refactoring

Category: PL/SQL Obsession

Congratulations to the newest Oracle Magazine PL/SQL Developer of the Year!

Steven Feuerstein — Mon, 03 Nov 2008 15:03:00 GMT

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:

Developer works with 250,000 lines of PL/SQL code that deliver success.

Successful PL/SQL developers require more than just good technical knowledge, according to Alex De Vergori, Oracle Magazine’s PL/SQL Developer of the Year.

“They also need fine attention to detail, a passion about what they’re doing, and a high degree of pride in the software that they’re producing,” says De Vergori, a database architect who channels those skills at Betfair, an online gaming company that pioneered the concept of betting exchanges in 2000.

To manage customers’ bets, De Vergori and the Betfair team have created a core betting engine and business logic that encompass more than 250,000 lines of PL/SQL code. The Betfair solution runs on Oracle Database using distributed databases around the world and an Oracle data warehouse running Oracle Real Application Clusters. Betfair also uses Oracle Coherence for its in-memory distributed data caching requirements, Java for its middle tier, and Ajax for its front-end applications.

“We take in excess of five million transactions a day, and they all go straight through to the database, where those bets match with other transactions in real time,” says De Vergori. “At peak, we can easily see something like 1,000 bet transactions per second. With Oracle, our growth has been exponential, so that has enabled us to succeed in our market.”

Click here if you would like to read more details about how Betfair is using PL/SQL.

Alex, welcome to the "select circle" of PL/SQL Developer of the Year awardees. Now, finally, you understand why you were spending all those long days, nights and weekends writing PL/SQL code!

More ...

Tags: plsql

Category: PL/SQL Obsession

Play games to become a better developer!

Steven Feuerstein — Tue, 28 Oct 2008 16:23:00 GMT

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.

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

[ Note that I do not include Solitaire in this list. Playing this game will definitely not help you become a better developer, but it will pass the time. ]

I even encourage you to play these games on company time, with management approval. It will be a good investment by your employer. Sound crazy? I have been told by a number of my students that their manager did, in fact, agree to do this, and everyone is happy with the results. I will come back to this point at the end of this blog.

So the game featured in this blog is Mastermind. Mastermind has been around for decades, usually played on a small plastic box filled with holes into which pegs of different colors are placed. You can find lots of electronic versions of Mastermind on the Internet. Here's the one I used in my blog:

http://www.irt.org/games/js/mind/index.htm?b=8&t=8&c=4

So how does it work? You have to guess the colors of the pegs in each of the positions, with the smallest possible number of guesses. Each time you make a guess, Mastermind (the electronic version) tells you how close you are by providing clues (when playing with another human being, they provide the clues – and hopefully they don't make any mistakes).

There are two kinds of clues:

You got the color and the position right. Usually this clue is indicated with a black peg.
You got the color right, but the position is wrong. Usually this clue is indicated with a white peg.

When you get four black pegs, you have won that round.

The game is more or less difficult depending on:

the number of positions
the number of choices of colors (including or excluding blanks)
whether or not you can repeat colors in the solution

You could, of course, simply guess each time, in which case you will be very lucky (and only lucky) to actually ever win. But the point of the game (at least, the way that I understood the point of the game) is to use logic to narrow down the choices that are valid based on the clues provided.

Let's walk through a game of Mastermind. In this version, we will not allow blanks nor repeated colors. Here's the starting point:

First try: it's luck and random selection. In other words, one guess is as good as any other (this first turn is the only time at which this statement is valid)....so I will simply choose the first four colors in the order shown in the game.

Then I press OK and Mastermind shows me how close I got. One white clue.

Not very close! So only one of these colors is in the solution. This is a good start, precisely because there is so little information. Once I figure out which of these colors is in the solution, I know that the other three are not.

What shall I do next? Pick the color that I will assume is in the solution, and move it to another location so that, theoretically, it could then result in a black clue.

I will make the smallest possible effort here, and move black over one. I will then use the next three colors on the second row:

Time to verify my second guess against my first clue. I have only color repeated from the first guess, and it is in a different position. OK, this could be right. Wish me luck....press OK..and:

Not bad at all! Now Mastermind is telling me that two of these four colors are in the solution and they are in the right place. One other color is also in the solution, but is in the wrong place.

Time to make another guess. Let's assume that red and black are the black clues and white is in the wrong place. We already know that green, brown and purples are not in the solution (if my assumption about black is correct), so what can I use for the fourth color? There is just one left: yellow.

Where should I put the yellow and white? I have already assumed that red and black are in the right positions. So that means that white would have to be in the fourth position (I don't have three black clues), and yellow in the third.

This is getting suspenseful! Press OK and....

Hmmm. I seem to have taken a step backwards. Instead of two black clues, I have just one. OK, it is time now to stop and take stock.

What can I now conclude about the solution, given these three sets of clues? Quite a lot, namely:

A fact	How do I know?
The white is in the third position or the blue is in the fourth.	I repeated red and black and lost a black clue. Therefore, one of the two on the right half must have been the source of the black clue.
Both black and red cannot be in the solution.	If black is in the solution, then green, brown and purple are not. If red is also in the solution, then only one of white and blue can be in it. If white is in it, then the fourth clue tells me that yellow cannot be in it. I have now exhausted all the colors and we do not have a fourth color. That's not possible. So it's either red or black, but not both.
White, blue and yellow must be in the solution.	The second guess has three clues, so three of the four must be in the solution, but only one of red and black can be, so the other two must be. The third guess also has three clues and again, this means that the two that are not red and black must be in the solution, so yellow must be in it.

Now I have enough information to make the smallest numbers of assumptions and produce a solution. If this works, we win. If it doesn't, I will get lots of additional data from which to work (that is, it will show me that an assumption was invalid, forcing me to re-evaluate and take a new path).

Let's keep going with black in the second position; I haven't yet encountered any reason to think it is not there. That accounts for the one black clue from the second turn. Let's now go with white in the third position. Blue must then be in the first position, and yellow in the fourth.

I then go back and make sure this last guess is consistent with all previous guesses and clues:

Guess/clue 1: Only black is repeated from the first turn and it is in a different position, so guess 4 could be correct based on this clue.
Guess/clue 2: The black and white colors are in the same position and the blue is in a different position, so guess 4 could be correct based on this clue.
Guess/clue 3: The black is in the same position, but yellow and white are now in two different positions, and I added in a different color. Guess 4 could be correct based on this clue.

OK, I have verified that this guess could be correct. So how did I do? Press OK and....

I did it!

Now, that's very satisfying, but I must also stay humble. I was lucky enough to make the correct assumption about black in the very first turn. I could easily have decided to assume that green was the correct color, and then spent several turns making guesses before I discovered I was wrong.

Regardless, at each step of the way, I used deductive logic to extract all possible information from the clues, and use them to construct another guess that is consistent will previous clues.

So what does this have to do with software?

Isn't it enough fun all by itself?

Sure, but I did claim that playing this game would help you become a better developer. So I really should explain. Here goes....

I claim that playing Mastermind a lot and getting really good at it will help you debug your code more quickly and accurately.

Conversely, if you have trouble "mining" conclusions from clues in Mastermind, I believe that you would find it very difficult, perhaps even torturous to debug your (or anyone else's) code.

Why do I say this? Well, let's walk through how we build our code.

I am handed requirements. I write a first pass at the code to implement those requirements. I get my code to compile (wow!). Then (theoretically), it is time to test my code.

So I run my test, and I get "clues" – my test (hopefully a regression test that exercises my code for a variety of input scenarios) tells me the circumstances of failure (When I pass in "ABC", I get 1 – the right answer. When I pass in "123", I get NULL – the wrong answer).

I might also trace execution of my code, giving me lots of additional clues.

I then take all this information (forget about up to four conveniently colored clues – I will usually get many more pieces of feedback then that!), use it to guide my debugging session, and identify changes to the code that I believe will result in a correct program.

That's my next "guess." I then rerun my tests, get my next set of clues, and on I go.

Now, doesn't that sound just like the process of finding a solution to Mastermind, only way more complicated?

So if you have trouble playing Mastermind, I've got to conclude that you will more easily get lost in the tangle of code and clues you get when testing. And if you can play Mastermind very well (which means, by the way, that you can also rapidly get to a solution even when duplicate colors and blanks are allowed, greatly increasing the number of possible solutions and requiring more sophisticated strategies for solving the puzzle), I am certain you will find it that much easier to juggle larger amounts of test feedback and apply all of that productively to your code.

More ...

Tags: plsql

Category: PL/SQL Obsession

Play games to become a better developer!

Steven Feuerstein — Mon, 20 Oct 2008 14:01:00 GMT

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.

[ Note that I do not include Solitaire in this list. Playing this game will definitely not help you become a better developer, but it will pass the time. ]

So the game featured in this blog is Set. Set is a "family game of visual perception," according to the creator (lots more details at www.setgame.com). I think it is better described as a subtle and sophisticated pattern analysis/matching game.

Set is a physical card game, though you can play the daily puzzle here. They used to sell a software version of the game, which I will be using in this blog, but it is no longer available.

So how does it work? You lay out twelve cards from the deck on the table of your choice. Here we go:

So, the object of the game is to find sets of three cards. Whoever finds the most sets wins the game.

You are now wondering: how do I know which three cards go together in a set? Here are the rules:

Each card has four characteristics: shape, coloring, number, and filling.
Each characteristic has three possible values. Oval, squiggle and diamond for shape; green, red or purple for color; one, two or three objects for number; lined, solid and empty for filling.
Three cards make up a set if each characteristic, independent of the others, is all the same or all different.

An example of a set: all reds, all ones, oval-squiggle-diamond and lined-empty-solid.

Three cards that do not make up a set: red-red-red, two-one-three, solid-solid-solid, and oval-diamond-oval. Two ovals and one diamond are neither all different or all the same.

In other words, two out of three is a no-go in Set.

Note that you can never have all four characteristics the same (they would then be the same card, and the Set deck contains no repeats). You can, however, have a set in which all characteristics are different. I find this to be the challenging and satisfying set to find.

OK, let's use deductive logic to find ourselves a set. Notice the two cards in the middle of the top row?

They could be in a set (any two could, in fact!). Let's see if we can figure out what the third card would have to be. Then if we are lucky enough to have that card on the table, I will have my first set.

The first two cards:

Each have two items. So the third card has to have two items, too.
Are green and red. So the third card would have to have a different color: purple.
Are solid and lined. So the third card would have to have a different filling: empty.
Are oval and squiggle. So the third card would have to have a different shape: empty.

So the third card would have to be: two-purple-diamond-empty.

Can you see it?

There it is on the bottom row, third column!

Now, if we were sitting around a table together, I would shout: "Set!" and if did so before anyone else, I would scoop up my set. If someone else said "Set" first, they could then take that or a different set, if they had found one (I don't think there is one).

So those three cards go into my pile and we put down another three cards:

And the game proceeds this way until all sets have been found.

Can you identify the (at least) three sets in the above twelve cards?

There are two different sets using and two different sets using . Can you find them? See answer at end of this entry.

So that is Set. When you first start to play this game, you will likely follow the path to a solution I showed above; namely, use deductive logic to find the third card given the first two.

You probably, however, will not play this way very long, at least not if you are playing (competing) with others.

In Set, stepping your way through a logical proof ("If the first two are red, the third must be red.") is too slow. You will lose again and again. Not to wrry! After you have been playing for a while, your brain will of its own accord (whatever that means) abandon explicit deductive logic. Instead, your eyes will roam over the entire set of twelve cards and the three cards that make up a set will "appear." Your brain will have by this time trained itself to assimilate the various kinds of input and resolve the problem very rapidly.

How does Set help software developers?

So what's the connection to software? There is a movement in the world of software called "Design Patterns." Here's the basic idea: there's nothing new under the sun; any code you write today is likely very similar to what you or someone else wrote yesterday. Most of our code, in other words, follow common patterns, because we are mostly solving the same kinds of problems, albeit described differently in different domains, over and over again.

So if you can abstract out the pattern, and then provide a similarly generic solution to the pattern, the next time you encounter a problem that fits the pattern, you can immediately apply the general solution, and work simply on fitting it precisely to the specifics of your current challenge.

In other words, lots of software development is all about pattern analysis and recognition, followed by taking logic-based action in response to that pattern.

The better you are at identifying patterns, the better you will be at writing elegant software that avoids the repetitions of logic that invites bugs and mitigates against maintainability.

And that is why I encourage playing this game while you are on company time. In fact, I suggest that team leads or managers organize a weekly Friday pizza lunch during which you play Set as a group. Great team builder and brain builder!

Is Set only good for work?

Ha! No way. In fact, if you have children under the age of twelve or thirteen – anyway, at an age when they are still willing to play a game with you, order Set now (you can find locations in the U.S. that sell Set right here) and start playing it with your kids.

I bet a child as young as four could get started (perhaps with only two colors, or some other way of simplifying the rules). The sooner the better – Set will help make your child smarter!

The other nice thing about playing Set with your kids is that it is very likely that once they get the hang of it, they will regularly and decisively defeat you. Their minds are so nimble, probably devoting their full concentration to the game, while yours is jumbled full of bills to pay, what to cook for dinner, and so on, that you will spare only a part of your total (and totally awesome) brain power.

And that's OK. Kids generally don't get to control too much of their environment. To know that you are trying hard as you can and still lose to them, well, that is very empowering. Great for kids when they're young, perhaps a point of regret when they become teenagers.

In conclusion, I urge you to get your copy of Set today. Play it at home with your family and friends. Play it at work with your co-workers. Make your brain strong and help make the world a far better place than it is today.

The Answers

Two with

Green-purple-red

One-one-one

Squiggle- squiggle- squiggle

Lined-lined-lined

One-two-three

Purple-purple-purple

Lined-lined-lined

Squiggle- squiggle- squiggle

Two with

Same as before, namely:

Green-purple-red

One-one-one

Squiggle- squiggle- squiggle

Lined-lined-lined

And

Green-purple-red

One-one-one

Squiggle- oval-diamond

Lined-solid-empty

More ...

Tags: plsql

Category: PL/SQL Obsession

Doing SQL in PL/SQL: key resource from Bryn Llewellyn

Steven Feuerstein — Wed, 08 Oct 2008 17:09:00 GMT

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.

The bad news about this talk is that most PL/SQL developers will never be able to hear Bryn present it.

The good news is that everything he talks about (and more) may be found in a newly published whitepaper of the same name.

Simply visit the Oracle Technology Network PL/SQL Homepage and click on the hyperlink for that paper. Notice also that Bryn has published an important whitepaper on SQL injection.

Your understanding of PL/SQL will increase dramatically, as will your ability to write high quality PL/SQL programs, by reading both of these papers.

Thanks, Bryn!

More ...

Tags: plsql,oracle,sql

Category: PL/SQL Obsession

For loops or While loops to scan collections?

Steven Feuerstein — Mon, 22 Sep 2008 16:21:00 GMT

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.

Here is an example of the kind of code that requires the use of the while loop:

DECLARE
   TYPE employee_tt
   IS
      TABLE OF employees%ROWTYPE
         INDEX BY pls_integer;

   employee_cache   employee_tt;
BEGIN
   /* Fill the collection using the employee ID
      as the index value - most like these values are
      NOT sequentially defined; primary keys can
      certainly have "gaps". */
   FOR rec IN (SELECT   *
                 FROM   employees)
   LOOP
      employee_cache (rec.employee_id) := rec;
   END LOOP;

   FOR indx IN 1 .. employee_cache.COUNT
   LOOP
      DBMS_OUTPUT.put_line (employee_cache (indx).last_name);
   END LOOP;
END;
/

When I execute this block, I get the following error:

Instead, I should use a while loop:

DECLARE
   TYPE employee_tt
   IS
      TABLE OF employees%ROWTYPE
         INDEX BY pls_integer;

   employee_cache   employee_tt;
   l_index           pls_integer;
BEGIN
   /* Fill the collection using the employee ID
      as the index value - most like these values are
      NOT sequentially defined; primary keys can
      certainly have "gaps". */
   FOR rec IN (SELECT   *
                 FROM   employees)
   LOOP
      employee_cache (rec.employee_id) := rec;
   END LOOP;

   l_index := employee_cache.FIRST;

   WHILE (l_index IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (employee_cache (l_index).last_name);
      l_index := employee_cache.NEXT (l_index);
   END LOOP;
END;
/

and then I will not see any errors.

So, no doubt about it, this is good advice – but should you always use the while loop?

I suggest that in situations when you know, when you are absolutely sure, that your collection is densely-filled, you should use the FOR loop, for two reasons:

It is a simpler solution, requiring less code (reducing the chance of a bug creeping into your code and making it easier to maintain).
It is more self-documenting. By using the for loop construct, you are stating that an assumption of this code is that the collection is densely-filled.
It is more efficient than the while loop.

So when you can be certain that your collection is densely-filled? When....

The collection is populated by a BULK COLLECT query. In this situation, the first index used is always 1, and the collection is filled sequentially (2, 3, 4...) from that index.
The collection is a nested table assigned its contents from a MULTISET operator (UNION, INTERSECT and EXCEPT). These set level operators always fill sequentially a nested table from index value 1.

And what kind of difference in performance can you expect to see? Not a very big difference, but the FOR loop is definitely faster. I put together the script below (relying on the sf_timer package, included in the demo.zip.

DECLARE
   l_index     pls_integer;
   l_source   DBMS_SQL.varchar2a;
BEGIN
   sf_timer.start_timer;

       SELECT    text
   BULK COLLECT INTO   l_source
         FROM    all_source;

   sf_timer.show_elapsed_time (
      'Retrieved ' || TO_CHAR(l_source.COUNT) || ' elements'
   );
   --
   sf_timer.start_timer;

   FOR indx IN 1 .. l_source.COUNT
   LOOP
      NULL;
   END LOOP;

   sf_timer.show_elapsed_time ('FOR loop through collection');
   --
   sf_timer.start_timer;
   l_index := l_source.FIRST;

   WHILE (l_index IS NOT NULL)
   LOOP
      NULL;
      l_index := l_source.NEXT (l_index);
   END LOOP;

   sf_timer.show_elapsed_time ('Full collection scan with NEXT');
END;

I saw the following results:

Retrieved 3079394 elements - Elapsed CPU : 11.14 seconds.
FOR loop through collection - Elapsed CPU : .05 seconds.
Full collection scan with NEXT - Elapsed CPU : .48 seconds.

In other words, the while loop is an order of magnitude slower than the for loop. That sounds like a big deal, but it's only a difference of .4 seconds with a scan of over 3,000,000 elements. With smaller collections, you'll probably never notice the difference.

More ...

Tags: plsql,for loop,while loop,collection,bulk collect,oracle

Category: PL/SQL Obsession

Analyzing code coverage with the PL/SQL profiler

Steven Feuerstein — Wed, 27 Aug 2008 22:55:00 GMT

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 of logic that are never run

The only way to get this kind of information is to turn on the PL/SQL profiler (and/or the new hierarchical profiler delivered with Oracle11g). The profiler keeps track of each line run by the program during the profile session. You then query the contents of the plsql_profiler_data|units|runs tables and draw your own conclusions.

And therein lies the rub.

Code Tester users don't want to see lengthy reports showing which lines where executed. Instead, they want us to provide them a single number that tells them all they need to know: "75% of my program was executed." Period.

And I would like to provide that feature, I really would. But I am feeling a little bit stumped and thought I would share with you what I have done so far, and my areas of befuddlement, in hopes that you, my dear reader, may be able to help provide additional clarity.

So what's the problem? Well....to figure out the % of code coverage, I need to....

decide which lines of code I should include in the total number of possible lines that could be executed. For example: do I include comments? What about the IS, BEGIN, END keywords?
understand what lines of the code the profiler actually pays attention to, and records as having been run. I have never found the profiler data to be entirely straightforward.

And so I have decided to do some research and exploration. I created a package that contains lines of code with all sorts of line breaks to see if I could isolate how Oracle treats such code in the context of profiling.

You will find below the code for this package, followed by a utility I wrote to show profiling data in a way that I thought would be useful, and then the output from a profiling session.

Which leads to my questions for you:

Do you have any ideas on how I should go about computing this single, golden number regarding code coverage?
What lines do you think should be included or not?
Should I take a different approach in analyzing code and profiler behavior?
What do you think of the output I got from my "test" package?

Looking forward to your comments,

Steven

Package to exercise profiler

CREATE OR REPLACE PACKAGE what_is_profiled
IS
   TYPE aa1 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   TYPE aa2 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   PROCEDURE proc1 (arg IN NUMBER, arg2 OUT VARCHAR2);

   FUNCTION func1
      RETURN VARCHAR2;
      
      procedure driver ;
END what_is_profiled;
/

CREATE OR REPLACE PACKAGE BODY what_is_profiled
IS
   TYPE p_aa1 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   TYPE p_aa2 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   PROCEDURE loops (arg IN NUMBER, arg2 OUT VARCHAR2)
   IS
      val
      INTEGER;
      condition1 boolean := true;
      condition2 boolean 
      := 
      true;
      
   BEGIN
      FOR indx IN 1 .. 100
      LOOP
         NULL;
      END LOOP;
            
      FOR 
      indx 
      IN 
      1 
      .. 
      100
      LOOP
         val := 1;
      END 
      LOOP; 
      
      FOR indx IN 1 .. 100 LOOP NULL; END LOOP;

      FOR rec IN (SELECT *
                    FROM all_source
                   WHERE ROWNUM < 101)
      LOOP
         val := 1;
      END LOOP;

      FOR 
      rec 
      IN 
      (
      SELECT *
                    FROM all_source
                   WHERE ROWNUM < 101
      )
      LOOP
         val := 1;
      END 
      LOOP;
      
      WHILE (condition1 AND condition2)
      LOOP
         condition1 := FALSE;
      END LOOP;

      WHILE 
      (
      condition1 
      AND 
      condition2
      )
      LOOP
         condition1 
         := 
         FALSE
         ;
      END LOOP;
      
      DECLARE
         indx   INTEGER := 1;
      BEGIN
         LOOP
            EXIT WHEN indx > 100;
            indx := indx + 1;
         END LOOP;
      END;
      
      DECLARE
         indx   INTEGER := 1;
      BEGIN
         LOOP
            EXIT 
            WHEN 
            indx 
            > 
            100;
            indx := indx + 
            1
            ;
         END LOOP;
      END;      
   END;

   PROCEDURE conditionals 
   IS
   a 
   boolean;
   b boolean;
   c boolean
   ;
   BEGIN
      IF (a AND b OR c)
      THEN
         NULL;
         elsif
         a
         then
         null;
         else
         dbms_output.put_line ('a');
      END IF;
      
      a := case
      true
      when true
      then
      false
      when 
      false then
      true
      else
      false
      end
      ;
      a := case true
      when true
      then
      false
      when 
      false then
      true
      else
      false
      end
      ;  
      
      case when 
      sysdate > sysdate + 1
      then
      a := false;
      when 1 > 2 then
      b := false;
      when 1
      > 2   
      then
      c := false;
      else null; end case; 
   END;

   FUNCTION p_func1
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN NULL;
   END;

   PROCEDURE proc1 (arg IN NUMBER, arg2 OUT VARCHAR2)
   IS
   BEGIN
      NULL;
   END;

   FUNCTION func1
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN p_func1;
   END;
   
   procedure driver is
   l varchar2(100);
   begin
   loops(1, l);
   conditionals;
   proc1
   (
   1
   ,
   l);
   GOTO checkloop;
   <>
   dbms_output.put_line ('a');
   end;
END what_is_profiled;
/

Utility to show profiler data

CREATE OR REPLACE PROCEDURE show_lines_profiled
/*
Assumptions: the ONLY data inside the profiler tables are for a single
run of what_is_profiled.driver
*/
IS
   l_type varchar2( 10000 );
BEGIN
   DBMS_OUTPUT.put_line( 'Profiling Report' );
   DBMS_OUTPUT.put_line( '  NOT PROFILED = No profile data for this line' );
   DBMS_OUTPUT.put_line( '  ZERO RUNS    = Profiled, but TOTAL_OCCURS = 0' );
   DBMS_OUTPUT.put_line( '  LINE RUN     = This line was executed at least once' );

   DBMS_OUTPUT.put_line( 'Profile Info   Line Source');
   DBMS_OUTPUT.put_line( 
'============== ==== ==============================================================');

   FOR rec
   IN (  SELECT line, text
           FROM all_source als
          WHERE     als.owner = USER
                AND als.name = 'WHAT_IS_PROFILED'
                AND als.TYPE = 'PACKAGE BODY'
       ORDER BY line )
   LOOP
      BEGIN
         SELECT CASE
                   WHEN total_occur = 0 THEN 'ZERO RUNS'
                   ELSE 'LINE RUN'
                END
                   profile_type
           INTO l_type
           FROM plsql_profiler_data ppd
          WHERE ppd.line# = rec.line AND ppd.unit_number = 2;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_type      := 'NOT PROFILED';
      END;

      DBMS_OUTPUT.put_line(   RPAD( l_type, 15 )
                           || LPAD( rec.line, 4 )
                           || ' '
                           || rtrim (rec.text, chr(10)));
   END LOOP;
END show_lines_profiled;

Results of profiling the package

BEGIN
   DELETE FROM plsql_profiler_data;

   DELETE FROM plsql_profiler_units;

   DELETE FROM plsql_profiler_runs;

   DBMS_OUTPUT.put_line(
                         DBMS_PROFILER.start_profiler( 'What is profiled?' )
   );
   what_is_profiled.driver( );
   DBMS_PROFILER.stop_profiler;
   --
   show_lines_profiled();
END;
/
Profiling Report
  NOT PROFILED = No profile data for this line
  ZERO RUNS    = Profiled, but TOTAL_OCCURS = 0
  LINE RUN     = This line was executed at least once
Profile Info   Line Source
============== ==== ==============================================================
NOT PROFILED      1 PACKAGE BODY what_is_profiled
NOT PROFILED      2 IS
NOT PROFILED      3    TYPE p_aa1 IS TABLE OF VARCHAR2 (100)
NOT PROFILED      4       INDEX BY PLS_INTEGER;
NOT PROFILED      5 
NOT PROFILED      6    TYPE p_aa2 IS TABLE OF VARCHAR2 (100)
NOT PROFILED      7       INDEX BY PLS_INTEGER;
NOT PROFILED      8 
ZERO RUNS         9    PROCEDURE loops (arg IN NUMBER, arg2 OUT VARCHAR2)
NOT PROFILED     10    IS
NOT PROFILED     11       val
NOT PROFILED     12       INTEGER;
LINE RUN         13       condition1 boolean := true;
LINE RUN         14       condition2 boolean 
NOT PROFILED     15       := 
NOT PROFILED     16       true;
NOT PROFILED     17       
NOT PROFILED     18    BEGIN
LINE RUN         19       FOR indx IN 1 .. 100
NOT PROFILED     20       LOOP
LINE RUN         21          NULL;
NOT PROFILED     22       END LOOP;
NOT PROFILED     23             
LINE RUN         24       FOR 
NOT PROFILED     25       indx 
NOT PROFILED     26       IN 
NOT PROFILED     27       1 
NOT PROFILED     28       .. 
NOT PROFILED     29       100
NOT PROFILED     30       LOOP
LINE RUN         31          val := 1;
NOT PROFILED     32       END 
NOT PROFILED     33       LOOP; 
NOT PROFILED     34       
LINE RUN         35       FOR indx IN 1 .. 100 LOOP NULL; END LOOP;      
NOT PROFILED     36 
LINE RUN         37       FOR rec IN (SELECT *
NOT PROFILED     38                     FROM all_source
NOT PROFILED     39                    WHERE ROWNUM < 101)
NOT PROFILED     40       LOOP
LINE RUN         41          val := 1;
NOT PROFILED     42       END LOOP;
NOT PROFILED     43 
LINE RUN         44       FOR 
NOT PROFILED     45       rec 
NOT PROFILED     46       IN 
NOT PROFILED     47       (
ZERO RUNS        48       SELECT *
NOT PROFILED     49                     FROM all_source
NOT PROFILED     50                    WHERE ROWNUM < 101
NOT PROFILED     51       )
NOT PROFILED     52       LOOP
LINE RUN         53          val := 1;
NOT PROFILED     54       END 
NOT PROFILED     55       LOOP;
NOT PROFILED     56       
LINE RUN         57       WHILE (condition1 AND condition2)
NOT PROFILED     58       LOOP
NOT PROFILED     59          condition1 := FALSE;
NOT PROFILED     60       END LOOP;
NOT PROFILED     61 
NOT PROFILED     62       WHILE 
NOT PROFILED     63       (
NOT PROFILED     64       condition1 
NOT PROFILED     65       AND 
NOT PROFILED     66       condition2
NOT PROFILED     67       )
NOT PROFILED     68       LOOP
NOT PROFILED     69          condition1 
NOT PROFILED     70          := 
NOT PROFILED     71          FALSE
NOT PROFILED     72          ;
NOT PROFILED     73       END LOOP;
NOT PROFILED     74       
NOT PROFILED     75       DECLARE
LINE RUN         76          indx   INTEGER := 1;
NOT PROFILED     77       BEGIN
LINE RUN         78          LOOP
LINE RUN         79             EXIT WHEN indx > 100;
LINE RUN         80             indx := indx + 1;
NOT PROFILED     81          END LOOP;
NOT PROFILED     82       END;
NOT PROFILED     83       
NOT PROFILED     84       DECLARE
LINE RUN         85          indx   INTEGER := 1;
NOT PROFILED     86       BEGIN
LINE RUN         87          LOOP
LINE RUN         88             EXIT 
NOT PROFILED     89             WHEN 
NOT PROFILED     90             indx 
NOT PROFILED     91             > 
NOT PROFILED     92             100;
LINE RUN         93             indx := indx + 
NOT PROFILED     94             1
NOT PROFILED     95             ;
NOT PROFILED     96          END LOOP;
NOT PROFILED     97       END;      
LINE RUN         98    END;
NOT PROFILED     99 
ZERO RUNS       100    PROCEDURE conditionals 
NOT PROFILED    101    IS
NOT PROFILED    102    a 
NOT PROFILED    103    boolean;
NOT PROFILED    104    b boolean;
NOT PROFILED    105    c boolean
NOT PROFILED    106    ;
NOT PROFILED    107    BEGIN
LINE RUN        108       IF (a AND b OR c)
NOT PROFILED    109       THEN
NOT PROFILED    110          NULL;
NOT PROFILED    111          elsif
LINE RUN        112          a
NOT PROFILED    113          then
NOT PROFILED    114          null;
NOT PROFILED    115          else
LINE RUN        116          dbms_output.put_line ('a');
NOT PROFILED    117       END IF;
NOT PROFILED    118       
LINE RUN        119       a := case
NOT PROFILED    120       true
NOT PROFILED    121       when true
NOT PROFILED    122       then
NOT PROFILED    123       false
NOT PROFILED    124       when 
NOT PROFILED    125       false then
NOT PROFILED    126       true
NOT PROFILED    127       else
NOT PROFILED    128       false
NOT PROFILED    129       end
NOT PROFILED    130       ;
LINE RUN        131       a := case true
NOT PROFILED    132       when true
NOT PROFILED    133       then
NOT PROFILED    134       false
NOT PROFILED    135       when 
NOT PROFILED    136       false then
NOT PROFILED    137       true
NOT PROFILED    138       else
NOT PROFILED    139       false
NOT PROFILED    140       end
NOT PROFILED    141       ;  
NOT PROFILED    142       
LINE RUN        143       case when 
NOT PROFILED    144       sysdate > sysdate + 1
NOT PROFILED    145       then
LINE RUN        146       a := false;
NOT PROFILED    147       when 1 > 2 then
NOT PROFILED    148       b := false;
NOT PROFILED    149       when 1
NOT PROFILED    150       > 2   
NOT PROFILED    151       then
NOT PROFILED    152       c := false;
NOT PROFILED    153       else null; end case; 
NOT PROFILED    154    END;
NOT PROFILED    155 
ZERO RUNS       156    FUNCTION p_func1
NOT PROFILED    157       RETURN VARCHAR2
NOT PROFILED    158    IS
NOT PROFILED    159    BEGIN
ZERO RUNS       160       RETURN NULL;
ZERO RUNS       161    END;
NOT PROFILED    162 
ZERO RUNS       163    PROCEDURE proc1 (arg IN NUMBER, arg2 OUT VARCHAR2)
NOT PROFILED    164    IS
NOT PROFILED    165    BEGIN
LINE RUN        166       NULL;
NOT PROFILED    167    END;
NOT PROFILED    168 
ZERO RUNS       169    FUNCTION func1
NOT PROFILED    170       RETURN VARCHAR2
NOT PROFILED    171    IS
NOT PROFILED    172    BEGIN
ZERO RUNS       173       RETURN p_func1;
ZERO RUNS       174    END;
NOT PROFILED    175    
ZERO RUNS       176    procedure driver is
NOT PROFILED    177    l varchar2(100);
NOT PROFILED    178    begin
LINE RUN        179    loops(1, l);
LINE RUN        180    conditionals;
LINE RUN        181    proc1
NOT PROFILED    182    (
NOT PROFILED    183    1
NOT PROFILED    184    ,
NOT PROFILED    185    l);
LINE RUN        186    GOTO checkloop;
NOT PROFILED    187    <>
LINE RUN        188    dbms_output.put_line ('a');
LINE RUN        189    end;
NOT PROFILED    190 END what_is_profiled;

More ...

Tags: code tester,code coverage,plsql

Category: Code Tester for Oracle

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!

More ...

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.

More ...

Tags: plsql,oracle,bulk collect,cursor for loop

Category: PL/SQL Obsession

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.

More ...

Tags: plsql,oracle,os command

Category: PL/SQL Obsession

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.

More ...

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!

More ...

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.

More ...