Steven Feuerstein's Blog

Real Developers Follow Standards

Steven Feuerstein — Mon, 14 May 2012 13:06:00 GMT

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

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

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

Tags: plsql,formatter,best practice,standards,maintainability,readability,format,code templates

Category: PL/SQL Obsession

Error Management: Criticism from Abroad

Steven Feuerstein — Tue, 24 Apr 2012 15:08:00 GMT

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

Hello Steven,

I've very much enjoyed your PL/SQL articles in Oracle Magazine for many years, and it's excellent that you are evangelising about good programming style and techniques in PL/SQL including exceptions.

However (and I bet you knew a however was coming), I have a concern with the recent article about exceptions; not so much from a programming point of view but from an architectural standards point of view, especially when trying to implement parts of a service oriented architecture in PL/SQL.

My concern is that the examples you give for application defined exceptions don't fit well with architectural best practice. The examples suggest that you could use exceptions as part of the implementation of business rules with PL/SQL, and I think that's not an appropriate use of exceptions. It also tends to lead the developer to embed business rules within larger business functions (resulting in brittle coding that needs frequent change), and it would be better to abstract and centralise business rules into a more flexible business rules PL/SQL package, and handle the results of a business rule using normal conditional logic statements.

The examples given also lead the programmer to embed "magic numbers" in the code, when these should be declared as appropriate package level constants at the very least, if not queried from a configuration table or other method that would avoid code change.

My view is that exceptions should be used to handle issues that are outside the specification of the function or that the programmer "hasn't thought of" (potentially non-functional requirements) and for robustness checks. Even handling standard exceptions such as divide by zero, no data found, duplicate value can veer quite close to business rules logic and therefore the hander blocks for these exceptions should wrap the statements which could raise these exceptions quite closely to convert the exception into normal conditional logic.

Based on this, my view is that the closest that an exception should come to implementing a business rule is to raise an exception inside a business rules package where the correct business rule cannot be determined from the inputs (e. g. there isn't a "catch all" rule that returns a definitive business result).

More formally, I think application defined exceptions should be restricted to validating detailed coding "assertions", i. e. validating the domain of the input arguments to a package or function and return values (supplementing proper data typing and object definition of the parameters) and coding predicates (preconditions, postconditions, and loop invariants), especially to validate detailed functional specifications of a procedure or function and confirm internal workings are as expected. This would fit well with safe coding/formal proof techniques such as those documented by Dijkstra, Floyd and Hoare since the 1970's, and typically used with safety-critical systems developed using languages such as SPARK Ada (and any PL/SQL developer should be right at home with SPARK Ada and vice versa, given the similarity of the languages).

The standard exceptions defined and used in Oracle follow these principles.

Based on these points of view, I think PL/SQL coders generally should define no more than 10 application exceptions within any sized group of PL/SQL code (ideally less than 5) to cover general classes of exception such as "Loop invariant failed" (with details handled by some error logging logic), and the outermost exception handlers should report a more specific error based on the error stack. And very often it will be possible to handle the predicate failure by raising an appropriate standard Oracle exception.

Finally, it would be great to explore error handling and reporting in more detail in another article. I'd would very much like to know your views of the pro's and con's of various error reporting techniques alongside those touched on in the current article, e. g. UTL_FILE/TEXT_IO, DBMS_OUTPUT, DBMS_APPLICATION_INFO, AQ, or PL/SQL calls to Java Error logging packages.

And my initial response:

First, many thanks to Miles for taking the time to think about the implications of the code shown in the article and to send your thoughts to me.

Second, you raise a concern that 'examples given also lead the programmer to embed "magic numbers" in the code'. Here is an example of what I assume you are talking about:

CREATE OR REPLACE PROCEDURE

validate_employee (

   birthdate_in IN DATE)

IS

BEGIN

   IF birthdate_in >

      ADD_MONTHS (SYSDATE, -12 * 18)

   THEN

      RAISE_APPLICATION_ERROR (-20500

        , 'Employee must be at least

           18 years old.');

   END IF;

END;

Miles is absolutely correct; this example and others do contain hard-codings. And, as many of my readers probably know, I have advocated in presentations and (other) articles to do everything you can to avoid such hard-codings in your code.

So what is my excuse here? Only (and inadequately) that in the short space allotted for these articles, it is not always possible to adhere to every best practice. Having said that, I could have (and will seek to do this in future articles) included a comment or sidebar that points out the hard-coded values and warns against writing code like this in your production applications.

As to Miles' concerns about application exceptions and how they should be defined and used, I would like to invite Miles to follow up from this original letter with a bit more explanation and examples that would demonstrate clearly to my readers how his approach would change things.

More ...

Tags: plsql,error management,exception handling,plsql package,magic numbers

Category: PL/SQL Obsession

Oracle Open World 2012 - Time to submit papers!

Steven Feuerstein — Fri, 23 Mar 2012 19:41:00 GMT

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

I usually spend almost all my time at the Oracle Develop hotel, so even though I am one of 45,000 or so attendees, I still manage to meet up with lots of old friends in the Oracle world.

Oracle has put out its call for papers for OOW12; it's very competitive to have one's abstract accepted for this conference. But that's no reason to skip the submission process! If you haven't been to one of these before, it is an amazing experience.The exhibition hall alone is somewhat breathtaking (and sometimes shocking - many vendors still hire very attractive people to attract the attention of attendees. Very highbrow operations!).

You are much more likely to get your manager to approve your participation in OOW12 if your paper is accepted. And even if your paper is not accepted, you will benefit from the experience of asking yourself these questions:

What have I learned lately?
What problems have I solved that others are likely to encounter as well?
What interesting applications of Oracle technology have I come up with?

Hope to see you there (if any of my papers are accepted!),

Steven

More ...

Tags: oracle,oracle open world,oow,plsql,oracle develop

Category: PL/SQL Obsession

The Date Literal

Steven Feuerstein — Tue, 21 Feb 2012 17:50:00 GMT

Consider the following block:

DECLARE

l_date DATE;

BEGIN

l_date := '15-FEB-2011';

END;

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

ORA-01858: a non-numeric character was found where a numeric was expected

This block also performs an implicit conversion, which is generally frowned upon. We are encouraged, instead, to explicitly convert from string to date as in:

DECLARE

l_date DATE;

BEGIN

l_date := TO_DATE ('15-FEB-2011');

END;

Yes, that is better – but still will raise the same error. To avoid making an assumption about the default date format, many of us have provided that format in the call to TO_DATE, as in:

DECLARE

l_date DATE;

BEGIN

l_date := TO_DATE ('FEB-15-2011', 'MON-DD-YYYY');

END;

Then, as long as the string matches the format, Oracle will not raise an error, regardless of the default date format.

But oh that is a pain in the you know what to write, over and over again. So Oracle now (actually, since Oracle9!) supports the ANSI date literal, which means that you can assign a value to a date as follows:

DECLARE

l_date DATE;

BEGIN

l_date := DATE '2011-02-15';

END;

In other words: the keyword DATE follows by a literal string in the form ‘YYYY-MM-DD’. You have no choice in this format; it cannot be changed by changing NLS settings. In addition, the ANSI date literal contains no time portion. So if you need to specify a time other than midnight for your date variable, you will still need to fall back on code like this:

DECLARE

l_date DATE;

BEGIN

l_date := TO_DATE ('FEB-15-2011 13:14:15'

, 'MON-DD-YYYY HH24:MI:SS');

END;

But it is certainly cleaner and simpler code when you only need to assign a hard-coded literal value to a date variable or column in the database.

As I noted above, support for the ANSI date literal has been in place for quite a while, but very few Oracle technologists are aware of this feature.

More ...

Tags: plsql,dates,to_date,date

Category: PL/SQL Obsession

"Our" Error Codes : -20999 to -20000

Steven Feuerstein — Fri, 03 Feb 2012 17:54:00 GMT

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

Here's an example of calling RAISE_APPLICATION_ERROR from inside a trigger, ensuring that employees must be at least 18 years old.

BEGIN

IF :new.birth_date > ADD_MONTHS (SYSDATE, -18 * 12)

THEN

RAISE_APPLICATION_ERROR (-20701

, 'Employee must be at least 18 years old.');

END IF;
END;

For many programmers, having 1,000 error codes to choose from is more than enough, but I have met developers over the years who have "run out" of error codes. Surely, Oracle could have let us use a few more of the infinite number of negative integers "out there." And if they are not going to do that, then at least you would think that Oracle would not itself use any of these "precious" user error codes.

That is, however, not the case. Oracle itself uses RAISE_APPLICATION_ERROR in a number of its own supplied packages. But which error codes does Oracle use?

The 29 December 2011 quiz at the PL/SQL Challenge, tested players' knowledge of the different ways that errors can be raised in a PL/SQL block. As has been the case with many quizzes, it served as a launch point for an interesting discussion, which you can read here. It also led Niels Hecker, one of the top players at the PL/SQL Challenge, to figure out exactly which of those -20NNN error codes are used by Oracle. Well, to be honest, he wasn't quite able to do that. Instead he finds all the PRAGMA EXCEPTION_INIT statements that reference an error code in that range.

I offer Niels' query below, but please note that you will need access to the DBA_SOURCE data dictionary view. You can change it to USER_SOURCE, but then you will "only" identify such usages in your own code – which is useful in and of itself. I have also restricted the search to objects owned by SYS and SYSMAN, so you will want to remove that WHERE clause predicate for a wider search.

WITH data

AS (SELECT ( '^.*PRAGMA\s+EXCEPTION_INIT\s*\(\s*' -- PRAGMA EXCPETION_INIT(

|| '([A-Z0-9_$#]{1,30}|"[^"]{1,30}")' -- Exception-Name

-- comment the following line out, if the exception_name

-- and the exception code are on different lines

|| '\s*,\s*''?(-?\d{1,5})''?' -- Exception-Code

-------

|| '.*$' -- End-of-Line

)

AS rx

, (' ' || CHR (10) || CHR (13) || CHR (9)) AS trimset

FROM DUAL)

, data_source

AS (SELECT s.owner

, s.TYPE

, s.name

, s.line

, UPPER (RTRIM (s.text, (' ' || CHR (10) || CHR (13)))) AS text

FROM data d, dba_source s

WHERE REGEXP_LIKE (s.text, d.rx, 'i'))

, data_prepared

AS (SELECT ds.owner

, ds.TYPE

, ds.name

, ds.line

, ds.text

, REGEXP_REPLACE (ds.text, d.rx, '\1') AS exname

, REGEXP_REPLACE (ds.text, d.rx, '\2') AS excode

FROM data d, data_source ds)

SELECT dp.excode

, dp.owner

, dp.TYPE

, dp.name

, dp.exname

, dp.line

, dp.text

FROM data_prepared dp

WHERE (TO_NUMBER (dp.excode) BETWEEN -20999 AND -20000)

AND dp.owner IN ('SYS', 'SYSMAN')

ORDER BY TO_NUMBER (dp.excode) DESC

, dp.owner

, dp.TYPE

, dp.name;

Oracle could still use RAISE_APPLICATION_ERROR in one of its package bodies to simply raise an error in that range, without associating it with a named exception. There isn't really any way to find those, since almost all built-in package bodies are wrapped (obfuscated).

More ...

Tags: plsql,raise_application_error,dbms_standard,error message

Category: PL/SQL Obsession

Debugging "Exotic" PL/SQL Data Types

Steven Feuerstein — Tue, 10 Jan 2012 17:14:00 GMT

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

CLOB: Only the first 511 characters are shown.
BLOB: Only the first 510 bytes are shown.
XMLTYPE, ANYDATA, ANYDATASET, ANYTYPE: No value is shown.

Obviously this is counterproductive when using these data types (and they are becoming more and more common), since it's more difficult to debug the values and often one needs to write custom logging/tracing code in order to "inspect" the values.

In August 2009, my friend and Oracle Test Coach Finn Ellebaek Nielsen raised the following Enhancement Request (ER) to Oracle, in order to lift these limitations in the debugging architecture:

ER 8837708: IMPLEMENT INVOKEMETHOD COMMAND IN JDWP-BASED DEBUG PROTOCOL

I believe that it is theoretically still possible to implement this ER for 12c, but I doubt it will happen unless developers around the world make clear that this would be important to them.

So if you often use the "exotic" data types mentioned above and are frustrated with the limitations on debugging, I suggest that you add your business case(s) to the ER as soon as possible.

Note: your PL/SQL tool provider would still then need to update their software to use this enhancement before the debugging capabilities in that tool are extended, but vendors' hands are tied until Oracle implements this enhancement request.

More ...

Tags: plsql,data type,debug,JDWP,clob,blob,xml,xmltype,anydata,anydataset,anytype

Category: PL/SQL Obsession

Using CONTINUE in a PL/SQL Block

Steven Feuerstein — Mon, 19 Dec 2011 15:33:00 GMT

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

CONTINUE is especially helpful when you are executing nested loops and need to skip the remainder of the inner loop's body and continue on to the next iteration of the outer loop. Sure, you can achieve the same effect with IF statements, but you will need to declare variables (Boolean flags to indicate "execute" or "skip") and the resulting code will be convoluted, hard to read and maintain.

CONTINUE offers a much simpler, understandable solution.

The CONTINUE statement can be used only inside a loop; you cannot use CONTINUE to force termination of a block (and continue in the enclosing block). An attempt to use CONTINUE outside of a loop will raise one of this errors:

PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside a loop

PLS-00373: EXIT/CONTINUE label 'label_name' must label a LOOP statement

If you specify a named loop to which you want to continue, that loop must have been named with a valid label, as in:

BEGIN
<>
   FOR indx IN 1 .. 5
   LOOP
      DBMS_OUTPUT.put_line ('Outer: ' || indx);
      CONTINUE for_loop;

      FOR indx2 IN 1 .. 5
      LOOP
         DBMS_OUTPUT.put_line ('Inner: ' || indx2);
      END LOOP;
   END LOOP;
END;
/

You can also use CONTINUE without a label, and include a WHEN clause (just like with EXIT):

BEGIN
   FOR indx IN 1 .. 5
   LOOP
      DBMS_OUTPUT.put_line ('Outer: ' || indx);
      CONTINUE WHEN indx > 3;

      FOR indx2 IN 1 .. 5
      LOOP
         DBMS_OUTPUT.put_line ('Inner: ' || indx2);
      END LOOP;
   END LOOP;
END;
/

If you include a label after CONTINUE, then the remaining code in the current and all enclosing loops up to the loop named by that label will be skipped over. The next iteration of the named loop will be executed.

If you use a CONTINUE statement to exit a cursor FOR loop prematurely (for example, to exit an inner loop and transfer control to the next iteration of an outer loop), the cursor is closed automatically (in this way, CONTINUE works like GOTO).

More ...

Tags: plsql,continue,loop,for loop

Category: PL/SQL Obsession

Every VARCHAR2(N) Declaration a Bug!

Steven Feuerstein — Tue, 13 Dec 2011 15:55:00 GMT

This is something I say in my best practice trainings: Consider every VARCHAR2(N) declaration to be a bug – unless it's a SPOD.

What, you might ask, is a SPOD? It's the single point of definition - the one place in your code where the thing you are working with (datatype, formula, magic value, etc.) is defined or, to put it another way, hard-coded.

Tags: plsql,varchar2,SPOD,VALUE_ERROR

Category: PL/SQL Obsession

The Power of Determinism in Oracle Database 11g

Steven Feuerstein — Mon, 28 Nov 2011 21:12:00 GMT

A function is considered to be deterministic if it returns the same result value whenever it is called with the same values for its IN and IN OUT arguments. Another way to think about deterministic programs is that they have no side-effects. Everything the program depends on and/or affects is reflected in the parameter list.

Tags: plsql,function,deterministic

Category: PL/SQL Obsession

QuickTips: The Simple, Yet Powerful SUBTYPE

Steven Feuerstein — Thu, 03 Nov 2011 19:49:00 GMT

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

Tags: plsql,sub-type

Category: PL/SQL Obsession

PL/SQL Grows in Popularity in Past Year

Steven Feuerstein — Tue, 11 Oct 2011 16:04:00 GMT

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

Tags: plsql

Category: PL/SQL Obsession

Avoiding Hard-coded Values in Views

Steven Feuerstein — Thu, 15 Sep 2011 12:59:00 GMT

Nancy recently wrote to me with the following question:

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

That is a good question! And, I am afraid, there is no single really good answer.

We all know that it is best to avoid hard-codings of literals anywhere in our programs. Since it is quite likely that the value of the literal could change, hard-codings create serious maintenance problems and likely bugs when the values get out of synch.

It is bad enough when a literal value appears in multiple places in our PL/SQL code. It is even worse, however, when that same value may need to appear in PL/SQL code, SQL database objects like views, and even front-end code (Java, .Net, APEX applications, etc.).

There are a number of ways to avoid hard-codings, and each technique comes with its own pluses and minuses. Here's a quick review of the choices, with examples based on the most basic of hard-codings: values for True and False, Yes and No, etc.

As you are likely aware, even though PL/SQL and most every programming language offers a Boolean datatype with actual True/False values, SQL does not support a Boolean type. So if I want to store a True or False value in the column of a table, I need to decide what "proxy" values to use (Y and N, T and F, 1 and 0, etc.) and then use them consistently throughout my entire application.

I have a view that currently looks like this:

CREATE OR REPLACE VIEW lots_of_yes_no_v
AS
   SELECT employee_id
     FROM employees
    WHERE is_active = 'Y'
      AND ineligible_for_bonus = 'N'
      AND missed_work = 'N'
/

and I'd really like to avoid hard-coding the values. How can I do this?

Packaged Constant

Here's a very simple technique: hide the value behind a constant defined in a package:

CREATE OR REPLACE PACKAGE app_config
IS
   /* YES value */
   c_yes   CONSTANT CHAR (1) := 'Y';

   /* NO value */
   c_no    CONSTANT CHAR (1) := 'N';
END app_config;
/

With these constants defined, I can use them when writing PL/SQL code in a schema that has execute authority on that package. Example:

CREATE OR REPLACE PROCEDURE check_employee (
   emp_in IN employees%ROWTYPE)
IS
BEGIN
   IF     emp_in.is_active = app_config.c_yes
      AND emp_in.ineligible_for_bonus = app_config.c_no
      AND emp_in.missed_work = app_config.c_no
   THEN
      DBMS_OUTPUT.put_line ('Check!');
   END IF;
END;
/

The major drawback of using a packaged constant is that you cannot reference a constant directly inside a SQL statement unless that SQL statement is compiled inside a PL/SQL block. You cannot, therefore, reference a packaged constant as part of a view definition, as you can see below:

SQL> CREATE OR REPLACE VIEW lots_of_yes_no_v
2 AS
3     SELECT employee_id
4       FROM my_employees
5      WHERE     is_active = app_config.c_yes
6            AND ineligible_for_bonus = app_config.c_no
7            AND missed_work = app_config.c_no
8 /
          AND missed_work = app_config.c_no
                            *
ERROR at line 7:
ORA-00904: "APP_CONFIG"."C_NO": invalid identifier

Packaged Function

A packaged function can be called from within a SQL statement, and therefore as part of a view's definition. It can be called, of course, from within a PL/SQL block and also from other languages. So it is more or less universally accessible.

So why not hide the literal values inside the package body, as in:

CREATE OR REPLACE PACKAGE app_config
IS
   /* YES value */
   FUNCTION yes
      RETURN VARCHAR2;

   /* NO value */
   FUNCTION no
      RETURN VARCHAR2;
END app_config;
/

CREATE OR REPLACE PACKAGE BODY app_config
IS
   /* YES value */
   FUNCTION yes
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN 'Y';
   END;

   /* NO value */
   FUNCTION no
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN 'N';
   END;
END app_config;
/

With this package, I can create a view that hides the values:

CREATE OR REPLACE VIEW lots_of_yes_no_v
AS
   SELECT employee_id
     FROM my_employees
    WHERE     is_active = app_config.yes
          AND ineligible_for_bonus = app_config.no
          AND missed_work = app_config.no
/

The main problem with calling a packaged function inside a view or, more generally, a SQL statement has to do with performance. Every time the function is called, you pay the price of a context switch (from the SQL engine to PL/SQL engine, and back). This price might not be too high if the function is called in the SELECT list. If, however, you need to call it in the WHERE clause, the additional cost of the function calls could be prohibitive. Use of functions inside SQL can also cause problems with the cost based optimizer.

So packaged functions are not the generalized solution we are looking for, though it can be suitable for many of our scenarios.

Stored in Table

You can create a table to store all of your "magic values" and then join to this table to obtain the soft-coded value.

The key drawbacks to this technique is the increased complexity of your applications. In a view, for example, for every value you need to reference, you will need to join to the magic values table. You will then have to identify the row by a hard-coding of the "name" of the value. Hard-coding a name is better than hard-coding a value (the name is less likely to change), but it still leads to cumbersome and verbose SQL.

You could, alternatively, generate a package that contains all the magic values from the table, encapsulated behind constants and/or functions. Whenever a change is made to the table, you re-generate the package. But then you face the same drawbacks of using constants and functions listed above.

I offer an example of a magic values table and package generator at the end of this posting.

Application Context

Now let's look at a more complex, but almost possibly the best solution: application contexts. An application context is a name-value pair defined within a "namespace." The value of the context can be assigned using DBMS_SESSION.SET_CONTEXT. The value can then be retrieved anywhere in your session code with a call to SYS_CONTEXT ('namespace', 'context_name', 'value_name').

Generally, the context values would be set whenever a user connects to the Oracle database (this can be done with an AFTER LOGON database trigger). They would then be available inside PL/SQL program units, view definitions, and more.

The downside of using system context values is added complexity of setup and maintenance of all the "moving parts" needed to use this feature, most of which must be done by a DBA. These "moving parts" include:

Definition of a "trusted package" - the package that contains the programs to set context values. This can only be done from this package.
CREATE CONTEXT statement to define the context.
AFTER LOGON trigger to ensure that the context values are set for every session.

With all of this in place, a reference to my magic value in a view would look like:

WHERE mytab.boolean_column = SYS_CONTEXT ('app_config', 'true_value')

To follow the example, here are the steps I would take to use application contexts in my view:

CREATE OR REPLACE PACKAGE app_context_pkg
IS
   PROCEDURE set_config_values;
END;
/

CREATE OR REPLACE PACKAGE BODY app_context_pkg
IS
   PROCEDURE set_config_values
   IS
   BEGIN
      DBMS_SESSION.set_context ('app_config_ctx', 'yes', 'Y');
      DBMS_SESSION.set_context ('app_config_ctx', 'no', 'N');
   END;
END;
/

CREATE CONTEXT app_config_ctx USING app_context_pkg;
/

CREATE OR REPLACE VIEW lots_of_yes_no_v
AS
   SELECT employee_id
     FROM my_employees
    WHERE     is_active = SYS_CONTEXT ('app_config_ctx', 'yes')
          AND ineligible_for_bonus = SYS_CONTEXT ('app_config_ctx', 'no')
          AND missed_work = SYS_CONTEXT ('app_config_ctx', 'no')
/

It's verbose, but it does hide the actual value used.

For more information on application contexts, check out: http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/app_context.htm

Manual Synchronization Through Comments

If none of these choices are acceptable, then you can fall back on hard-coding of the literal values in those places where, say, a PL/SQL constant is not accepted, combined with a comment to make it easier to identify all occurrences of the value.

WHERE mytab.boolean_column = 'Y' /*YES*/

I would also put the same comment with explanation in my package, as in:

CREATE OR REPLACE PACKAGE app_config
IS
   -- YES value. If this value is ever changed, search for
   -- /*YES*/ and change the value there as well.
   c_yes   CONSTANT CHAR (1) := 'Y';

   -- NO value. If this value is ever changed, search for
   -- /*NO*/ and change the value there as well.
   c_no    CONSTANT CHAR (1) := 'N';
END app_config;
/

I know, I know - it's ugly, it relies on the behavior and diligence of individual programmers, and it could lead to problems with out-of-synch views, but at least you now, theoretically, have a way to track down all the occurrences.

Well, those are my ideas. I bet my readers have other ideas and critiques of what I wrote. So post a comment!

Magic Values Table and Package Generation

If you would like to try this approach, the following code should give you some ideas and, perhaps, a head start.

CREATE TABLE magic_values
(
   name          VARCHAR2 (1000)
, identifier    VARCHAR2 (30)
, magic_value    VARCHAR2 (1000)
, datatype      VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO magic_values
        VALUES ('Maximum Salary'
              , 'max_salary'
              , 10000000
              , 'NUMBER');

   INSERT INTO magic_values
        VALUES ('Company Name'
              , 'company_name'
              , 'Oracle Corporation'
              , 'VARCHAR2');

   INSERT INTO magic_values
        VALUES ('Earliest Date'
              , 'earliest_date'
              , '2000-10-11 12:10:00'
              , 'DATE');

   COMMIT;
END;
/

CREATE OR REPLACE FUNCTION magic_value_for (NAME_IN IN VARCHAR2)
   RETURN VARCHAR2
   RESULT_CACHE
IS
   l_return   magic_values.magic_value%TYPE;
BEGIN
   SELECT magic_value
     INTO l_return
     FROM magic_values mv
    WHERE mv.name = NAME_IN;
   RETURN l_return;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
END magic_value_for;
/

CREATE OR REPLACE PROCEDURE gen_magic_values_pkg (
   NAME_IN        IN VARCHAR2 DEFAULT 'magic_values_mgr'
, to_file_in     IN BOOLEAN DEFAULT FALSE
, dir_in         IN VARCHAR2 DEFAULT 'DEMO'
, ext_in         IN VARCHAR2 DEFAULT 'pkg'
, date_mask_in   IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS')
IS
   c_to_screen   CONSTANT BOOLEAN := NVL (NOT to_file_in, TRUE);
   c_file        CONSTANT VARCHAR2 (1000) := NAME_IN || '.' || ext_in;

   /*
   Array of output for package. First I fill up the array,
   then I dump it out to the requested location (screen or file).
   */
   TYPE lines_t IS TABLE OF VARCHAR2 (1000)
                      INDEX BY PLS_INTEGER;

   output                 lines_t;

   PROCEDURE add_to_output (str IN VARCHAR2)
   IS
   BEGIN
      output (output.COUNT + 1) := str;
   END;

   PROCEDURE dump_output
   IS
   BEGIN
      IF c_to_screen
      THEN
         FOR indx IN output.FIRST .. output.LAST
         LOOP
            DBMS_OUTPUT.put_line (output (indx));
         END LOOP;
      ELSE
         -- Send output to the specified file.
         DECLARE
            fid   UTL_FILE.file_type;
         BEGIN
            fid := UTL_FILE.fopen (dir_in, c_file, 'W');

            FOR indx IN output.FIRST .. output.LAST
            LOOP
               UTL_FILE.put_line (fid, output (indx));
            END LOOP;

            UTL_FILE.fclose (fid);
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Failure to write output to ' || dir_in || '/' || c_file);
               UTL_FILE.fclose (fid);
         END;
      END IF;
   END dump_output;
BEGIN
   add_to_output ('CREATE OR REPLACE PACKAGE ' || NAME_IN);
   add_to_output ('IS ');

   FOR mv_rec IN ( SELECT *
                      FROM magic_values
                  ORDER BY name)
   LOOP
      add_to_output (
            '   FUNCTION '
         || mv_rec.identifier
         || ' RETURN '
         || mv_rec.datatype
         || ';');
   END LOOP;

   add_to_output ('END ' || NAME_IN || ';');
   add_to_output ('/');

   add_to_output ('CREATE OR REPLACE PACKAGE BODY ' || NAME_IN);
   add_to_output ('IS ');

   FOR mv_rec IN ( SELECT *
                      FROM magic_values
                  ORDER BY name)
   LOOP
      add_to_output (
         '   FUNCTION ' || mv_rec.identifier || ' RETURN ' || mv_rec.datatype);

      add_to_output (
         '   IS BEGIN RETURN '
         || CASE mv_rec.datatype
               WHEN 'VARCHAR2'
               THEN
                  '''' || mv_rec.magic_value || ''''
               WHEN 'NUMBER'
               THEN
                  mv_rec.magic_value
               WHEN 'DATE'
               THEN
                     'TO_DATE ('
                  || ''''
                  || mv_rec.magic_value
                  || ''', '''
                  || date_mask_in
                  || ''')'
            END
         || '; END;');
      add_to_output ('   ');
   END LOOP;

   add_to_output ('END ' || NAME_IN || ';');
   add_to_output ('/');

   dump_output;
END gen_magic_values_pkg;
/

BEGIN
   gen_magic_values_pkg ();
END;
/

More ...

Tags: plsql,literals,hard-code,package,constant,function,application context

Category: PL/SQL Obsession

APEX Support in Toad

Steven Feuerstein — Tue, 23 Aug 2011 19:42:00 GMT

Quest is exploring whether to and how best to add support for Oracle Application Express in Toad for Oracle.

More and more Oracle technologists are building websites in APEX technology. It is moving rapidly from the sidelines of departmental applications to the mainstream of robust, public and private websites implementing mission critical applications involving many users. For example, my PL/SQL Challenge website, offering daily, weekly and monthly quizzes on Oracle technology, is built on APEX.

Hundreds of thousands of these same technologists also spend lots of their time in Toad for Oracle, building out the backend elements (tables, packages, etc.). We at Quest Software believe that Toad for Oracle should be able to help APEX developers understand, analyze, maintain and even enhance their applications from.

We also, realize, however, that there is an enormous range and depth of experience with APEX "out there." So rather than just come up with a list of features we think would be helpful to APEX developers, we'd like to hear from you, the APEX developer and also development DBAs supporting APEX developers, about how you think Toad for Oracle can best support your work with APEX.

You can do so by responding to this blog post. You can also email me directly at steven.feuerstein@quest.com. I will be compiling requests and requirements for consideration by the Toad for Oracle Product Manager and development team.

More ...

Tags: toad,toad for oracle,apex;

Category: Toad for Oracle

One Way Out of Functions

Steven Feuerstein — Tue, 09 Aug 2011 13:56:00 GMT

I received this request from a reader in July:

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

Actually, it's not just Oracle. Most "gurus" in the software world make the same suggestion. There are two key reasons for the recommendation that you should only return data through the RETURN clause of a function, not the parameter list. I sum up these answers here, and then I offer a section from Oracle PL/SQL Best Practices, 2^nd edition, that explores the same topic using the "story" style of that book.

Two key reasons to use only IN parameters with functions:

The whole point of a function is to return a value (whether it’s a single, scalar value or a composite, such as a record or a collection). If you also return data back through the parameter list with OUT or IN OUT arguments, the purpose and usage of the function will be obscured.
A function that contains an OUT or IN OUT parameter cannot be called from within a SQL statement. Here's an example:

Perhaps you don't often run across the need to call your own application-specific functions inside SQL. I take advantage of this feature frequently in the backend and APEX code of the PL/SQL Challenge, a site that offers quizzes on PL/SQL, SQL and APEX.

So remember: just because Oracle let's you do something (like define an OUT parameter in a function's parameter list), doesn't mean that you should do it!

And now an excerpt from Oracle PL/SQL Best Practices, 2^nd edition (Chapter 8, "Playing with Blocks"):

Where'd that data come from?
AKA: Functions should return data only through the RETURN clause.

Problem: Jasper returns data in a very confusing manner.

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

FUNCTION excuse_title (

excuse_id_in IN mfe_excuse.isbn%TYPE

, author_out OUT mfe_excuse.author%TYPE

, word_count_out OUT mfe_excuse.word_count%TYPE)

RETURN mfe_excuse.title%TYPE

IS BEGIN

... implementation unimportant! ...

END excuse_title;

And then he puts this function to use as follows:

PROCEDURE process_excuse (excuse_id_in IN mfe_excuse.isbn%TYPE)

l_title mfe_excuse.title%TYPE;

l_author mfe_excuse.author%TYPE;

l_word_count mfe_excuse.word_count%TYPE;

BEGIN

l_title := excuse_title (l_id, l_author, l_word_count);

...

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

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

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

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

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

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

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

FUNCTION excuse_info (id_in IN mfe_excuse.id%TYPE)

RETURN mfe_excuse%ROWTYPE

And here is the revised usage of this function:

PROCEDURE process_excuse (excuse_id_in IN mfe_excuse.isbn%TYPE)

l_excuse mfe_excuse%ROWTYPE;

BEGIN

l_excuse := excuse_info (l_id);

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

PACKAGE excuse_pkg

TYPE key_info IS RECORD (

title mfe_excuse.title%TYPE

, author mfe_excuse.author%TYPE

, word_count mfe_excuse.word_count%TYPE

);

FUNCTION excuse_info (id_in IN mfe_excuse.id%TYPE)

RETURN key_info;

END excuse_pkg;

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

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

Return a record or collection of values

Make sure to publish the structure of your record or collection (the TYPE statement) in a package specification so that developers can understand and use the function more easily.
Break up a single function into multiple functions, all returning scalar values

With this approach, you can call the functions from within SQL statements.
Change a function into a procedure

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

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

More ...

Tags: plsql,function,parameters

Category: PL/SQL Obsession

QuickTips: Using BULK COLLECT with Queries

Steven Feuerstein — Wed, 13 Jul 2011 12:36:00 GMT

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

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

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

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

Tags: oracle,plsql,bulk collect,forall

Category: PL/SQL Obsession

Use Forward References in Package Bodies?

Steven Feuerstein — Mon, 06 Jun 2011 19:52:00 GMT

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

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

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

I've been pushing for developers to put explicit declarations of private functions and procedures in their packages. My co-workers argue back that they are unnecessary. Other than giving us the ability to reorganize the source as we see fit, I can't find anything that states additional benefits of using them. However, as I've always used them, I feel uncomfortable not asking for them.

Here's an example of how I use them:

CREATE OR REPLACE PACKAGE BODY my_sample_package
IS
   --===========================================
   -- PRIVATE DECLARATIONS
   --===========================================
   FUNCTION my_private_function_f (p_parm1_in IN NUMBER)
      RETURN BOOLEAN;

   PROCEDURE my_private_proc_p (
       p_parm1_in IN NUMBER, p_parm2_in IN NUMBER);

   --===========================================
   -- PRIVATE SOURCE
   --===========================================
   FUNCTION my_private_function_f (p_parm1_in IN NUMBER)
      RETURN BOOLEAN
   IS
   BEGIN
      IF p_parm1_in > 0
      THEN
         RETURN TRUE;
      ELSE
         RETURN FALSE;
      END IF;
   END my_private_function_f;

   PROCEDURE my_private_proc_p (
       p_parm1_in IN NUMBER, p_parm2_in IN NUMBER)
   IS
      l_my_var   NUMBER;
   BEGIN
      IF p_parm1_in > 0
      THEN
         l_my_var := p_parm2_in / p_parm1_in;
      END IF;
   END;

   --===========================================
   -- PUBLIC SOURCE
   --===========================================
   -- Blah
   -- Blah
END my_sample_package;

Can you give me any reasons why or why not private declarations should be used?

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

What Dan calls a "private declaration" is commonly referred to as a forward reference, also known as a forward declaration, which is defined by Wikipedia as:

In computer programming, a forward declaration is a declaration of an identifier (denoting an entity such as a type, a variable, or a function) for which the programmer has not yet given a complete definition.

As you can see in the code example, the section under "PRIVATE DECLARATIONS" contain the forward declarations: just the headers of the subprograms without their implementation, same as you would find in the package specification.

I do not use forward declarations in my code in any sort of routine way. I only use them when they are required - and that happens when two programs are inter-dependent (A calls B and B calls A). In this scenario, my code will not compile without forward declarations. But that is quite the rare occurrence.

The problem with using forward declarations routinely is that it introduces another kind of redundancy into my code. When and if the parameter list of a private subprogram changes, I have to change it in both places (forward declaration and actual definition of the subprogram). I'd really rather not have to do that if I do not need to.

So instead I rely on the compiler to tell me when my subprograms are "out of order", and I cut and paste the entire subprogram to an earlier point in the package body to make sure that all references can be resolved.

Well, that's my viewpoint on forward declarations. What do you think?

More ...

Tags: plsql,forward declaration

Category: PL/SQL Obsession

Write Tiny Little Chunks of Code - or Maybe Not

Steven Feuerstein — Fri, 27 May 2011 21:51:00 GMT

I have for many years now promoted "Write tiny little chunks of code" as one of the most impactful and important steps we can all take to improve the readability and maintainability of code. The basic idea is that you should not let your executable sections get to be more than 50 or 60 lines of code in length. You should, in other words, be able to at a single glance take in the full algorithm/logical flow of the subprogram. Compare that to a procedure whose executable section goes on and on for hundreds of lines. Sure, you can page through all that, but how easy is it to step back and gain an overall understanding of what the module does?

The best way to achieve this goal is to modularize one's code: hide detailed logic behind a name and then reference that logic by calling the procedure with that name. The name describes what is going on "under the covers"; you only drill down into the procedure as needed.

You can modularize at any of thse levels:

Schema-level procedure or function: defined outside of a package, callable by any schema with execute authority on that module. I generally recommend against these. Put all your code in packages; easier to find, manage, enhance.
Packaged subprograms: this is the main method of modularization. You define a set of programs in the package specification that will be used by other subprograms in the appliation. You can also define private subprograms that can only be called by other subprograms in that package.
Local or nested subprograms: a procedure or function that is defined in the declaration section of a PL/SQL block (anonymous or named). This module is considered local because it is defined only within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside that enclosing block.

The following anonymous block, for example, declares a local procedure:

DECLARE
   PROCEDURE show_date (date_in IN DATE) IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (TO_CHAR (date_in, 'Month DD, YYYY');
   END show_date;
BEGIN
   show_date (SYSDATE);
   show_date (SYSDATE+30);
END ;

I think that nested subprograms are one of the most powerful and underutilized techniques in the PL/SQL developer community. I use them all the time, all over the place. There are two central benefits to nested subprograms (aka, local modules):

Reduce the size of the module by stripping it of repetitive code

Code reduction leads to higher code quality because you have fewer lines to test and fewer potential bugs. It takes less effort to maintain the code because there is less to maintain. And when you do have to make a change, you make it in one place in the local module, and the effects are felt immediately throughout the parent module.

Improve the readability of your code

Even if you do not repeat sections of code within a module, you still may want to pull out a set of related statements and package them into a local module. This can make it easier to follow the logic of the main body of the parent module.

When making use of lots of nested subprograms, your executable section becomes small and transparent in meaning. I will run through an example and then leave it to my readers to respond. The reason I say this is that I wrote this posting in response to a developer, who wrote to me as follows:

"I would like to have a posting to discuss local sub programs or functions within a function or procedure. This could be a good debate because I have my issues on the subject but I was watching the "Say no to spaghetti code, say yes to Extreme Modularization" video and Steven was promoting that kind of programming."

There are certainly potential downsides to nested subprograms; you can "go overboard," as with any other technique or feature. But I certainly would be more worried about inadequate modularization and the resulting spaghetti code, rather than overdoing it.

An Example

Suppose my team is building a support application. Customers call with problems, and we put their call in a queue if it cannot be handled immediately. I must now write a program that distributes unhandled calls out to members of the support team. While there are still unhandled calls in the queue, assign them to employees who are under-utilized (have fewer calls assigned to them then the average for their department). The technical specifications run to 50 pages of detailed documentation, and I could just "start coding" - transcribing specs into code. Instead, I translate the "executive summary" into the following tiny little program.

PROCEDURE distribute_calls (
   department_id_in IN employees.department_id%TYPE
)
IS
BEGIN
   WHILE (calls_still_unhandled ())
   LOOP
      FOR emp_rec IN support_dept_cur (department_id_in)
      LOOP
         IF call_analysis.current_caseload (emp_rec.employee_id) <
            call_analysis.avg_caseload_for_dept (department_id_in)
         THEN
            assign_next_open_call_to (emp_rec.employee_id, l_case_id);
            notify_customer (l_case_id);
         END IF;
      END LOOP;
   END LOOP;
END distribute_calls;

This executable section contains calls to predefined programs in the call_analysis, but also I simply come up with names for subprograms for other areas of functionality that are not yet implemented.

I then create stubs for the yet-to-be-defined programs:

PROCEDURE distribute_calls (

   department_id_in IN employees.department_id%TYPE

)

IS

   l_case_id   cases.ID%TYPE;

   CURSOR support_dept_cur (

      department_id_in IN employees.department_id%TYPE

   )

   IS

      SELECT *

        FROM employees

       WHERE department_id = department_id_in;

   FUNCTION calls_still_unhandled

      RETURN BOOLEAN

   IS

   BEGIN



          RETURN NULL;

   END calls_still_unhandled;

   PROCEDURE assign_next_open_call_to (

      employee_id_in IN employees.employee_id%TYPE

    , case_id_out OUT cases.ID%TYPE

   )

   IS

   BEGIN

     NULL;

   END assign_next_open_call_to;

   PROCEDURE notify_customer (case_id_in IN cases.ID%TYPE)

   IS

   BEGIN

      NULL;

   END notify_customer;

BEGIN

   WHILE (calls_still_unhandled ())

   LOOP

      FOR emp_rec IN support_dept_cur (department_id_in)

      LOOP

         IF call_analysis.current_caseload (emp_rec.employee_id) <

                    call_analysis.avg_caseload_for_dept (department_id_in)

         THEN

            assign_next_open_call_to (emp_rec.employee_id, l_case_id);

            notify_customer (l_case_id);

         END IF;

      END LOOP;

   END LOOP;

END distribute_calls;

I can now drill down into each nested subprogram and do the same thing: write the "executive summary," dream up new subprograms "on the fly" and delay worrying about the implementation details until I get down to that level.

This approach lets me focus on logic flow, makes it easier to identify - proactively - opportunities for code reuse, and results in code that is so very easy to read.

I am pretty well addicted to nested subprograms, but I fear that not everyone shares my passion on this topic. So...please contribute your thoughts.

More ...

Tags: plsql,oracle,package,anonymous block,procedure

Category: PL/SQL Obsession

REPLACE and CLOBs: Watch out for "_"!

Steven Feuerstein — Thu, 21 Apr 2011 15:21:00 GMT

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

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

One of my developers just noticed a strange behavior in all versions of Oracle 10g that we could test (10.2.0.3, 10.2.0.5). Here is the scenario:

Use the REPLACE function with a CLOB variable

Replace "T_" with "ZZ"; the variables contains a string "T(", as well as several occurrences of "T_".

In the result, Oracle replaces both "T_" and "T(" with "ZZ"!

The underscore character is clearly being interpreted as a wild-card!

This behavior does not occur with VARCHAR2 and it does not happen on Oracle Database 11g Release 2.

Here's an example if you'd like to try this at home:

DECLARE

   v_clob   CLOB:='T_T(a number)';

BEGIN

   v_clob   :=REPLACE(v_clob,'T_','ZZ');

   DBMS_OUTPUT.put_line(v_clob);

END;

Result on 10g: "ZZZZa number)"

Expected result (and result on 11g): "ZZT(a number)"

Michael wondered if he'd missed something in the documentation or if this was a bug...and a search in Metalink revealed the following:

Bug 4598943

Database Version - 9.2.0.6

Fixed in Product Version- 11.0.0.0.0

Workaround: None

So watch out for using REPLACE with clobs prior to 11g - you may not get the results you expect!

More ...

Tags: oracle,11g,10g,plsql,clob,replace

Category: PL/SQL Obsession

QuickTips: Using FORALL with DML Statements

Steven Feuerstein — Wed, 23 Mar 2011 12:28:00 GMT

This blog is the first in a series of "QuickTips" - a collection of advice and guidance on specific features of the Oracle PL/SQL language. The purpose of a QuickTip is not to provide a comprehensive resource on the topic, such as full description of syntax, etc. Instead, I will point you to Oracle documentation for such things, and focus instead on some "added value" content.

I will inaugurate the series with FORALL, undoubtedly one of the most important features of the PL/SQL.

I assume that you are running an instance of Oracle Database 10 Release 2 or higher.

Tags: oracle,plsql,bulk collect,forall

Category: PL/SQL Obsession

Curious PL/SQL Code

Steven Feuerstein — Tue, 15 Mar 2011 18:35:00 GMT

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

DECLARE
   l_char   CHAR (1);

   CURSOR x
   IS
      SELECT dummy INTO l_char FROM DUAL;
BEGIN
   OPEN x;

   FETCH x INTO l_char;

   CLOSE x;
END;

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

Cheers, Steven

More ...

Tags: plsql

Category: PL/SQL Obsession

Take Care of Your Eyes!

Steven Feuerstein — Wed, 23 Feb 2011 00:04:00 GMT

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

Unfortunately, since then I have come to realize and accept that Lasik is not a panacea for all eye problems (not that I ever really thought that was true). In the past year or so, I have noticed my eyesight degrading a bit, and also that my eyes were getting really sore by the end of the day.

Gee, big surprise, considering that I spent 6-8 hours a day in front of one screen or another (laptop, LCD monitor, etc.). Surprise or not, this is a serious problem, since I see no prospects of escaping from massive amounts of screen time for at least a couple of years.

So what's a developer to do? Leaf through a Skymall magazine, of course!

On a recent flight, I noticed the following:

They are described as follows:

"These eyeglasses reduce eyestrain that results from viewing a computer screen for extended periods. The glasses combat the painful symptoms of Computer Vision Syndrome--at least 90% of the U.S. computer-using public is affected by the syndrome according to the National Institute of Occupational Safety and Health.

"The yellow-tinted polycarbonate lenses' proprietary tinting blocks out 65% of blue light, a wavelength that reduces the sharpness of objects, leading to eye fatigue, tired or sore eyes, and the chance of headaches. The lenses protect against UV radiation up to 400nm, protecting eyes from harmful neon and halogen light sources. An inner anti-reflective coating on both the inner and outer side of the lens decreases the glare from overhead fluorescent lighting. Intended for use by those who wear contacts or do not require corrective lenses."

I was skeptical, but my wife insisted I check them out. So I bought a pair and I must say that almost instantly I could tell the difference. They "warm up" the screen in a way that is hard to describe, but the bottom line is that my eyes do not have to strain to look at the screen and at the end of the day my eyes are not nearly so tired.

I strongly encourage you to give them (or some other similar pair of glasses) a try. I don't intend to spend any more time in front of a computer screen unless I am wearing them.

More ...

Category: PL/SQL Obsession

ORA-03113 and Getting CLOB Value from XMLTYPE

Steven Feuerstein — Mon, 07 Feb 2011 17:28:00 GMT

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

After adding an XMLTYPE column to our table, we then updated our table API packages to support retrieval of the XML document as a CLOB. You do this by invoking the GetClobVal method of the XMLTYPE object type.

Here's an example of the way we did this:

DECLARE
   l_clob   CLOB;
BEGIN
   SELECT qu_attributes.dp_value.getclobval ()
     INTO l_clob
     FROM qu_attributes
    WHERE parent_guid = 'a' AND usage_type = 'a';
END;

Sadly, when I run this block of code on 11.2 (and we've seen in on 10g as well), I get the following error:

ORA-03113: end-of-file on communication channel

That is one nasty error! And, as any of you who have encountered ORA-03113 must know, this error does not identify a problem in our code. It reflects a bug of some sort in Oracle's code.

So the question that comes to mind immediately is: what's the workaround?

It looks like the problem has to do with executing this method inside the query. I moved the call to the method into its own function:

CREATE OR REPLACE FUNCTION gcv (xmltype_in IN XMLTYPE)
RETURN CLOB
IS
BEGIN
RETURN xmltype_in.getclobval ();
END;
/

and then I called the function in my query:

DECLARE
   l_clob   CLOB;
BEGIN
   SELECT gcv (qu_attributes.dp_value)
     INTO l_clob
     FROM qu_attributes
    WHERE parent_guid = 'a' AND usage_type = 'a';
END;

Then the ORA-03113 error disappeared. So that's the workaround, folks.

If you get an ORA-03113 in a query that works with XMLType columns, check to see if GetClobVal is being called and if so, move it out to a separate function and call the function from within the query.

More ...

Tags: plsql,code tester,xml,clob,oracle,11g

Category: Code Tester for Oracle

Category: PL/SQL Obsession

Code Metric Tools for PL/SQL?

Steven Feuerstein — Tue, 11 Jan 2011 16:28:00 GMT

I recently received this email:

Hi Steven,

I am looking for tools to measure code quality and via which you can drill down to the problem areas and for tools to refactor PL/SQL.

Recently we insourced a team that is doing maintenance on an Oracle product. We are an Agile company and are now in the process of transforming the team to a Scrum team.

The other teams develop Java desktop applications and have all the means to support Agile development. As a Scrum master of the insourced team I want to stimulate the team to explore ways to improve PL/SQL code quality, small steps at a time. An important aspect of Scrum is continuous improvement. One of the bigger problems I see is code duplication.

The Java teams have tooling to collect code metrics and there is a company goal that all code must have at least 4 out of 5 "stars". In conjunction with refactoring tools it helps to maintain the desired code quality level.

I hope there are similar tools for PL/SQL.

And my response:

There are some similar tools, but generally the Java market is ahead of the PL/SQL market with tools like this. Here's a quick overview of what is available for PL/SQL development teams:

CodeXpert of Toad and SQL Navigator (Professional Editions): CodeXpert is a feature of Quest's popular IDEs that performs static code analysis (that is, the analysis does not require a connection to the Oracle database). Here are some examples of reports from CodeXpert:

CodeXpert even includes standard industry analysis, such as the Halstead Volume computation:
ClearSQL: another third party tool that provides feedback on the quality of your code, quite similar to CodeXpert. Described as follows on their website [http://www.conquestsoftwaresolutions.com/page/clearsql_at_a_glance]: "ClearSQL is a code review and quality control tool for Oracle PL/SQL. With ClearSQL's code review feature you get recommendations for better coding style and check for error-prone places in your project."
Quest Code Tester for Oracle offers the only code coverage analysis in the PL/SQL market that includes an overall percentage of lines of code executed (SQL Developer does offer some code coverage reporting but not as strong as Code Tester). All such analysis and reporting is based on the built-in DBMS_PROFILER package. The profile data is a bit messy, making very accurate analyses a significant challenge, but we do the best we can. Here is a sample report:

As for refactoring, Toad offers built-in refactoring tools, which are covered in this blog post. This screen shot gives you an idea of what is possible from the Editor; I especially like the Extract Procedure option.

Do any of my readers know of other code analysis tools that might be helpful? Please add your comments!

More ...

Tags: plsql,toad for oracle,sql navigator,code tester,refactoring,code xpert

Category: Toad for Oracle

Category: Code Tester for Oracle

Category: SQL Navigator

Category: PL/SQL Obsession

Incrementing Sequence Values

Steven Feuerstein — Mon, 08 Nov 2010 21:44:00 GMT

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

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

" apologize if this is an easy tip, but was wondering if there's a bulk way to increase a sequence without EXECUTE IMMEDIATE 'alter sequence {sequence_name} begin with ... ' ?

"I'm copying data from one table (for test data) and altering a couple of columns (namely ID_KEY and changing period value). I am doing 1.3M rows in 25K increments using BULK COLLECT, a quick for i in FIRST..LAST to reset sequence ids using from DUAL (x25,000), then a FORALL...INSERT VALUES {row}. Suggestions ? Is there a DBMS_SEQ package or anything that can take a sequence name and a numeric value ?"

I gave the email a quick glance, but did not respond; I didn't have a quick and easy answer. J

Which worked out just fine, because a couple of days later, I received this follow-up from Bryan:

"I sent you an email yesterday asking if you knew of any built-in DBMS packages that have this process. A friend at work directed me to the ALTER SEQUENCE INCREMENT property. My company is running 11g, so it appears compilation issues aren't a concern (not sure if this would work in 10g or less).

"In my example I was BULK loading 1.3M rows 25k at a time to a COLLECTION and modifying the sequence (as well as another column) using a for i in FIRST..LAST loop. Using this procedure, I was able to reduce the number of FROM DUAL calls from 1.3M to 52. I monitored the execution times and the entire original process ran in 640 seconds and the new ran in 492 for a 23% improvement."

Wow! That's quite an improvement, so I thought I would share with you the utility Bryan put together; I look forward to your comments.

CREATE OR REPLACE PROCEDURE prc_sequence_increment (
   p_in_seq_nm      IN     VARCHAR2
, p_in_increment   IN     NUMBER
, p_out_start         OUT NUMBER
, p_out_end           OUT NUMBER)
AS
   v_nbr1      PLS_INTEGER := 0;
   v_nbr2      PLS_INTEGER := 0;
   v_cmd       VARCHAR2 (32767);
   v_hold_id   PLS_INTEGER := 0;
BEGIN
   EXECUTE IMMEDIATE   'alter sequence '
                    || p_in_seq_nm
                    || ' increment by '
                    || p_in_increment;

EXECUTE IMMEDIATE 'select ' || p_in_seq_nm || '.nextval from dual '
INTO v_hold_id;

p_out_end := v_hold_id;
p_out_start := p_out_end - p_in_increment + 1;

EXECUTE IMMEDIATE 'alter sequence ' || p_in_seq_nm || ' increment by 1 ';
END;
/

More ...

Tags: plsql

Category: PL/SQL Obsession

Help Me Avoid the Weekends!

Steven Feuerstein — Mon, 01 Nov 2010 15:35:00 GMT

I received this request from Jayme last week:

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

We needed logic similar to this for the PL/SQL Challenge (you don't play the daily quiz on the weekend).

So I generalized that function and sent it off to Jayme. I thought I would share it on this blog as well.

I am quite certain there is a smarter way to get the job done. Please feel free to post your own implementation to share with others!

Steven


CREATE OR REPLACE FUNCTION proximate_weekday (
   date_in        IN DATE
 , direction_in   IN signtype DEFAULT 1)
   RETURN DATE
IS
   c_before_weekend   CONSTANT VARCHAR2 (3) := 'FRI';
   c_weekend_one      CONSTANT VARCHAR2 (3) := 'SAT';
   c_weekend_two      CONSTANT VARCHAR2 (3) := 'SUN';
   c_after_weekend    CONSTANT VARCHAR2 (3) := 'MON';
   c_day_abbrev       CONSTANT VARCHAR2 (3)
      := TO_CHAR (date_in, 'DY', 'NLS_DATE_LANGUAGE = AMERICAN') ;
   l_return                    DATE;
BEGIN
   CASE
      WHEN    c_day_abbrev IN ('TUE', 'WED', 'THU')
           OR (c_day_abbrev = c_after_weekend AND direction_in = 1)
           OR (c_day_abbrev = c_before_weekend AND direction_in = -1)
      THEN
         l_return := date_in + direction_in;
      WHEN (c_day_abbrev = c_before_weekend AND direction_in = 1)
           OR (c_day_abbrev = c_after_weekend AND direction_in = -1)
      THEN
         l_return := date_in + (direction_in * 3);
      WHEN (c_day_abbrev = c_weekend_one AND direction_in = 1)
           OR (c_day_abbrev = c_weekend_two AND direction_in = -1)
      THEN
         l_return := date_in + (direction_in * 2);
      WHEN (c_day_abbrev = c_weekend_two AND direction_in = 1)
           OR (c_day_abbrev = c_weekend_one AND direction_in = -1)
      THEN
         l_return := date_in + (direction_in * 1);
   END CASE;

   RETURN l_return;
END proximate_weekday;
/

Here's a test script to verify the behavior of the function:

DECLARE
   /* Start with a Monday */
   l_date   DATE := DATE '2010-11-01';   PROCEDURE show_dates_around (date_in IN DATE)
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Day is ' || TO_CHAR (date_in, 'Day'));
      DBMS_OUTPUT.
       put_line (
         'One day before is '
         || TO_CHAR (proximate_weekday (date_in, -1), 'Day'));
      DBMS_OUTPUT.
       put_line (
         'One day after is ' || TO_CHAR (proximate_weekday (date_in, 1), 'Day'));
   END;
BEGIN
   FOR indx IN 1 .. 7
   LOOP
      show_dates_around (l_date + indx - 1);
   END LOOP;
END;
/

More ...

Tags: plsql

Category: PL/SQL Obsession

If a Tree Falls in a Forest...

Steven Feuerstein — Mon, 18 Oct 2010 14:38:00 GMT

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

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

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

A good example of this would be the syscache package, found in the syscache.pkg file. It uses DBMS_PIPE as a proof of concept for sharing PGA-based information among multiple sessions. It is oh-so cool, but the chance of it being used is almost vanishingly small.

And so I am always very pleased, indeed, when a developer informs me that she or he is using some of my code. Such a momentous event occurred last week:

From: Pepijn Slappendel
Subject: Using the general parse package

Hello Steven,

Recently I have created a PL/SQL utility which can be used to Query data from Palo-cubes. Palo is an open-source cube-based BI Solution. The utility uses your generic parse package, which makes things very easy! We have now published the utility to the Palo-community on sourceforge.net, including the parse package. The files are stored at the following url: http://palo.svn.sourceforge.net/viewvc/palo/contrib/scamander/

Met vriendelijke groet,
Pepijn Slappendel

For any of you wondering how to get your hands on this parse package, it may be found in the parse.pkg file in demo.zip.

Thanks for informing me, Pepijn! I hope that lots of developers benefit from your contribution to the PL/SQL open source community.

And if anyone reading this post has a similar story to tell, please get in touch.

More ...

Tags: plsql,oracle

Category: PL/SQL Obsession

"Insufficient Privileges" and DBMS_ERRLOG

Steven Feuerstein — Mon, 11 Oct 2010 14:02:00 GMT

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

Tags: oracle,plsql

Category: PL/SQL Obsession

Helping the CBO "cost out" SQL with PL/SQL functions

Steven Feuerstein — Thu, 30 Sep 2010 19:52:00 GMT

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

Tags: plsql

Category: PL/SQL Obsession

Using Conditional Compilation to Enforce Coding Guidelines

Steven Feuerstein — Wed, 15 Sep 2010 19:37:00 GMT

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

Tags: plsql,oracle

Category: PL/SQL Obsession

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