Why no CONTINUE command in PL/SQL?
Question:
Is there any CONTINUE command in PL/SQL (the counterpart of break, which exits from a loop if a condition is met) like the one in the C programming language?
Answer:
I've often wondered the same thing myself. So I asked Bryn Llewellyn, Oracle's PL/SQL Product Manager, the same question. Here's what he said:
"I can't defend PL/SQL's lack of a continue statement. There's no question that some algorithms are considerably easier to express when a CONTINUE statement is used. If you need convincing, consider this:
<<outer>>for ... loop
...
<<inner>>for ... loop
...
-- exit both loops
exit outer when ...;
end loop inner;
...
end loop outer;
It would take a fair effort to achieve this effect without using an EXIT statement. (You could try it, for sport.) The CONTINUE statement would look rather similar:
<<outer>>for ... loop
...
<<inner>>for ... loop
...
-- immediately start the next iteration of the outer loop
continue outer when ...;
end loop inner;
...
end loop outer;
Now write some PL/SQL that achieves this. You'll soon see that a continue statement in PL/SQL would be quite useful."
Of course, I asked Bryn when we might see this enhancement made. "Don't hold your breath," he said. "I'm afraid that there are too many other potential enhancements that would deliver bigger benefits competing for priority."
Friday 26 August 2011
Working with Collections in Subprogram Headers
Question:
I have two questions: (1) How I can pass a collection as an argument to a procedure? and (2) How I return a collection from a procedure?
Answer:
I have received this question from a number of people over the years, and I find the question both surprising and understandable.
I am surprised because the syntax for passing a collection as an argument is actually no different than that for passing a string or a date. You simply define the parameter as having a datatype matching the collection type and....off you go!
I understand the question because many PL/SQL developers seem to be generally a bit wary of and confused by collections.
So perhaps more of an explanation is needed. Here we go!
Collections are array-like structures in PL/SQL. With them, you can manage lists of information, build stacks, queues, linked lists, single dimensional arrays, and more. You can emulate multi-dimensional arrays; you can index not only by integers but also by strings, giving you enormous flexibility. With the TABLE operator, you can treat a collection like a relational table inside a query.
In addition, you need to use collections in order to take advantage of two of the most powerful and useful statements in the PL/SQL tool box: FORALL and BULK COLLECT, which dramatically improve performance of multi-row SQL operations from a PL/SQL program.
To sum up, I believe that collections are an absolutely amazing and crucial feature of PL/SQL. All developers should come up to speed on collections, be comfortable using them, and apply them wherever possible.
Now, one way to apply them is to pass them to and from subprograms: procedures and functions. So, like I said above, there is nothing special about passing a collection as an argument. When you define the parameter in your subprogram, you must provide a name, a mode (IN is the default) and the datatype. I think that your question comes down to: how to specify the datatype?
And this can be a little bit tricky, because in most cases you will need to have previously defined the datatype
.
But let's take things a step at a time. Suppose that I want to write a program that accepts a collection of a type that has already been defined, for example, DBMS_SQL.VARCHAR2A. This collection type is defined in the DBMS_SQL package as follows:
type varchar2a is table of varchar2(32767)
index by binary_integer;
It is used for parsing very long SQL statements (those that are greater than 32K in length). But I could also use it as a "generic type" for "list of strings".
Here is an example of using the type in an anonymous block:
DECLARE
my_strings DBMS_SQL.VARCHAR2A;
BEGIN
DBMS_OUTPUT.put_line ('>>> Populate a DBMS_SQL.VARCHAR2A array');
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
END;
/
I declare a list of "my strings" based on the pre-defined collection type, and then populate the list with three values.
Now let's take one more step and create a local subprogram that accepts a collection of this type as an IN argument, and displays its contents:
DECLARE
my_strings DBMS_SQL.varchar2a;
I have two questions: (1) How I can pass a collection as an argument to a procedure? and (2) How I return a collection from a procedure?
Answer:
I have received this question from a number of people over the years, and I find the question both surprising and understandable.
I am surprised because the syntax for passing a collection as an argument is actually no different than that for passing a string or a date. You simply define the parameter as having a datatype matching the collection type and....off you go!
I understand the question because many PL/SQL developers seem to be generally a bit wary of and confused by collections.
So perhaps more of an explanation is needed. Here we go!
Collections are array-like structures in PL/SQL. With them, you can manage lists of information, build stacks, queues, linked lists, single dimensional arrays, and more. You can emulate multi-dimensional arrays; you can index not only by integers but also by strings, giving you enormous flexibility. With the TABLE operator, you can treat a collection like a relational table inside a query.
In addition, you need to use collections in order to take advantage of two of the most powerful and useful statements in the PL/SQL tool box: FORALL and BULK COLLECT, which dramatically improve performance of multi-row SQL operations from a PL/SQL program.
To sum up, I believe that collections are an absolutely amazing and crucial feature of PL/SQL. All developers should come up to speed on collections, be comfortable using them, and apply them wherever possible.
Now, one way to apply them is to pass them to and from subprograms: procedures and functions. So, like I said above, there is nothing special about passing a collection as an argument. When you define the parameter in your subprogram, you must provide a name, a mode (IN is the default) and the datatype. I think that your question comes down to: how to specify the datatype?
And this can be a little bit tricky, because in most cases you will need to have previously defined the datatype
.
But let's take things a step at a time. Suppose that I want to write a program that accepts a collection of a type that has already been defined, for example, DBMS_SQL.VARCHAR2A. This collection type is defined in the DBMS_SQL package as follows:
type varchar2a is table of varchar2(32767)
index by binary_integer;
It is used for parsing very long SQL statements (those that are greater than 32K in length). But I could also use it as a "generic type" for "list of strings".
Here is an example of using the type in an anonymous block:
DECLARE
my_strings DBMS_SQL.VARCHAR2A;
BEGIN
DBMS_OUTPUT.put_line ('>>> Populate a DBMS_SQL.VARCHAR2A array');
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
END;
/
I declare a list of "my strings" based on the pre-defined collection type, and then populate the list with three values.
Now let's take one more step and create a local subprogram that accepts a collection of this type as an IN argument, and displays its contents:
DECLARE
my_strings DBMS_SQL.varchar2a;
PROCEDURE show_list (
title_in IN VARCHAR2, list_in IN DBMS_SQL.varchar2a)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> DBMS_SQL.VARCHAR2A as IN parameter ' || title_in);
/* I assume below that the collection is densely filled. */
FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
And here, then, you can see how to pass a collection into a procedure. "Just do it!" I can also return a collection through a function. In the following block, I added a function to "double" the values in my list. Notice that I pass in a collection as an IN argument, but also pass a collection of the same type as the RETURN clause of the function.
DECLARE
my_strings DBMS_SQL.varchar2a;
my_strings2 DBMS_SQL.varchar2a;
PROCEDURE show_list (
title_in IN VARCHAR2, list_in IN DBMS_SQL.varchar2a)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> DBMS_SQL.VARCHAR2A as RETURN clause ' || title_in);
FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
FUNCTION double_values (list_in IN DBMS_SQL.varchar2a)
RETURN DBMS_SQL.varchar2a
IS
l_return DBMS_SQL.varchar2a;
BEGIN
FOR indx IN 1 .. list_in.COUNT
LOOP
l_return (l_return.COUNT + 1) :=
list_in (indx) || '-'
|| list_in (indx);
END LOOP;
RETURN l_return;
END double_values;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
my_strings2 := double_values (my_strings);
show_list ('My name - Doubled', my_strings2);
END;
/
Again, note that there is no special syntax required to return a collection; just specify the datatype.
I have now showed you how to use a pre-defined collection type in your parameter list. One last topic to cover: how to create your own collection types and use them in your parameter list. The collection type has to be available in the header of your program, so either it has to be defined:
1. at the schema level as a database object;
2. in the specification of a package on which your schema has execute authority;
3. in the declaration section of the block in which the program is then defined.
I demonstrate each of these below:
1. Define a collection type at the schema level as a database object. This is possible only for nested tables and varrays. Notice that there is very little difference from the original example, except for syntax changes needed to work with nested tables (use of the constructor function, strings_ntt (), and the extend statement).
CREATE OR REPLACE TYPE strings_ntt IS TABLE OF VARCHAR2 (32767)
/
DECLARE
my_strings strings_ntt := strings_ntt ();
PROCEDURE show_list (title_in IN VARCHAR2, list_in IN strings_ntt)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> Schema-level collection type ' || title_in);
FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings.EXTEND (3);
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
2. Define the collection type in the specification of a package on which your schema has execute authority. I will do, in other words, precisely what Oracle did in the DBMS_SQL package, but for my own type:
CREATE OR REPLACE PACKAGE coll_types_pkg
IS
TYPE strings_aat IS TABLE OF VARCHAR2 (32767)
INDEX BY PLS_INTEGER;
TYPE dates_aat IS TABLE OF DATE
INDEX BY PLS_INTEGER;
TYPE booleans_aat IS TABLE OF BOOLEAN
INDEX BY PLS_INTEGER;
END coll_types_pkg;
/
DECLARE
my_strings coll_types_pkg.strings_aat;
PROCEDURE show_list (
title_in IN VARCHAR2, list_in IN coll_types_pkg.strings_aat)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> Collection type in package spec ' || title_in);
FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
3. Declare the collection type in the declaration section of the block in which the program is then defined. The code below is exactly the same as the original example using DBMS_SQL.VARCHAR2A, but I have my own type statement.
DECLARE
TYPE strings_aat IS TABLE OF VARCHAR2 (32767)
INDEX BY PLS_INTEGER;
my_strings strings_aat;
PROCEDURE show_list (title_in IN VARCHAR2, list_in IN strings_aat)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> Locally declared collection type' || title_in);
FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
To conclude with a best practice recommendation or two:
• Avoid declaring collection types inside anonymous blocks or declaration sections of subprograms. It is very likely that you will need to use the type more than once.
• Define your collection types in the database (with a schema-level CREATE OR REPLACE TYPE) if you need to use that type as a column in a relational table.
• Define your collection types in a package specification whenever you want or need to use an associative array (they support negative indexes, sparsely-filled collections, string indexes), or you are using these collection types solely in PL/SQL code (not as columns in tables.
Why doesn't an UPDATE raise the NO_DATA_FOUND exception
Question:
Why doesn't an UPDATE raise the NO_DATA_FOUND exception?
Answer:
Arun, a number of people have asked that question over the years. At first glance, it seems like an inconsistency. I try to query some rows and if none are found, I get an error. Yet if I try to update some rows and none are found, I don't receive an error. Instead, SQL%ROWCOUNT is set to 0.
Certainly, Oracle as well as the ANSI standards committee could have specified that NO_DATA_FOUND be raised when no rows are updated. I can only speculate as follows:
• When I execute an update statement I am saying "Update all the rows that satisfy this WHERE clause, or all rows if the WHERE clause is missing." If my WHERE clause identifies no rows, that isn't really an error; the UPDATE simply followed your command and updated the rows you specified—of which there were none.
• When you execute a query, you want to retrieve data. That's the point of the query. So if I don't get any back, that is an exceptional situation and Oracle raises the exception to indicate that fact.
Of course, another is simply "That's the way the ANSII standard goes..."
By the way, I suggest that you always place your queries inside a function so that you can trap the NO_DATA_FOUND exception and decide if you want to propagate that exception out to the enclosing block. You could take the same approach with UPDATE: put it inside a procedure and have it raise an exception when SQL%ROWCOUNT is set to 0, if that is the behavior you want/need in your application.
I recommend functions and procedures, by the way, so that you are more easily able to reuse those SQL statements.
Why doesn't an UPDATE raise the NO_DATA_FOUND exception?
Answer:
Arun, a number of people have asked that question over the years. At first glance, it seems like an inconsistency. I try to query some rows and if none are found, I get an error. Yet if I try to update some rows and none are found, I don't receive an error. Instead, SQL%ROWCOUNT is set to 0.
Certainly, Oracle as well as the ANSI standards committee could have specified that NO_DATA_FOUND be raised when no rows are updated. I can only speculate as follows:
• When I execute an update statement I am saying "Update all the rows that satisfy this WHERE clause, or all rows if the WHERE clause is missing." If my WHERE clause identifies no rows, that isn't really an error; the UPDATE simply followed your command and updated the rows you specified—of which there were none.
• When you execute a query, you want to retrieve data. That's the point of the query. So if I don't get any back, that is an exceptional situation and Oracle raises the exception to indicate that fact.
Of course, another is simply "That's the way the ANSII standard goes..."
By the way, I suggest that you always place your queries inside a function so that you can trap the NO_DATA_FOUND exception and decide if you want to propagate that exception out to the enclosing block. You could take the same approach with UPDATE: put it inside a procedure and have it raise an exception when SQL%ROWCOUNT is set to 0, if that is the behavior you want/need in your application.
I recommend functions and procedures, by the way, so that you are more easily able to reuse those SQL statements.
Who needs user-defined exceptions
Question:
Oracle defines a number of exceptions for me, like no_data_found. Why would I ever need to or want to define my own exceptions?
Answer:
Oracle does, indeed, pre-define a number of exceptions. Many of these may be found inside the standard package of PL/SQL, one of the two default packages in the PL/SQL language (the other being dbms_standard). You can view these exception definitions by looking at the stdspec.sql file in the $ORACLE_HOME/Rdbms/Admin directory of an Oracle10g installation.
Here is a portion of that file showing the declarations of a few such exceptions:
/********** Predefined exceptions **********/
Oracle defines a number of exceptions for me, like no_data_found. Why would I ever need to or want to define my own exceptions?
Answer:
Oracle does, indeed, pre-define a number of exceptions. Many of these may be found inside the standard package of PL/SQL, one of the two default packages in the PL/SQL language (the other being dbms_standard). You can view these exception definitions by looking at the stdspec.sql file in the $ORACLE_HOME/Rdbms/Admin directory of an Oracle10g installation.
Here is a portion of that file showing the declarations of a few such exceptions:
/********** Predefined exceptions **********/
CURSOR_ALREADY_OPEN exception;
pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-6511');
DUP_VAL_ON_INDEX exception;
pragma EXCEPTION_INIT(DUP_VAL_ON_INDEX, '-0001');
TIMEOUT_ON_RESOURCE exception;
pragma EXCEPTION_INIT(TIMEOUT_ON_RESOURCE, '-0051');
In this code, Oracle declares an exception and then associates that named exception with an error code using the exception_init pragma.
You can declare exceptions just like Oracle does in the above code fragment; you can also raise application-specific exceptions "on the fly" with the raise_application_error built-in.
You will generally want to do this in order to improve the readability and maintainability of your code. Let's look at two examples: handling exceptions raised by Oracle and raising application-specific exceptions.
Which collection type should I use
Question:
I need to pass a collection from one PL/SQL program to another. Which collection type should I use, or does it not make any difference?
Answer:
Oracle PL/SQL offers three types of collections (arraylike structures): associative array, nested table, and VARRAY. Which collection type makes sense for your application? In some cases, the choice is obvious. In others, there may be several acceptable choices. I offer some guidance and a table that describes many of the differences between associative arrays, nested tables, and VARRAYs.
As a PL/SQL developer, I find myself leaning toward using associative arrays as a first instinct. There are several reasons for this preference: At one time (Oracle Database 7.3), associative arrays—then called PL/SQL tables—were the only type of collection in PL/SQL. So I got used to using them. They also involve the least amount of coding: You don't have to initialize or extend them. In Oracle9i Database Release 2 and later, you can index associative arrays by strings as well as by integers. However, if you want to store your collection within a database table, you cannot use an associative array. The question then becomes: nested table or VARRAY?
The following guidelines will help you choose an associative array, nested table, or VARRAY:
• If you need a sparsely indexed list (for "data-smart" storage, for example), your only practical option is an associative array. True, you could allocate and then delete elements of a nested table variable, but it is inefficient to do so for anything but the smallest collections.
• If your PL/SQL application requires negative subscripts, you have to use associative arrays.
• If you are running Oracle Database 10g and would find it useful to perform high-level set operations on your collections, choose nested tables over associative arrays.
• If you want to enforce a limit on the number of rows stored in a collection, use VARRAYs.
• If you intend to store large amounts of persistent data in a column collection, your only option is a nested table. Oracle Database will then use a separate table behind the scenes to hold the collection data, so you can allow for almost limitless growth.
• If you want to preserve the order of elements stored in the collection column and if your data set will be small, use a VARRAY. What is "small"? I tend to think in terms of how much data you can fit into a single database block; if you span blocks, you get row chaining, which decreases performance. The database block size is established at database creation time and is typically 2K, 4K, or 8K.
Here are some other indications that a VARRAY would be appropriate: You don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.
I need to pass a collection from one PL/SQL program to another. Which collection type should I use, or does it not make any difference?
Answer:
Oracle PL/SQL offers three types of collections (arraylike structures): associative array, nested table, and VARRAY. Which collection type makes sense for your application? In some cases, the choice is obvious. In others, there may be several acceptable choices. I offer some guidance and a table that describes many of the differences between associative arrays, nested tables, and VARRAYs.
As a PL/SQL developer, I find myself leaning toward using associative arrays as a first instinct. There are several reasons for this preference: At one time (Oracle Database 7.3), associative arrays—then called PL/SQL tables—were the only type of collection in PL/SQL. So I got used to using them. They also involve the least amount of coding: You don't have to initialize or extend them. In Oracle9i Database Release 2 and later, you can index associative arrays by strings as well as by integers. However, if you want to store your collection within a database table, you cannot use an associative array. The question then becomes: nested table or VARRAY?
The following guidelines will help you choose an associative array, nested table, or VARRAY:
• If you need a sparsely indexed list (for "data-smart" storage, for example), your only practical option is an associative array. True, you could allocate and then delete elements of a nested table variable, but it is inefficient to do so for anything but the smallest collections.
• If your PL/SQL application requires negative subscripts, you have to use associative arrays.
• If you are running Oracle Database 10g and would find it useful to perform high-level set operations on your collections, choose nested tables over associative arrays.
• If you want to enforce a limit on the number of rows stored in a collection, use VARRAYs.
• If you intend to store large amounts of persistent data in a column collection, your only option is a nested table. Oracle Database will then use a separate table behind the scenes to hold the collection data, so you can allow for almost limitless growth.
• If you want to preserve the order of elements stored in the collection column and if your data set will be small, use a VARRAY. What is "small"? I tend to think in terms of how much data you can fit into a single database block; if you span blocks, you get row chaining, which decreases performance. The database block size is established at database creation time and is typically 2K, 4K, or 8K.
Here are some other indications that a VARRAY would be appropriate: You don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.
Where should I store hard-coded values
Question:
I need to change the PL/SQL packages of 24 apps, 10 of which are business critical. These packages have a few hard coded values (like company name).
I know I can use package-level variables with default values (put in a common schema to establish one location for these values across the Oracle instance). Or, I was thinking of using a table to init these values (keep them empty) and load them up only when necessary. Thus the table will be accessed once per connection.
What do you think is a better approach, given that there are four such hard-coded values?
Answer:
I need to change the PL/SQL packages of 24 apps, 10 of which are business critical. These packages have a few hard coded values (like company name).
I know I can use package-level variables with default values (put in a common schema to establish one location for these values across the Oracle instance). Or, I was thinking of using a table to init these values (keep them empty) and load them up only when necessary. Thus the table will be accessed once per connection.
What do you think is a better approach, given that there are four such hard-coded values?
Answer:
Your overall direction and intentions are great! You certainly should get those hard-coded values out of your programs and define them in a single place. Then if any of them need to be changed, you change the value in one place, recompile code as necessary, and from that point on all the programs are using the new value.
So the question becomes: Where to store the values?
As you point out, we have several options:
• Define them in a package, in the specification as constants, or as functions with the value hidden (hard-coded) in the package body.
• Define them in a database table. Then provide a function in a package that returns the value from the database table. Use the initialization section of the package to load the values just once per session (which is cleaner than the technique you mention above).
• Define them in a file, and then load the values from the file upon package initialization—ugh!
So let's agree on one thing: delivering the values through a package specification is best. It is also preferable to place the values in the package body instead of the specification so that when the value changes, you don't have to recompile the package spec and thereby invalidate all references to the package.
I suggest you absorb the overhead and complexity of storing the values in a database table only if the values change fairly often, and nonprogrammers can or should be allowed to set the values themselves.
If the values change rarely, if ever, a simple package specification of constants will work just fine. Here is an example of such a package for use with UTL_FILE:
CREATE OR REPLACE PACKAGE utl_file_constants
IS
c_read_only CONSTANT VARCHAR2 (1) := 'R';
c_write_only CONSTANT VARCHAR2 (1) := 'W';
c_append CONSTANT VARCHAR2 (1) := 'A';
c_min_linesize CONSTANT PLS_INTEGER := 1;
c_def_linesize CONSTANT PLS_INTEGER := 1024;
c_max_linesize CONSTANT PLS_INTEGER := 32767;
END utl_file_constants;
/
Where did my zeroes go
Question:
I'm using native dynamic SQL to insert data into my table. I'm finding that if I concatenate my data into the table, I get different results than if I bind the values in the USING clause. Specifically, in the program below, when I concatenate my value of '000011111' for the value1 column is stored in the database as '11111'.
DROP TABLE otn_insert
/
CREATE TABLE otn_insert (CONTEXT VARCHAR2(100),
value1 VARCHAR2(100), value2 VARCHAR2(100))
/
I'm using native dynamic SQL to insert data into my table. I'm finding that if I concatenate my data into the table, I get different results than if I bind the values in the USING clause. Specifically, in the program below, when I concatenate my value of '000011111' for the value1 column is stored in the database as '11111'.
DROP TABLE otn_insert
/
CREATE TABLE otn_insert (CONTEXT VARCHAR2(100),
value1 VARCHAR2(100), value2 VARCHAR2(100))
/
DECLARE
l_insert VARCHAR2 (500);
l_value1 otn_insert.value1%TYPE;
l_value2 otn_insert.value2%TYPE;
BEGIN
l_value1 := '00001111';
l_value2 := '11110000';
INSERT INTO otn_insert
(value1, value2
)
VALUES ('Static INSERT', l_value1, l_value1
);
l_insert :=
'INSERT INTO otn_insert (value1, value2)
VALUES (''Dynamic Concatenation'','
|| l_value1
|| ','
|| l_value2
|| ')';
EXECUTE IMMEDIATE l_insert;
l_insert :=
'INSERT INTO otn_insert (value1, value2)
VALUES (''Dynamic Binding'',:r1, :r2)';
EXECUTE IMMEDIATE l_insert
USING l_value1, l_value2;
END;
/
SELECT *
FROM otn_insert
/
DROP TABLE otn_insert
/
Why is this happening?
Answer:
Junfei,
The problem you encountered points out very precisely one of the reasons that binding should be generally chosen over concatenation as the way to pass variable values into a dynamic SQL string: you will avoid unwanted implicit conversions that give you unintended results.
In the world of dynamic SQL, you construct a string at run-time and then pass that string to Oracle for parsing. So whenever you have a problem with dynamic SQL, the question you must ask yourself before any other is: What string is Oracle parsing? I added a call to DBMS_OUTPUT.PUT_LINE to display the l_insert value just before the EXECUTE IMMEDIATE of the concatenated string.
This is what displayed on my screen:
INSERT INTO otn_insert (context, value1, value2)
VALUES ('Dynamic Concatenation',00001111,11110000)
Notice that there aren't any single quotes around the values assigned to the value1 and value2 columns. You are, in other words, passing numbers, not strings. Leading zeroes in a number are not significant; they are ignored. Furthermore, because your columns are strings and your input values are numbers, Oracle must perform an implicit conversion. Thus, when Oracle deposits this row in the table, the 00001111 value has become 1111, and that is what you see when you query the contents of the table.
NOTE: As an alternative to adding a call to DBMS_OUTPUT.PUT_LINE, if your schema is able to read from the v$sqlarea view, you can also view the SQL statement executed by the above block of code with a query like this:
select sql_text from v$sqlarea
where sql_text like '%INSERT INTO otn_insert%'
and sql_text not like '%v$sqlarea%'
Now, it is true that you could fix this problem of "disappearing zeroes" by placing single quotes around your values in the concatenated insert, as follows:
l_insert :=
'INSERT INTO otn_insert (context, value1, value2) VALUES (''Dynamic Concatenation'','''
|| l_value1
|| ''','''
|| l_value2
|| ''')';
but the resulting code is hard to read and maintain. Binding, as I explain below, is a better way to go. Still, if you choose to perform concatenation and have to deal with embedding single quotes within your dynamic string, remember that in Oracle Database 10g, you can use a user-defined quote character to simplify things, as in:
l_insert :=
q'{INSERT INTO otn_insert (context, value1, value2) VALUES ('Dynamic Concatenation','}'
|| l_value1
|| q'{','}'
|| l_value2
|| q'{')}';
Rather than having to figure out how many single quotes you need to put together to get the correct results, however, you would be much better off simply binding the variable values with the USING clause. Your code is simpler and likely to run more efficiently as well, as even if the values you insert change, the SQL statement stays the same and will therefore not have to be parsed again. In addition, you avoid the problem of code injection, because a malicious user cannot execute undesirable and unexpected code through a bind variable.
Here is an example of performing the insert with bind variables (passed via the USING clause of EXECUTE IMMEDIATE):
DECLARE
l_insert VARCHAR2 (500) :=
q'{INSERT INTO otn_insert (context, value1, value2) VALUES ('Dynamic Binding',:r1, :r2)}';
l_value1 otn_insert.value1%TYPE;
l_value2 otn_insert.value2%TYPE;
BEGIN
l_value1 := '00001111';
l_value2 := '11110000';
EXECUTE IMMEDIATE l_insert
USING l_value1, l_value2;
END;
/
Where did my error go
I am having trouble understanding how PL/SQL's exception raising and handling works. First, I wrote this code:
DECLARE
CURSOR c1 IS SELECT sal, comm FROM emp;
ratio NUMBER;
BEGIN
FOR r1 IN c1
LOOP
ratio := r1.sal / NVL ( r1.comm, 1 );
DECLARE
CURSOR c1 IS SELECT sal, comm FROM emp;
ratio NUMBER;
BEGIN
FOR r1 IN c1
LOOP
ratio := r1.sal / NVL ( r1.comm, 1 );
IF ( ratio < 1 )
THEN
raise_application_error ( -20555
, 'Ratio was less than 1' );
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ( 'No data was found!' );
END;
And when ratio is more than 1, RAISE_APPLICATION_ERROR is called, and the proper error is raised and sent out of the block.
Then I add a WHEN OTHERS Clause in the exception section:
DECLARE
CURSOR c1 IS SELECT sal, comm FROM emp;
ratio NUMBER;
BEGIN
FOR r1 IN c1
LOOP
ratio := r1.sal / NVL ( r1.comm, 1 );
IF ( ratio < 1 )
THEN
raise_application_error ( -20555
, 'Ratio was less than 1' );
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ( 'No data was found!' );
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'I don''t want to see this message!' );
END;
And now when the ratio is more than 1, the WHEN OTHERS exception is raised instead of RAISE_APPLICATION_ERROR. Why do I get different behavior in my code for the same error condition?
Answer:
I will start with an explanation of what is going on in your code, including a quick overview regarding PL/SQL exception raising and handling behavior, and then finish up by pointing you to resources for further education and for best practice recommendations.
First of all, your blocks of code are performing exactly how you told them to perform (which, of course, is virtually always the case, since all the PL/SQL runtime engine can do is follow the commands you have given it!); you just need some additional clarity and understanding of what, in fact, you have told it do, and what you can then expect.
The RAISE_APPLICATION_ERROR built-in, actually defined in the DBMS_STANDARD package (one of the two default packages of PL/SQL, meaning you do not have to qualify references to elements of that package with the package name), is used to raise an application-specific error, usually to communicate that error back to a non-PL/SQL host environment (be it SQL*Plus, Oracle Forms, a Java program, etc.).
In the first block, you use this built-in to raise an exception, to which you assign the -20555 number and a related message.
Now, when an exception is raised, the current executable section shuts down and PL/SQL then checks to see if there is an exception section defined for that block. If so, it checks to see if there is a WHEN clause that will catch or handle the specific error raised.
Your only WHEN clause in the first block checks for NO_DATA_FOUND, whose SQL error codes are 100 and -1403 (yes, that's right: two different error codes for the same error, though at run-time only -1403 is used).
Your code, on the other hand, raises an error using RAISE_APPLICATION_ERROR. You specify the value -20555 as the error code. You have not associated that code with a PL/SQL exception using the EXCEPTION_INIT pragma (see below for more details on this pragma). The only way, therefore, that this error can be caught in the exception section is with a WHEN OTHERS clause. Without that, the error propagates out unhandled from the block and the SQLCODE function, when called, will return -20555.
In your second block, you have added a WHEN OTHERS handler. This is a catch-all handler that will catch any error not previously caught by another WHEN clause in the same exception section.
So when you run the second block, RAISE_APPLICATION_ERROR raises the ORA-20555 error, which is then caught by the WHEN OTHERS clause. Unless you include a RAISE; statement to re-raise the same exception out of the WHEN OTHERS handler, that exception information will be "swallowed up." Here is an example of using RAISE; to log the error occurrence, but then propagate it out to the enclosing block or host program:
EXCEPTION
WHEN OTHERS
THEN
log_the_error;
RAISE;
END;
One of the reasons you had trouble understanding the cause for the way your modified block behaved is that you are not distinguishing clearly enough between raising an exception and handling that exception. I could sense your confusion by this sentence:
"...the WHEN OTHERS exception is raised instead of RAISE_APPLICATION_ERROR."
Remember:
• The RAISE statement and the RAISE_APPLICATION_ERROR procedure both raise an exception.
• The EXCEPTION section with its WHEN clauses handle (or may handle) errors that are raised in the executable section of a block.
So...WHEN OTHERS is not an exception. It is not raised. It is simply used as a way to catch any "other" exceptions that have not already been handled in the exception section.
And RAISE_APPLICATION_ERROR itself is not raised. Instead, it raises the error you specify.
Finally, I mentioned the EXCEPTOIN_INIT pragma earlier. Let's look at an example of using this statement. Consider the following block of code:
SQL> DECLARE
2 my_bad EXCEPTION;
3 PRAGMA EXCEPTION_INIT ( my_bad, -20555 );
4 BEGIN
5 RAISE_APPLICATION_ERROR ( -20555, 'My bad' );
6 EXCEPTION
7 WHEN NO_DATA_FOUND
8 THEN
9 DBMS_OUTPUT.put_line ( 'No_Data_Found UNEXPECTEDLY handled' );
10 END;
11 /
On line 2 I declare an exception. On line 3 I associate that named exception with the -20555 error code. I then raise the -20555 error on line 5. Since I only have a handler for NO_DATA_FOUND, the error goes unhandled and within SQL*Plus I see this error stack:
ERROR at line 1:
ORA-20555: My bad
ORA-06512: at line 5
Notice that the stack contains both the error number and message I specified.
Now I will change that block of code to include a handler by name for my application-specific exception:
SQL> DECLARE
2 my_bad EXCEPTION;
3 PRAGMA EXCEPTION_INIT ( my_bad, -20555 );
4 BEGIN
5 RAISE_APPLICATION_ERROR ( -20555, 'My bad' );
6 EXCEPTION
7 WHEN NO_DATA_FOUND
8 THEN
9 DBMS_OUTPUT.put_line (
10 'No_Data_Found UNEXPECTEDLY handled' );
11 WHEN my_bad
12 THEN
13 DBMS_OUTPUT.put_line ( 'My_Bad handled' );
14 END;
15 /
When I run this block of code with serveroutput enabled, I see the following text:
My_Bad handled
The error was caught and the associated code was executed.
Whats the DBMS_OUTPUT Overhead
Question:
What is the overhead involved in having DBMS_OUTPUT calls in production code where serveroutput is turned off? I've read about how to selectively call or compile code, but I can't find anything that says, "Yes, there is considerable overhead in making DBMS_OUTPUT calls; conditionally skip them where possible" or "No, there is minimal impact; don't go to the effort of trying to conditionally make the calls."
Answer:
What a fine and very practical question you ask.
Before I answer it, I'll briefly describe how DBMS_OUTPUT works and provide some information about tracing.
How DBMS_OUTPUT works. The DBMS_OUTPUT package provides a procedure (PUT_LINE) that writes text out to a buffer (an array in memory) if output has been enabled. Host environments like SQL*Plus can then call the DBMS_OUTPUT.GET_LINES procedure to retrieve the contents of the buffer and display those strings through some kind of visual interface. DBMS_OUTPUT is, therefore, only useful when executing code from an environment that can/will display output from this built-in package.
DBMS_OUTPUT is certainly the simplest and most direct means of seeing what is happening inside your PL/SQL subprogram. The need for the host environment to extract data from the buffer, however, limits the usefulness of DBMS_OUTPUT as a tracing mechanism.
Tracing issues and requirements. Tracing generally refers to implementing a mechanism in your code so that you can trace or watch what is going on in your code. Tracing is different, by the way, from debugging. When you trace, you run your application and gather information about its real-time behavior. Afterwards, you examine the trace to identify bugs. When you debug, you step through the code line by line and examine the goings-on in your code. This is a crucial technique for identifying the cause of a bug.
There are a variety of tracing scenarios:
• Development tracing. I want to trace the execution of my program during development, to understand, test, and debug the code thoroughly. I neither want nor need tracing in my production code.
• Production tracing. I need to include optional tracing in my production application. If a user reports a problem, I can then turn on tracing within production, gather the necessary information, and then perform my analysis.
• Tracing flexibility. In some simple cases, I can rely solely on DBMS_OUTPUT to display tracing data to the screen. In many other situations, however, I will want to use a different repository for my trace. For example, suppose my program runs for hours. I want to see the trace information while the program continues to execute. So I will write information out to a table using an autonomous transaction procedure. I can then see the output immediately from another session.
As with every other aspect of your application design, you should think through the kinds of tracing you need and the repositories in which the trace information must be written before you begin writing your application.
Test case Length of String Number of Calls Elapsed Time
(hundredths of seconds)
DBMS_OUTPUT enabled 10 1000000 767
DBMS_OUTPUT disabled 10 1000000 392
DBMS_OUTPUT enabled 10000 1000000 984
DBMS_OUTPUT disabled 10000 1000000 739
DBMS_OUTPUT disabled NULL 1000000 61
Call to DBMS_OUTPUT.PUT_LINE Removed N/A 1000000 11
Testing. To answer your question, I put together a script—located in Listing 4—to test the overhead of calling DBMS_OUTPUT.PUT_LINE. I explain it and the results in a bit more detail below, but I will first offer my conclusions to you:
1. The impact on application performance is (here comes one of those classically infuriating answers) application-specific. That is, a call to DBMS_OUTPUT.PUT_LINE all by itself doesn't incur very much overhead. (For example, over 100,000 invocations of my test code, calling DBMS_OUTPUT.PUT_LINE and passing NULL, took .61 seconds. Removing the call to this built-in reduced execution time to .11 seconds.) What can take a lot of time is evaluating the value that you pass to the single string parameter of DBMS_OUTPUT.PUT_LINE. More on this below.
2. The more important question when examining the issue of tracing in one's code is, "What is the best way to do it, from the standpoint of readability and maintainability of code?" (Hey, this is, after all, a best-practices column!) Generally, one's code is more readable when it is not littered with lots of calls to trace programs.
The results are in. Having said all that, let's now take a look at the results of my analysis of the overhead of calling DBMS_OUTPUT.PUT_LINE. I will then conclude with more best-practice- oriented comments.
To construct the script to analyze overhead in DBMS_OUTPUT.PUT_LINE in Listing 4, here is the approach I took:
• I used consecutive calls to DBMS_UTILITY.GET_CPU_TIME to compute elapsed time down to the hundredth of a second (you can substitute this program with DBMS_UTILITY.GET_TIME if you are not yet running Oracle Database 10g).
• I recorded the results in a database table, because DBMS_OUTPUT must be disabled for at least part of this script and is, in any case, the focus of our analysis and should not be used to record and display results.
• For most of the tests, I passed a string that requires some effort to construct each time the built-in is called. Specifically, I used RPAD to create a string of a length specified at the time the test is run, and concatenated to that a date and a Boolean. This may be more work than is usually performed when you call DBMS_OUTPUT.PUT_LINE, but it is surely not outside the realm of normal tracing activity.
• I called DBMS_OUTPUT.PUT_LINE with the following variations: output enabled, output disabled, pass just a NULL, and don't call DBMS_OUTPUT.PUT_LINE at all.
Table 1 shows what I found after running this script. As you can see, there is certainly overhead associated with calling DBMS_OUTPUT.PUT_LINE, and the scale of that overhead depends very much on the amount of work it takes to construct and pass the string to DBMS_OUTPUT.PUT_LINE. Is this overhead "considerable" or "minimal"? I cannot answer that question for you—it depends on your application.
Tracing recommendations. I offer the following tracing recommendations:
• Never call DBMS_OUTPUT.PUT_LINE directly in your application code. Create a wrapper that accepts the application data you want to see in your trace and then calls DBMS_OUTPUT.PUT_LINE. Why do I suggest this? Prior to Oracle Database 10g, DBMS_OUTPUT.PUT_LINE will raise an error if you try to display strings with more than 255 characters (that limit has now been raised to 32K). Also, you may want to send trace information to a file or table rather than the screen, and a direct call to DBMS_OUTPUT.PUT_LINE forces you to sacrifice that flexibility.
• If you are running Oracle Database 10g Release 2, take advantage of conditional compilation so that you can easily "compile out" the trace calls when your program goes into production (unless you need production-time tracing, of course). Now, one issue I believe you will currently encounter when using lots of conditional compilation statements in your code ($IF, $ENDIF, $ERROR, $$ccflagname, and so on) is that the code is harder to read. I expect, however, that the various PL/SQL editors, such as Toad, SQL Navigator, PL/SQL Developer, and—the latest entry—Oracle SQL Developer, will soon offer toggles to hide this logic as you are editing and reviewing your code, greatly improving that situation.
• If you are not yet running Oracle Database 10g Release 2, also consider enclosing trace calls inside your own conditional statement, which might look like this:
• IF trace_pkg.trace_enabled
• THEN
• trace_pkg.trace (l_my_data);
• END IF;
With this approach, the argument(s) passed to the trace mechanism will be evaluated only if tracing is enabled. You can, therefore, keep to an absolute minimum the overhead your application will experience when tracing is disabled.
What is the overhead involved in having DBMS_OUTPUT calls in production code where serveroutput is turned off? I've read about how to selectively call or compile code, but I can't find anything that says, "Yes, there is considerable overhead in making DBMS_OUTPUT calls; conditionally skip them where possible" or "No, there is minimal impact; don't go to the effort of trying to conditionally make the calls."
Answer:
What a fine and very practical question you ask.
Before I answer it, I'll briefly describe how DBMS_OUTPUT works and provide some information about tracing.
How DBMS_OUTPUT works. The DBMS_OUTPUT package provides a procedure (PUT_LINE) that writes text out to a buffer (an array in memory) if output has been enabled. Host environments like SQL*Plus can then call the DBMS_OUTPUT.GET_LINES procedure to retrieve the contents of the buffer and display those strings through some kind of visual interface. DBMS_OUTPUT is, therefore, only useful when executing code from an environment that can/will display output from this built-in package.
DBMS_OUTPUT is certainly the simplest and most direct means of seeing what is happening inside your PL/SQL subprogram. The need for the host environment to extract data from the buffer, however, limits the usefulness of DBMS_OUTPUT as a tracing mechanism.
Tracing issues and requirements. Tracing generally refers to implementing a mechanism in your code so that you can trace or watch what is going on in your code. Tracing is different, by the way, from debugging. When you trace, you run your application and gather information about its real-time behavior. Afterwards, you examine the trace to identify bugs. When you debug, you step through the code line by line and examine the goings-on in your code. This is a crucial technique for identifying the cause of a bug.
There are a variety of tracing scenarios:
• Development tracing. I want to trace the execution of my program during development, to understand, test, and debug the code thoroughly. I neither want nor need tracing in my production code.
• Production tracing. I need to include optional tracing in my production application. If a user reports a problem, I can then turn on tracing within production, gather the necessary information, and then perform my analysis.
• Tracing flexibility. In some simple cases, I can rely solely on DBMS_OUTPUT to display tracing data to the screen. In many other situations, however, I will want to use a different repository for my trace. For example, suppose my program runs for hours. I want to see the trace information while the program continues to execute. So I will write information out to a table using an autonomous transaction procedure. I can then see the output immediately from another session.
As with every other aspect of your application design, you should think through the kinds of tracing you need and the repositories in which the trace information must be written before you begin writing your application.
Test case Length of String Number of Calls Elapsed Time
(hundredths of seconds)
DBMS_OUTPUT enabled 10 1000000 767
DBMS_OUTPUT disabled 10 1000000 392
DBMS_OUTPUT enabled 10000 1000000 984
DBMS_OUTPUT disabled 10000 1000000 739
DBMS_OUTPUT disabled NULL 1000000 61
Call to DBMS_OUTPUT.PUT_LINE Removed N/A 1000000 11
Testing. To answer your question, I put together a script—located in Listing 4—to test the overhead of calling DBMS_OUTPUT.PUT_LINE. I explain it and the results in a bit more detail below, but I will first offer my conclusions to you:
1. The impact on application performance is (here comes one of those classically infuriating answers) application-specific. That is, a call to DBMS_OUTPUT.PUT_LINE all by itself doesn't incur very much overhead. (For example, over 100,000 invocations of my test code, calling DBMS_OUTPUT.PUT_LINE and passing NULL, took .61 seconds. Removing the call to this built-in reduced execution time to .11 seconds.) What can take a lot of time is evaluating the value that you pass to the single string parameter of DBMS_OUTPUT.PUT_LINE. More on this below.
2. The more important question when examining the issue of tracing in one's code is, "What is the best way to do it, from the standpoint of readability and maintainability of code?" (Hey, this is, after all, a best-practices column!) Generally, one's code is more readable when it is not littered with lots of calls to trace programs.
The results are in. Having said all that, let's now take a look at the results of my analysis of the overhead of calling DBMS_OUTPUT.PUT_LINE. I will then conclude with more best-practice- oriented comments.
To construct the script to analyze overhead in DBMS_OUTPUT.PUT_LINE in Listing 4, here is the approach I took:
• I used consecutive calls to DBMS_UTILITY.GET_CPU_TIME to compute elapsed time down to the hundredth of a second (you can substitute this program with DBMS_UTILITY.GET_TIME if you are not yet running Oracle Database 10g).
• I recorded the results in a database table, because DBMS_OUTPUT must be disabled for at least part of this script and is, in any case, the focus of our analysis and should not be used to record and display results.
• For most of the tests, I passed a string that requires some effort to construct each time the built-in is called. Specifically, I used RPAD to create a string of a length specified at the time the test is run, and concatenated to that a date and a Boolean. This may be more work than is usually performed when you call DBMS_OUTPUT.PUT_LINE, but it is surely not outside the realm of normal tracing activity.
• I called DBMS_OUTPUT.PUT_LINE with the following variations: output enabled, output disabled, pass just a NULL, and don't call DBMS_OUTPUT.PUT_LINE at all.
Table 1 shows what I found after running this script. As you can see, there is certainly overhead associated with calling DBMS_OUTPUT.PUT_LINE, and the scale of that overhead depends very much on the amount of work it takes to construct and pass the string to DBMS_OUTPUT.PUT_LINE. Is this overhead "considerable" or "minimal"? I cannot answer that question for you—it depends on your application.
Tracing recommendations. I offer the following tracing recommendations:
• Never call DBMS_OUTPUT.PUT_LINE directly in your application code. Create a wrapper that accepts the application data you want to see in your trace and then calls DBMS_OUTPUT.PUT_LINE. Why do I suggest this? Prior to Oracle Database 10g, DBMS_OUTPUT.PUT_LINE will raise an error if you try to display strings with more than 255 characters (that limit has now been raised to 32K). Also, you may want to send trace information to a file or table rather than the screen, and a direct call to DBMS_OUTPUT.PUT_LINE forces you to sacrifice that flexibility.
• If you are running Oracle Database 10g Release 2, take advantage of conditional compilation so that you can easily "compile out" the trace calls when your program goes into production (unless you need production-time tracing, of course). Now, one issue I believe you will currently encounter when using lots of conditional compilation statements in your code ($IF, $ENDIF, $ERROR, $$ccflagname, and so on) is that the code is harder to read. I expect, however, that the various PL/SQL editors, such as Toad, SQL Navigator, PL/SQL Developer, and—the latest entry—Oracle SQL Developer, will soon offer toggles to hide this logic as you are editing and reviewing your code, greatly improving that situation.
• If you are not yet running Oracle Database 10g Release 2, also consider enclosing trace calls inside your own conditional statement, which might look like this:
• IF trace_pkg.trace_enabled
• THEN
• trace_pkg.trace (l_my_data);
• END IF;
With this approach, the argument(s) passed to the trace mechanism will be evaluated only if tracing is enabled. You can, therefore, keep to an absolute minimum the overhead your application will experience when tracing is disabled.
Whats the best way to write a cursor in PLSQL
Question:
For years now (and in the first two editions of your book, Oracle PL/SQL Programming), you have been telling us that we should always use explicit cursors and avoid implicit ones because they are always slower. Why did you change your mind in the third edition of your book, and what is your current advice about the type of cursors to use?
Answer:
Asim, I will answer those questions in the reverse order. But first a quick review of the basic options for writing cursors:
• Implicit (static) cursor: commonly refers to the good old SELECT INTO, in which Oracle implicitly opens, executes and closes the cursor for you, depositing any selected values of a single row INTO program data structures.
• CREATE OR REPLACE PROCEDURE show_title (author_in IN magazine.author%TYPE)
• IS
• l_title magazine.title%TYPE;
• BEGIN
• SELECT title
• INTO l_title
• FROM magazine
• WHERE author = author_in;
• END;
• Explicit (static) cursor: in which you explicitly declare your cursor, and then explicitly perform each operation on that cursor yourself. This form will not raise NO_DATA_FOUND or TOO_MANY_ROWS; you decide for yourself if those conditions are actually errors.
• CREATE OR REPLACE PROCEDURE show_title (author_in IN magazine.author%TYPE)
• IS
• CURSOR title_cur
• IS
• SELECT title
• FROM magazine
• WHERE author = author_in;
•
• title_rec title_cur%ROWTYPE;
• BEGIN
• OPEN title_cur;
• FETCH title_cur INTO title_rec;
• CLOSE title_cur;
• END;
•
• Cursor FOR loop: another type of implicit cursor, but intended for use with multi-row queries (you could use it for a single row query). It opens the cursor, fetches each row within a loop, and then closes the cursor:
• BEGIN
• FOR title_rec IN (SELECT title
• FROM magazine)
• LOOP
• calculate_magazine_sales (title_rec.title);
• END LOOP;
• END;
• BULK COLLECT INTO: introduced in Oracle8i, BULK COLLECT allows you to retrieve multiple rows of data directly into PL/SQL Collections. It will raise NO_DATA_FOUND if it doesn't find any rows, but it certainly doesn't raise TOO_MANY_ROWS if it finds more than one!
• DECLARE
• TYPE title_aat IS TABLE OF magazine.title%TYPE
• INDEX BY BINARY_INTEGER;
•
• l_titles title_aat;
• BEGIN
• SELECT title
• BULK COLLECT INTO l_titles
• FROM magazine;
• END;
•
• Dynamic variations of the same: you can use DBMS_SQL or the newer Native Dynamic SQL to execute queries in PL/SQL that are constructed at run-time. I won't go into dynamic SQL in any more detail now; perhaps (likely) it will surface in a future Q&A.
You can download the examples for each of the above cursors here.
Now, here is my advice regarding the kinds of cursors to use when fetching data from Oracle in PL/SQL programs:
• Whenever possible, use BULK COLLECT—it offers dramatic performance gains. In Oracle9i Release 2, you can even use BULK COLLECT to fetch multiple rows directly into a collection of records. Of course, when you want/need to fetch just a single row, BULK COLLECT doesn't make sense. So in those cases...
• Whatever kind of cursor you decide to use to retrieve a single row of data, hide that cursor behind a function interface. In other words, ahem, I don't really care which form of cursor you use. Choose the one that performs best and/or is most easily maintained—and then stuff it behind a function. By placing a layer of PL/SQL code between your application logic and the underlying SQL, you give yourself the latitude to change the implementation of the query without affecting your application code.
• Implicit cursors are often faster than explicit cursors (although I imagine that in most situations, the performance difference won't make much difference to the user), so you should certainly consider using them—as long as you place the SELECT INTO inside a function.
• If the table from which you are querying is small and static (or perhaps very rarely changes and then only when users are not on the system), consider caching that table in a package-level PL/SQL collection. Your query function can then retrieve data from the collection (a PGA-based cache) rather than from the SGA—and it will be significantly faster.
• In a special case, please place your SELECT FROM dual statements inside a function. The use of dual is generally a workaround to access SQL functionality not yet natively available in PL/SQL. The most common example of this is
• SELECT my_sequence.NEXTVAL
• INTO l_new_key
FROM dual
I cannot call my_sequence.NEXTVAL in PL/SQL, so I embed the reference to it inside SQL. I use dual because I want to increment my sequence by just one value, and dual (theoretically) has just a single row.
But don't expose this logic in your application; put it inside its own function. For one thing, we generally assume just a single row and don't check for TOO_MANY_ROWS. As many developers have learned, this can be a dangerous assumption. Second, someday Oracle may let us reference NEXTVAL directly in our PL/SQL code. If we don't hide the logic, we are then stuck with this "mythological" code forever. (Read my column in the September/October 2004 issue of Oracle Magazine for more on this topc.)
Finally, if you would like to get lots more detail about cursor management and reuse in PL/SQL code, please check out Bryn Llewellyn's excellent article, "Cursor Reuse in PL/SQL Static SQL."
For years now (and in the first two editions of your book, Oracle PL/SQL Programming), you have been telling us that we should always use explicit cursors and avoid implicit ones because they are always slower. Why did you change your mind in the third edition of your book, and what is your current advice about the type of cursors to use?
Answer:
Asim, I will answer those questions in the reverse order. But first a quick review of the basic options for writing cursors:
• Implicit (static) cursor: commonly refers to the good old SELECT INTO, in which Oracle implicitly opens, executes and closes the cursor for you, depositing any selected values of a single row INTO program data structures.
• CREATE OR REPLACE PROCEDURE show_title (author_in IN magazine.author%TYPE)
• IS
• l_title magazine.title%TYPE;
• BEGIN
• SELECT title
• INTO l_title
• FROM magazine
• WHERE author = author_in;
• END;
• Explicit (static) cursor: in which you explicitly declare your cursor, and then explicitly perform each operation on that cursor yourself. This form will not raise NO_DATA_FOUND or TOO_MANY_ROWS; you decide for yourself if those conditions are actually errors.
• CREATE OR REPLACE PROCEDURE show_title (author_in IN magazine.author%TYPE)
• IS
• CURSOR title_cur
• IS
• SELECT title
• FROM magazine
• WHERE author = author_in;
•
• title_rec title_cur%ROWTYPE;
• BEGIN
• OPEN title_cur;
• FETCH title_cur INTO title_rec;
• CLOSE title_cur;
• END;
•
• Cursor FOR loop: another type of implicit cursor, but intended for use with multi-row queries (you could use it for a single row query). It opens the cursor, fetches each row within a loop, and then closes the cursor:
• BEGIN
• FOR title_rec IN (SELECT title
• FROM magazine)
• LOOP
• calculate_magazine_sales (title_rec.title);
• END LOOP;
• END;
• BULK COLLECT INTO: introduced in Oracle8i, BULK COLLECT allows you to retrieve multiple rows of data directly into PL/SQL Collections. It will raise NO_DATA_FOUND if it doesn't find any rows, but it certainly doesn't raise TOO_MANY_ROWS if it finds more than one!
• DECLARE
• TYPE title_aat IS TABLE OF magazine.title%TYPE
• INDEX BY BINARY_INTEGER;
•
• l_titles title_aat;
• BEGIN
• SELECT title
• BULK COLLECT INTO l_titles
• FROM magazine;
• END;
•
• Dynamic variations of the same: you can use DBMS_SQL or the newer Native Dynamic SQL to execute queries in PL/SQL that are constructed at run-time. I won't go into dynamic SQL in any more detail now; perhaps (likely) it will surface in a future Q&A.
You can download the examples for each of the above cursors here.
Now, here is my advice regarding the kinds of cursors to use when fetching data from Oracle in PL/SQL programs:
• Whenever possible, use BULK COLLECT—it offers dramatic performance gains. In Oracle9i Release 2, you can even use BULK COLLECT to fetch multiple rows directly into a collection of records. Of course, when you want/need to fetch just a single row, BULK COLLECT doesn't make sense. So in those cases...
• Whatever kind of cursor you decide to use to retrieve a single row of data, hide that cursor behind a function interface. In other words, ahem, I don't really care which form of cursor you use. Choose the one that performs best and/or is most easily maintained—and then stuff it behind a function. By placing a layer of PL/SQL code between your application logic and the underlying SQL, you give yourself the latitude to change the implementation of the query without affecting your application code.
• Implicit cursors are often faster than explicit cursors (although I imagine that in most situations, the performance difference won't make much difference to the user), so you should certainly consider using them—as long as you place the SELECT INTO inside a function.
• If the table from which you are querying is small and static (or perhaps very rarely changes and then only when users are not on the system), consider caching that table in a package-level PL/SQL collection. Your query function can then retrieve data from the collection (a PGA-based cache) rather than from the SGA—and it will be significantly faster.
• In a special case, please place your SELECT FROM dual statements inside a function. The use of dual is generally a workaround to access SQL functionality not yet natively available in PL/SQL. The most common example of this is
• SELECT my_sequence.NEXTVAL
• INTO l_new_key
FROM dual
I cannot call my_sequence.NEXTVAL in PL/SQL, so I embed the reference to it inside SQL. I use dual because I want to increment my sequence by just one value, and dual (theoretically) has just a single row.
But don't expose this logic in your application; put it inside its own function. For one thing, we generally assume just a single row and don't check for TOO_MANY_ROWS. As many developers have learned, this can be a dangerous assumption. Second, someday Oracle may let us reference NEXTVAL directly in our PL/SQL code. If we don't hide the logic, we are then stuck with this "mythological" code forever. (Read my column in the September/October 2004 issue of Oracle Magazine for more on this topc.)
Finally, if you would like to get lots more detail about cursor management and reuse in PL/SQL code, please check out Bryn Llewellyn's excellent article, "Cursor Reuse in PL/SQL Static SQL."
Whats going on inside that cursor FOR loop
Question:
I have created a procedure that uses a cursor FOR loop. This procedure works fine until I try to use an exception handler to trap and display a no data found message. Realizing that WHEN NO_DATA_FOUND doesn't work with the FOR loop, I declared an exception and put an IF THEN structure as the first statement in the FOR loop—trying both cursor_name%NOTFOUND and cursor_name%ROWCOUNT = 0 to raise the exception. I also tried it everywhere else in the code block, but this is the only place where it doesn't interfere with execution when data is found.
Here is my code:
CREATE OR REPLACE PROCEDURE customer_orders (custid CHAR)
AS
CURSOR order_cursor
IS
SELECT order_number, order_date, order_total
FROM orders
WHERE customer_number = custid;
I have created a procedure that uses a cursor FOR loop. This procedure works fine until I try to use an exception handler to trap and display a no data found message. Realizing that WHEN NO_DATA_FOUND doesn't work with the FOR loop, I declared an exception and put an IF THEN structure as the first statement in the FOR loop—trying both cursor_name%NOTFOUND and cursor_name%ROWCOUNT = 0 to raise the exception. I also tried it everywhere else in the code block, but this is the only place where it doesn't interfere with execution when data is found.
Here is my code:
CREATE OR REPLACE PROCEDURE customer_orders (custid CHAR)
AS
CURSOR order_cursor
IS
SELECT order_number, order_date, order_total
FROM orders
WHERE customer_number = custid;
customerrow order_cursor%ROWTYPE;
e_no_data EXCEPTION;
BEGIN
FOR customerrow IN order_cursor
LOOP
IF order_cursor%NOTFOUND
THEN
RAISE e_no_data;
END IF;
DBMS_OUTPUT.put_line ( 'Order: '
|| customerrow.order_number
|| ' was placed on '
|| customerrow.order_date
|| ' for '
|| customerrow.order_total
);
END LOOP;
EXCEPTION
WHEN e_no_data
THEN
raise_application_error (-20101, 'This is not a valid Customer');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error- ' || SQLERRM);
END;
So my question is: how can I loop through all of the rows identified by my SELECT statement and still detect when I haven't fetched anything at all?
Answer:
Jim, you are closer to achieving your objective than you might think. Let's review some of the basics of the cursor FOR loop:
• It does lots of work for you, namely opening, fetching from, and closing the cursor. That's nice, except you lose some control and you can also "experience technical difficulties" when it comes to getting information about what happened in your cursor FOR loop, when the loop as finished executing (such as "How many rows did I fetch? In fact, did I fetch any at all?").
• A cursor FOR loop never raises NO_DATA_FOUND and the reference to the cursor attribute %NOTFOUND within a cursor FOR loop will never return TRUE. After all, if you are executing the code inside the loop body, you are doing so precisely because a row was fetched and found!
With those points in mind, I expect the flaw in your logic above is clear. You will find below a modification of your program, with new lines bolded. By keeping track of the number of rows fetched inside the cursor FOR loop, I can now detect a "no rows found" condition after the loop is complete.
CREATE OR REPLACE PROCEDURE customer_orders (custid CHAR)
AS
CURSOR order_cursor
IS
SELECT order_number, order_date, order_total
FROM orders
WHERE customer_number = custid;
l_rowcount PLS_INTEGER DEFAULT 0;
e_no_data EXCEPTION;
BEGIN
FOR customerrow IN order_cursor
LOOP
l_rowcount := order_cursor%ROWCOUNT;
DBMS_OUTPUT.put_line ( 'Order: '
|| customerrow.order_number
|| ' was placed on '
|| customerrow.order_date
|| ' for '
|| customerrow.order_total
);
END LOOP;
IF l_rowcount = 0 THEN RAISE e_no_data; END IF;
EXCEPTION
WHEN e_no_data
THEN
raise_application_error (-20101, 'This is not a valid Customer');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error- ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
Note that I use DBMS_UTILITY.FORMAT_ERROR_STACK instead of SQLERRM. They return the same information, but the latter doesn't suffer from the former's size limit of 255 characters.
I could also, by the way, switch from a cursor FOR loop to a simple loop, and then simply check my cur%ROWCOUNT attribute after I am done looping, but before I close the cursor. This approach (shown below) would save me from having to declare a local variable to hold the count, though I would now need to declare a record to hold the data retrieved by the cursor (the cursor FOR loop does that for me implicitly).
CREATE OR REPLACE PROCEDURE customer_orders (custid CHAR)
AS
CURSOR order_cursor
IS
SELECT order_number, order_date, order_total
FROM orders
WHERE customer_number = custid;
customerrow order_cursor%ROWTYPE;
e_no_data EXCEPTION;
BEGIN
OPEN order_cursor;
LOOP
FETCH order_cursor INTO customerrow;
EXIT WHEN order_cursor%NOTFOUND;
DBMS_OUTPUT.put_line ( 'Order: '
|| customerrow.order_number
|| ' was placed on '
|| customerrow.order_date
|| ' for '
|| customerrow.order_total
);
END LOOP;
IF order_cursor%ROWCOUNT = 0 THEN RAISE e_no_data; END IF;
CLOSE order_cursor;
EXCEPTION
WHEN e_no_data
THEN
RAISE_APPLICATION_ERROR (-20101, 'This is not a valid Customer');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error- ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/
Side note: you might want to review these concepts in more detail with a trip to the "Using Cursor Attributes" section of Chapter 6 in the PL/SQL User's Guide and Reference.
We could stop there, but I also had the sense, looking at your code, that you probably wrote this program following the traditional "quick and dirty" method: sitting down and typing!
I strongly encourage you to always take at least a few minutes to reflect on the "big picture" of what you are about to write—and then use that picture to guide your coding. For example, after reviewing your code, I "reverse engineered" it into the following brief narrative:
• Overview: display the orders for a customer ID.
• Validations: make sure that the customer ID is valid. If invalid print out a message informing the user of the problem.
• Basic algorithm: If customer number is valid, display any orders found in the orders table for the customer.
I then crafted a program that is structured as follows:
1 CREATE OR REPLACE PROCEDURE display_orders_for_cust (
2 customer_number_in IN orders.customer_number%TYPE
3 )
4 IS
5 l_at_least_one BOOLEAN;
6
7 CURSOR order_cur (cn IN orders.customer_number%TYPE)
8 IS
9 SELECT order_number
10 ,order_date
11 ,order_total
12 FROM orders
13 WHERE customer_number = cn;
14
15 PROCEDURE validate_customer (cn IN orders.customer_number%TYPE)
16 IS
17 BEGIN
18 -- Insert check against customer table to make sure
19 -- that this customer number if valid, which is a
20 -- separate check from whether or not it has any
21 -- orders. You will probably want to raise an
22 -- exception if it is invalid.
23 NULL;
24 END validate_customer;
25
26 PROCEDURE display_order (order_in IN order_cur%ROWTYPE)
27 IS
28 BEGIN
29 DBMS_OUTPUT.put_line ( 'Order: '
30 || order_in.order_number
31 || ' was placed on '
32 || order_in.order_date
33 || ' for '
34 || order_in.order_total
35 );
36 END display_order;
37
38 PROCEDURE display_no_orders (cn IN orders.customer_number%TYPE)
39 IS
40 BEGIN
41 DBMS_OUTPUT.put_line ( 'No orders found for customer ID '
42 || cn
43 );
44 END display_no_orders;
45 BEGIN
46 validate_customer (customer_number_in);
47
48 FOR order_rec IN order_cur (customer_number_in)
49 LOOP
50 display_order (order_rec);
51 l_at_least_one := TRUE;
52 END LOOP;
53
54 IF NOT l_at_least_one
55 THEN
56 display_no_orders (customer_number_in);
57 END IF;
58* END display_orders_for_cust;
Notice that the main body of this program (lines 46 through 57) hides the complexity and details behind local procedures. By replacing multiple lines of code with a named program unit, I essentially let my code "speak for itself." Of course, there isn't much complexity to hide in this case—but then most of our application code is considerably more complicated than this!
You can download the code for this program, with all the DDL needed to get it to compile, here.
By the way, I didn't have access to the DDL for your orders table, but is the custID column really of type CHAR? You should avoid declaring columns, variables—just about anything—of type CHAR, because it is fixed-length and can cause unexpected results when performing comparisons.
Comment:
from Ben Griffiths, Brisbane, Australia
Steven,
With the 2000 byte return value of DBMS_UTILITY.FORMAT_ERROR_STACK inside the 255 byte DBMS_OUTPUT.PUT_LINE, don't you run the risk of creating an exception within your exception handler?
What Loop with BULK COLLECT collections
Question:
I noticed in your answer to a question regarding PL/SQL support for bi-directional cursors that you used BULK COLLECT to fill a collection, and then iterated through the contents of that collection using a WHILE loop, and the FIRST and NEXT methods.
As I am sure you know, BULK COLLECT queries always fill the collection sequentially, starting from row 1. Therefore, you could simply use a FOR loop (from 1 to collection.COUNT) to iterate through the collection elements. This approach requires less code, is more efficient, and is very readable, so why didn't you take that approach?
Answer:
Bryn is correct; I could have used a FOR loop in my bidirectional emulation example. You can, in fact, use any kind of PL/SQL loop to iterate through the indexes of a collection: FOR loop, WHILE loop, or a simple loop. And regardless of the loop type that you choose, you need to take into account the following concerns:
• If the collection is empty, you don't want the loop body to execute even a single time.
• If within the loop body you attempt to read an element at an index that does not exist, Oracle will raise the NO_DATA_FOUND exception.
Before I explain why I chose the WHILE loop, let's take a look at the code you would write when using each of these kinds of loops. Then I will respond to Bryn's suggestion and finish up with my advice to you.
Suppose I have a table that contains jokes:
CREATE TABLE jokes (
joke_id INTEGER,
title VARCHAR2(100),
text VARCHAR2(4000)
)
/
I need to iterate through all the jokes in the table and display the title of each joke. In each block below, I will use a BULK COLLECT query to retrieve all the rows of the table and move them into my collection. I will then write three different loops to iterate through the collection's contents.
As Bryn correctly points out, when I use BULK COLLECT to retrieve multiple rows of data, that data is deposited into a collection that is populated sequentially, starting with index value 1. If no rows are identified by the query, the collection is empty.
Here we go!
Using the FOR loop
DECLARE
TYPE joke_tt IS TABLE OF jokes%ROWTYPE INDEX BY PLS_INTEGER;
joke_cache joke_tt;
BEGIN
SELECT * BULK COLLECT INTO joke_cache FROM jokes;
I noticed in your answer to a question regarding PL/SQL support for bi-directional cursors that you used BULK COLLECT to fill a collection, and then iterated through the contents of that collection using a WHILE loop, and the FIRST and NEXT methods.
As I am sure you know, BULK COLLECT queries always fill the collection sequentially, starting from row 1. Therefore, you could simply use a FOR loop (from 1 to collection.COUNT) to iterate through the collection elements. This approach requires less code, is more efficient, and is very readable, so why didn't you take that approach?
Answer:
Bryn is correct; I could have used a FOR loop in my bidirectional emulation example. You can, in fact, use any kind of PL/SQL loop to iterate through the indexes of a collection: FOR loop, WHILE loop, or a simple loop. And regardless of the loop type that you choose, you need to take into account the following concerns:
• If the collection is empty, you don't want the loop body to execute even a single time.
• If within the loop body you attempt to read an element at an index that does not exist, Oracle will raise the NO_DATA_FOUND exception.
Before I explain why I chose the WHILE loop, let's take a look at the code you would write when using each of these kinds of loops. Then I will respond to Bryn's suggestion and finish up with my advice to you.
Suppose I have a table that contains jokes:
CREATE TABLE jokes (
joke_id INTEGER,
title VARCHAR2(100),
text VARCHAR2(4000)
)
/
I need to iterate through all the jokes in the table and display the title of each joke. In each block below, I will use a BULK COLLECT query to retrieve all the rows of the table and move them into my collection. I will then write three different loops to iterate through the collection's contents.
As Bryn correctly points out, when I use BULK COLLECT to retrieve multiple rows of data, that data is deposited into a collection that is populated sequentially, starting with index value 1. If no rows are identified by the query, the collection is empty.
Here we go!
Using the FOR loop
DECLARE
TYPE joke_tt IS TABLE OF jokes%ROWTYPE INDEX BY PLS_INTEGER;
joke_cache joke_tt;
BEGIN
SELECT * BULK COLLECT INTO joke_cache FROM jokes;
FOR indx IN 1 .. joke_cache.COUNT
LOOP
DBMS_OUTPUT.put_line (' ' || joke_cache (indx).title);
END LOOP;
END;
/
With a FOR loop, I iterate from the first index, 1, to the sm COUNT of rows in the collection. If the collection is empty (there's nothing in the jokes table), sm COUNT returns 0, and the sm FOR loop body will not execute even once. Because I know that the collection will be densely-filled, I don't have to worry about raising the NO_DATA_FOUND exception.
Notice that with the FOR loop implementation, I don't have to: declare the local variable to hold the index value; set up the WHILE loop with a call to the FIRST method; explicitly code a loop terminating condition; or call the NEXT method to move to the next row. The FOR loop does all of that for me, implicitly.
Next stop: the WHILE loop.
Using the WHILE Loop
DECLARE
TYPE joke_tt IS TABLE OF jokes%ROWTYPE INDEX BY PLS_INTEGER;
joke_cache joke_tt;
l_index PLS_INTEGER;
BEGIN
SELECT * BULK COLLECT INTO joke_cache FROM jokes;
l_index := joke_cache.FIRST;
WHILE (l_index IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (' ' || joke_cache (l_index).title);
l_index := joke_cache.NEXT (l_index);
END LOOP;
END;
/
With the WHILE loop, I must declare a local variable to hold the current index into the collection. After populating the collection, I call the FIRST method to obtain the lowest-defined element in the collection. If the collection is empty, FIRST returns NULL and the loop body never executes. Otherwise, the title is displayed and then I move to the next defined index with the NEXT method.
Using the Simple Loop
DECLARE
TYPE joke_tt IS TABLE OF jokes%ROWTYPE INDEX BY PLS_INTEGER;
joke_cache joke_tt;
l_index PLS_INTEGER;
BEGIN
SELECT * BULK COLLECT INTO joke_cache FROM jokes;
l_index := joke_cache.FIRST;
LOOP
EXIT WHEN l_index IS NULL;
DBMS_OUTPUT.put_line (' ' || joke_cache (l_index).title);
l_index := joke_cache.NEXT (l_index);
END LOOP;
END;
/
The code using a simple loop varies only slightly from the WHILE loop implementation. I still call the FIRST method to obtain the lowest-defined element in the collection. But I move the termination condition for the loop inside the loop with the EXIT statement. Again, if the collection is empty, FIRST returns NULL and the loop immediately terminates. Otherwise, the title is displayed and then I move to the next defined index with the NEXT method.
Comparing the Approaches
So: three different types of loops can be used to iterate through the contents of a collection. Which should you use?
The FOR loop is a good choice for iterating through a collection's contents when you know for certain that the collection is densely-filled; that is, ever index between the first-defined row and the last-defined row is defined. If there are no gaps in the collection, then you can use the FOR loop with confidence and, as Bryn points out in his question, write and maintain less code in the process.
The WHILE loop is your best bet when you are working with a collection that might have a gap: one or more index values between lowest and highest that are not defined. In this situation, the use of the NEXT method neatly avoids the NO_DATA_FOUND exception.
You could also use the simple loop with a sparsely-filled collection. Generally, however, you should use a simple loop when you want to execute the body of the loop at least once. If the first line in your loop body is the EXIT WHEN statement, the WHILE loop is a more natural and intuitive implementation.
Should performance be a factor in choosing the type of loop?
Bryn also points out that the FOR loop implementation should be more efficient. How much of a factor should that be in my decision on which kind of loop to write?
My analysis shows that the FOR loop is, indeed, more efficient than the WHILE loop for iterating through a densely-filled collection, but the different is so slight as to be irrelevant for most applications. For example, in the anonymous block shown below, I populate a collection with 10,000,000 elements. I then iterate through that collection using FOR and WHILE loops.
The FOR loop consistently takes about 1.25 seconds to iterate through all the rows. The WHILE loop took about 3.15 seconds. So alternatively, you could argue that the FOR loop approach is almost 3X faster than the WHILE loop—but that only amounts to an extra 2 seconds over 10,000,000 elements. Since your collections are generally going to be several orders of magnitude smaller, I don't think this performance differential should be a factor in your selection of a collection-iterating technique.
Summary
In the comparison above, I noted that the FOR loop is most appropriate when you know that the collection is densely-filled. The bidirectional cursor emulation example fits that bill, and yet still I used a WHILE loop. In fact, I virtually always use a WHILE loop when iterating through a collection and I will continue to make that recommendation to my readers and students.
You can't really go wrong with a WHILE loop; it might run marginally slower than a FOR loop, but I doubt it's a performance hit that will ever be noticed. But it will ensure that you never raise NO_DATA_FOUND, whether your collection is sparsely- or densely-filled.
To use the FOR loop, I must make an assumption about the collection—namely, that it is either densely-filled or empty; the collection may not be sparse. (The FOR loop will raise NO_DATA_FOUND if it encounters a "gap" or undefined index location between 1 and the COUNT.) Today, that seems like an awfully safe assumption to make. After all, the FOR loop comes immediately after the SELECT statement.
What if over time, however, some additional code is placed between
SELECT * BULK COLLECT INTO joke_cache FROM jokes;
and
FOR indx IN 1 .. joke_cache.COUNT
and that code includes a call to the DELETE method to remove one or more rows from the collection? Something like this:
DECLARE
TYPE joke_tt IS TABLE OF jokes%ROWTYPE INDEX BY PLS_INTEGER;
joke_cache joke_tt;
BEGIN
SELECT * BULK COLLECT INTO joke_cache FROM jokes;
... lots of code
jokes.DELETE (jokes.LAST – 10);
... lots more code
FOR indx IN 1 .. joke_cache.COUNT
LOOP
DBMS_OUTPUT.put_line (' ' || joke_cache (indx).title);
END LOOP;
END;
/
Perhaps it should be obvious to the person adding this code that the FOR loop needs to be converted to a WHILE loop, but it is very likely that, in fact, this will be overlooked and a problem will have been introduced into the program. I'd much rather make the fewest assumptions possible in my code and thus make the code more resilient over time.
Having said that, when your code requires and expects the collection to be densely-filled, you should use the FOR loop implementation. Under these circumstances, a gap in the collection would indicate an error. This error should not be "covered up" by the WHILE loop; instead, you will want the error to interrupt normal program processing. You can do this by allowing the exception to propagate unhandled from the current block or you could place the FOR loop within its own block, then trap and log the error. Here is an example, courtesy of Bryn:
begin
for Idx in 1..Supposedly_Dense_Collection.Count()
loop
DBMS_Output.Put_Line(Supposedly_Dense_Collection(Idx));
end loop;
exception when No_Data_Found then
-- Your favorite logging technique
Raise_Application_Error(-20000, 'Supposedly_Dense_Collection wasn''t');
end;
Bryn also offers the following code to demonstrate (in his words) "a nice idiom for asserting that your collection is dense (actually, that it's unchanging after first being populated dense.) It relies on using the 'constant' keyword together with a forward-declared init function. I believe that this technique is relatively unknown."
procedure P is
begin
--...
<<Block_To_Process_Dense_Array>>declare
type Users_t is table of All_Users.Username%type;
function Users_In_DB return Users_t;
Users constant Users_t := Users_In_DB();
Nof_Users constant pls_integer := Users.Count();
function Users_In_DB return Users_t is
Users Users_t;
begin
select Username bulk collect into Users
from All_Users
order by Created;
if Users.Count() < 1 then raise Program_Error; end if;
return Users;
end Users_In_DB;
begin
-- Causes a compile error.
-- Users.Delete(1);
for j in 1..Nof_Users loop
DBMS_Output.Put_Line(Users(j));
end loop;
end Block_To_Process_Dense_Array;
--...
end P;
Bryn's continued explanation: "Block_To_Process_Dense_Array is set in a procedure just so that you can compile it first and then run it. It could occur anywhere at any depth of nesting. It's particularly nice for setting up a package body constant. (The idiom won't work for a spec constant unless the init function is in a separate compilation unit.)
"Note: I use Program_Error in these little demo programs just to keep the code shorter and not to detract from the main point with detail. In real life, you'd want to declare your own exception and/or use Raise_Application_Error();."
Script to compare performance of FOR and WHILE loops to iterate a collection
DECLARE
TYPE array_t IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
ARRAY array_t;
--
l_start_time PLS_INTEGER;
l_end_time PLS_INTEGER;
--
l_index PLS_INTEGER;
BEGIN
-- Populate the collection.
FOR n IN 1 .. 10000000
LOOP
ARRAY (n) := n;
END LOOP;
-- Cache the starting time.
l_start_time := DBMS_UTILITY.get_cpu_time;
FOR n IN 1 .. ARRAY.COUNT
LOOP
ARRAY (n) := n;
END LOOP;
-- Subtract end time from start time.
DBMS_OUTPUT.put_line ( 'FOR loop elapsed CPU time: '
|| TO_CHAR (DBMS_UTILITY.get_cpu_time - l_start_time)
);
--
l_start_time := DBMS_UTILITY.get_cpu_time;
l_index := ARRAY.FIRST;
WHILE (l_index IS NOT NULL)
LOOP
ARRAY (l_index) := l_index;
l_index := ARRAY.NEXT (l_index);
END LOOP;
DBMS_OUTPUT.put_line ( 'WHILE loop elapsed CPU time: '
|| TO_CHAR (DBMS_UTILITY.get_cpu_time - l_start_time)
);
END;
/
What is the proper use of AUTHID CURRENT_USER
Question:
I encountered a bottleneck recently when coding Oracle PL/SQL programs around a data dictionary view. This is the kind of code I want to run:
CREATE OR REPLACE PROCEDURE view_schema_tables (v_schema VARCHAR2)
IS
CURSOR c1
IS
SELECT table_name
FROM all_tables
WHERE owner = v_schema;
I encountered a bottleneck recently when coding Oracle PL/SQL programs around a data dictionary view. This is the kind of code I want to run:
CREATE OR REPLACE PROCEDURE view_schema_tables (v_schema VARCHAR2)
IS
CURSOR c1
IS
SELECT table_name
FROM all_tables
WHERE owner = v_schema;
rec_schema c1%ROWTYPE;
v_count NUMBER := 0;
BEGIN
FOR rec_schema IN c1
LOOP
DBMS_OUTPUT.put_line (rec_schema.table_name);
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.put_line ('Count: ' || v_count);
END;
I granted EXECUTE authority on this program to certain users who have the authority to view other schemas' complete table list.
The problem here is that I can logon to SQL*Plus and execute this SQL statement directly:
SQL> SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='HR';
to get the expected results. But if I run the procedure, the results are different. Shouldn't I get the same results, regardless of where I run the query?
Answer:
Samuel, assuming you are running Oracle8i or later, the solution is close at hand: simply add the AUTHID CURRENT_USER clause to your program header. In other words, define your program as follows:
CREATE OR REPLACE PROCEDURE view_schema_tables (v_schema VARCHAR2)
AUTHID CURRENT_USER
IS
CURSOR c1
IS
SELECT table_name
FROM all_tables
WHERE owner = v_schema;
rec_schema c1%ROWTYPE;
v_count NUMBER := 0;
BEGIN
FOR rec_schema IN c1
LOOP
DBMS_OUTPUT.put_line (rec_schema.table_name);
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.put_line ('Count: ' || v_count);
END;
By adding AUTHID CURRENT_USER, you are telling the PL/SQL runtime engine to use the authority (privileges) of the current user (caller of the program), rather than those of the owner or definer of the program.
Note that with AUTHID CURRENT_USER (known as "invoker rights" execution mode), the runtime engine will also use roles active in that session to resolve references to any tables or views in your SQL statements.
What is the preferred way to make functions that have Boolean parameters or return types accessible from within queries
Question:
Thanks for all your books over the years, they have made me a much more effective developer.
Following on your mantra of encapsulating and naming business rules within functions (Oracle PL/SQL Best Practices, O'Reilly & Associates), I wonder if you have a standard or preferred way of making functions that have Boolean parameters or return types accessible from within queries.SQL lacks a Boolean data type and that leaves me wondering what to do and how best to do it.
Answer:
Dear Andy: That is an irritation, isn't it? The PL/SQL language features a native Boolean datatype, but BOOLEAN hasn't yet made its way into the Oracle SQL language. So if you want to store true/false flags in columns of a table, you will need to use string or integer values to represent those true/false values. You could, for example, decide that 'Y' and 'N' do the trick. Perhaps it should be 'T' and 'F'? Of course, you could always use the classic integer values 0 and 1?
To my mind, it doesn't really matter which approach you take, as long as it is self-documenting and applied consistently across all tables (and don't forget that NULL is a valid value for a Boolean).
And that is the main challenge. How do you make sure that these values are handled consistently across your application?
The best way to solve this problem is to create a package that performs the translations for you, based on functions that can be used both in PL/SQL programs and SQL statements. Here is a bare-bones implementation of such a package. (Click here to download this package.)
CREATE OR REPLACE PACKAGE boolean_pkg
IS
FUNCTION bool_to_str (boolean_in IN BOOLEAN)
RETURN VARCHAR2;
Thanks for all your books over the years, they have made me a much more effective developer.
Following on your mantra of encapsulating and naming business rules within functions (Oracle PL/SQL Best Practices, O'Reilly & Associates), I wonder if you have a standard or preferred way of making functions that have Boolean parameters or return types accessible from within queries.SQL lacks a Boolean data type and that leaves me wondering what to do and how best to do it.
Answer:
Dear Andy: That is an irritation, isn't it? The PL/SQL language features a native Boolean datatype, but BOOLEAN hasn't yet made its way into the Oracle SQL language. So if you want to store true/false flags in columns of a table, you will need to use string or integer values to represent those true/false values. You could, for example, decide that 'Y' and 'N' do the trick. Perhaps it should be 'T' and 'F'? Of course, you could always use the classic integer values 0 and 1?
To my mind, it doesn't really matter which approach you take, as long as it is self-documenting and applied consistently across all tables (and don't forget that NULL is a valid value for a Boolean).
And that is the main challenge. How do you make sure that these values are handled consistently across your application?
The best way to solve this problem is to create a package that performs the translations for you, based on functions that can be used both in PL/SQL programs and SQL statements. Here is a bare-bones implementation of such a package. (Click here to download this package.)
CREATE OR REPLACE PACKAGE boolean_pkg
IS
FUNCTION bool_to_str (boolean_in IN BOOLEAN)
RETURN VARCHAR2;
FUNCTION str_to_bool (string_in IN VARCHAR2)
RETURN BOOLEAN;
FUNCTION true_value
RETURN VARCHAR2;
FUNCTION false_value
RETURN VARCHAR2;
END boolean_pkg;
/
CREATE OR REPLACE PACKAGE BODY boolean_pkg
IS
c_true CONSTANT VARCHAR2 (1) := 'T';
c_false CONSTANT VARCHAR2 (1) := 'F';
FUNCTION bool_to_str (boolean_in IN BOOLEAN)
RETURN VARCHAR2
IS
BEGIN
IF boolean_in
THEN
RETURN c_true;
ELSIF NOT boolean_in
THEN
RETURN c_false;
ELSE
RETURN NULL;
END IF;
END bool_to_str;
FUNCTION str_to_bool (string_in IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
IF string_in = c_true
THEN
RETURN TRUE;
ELSIF string_in = c_false
THEN
RETURN FALSE;
ELSE
RETURN NULL;
END IF;
END str_to_bool;
FUNCTION true_value
RETURN VARCHAR2
IS
BEGIN
RETURN c_true;
END true_value;
FUNCTION false_value
RETURN VARCHAR2
IS
BEGIN
RETURN c_false;
END false_value;
END boolean_pkg;
/
With this package in place, I can write statements like these:
REM Create a demonstration table.
CREATE TABLE my_emp (
name VARCHAR2(100), bonus_eligibility VARCHAR2(1));
REM Find all employees eligible for a raise.
SELECT *
FROM my_emp
WHERE bonus_eligibility = boolean_pkg.true_value;
REM Steven is not eligible.
INSERT INTO my_emp VALUES ('Steven', boolean_pkg.false_value);
REM Convert from table-based string to PL/SQL Boolean variable.
DECLARE
l_is_eligible BOOLEAN;
BEGIN
FOR emp_rec IN (SELECT * FROM my_emp)
LOOP
l_is_eligible :=
boolean_pkg.str_to_bool (
emp_rec.bonus_eligibility);
IF l_is_eligible
THEN
END LOOP;
END;
/
One final thought: as I have recommended frequently in my writings, the best way to manage data in tables is to provide a table API or package layer that sits over the table and controls access to it. With this layer in place, you can also build the Boolean translation into the table API, thereby hiding it entirely from application developers (the users of this table).
What is the difference between INSERT-SELECT and FORALL-INSERT
Question:
What is the difference between INSERT-SELECT and FORALL-INSERT, aside from having to load the latter to the collection before issuing INSERT?
Answer:
What is the difference between INSERT-SELECT and FORALL-INSERT, aside from having to load the latter to the collection before issuing INSERT?
Answer:
Tere, INSERT-SELECT and FORALL-INSERT both offer very high-speed methods for inserting multiple rows into a table.
INSERT-SELECT is a SQL implementation that transfers data from one table to another. You can, however, call a PL/SQL function in the column list of the SELECT statement.
FORALL-INSERT is a PL/SQL extension to the standard SQL INSERT. It requires, as you note correctly in your question, the use of a collection to cache the data and then push it very efficiently into the table of your choice.
Which you choose to use depends on your application requirements, but I suggest you follow these guidelines:
1. If you can perform the data transfer within an INSERT-SELECT format, it is likely to be the fastest approach. For example, in some of my own tests, I was able to execute an INSERT-SELECT of approximately 100,000 rows in 0.38 seconds, while it took FORALL-INSERT 1.02 seconds to insert the same number of rows.
2. If you need to rely on PL/SQL processing to apply complex transformations to your data before inserting it into the new table, you will probably want to shift to the FORALL approach. Manipulation of the contents of a collection is very efficient, as is the FORALL mechanism. (One second for 100,000 rows is, after all, very fast!)
Weekdays, Weekends, and Holidays
Question:
I need to use an anonymous block to populate a table with 30 consecutive dates. The table contains the columns SALE DAY and DAY TYPE, with R indicating regular days, W indicating weekends, and H indicating holidays. How would I do that?
Answer:
Otis, you can use the TO_CHAR function to determine the number or name of the day, and from that whether your day falls on a weekday or weekend. Example:
DECLARE
FUNCTION day_type (date_in IN DATE)
RETURN VARCHAR2
IS
l_daynum PLS_INTEGER := TO_NUMBER (TO_CHAR (date_in, 'D'));
BEGIN
IF l_daynum IN (1, 7)
THEN
RETURN 'Weekend';
ELSE
RETURN 'Weekday';
END IF;
END day_type;
BEGIN
FOR daynum IN 0 .. 29
LOOP
DBMS_OUTPUT.put_line (SYSDATE+daynum);
DBMS_OUTPUT.put_line (' is a ' || day_type (SYSDATE+daynum));
END LOOP;
END;
I need to use an anonymous block to populate a table with 30 consecutive dates. The table contains the columns SALE DAY and DAY TYPE, with R indicating regular days, W indicating weekends, and H indicating holidays. How would I do that?
Answer:
Otis, you can use the TO_CHAR function to determine the number or name of the day, and from that whether your day falls on a weekday or weekend. Example:
DECLARE
FUNCTION day_type (date_in IN DATE)
RETURN VARCHAR2
IS
l_daynum PLS_INTEGER := TO_NUMBER (TO_CHAR (date_in, 'D'));
BEGIN
IF l_daynum IN (1, 7)
THEN
RETURN 'Weekend';
ELSE
RETURN 'Weekday';
END IF;
END day_type;
BEGIN
FOR daynum IN 0 .. 29
LOOP
DBMS_OUTPUT.put_line (SYSDATE+daynum);
DBMS_OUTPUT.put_line (' is a ' || day_type (SYSDATE+daynum));
END LOOP;
END;
Ways to hide your code
Question:
I want to allow everyone to run my procedures and functions, but I don't want them to be able to see the code behind the program header. How can I accomplish that?
Answer:
Oracle offers two ways to hide your code:
1. "Wrap" or obfuscate your code so that it can still be compiled and executed, but cannot be read
2. Hide program implementations within a package body
Wrapping is certainly the best way to ensure that others cannot read your code, even if they connect to the schema that owns the code. However, it does have drawbacks. Let's take a look at both options.
To wrap your code, call the wrap executable. Suppose, for example, that I start with the following procedure definition in the file before_wrap.sql:
CREATE OR REPLACE PROCEDURE my_procedure
IS
BEGIN
DBMS_OUTPUT.put_line ( 'running my_procedure' );
END my_procedure;
/
I then call the wrap executable as follows:
wrap iname=before_wrap.sql oname=after_wrap.plb
The after_wrap.plb file (plb is a rough-hewn acronym for "PL/SQL Binary" and is the standard extension used by Oracle for its wrapped code) then contains the following text:
CREATE OR REPLACE PROCEDURE my_procedure wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
66 96
JglYKBnago+nNjoDigwhI233Q8Ywg5nnm7+fMr2ywFyl8F8oqVJTeta+bnFVAHNTjo6OcW7P
1oZzhITh5oTSqVcZxutyXDWENe1NIZaWgpZMqMRlvA4WJK0qY6AWXEw16+zZPXJElSH2OaY9
CKRI
I want to allow everyone to run my procedures and functions, but I don't want them to be able to see the code behind the program header. How can I accomplish that?
Answer:
Oracle offers two ways to hide your code:
1. "Wrap" or obfuscate your code so that it can still be compiled and executed, but cannot be read
2. Hide program implementations within a package body
Wrapping is certainly the best way to ensure that others cannot read your code, even if they connect to the schema that owns the code. However, it does have drawbacks. Let's take a look at both options.
To wrap your code, call the wrap executable. Suppose, for example, that I start with the following procedure definition in the file before_wrap.sql:
CREATE OR REPLACE PROCEDURE my_procedure
IS
BEGIN
DBMS_OUTPUT.put_line ( 'running my_procedure' );
END my_procedure;
/
I then call the wrap executable as follows:
wrap iname=before_wrap.sql oname=after_wrap.plb
The after_wrap.plb file (plb is a rough-hewn acronym for "PL/SQL Binary" and is the standard extension used by Oracle for its wrapped code) then contains the following text:
CREATE OR REPLACE PROCEDURE my_procedure wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
66 96
JglYKBnago+nNjoDigwhI233Q8Ywg5nnm7+fMr2ywFyl8F8oqVJTeta+bnFVAHNTjo6OcW7P
1oZzhITh5oTSqVcZxutyXDWENe1NIZaWgpZMqMRlvA4WJK0qY6AWXEw16+zZPXJElSH2OaY9
CKRI
/
While that doesn't look anything like PL/SQL code, you can compile it into the database and execute the program, just as you would if it were not wrapped. For example:
SQL> @after_wrap.plb
Procedure created.
SQL> exec my_procedure
running my_procedure
For more information about Oracle's obfuscation capabilities, read the documentation.
By the way, in Oracle Database 10g Release 2, you can use the DBMS_DDL package to obfuscate PL/SQL program units that are created dynamically (at run-time).
Obfuscation is very handy for independent software vendors who need to hide their proprietary code from the prying eyes of competitors. It is less obvious that one should use it as a general mechanism to hide code from other developers in your organization.
The problem with obfuscating your code is that no one can read the source code in the database, even the owner of the code, even those who need to debug the code to fix problems.
If you would like to compile your program into the database so that the owner of the program can read and work with code, but others (who are allowed to run the program) cannot see the implementation, you should define your programs in packages.
If you implement your functionality in schema-level (a.k.a, stand-alone) procedures and functions, then granting execute authority also allows programmers connected to other schema to see the source code of those program units. This fact is demonstrated below:
SQL> CONNECT scott/tiger
Connected.
SQL> CREATE OR REPLACE PROCEDURE my_procedure
2 IS
3 BEGIN
4 DBMS_OUTPUT.put_line ( 'my_procedure' );
5 END my_procedure;
6 /
Procedure created.
SQL> GRANT EXECUTE ON my_procedure TO hr
2 /
Grant succeeded.
SQL> CONNECT hr/hr
Connected.
SQL> SELECT TYPE, text
2 FROM all_source
3 WHERE owner = 'SCOTT' AND NAME = 'MY_PROCEDURE'
4 ORDER BY TYPE, line
5 /
TYPE TEXT
-------------------- -----------------------------------------------
PROCEDURE PROCEDURE my_procedure
PROCEDURE IS
PROCEDURE BEGIN
PROCEDURE DBMS_OUTPUT.put_line ( 'my_procedure' );
PROCEDURE END my_procedure;
Suppose that you instead implement your procedures and functions within a package. You then can grant execute authority on that package to other schemas. Users connected to those schemas will be able to only see the package specifications, and not the bodies. This fact is demonstrated below:
SQL> CONNECT scott/tiger
Connected.
SQL> CREATE OR REPLACE PACKAGE my_package
2 IS
3 PROCEDURE hidden_implementation;
4 END my_package;
5 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY my_package
2 IS
3 PROCEDURE hidden_implementation
4 IS
5 BEGIN
6 DBMS_OUTPUT.put_line ( 'hidden_implementation' );
7 END hidden_implementation;
8 END my_package;
9 /
Package body created.
SQL> GRANT EXECUTE ON my_package TO hr
2 /
Grant succeeded.
SQL> CONNECT hr/hr
Connected.
SQL> SELECT TYPE, text
2 FROM all_source
3 WHERE owner = 'SCOTT' AND NAME = 'MY_PACKAGE'
4 ORDER BY TYPE, line
5 /
TYPE TEXT
-------------------- ----------------------------------------------
PACKAGE PACKAGE my_package
PACKAGE IS
PACKAGE PROCEDURE hidden_implementation;
PACKAGE END my_package;
Notice that none of the lines from the package body are visible through ALL_SOURCE.
Using subtypes to work with string-indexed collections more easily
Question:
I have started working with multi-level and string-indexed collections. I very much like the way these features can simply the code I need to write to manipulate complex structures. But sometimes I get really confused trying to remember which data is used in which index. For example, in one program, I needed to query and keep track of some key employee information by department, using the names instead of numbers for indexes (in our company, last names of employees are unique!). So I wrote the code below, but got really confused when it was time to assign the record to the right place in my complex collection. (see "?????"). I finally figured it out, but I wonder if there is something I could change in my code to avoid the confusion. —Alejandra
DECLARE
TYPE key_info_rt IS RECORD (
hire_date employees.hire_date%TYPE
, salary employees.salary%TYPE );
I have started working with multi-level and string-indexed collections. I very much like the way these features can simply the code I need to write to manipulate complex structures. But sometimes I get really confused trying to remember which data is used in which index. For example, in one program, I needed to query and keep track of some key employee information by department, using the names instead of numbers for indexes (in our company, last names of employees are unique!). So I wrote the code below, but got really confused when it was time to assign the record to the right place in my complex collection. (see "?????"). I finally figured it out, but I wonder if there is something I could change in my code to avoid the confusion. —Alejandra
DECLARE
TYPE key_info_rt IS RECORD (
hire_date employees.hire_date%TYPE
, salary employees.salary%TYPE );
l_emp_rec key_info_rt;
TYPE employees_t IS TABLE OF key_info_rt
INDEX BY VARCHAR2 (100);
TYPE emps_in_department_t IS TABLE OF employees_t
INDEX BY VARCHAR2 (100);
l_emp_info emps_in_department_t;
BEGIN
FOR temp_rec IN (SELECT d.department_name, e.last_name, e.hire_date
, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id)
LOOP
l_emp_rec.hire_date := temp_rec.hire_date;
l_emp_rec.salary := temp_rec.salary;
/*
Now I need to put the record into the right location in my
two-level collection. But which name do I put where?
*/
?????
END LOOP;
END;
Answer:
I am glad to see you are taking advantage of these fascinating collection features. I, too, have found many ways to apply multi-level and string-indexed collections. And I, too, have sometimes gotten very confused about which string should be used in which level of the collection.
A hint to the solution, however, is right there in your code, in the user-defined record you created: use %TYPE instead of hard-coded declarations, VARCHAR2(100).
To demonstrate, I have rewritten the two TYPE statements for the collections to remove the VARCHAR2(100) usages (in bold) and put in their place a %TYPE against the appropriate column.
DECLARE
TYPE key_info_rt IS RECORD (
hire_date employees.hire_date%TYPE
, salary employees.salary%TYPE );
l_emp_rec key_info_rt;
TYPE employees_t IS TABLE OF key_info_rt
INDEX BY employees.last_name%TYPE;
TYPE emps_in_department_t IS TABLE OF employees_t
INDEX BY departments.department_name%TYPE;
l_emp_info emps_in_department_t;
Notice that this code now "self-declares" exactly what kind of data is being used for the index. You can then use this declaration as a guide to writing the correct code.
When you are first working with multi-level collections, it is best to take things a step at a time. The first step was to declare the index type very clearly.
The next step in assigning the record to the right location in the collection is to figure out the correct order in which to supply the names.
You should work from left to right. Start by writing down the name of the variable:
One problem with this code is that I am handling a specific, anticipated exception within WHEN OTHERS. This is generally a bad idea. If you know an exception might be raised, you should have a separate WHEN section for that error.
l_emp_info
It is a collection (never mind for the moment) that it is a collection of other collections. This means that you need to provide an index value to specify a particular element in the collection. Looking back at the declaration of this collection:
TYPE emps_in_department_t IS TABLE OF employees_t
INDEX BY departments.department_name%TYPE;
it is now clear that you need to provide a department name. So I then have:
l_emp_info (temp_rec.department_name)
That single element is actually another collection - each element of which, in turn, is a record containing hire date and salary. The index for this inner collection is the employee last name, so I add that information:
l_emp_info (temp_rec.department_name) (temp_rec.last_name)
and then I can assign the record to this element:
l_emp_info (temp_rec.department_name) (temp_rec.last_name)
:= l_emp_rec;
And that, Alejandra, is the assignment you need to write:
DECLARE
TYPE key_info_rt IS RECORD (
hire_date employees.hire_date%TYPE
, salary employees.salary%TYPE
);
TYPE employees_t IS TABLE OF key_info_rt
INDEX BY employees.last_name%TYPE;
TYPE emps_in_department_t IS TABLE OF employees_t
INDEX BY departments.department_name%TYPE;
l_emp_info emps_in_department_t;
BEGIN
FOR emp_rec IN (SELECT d.department_name, e.last_name, e.hire_date
, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id)
LOOP
l_emp_info (emp_rec.department_name) (emp_rec.last_name)
.hire_date := emp_rec.hire_date;
l_emp_info (emp_rec.department_name) (emp_rec.last_name)
.salary := emp_rec.salary;
END LOOP;
END;
I hope you will agree that by using the %TYPE attribute in the INDEX BY clause of the collection declarations, I was much more easily able to sort out which string to use where.
Often when working with multi-level collections, you will want to iterate through all the values, for example to display the contents. In case you are wondering how you might achieve this, here is the code I would write to display each of the employees in each of the departments (a loop within a loop):
DECLARE
.... same declaration section as above
BEGIN
.... same code populating the l_emp_info collection ....
DECLARE
l_dept_name departments.department_name%TYPE;
l_emp_name employees.last_name%TYPE;
BEGIN
l_dept_name := l_emp_info.FIRST ();
WHILE (l_dept_name IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (CHR (10) || l_dept_name);
l_emp_name := l_emp_info (l_dept_name).FIRST ();
WHILE (l_emp_name IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line
( ' '
|| RPAD (l_emp_name, 15)
|| TO_CHAR (l_emp_info (l_dept_name)
(l_emp_name).hire_date
, 'dd-Mon-yyyy'
)
|| ' '
|| l_emp_info (l_dept_name) (l_emp_name).salary
);
l_emp_name := l_emp_info (l_dept_name).NEXT (l_emp_name);
END LOOP;
l_dept_name := l_emp_info.NEXT (l_dept_name);
END LOOP;
END;
END;
A final point regarding declarations of strings:
I generally work hard to avoid ever using hard-coded VARCHAR2(N) declarations in my application code. Instead, I rely on %TYPE whenever possible, and if that is not possible (I don't have a column to reference), then I use a SUBTYPE declaration to create my own application-specific type. I then reference that subtype whenever I need to declare a variable of that type.
For example, when I want to declare a string to be "the largest size possible in PL/SQL," I will not do this:
l_big_one VARCHAR2(32767);
I don't want to hard-code that length, because I anticipate the day when Oracle will extend the maximize size of a string.
Instead, I create a subtype in a central, generic package:
PACKAGE plsql_limits
IS
/* Maximum size for VARCHAR2 in PL/SQL */
SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);
/* Maximum size for VARCHAR2 in database */
SUBTYPE dbmaxvarchar2_t IS VARCHAR2 (4000);
END plsql_limits;
Now my variable can be declare simply as:
l_big_one plsql_limits.maxvarchar2_t;
Much better!
Use LIMIT to manage memory as you fetch
Question:
I need to query more than 100,000 rows from a table into my PL/SQL program. I want to use BULK COLLECT and from a memory consumption standpoint can only afford to populate a collection with 5000 rows at a time. I know that I should use the LIMIT clause, like this: FETCH my_cursor BULK COLLECT INTO my_collection LIMIT 5000; But how do I execute this statement so that it processes all the rows of data and not just the first 5000 rows?
Answer:
You are absolutely correct: using the LIMIT clause will constrain the amount of PGA (Process Global Area) memory consumed by your session when using a BULK COLLECT.
All you will need to do is place that FETCH statement inside a loop and make sure to terminate that loop by analyzing the contents of the collection, rather than the %NOTFOUND cursor attribute. I offer an example of the kind of code you will want to write below.
Suppose I define the following table and then populate it with 107,000 rows of data:
I need to query more than 100,000 rows from a table into my PL/SQL program. I want to use BULK COLLECT and from a memory consumption standpoint can only afford to populate a collection with 5000 rows at a time. I know that I should use the LIMIT clause, like this: FETCH my_cursor BULK COLLECT INTO my_collection LIMIT 5000; But how do I execute this statement so that it processes all the rows of data and not just the first 5000 rows?
Answer:
You are absolutely correct: using the LIMIT clause will constrain the amount of PGA (Process Global Area) memory consumed by your session when using a BULK COLLECT.
All you will need to do is place that FETCH statement inside a loop and make sure to terminate that loop by analyzing the contents of the collection, rather than the %NOTFOUND cursor attribute. I offer an example of the kind of code you will want to write below.
Suppose I define the following table and then populate it with 107,000 rows of data:
CREATE TABLE stuff (pk INTEGER, description VARCHAR2(100))
/
BEGIN
FOR indx IN 1 .. 107000
LOOP
INSERT INTO stuff
( pk, description
)
VALUES ( indx, 'Stuff ' || indx
);
END LOOP;
COMMIT;
END;
/
I then need to iterate through all rows and call the process_one_row procedure for each. I will use BULK COLLECT to improve the performance of my query, but I can only fetch 5000 rows at a time, due to memory constraints. My PL/SQL block will then look like this:
1 DECLARE
2 CURSOR stuff_cur
3 IS
4 SELECT *
5 FROM stuff;
6
7 TYPE stuff_aat IS TABLE OF stuff%ROWTYPE
8 INDEX BY BINARY_INTEGER;
9
10 l_stuff stuff_aat;
11
12 PROCEDURE process_one_row ( row_in IN stuff%ROWTYPE )
13 IS
14 BEGIN
15 NULL;
16 END;
17
18 PROCEDURE PRINT ( count_in IN PLS_INTEGER, notfound_in IN BOOLEAN )
19 IS
20 BEGIN
21 IF notfound_in
22 THEN
23 DBMS_OUTPUT.put_line ( 'Fetched '
24 || TO_CHAR ( count_in )
25 || ' rows and %NOTFOUND = TRUE'
26 );
27 ELSIF NOT notfound_in
28 THEN
29 DBMS_OUTPUT.put_line ( 'Fetched '
30 || TO_CHAR ( count_in )
31 || ' rows and %NOTFOUND = FALSE'
32 );
33 ELSE
34 DBMS_OUTPUT.put_line ( 'Fetched '
35 || TO_CHAR ( count_in )
36 || ' rows and %NOTFOUND IS NULL'
37 );
38 END IF;
39 END PRINT;
40 BEGIN
41 OPEN stuff_cur;
42
43 LOOP
44 FETCH stuff_cur
45 BULK COLLECT INTO l_stuff LIMIT 5000;
46
47 PRINT ( l_stuff.COUNT, stuff_cur%NOTFOUND );
48 EXIT WHEN l_stuff.COUNT = 0;
49
50 --
51 FOR l_row IN l_stuff.FIRST .. l_stuff.LAST
52 LOOP
53 process_one_row ( l_stuff ( l_row ));
54 END LOOP;
55 END LOOP;
56
57 CLOSE stuff_cur;
58* END;
Here is an explanation of this code:
Lines 2-5
Declare the cursor that will identify the 107,000 rows I need.
Lines 7-10
Declare a collection type that mimics the structure of the just_5000 relational table, and then declare an instance of that type.
Line 41
Open the cursor, but do not fetch any data.
Lines 43-55
Fetch the data inside a loop. Since I can only fetch 5000 rows at a time, I need to execute the fetch 21 times. Of course, you very rarely know the exact number of rows being fetched, so I will not use a numeric FOR loop (from 1 to 21). Instead, I use a simple loop.
Lines 44-45
Fetch the next 5000 rows from the result set, and put them in the l_just_5000 collection.
Line 48
Check to see if I have fetched all rows. Notice that I see if there is anything in the collection by calling the COUNT method. With non-bulk queries, I would usually write
EXIT WHEN stuff_cur%NOTFOUND
but if I take this approach in my program, I will not process the last 2000 rows of data. The 21st fetch will deposit the last 2000 rows into my collection, but will also set the %NOTFOUND attribute to TRUE (Oracle has fetched past the end of the result set).
Lines 51-54
Now it is time to process each of the individual rows. BULK COLLECT always fills sequentially from row 1, so I will use a numeric FOR loop to iterate through all the rows and call the process_one_row procedure.
Line 57
Close the cursor when done.
When you run the above script (appended below without line numbers), you will see this output:
Fetched 5000 rows and %NOTFOUND IS FALSE
...
Fetched 5000 rows and %NOTFOUND IS FALSE
Fetched 2000 rows and %NOTFOUND = TRUE
Fetched 0 rows and %NOTFOUND = TRUE
So when you use the LIMIT clause, you need to put your fetch inside a simple loop, and then after you fetch N rows, you will execute a FOR loop (within the simple loop) to iterate through those rows, taking the necessary application-specific action.
Full script:
CREATE TABLE stuff (pk INTEGER, description VARCHAR2(100))
/
BEGIN
FOR indx IN 1 .. 107000
LOOP
INSERT INTO stuff
( pk, description
)
VALUES ( indx, 'Stuff ' || indx
);
END LOOP;
COMMIT;
END;
/
DECLARE
CURSOR stuff_cur
IS
SELECT *
FROM stuff;
TYPE stuff_aat IS TABLE OF stuff%ROWTYPE
INDEX BY BINARY_INTEGER;
l_stuff stuff_aat;
PROCEDURE process_one_row ( row_in IN stuff%ROWTYPE )
IS
BEGIN
NULL;
END;
PROCEDURE PRINT ( count_in IN PLS_INTEGER, notfound_in IN BOOLEAN )
IS
BEGIN
IF notfound_in
THEN
DBMS_OUTPUT.put_line ( 'Fetched '
|| TO_CHAR ( count_in )
|| ' rows and %NOTFOUND = TRUE'
);
ELSIF NOT notfound_in
THEN
DBMS_OUTPUT.put_line ( 'Fetched '
|| TO_CHAR ( count_in )
|| ' rows and %NOTFOUND = FALSE'
);
ELSE
DBMS_OUTPUT.put_line ( 'Fetched '
|| TO_CHAR ( count_in )
|| ' rows and %NOTFOUND IS NULL'
);
END IF;
END PRINT;
BEGIN
OPEN stuff_cur;
LOOP
FETCH stuff_cur
BULK COLLECT INTO l_stuff LIMIT 5000;
PRINT ( l_stuff.COUNT, stuff_cur%NOTFOUND );
EXIT WHEN l_stuff.COUNT = 0;
FOR l_row IN l_stuff.FIRST .. l_stuff.LAST
LOOP
process_one_row ( l_stuff ( l_row ));
END LOOP;
END LOOP;
CLOSE stuff_cur;
END;
/
Subscribe to:
Posts (Atom)