Oracle 11g Sql Hands On Assignment Answers

On By In 1

Chapter 11 Lab AssignmentHands-On ExercisesAnswer to Question 1====================SQL> select count(*)2 from books3 where category = 'cooking';COUNT(*)----------0SQL>Answer to Question 2====================SQL> select count(*)2 from books3 where retail > 30;COUNT(*)----------8Answer to Question 3====================SQL> select max(pubdate)2 from books;MAX(PUBDA---------11-NOV-06Answer to Question 4====================SQL> select sum((retail-cost)*quantity)2 from orders join orderitems using(order#) join books using(isbn)3 where customer# = 1017;SUM((RETAIL-COST)*QUANTITY)---------------------------59.78Answer to Question 5====================SQL> select min(retail)2 from books;MIN(RETAIL)-----------8.95select Answer to Question 6====================SQL> select avg(sum((retail-cost)*quantity))2 from orders join orderitems using(order#) join books using(isbn)3 group by order#;AVG(SUM((RETAIL-COST)*QUANTITY))--------------------------------33.772381

As Published In

January/February 2006


Answering PL/SQL

By Steven Feuerstein

Apply best practices to defining arrays and stripping strings.

PL/SQL expert Steven Feuerstein answers your PL/SQL questions at Best Practice PL/SQL ( This column includes highlights from that forum.

How can I define a two-dimensional array of numbers in PL/SQL?

Although PL/SQL does not natively support the declaration and manipulation of multidimensional arrays, you can emulate these structures using nested collection definitions, which were first supported in Oracle9i Database Release 1.

Here is a brief example to get you started and introduce you to some of the challenges you may encounter as you use collections in this way.

First, create a collection of associative arrays. 


The first, inner collection— —contains the data for each cell in the two-dimensional array. Each row in the outer collection— —contains a collection of the first type.

Now declare a variable based on that outer collection type — —, which will serve as a two-dimensional array. In the following script, I declare such a collection— 

DECLARE l_2d_grid twodim_aa.array_t;

—and then assign values to three cells: (1,1), (1,2), and (200,206). Notice that the syntax is different from that used in traditional array cell specification, namely: (1)(1), (1)(2), and (200)(206). Also, since I am using associative arrays to define my two-dimensional array, I do not have to specify a size for this two-dimensional array. 

DECLARE l_2d_grid twodim_aa.array_t; BEGIN l_2d_grid (1) (1) := 100; l_2d_grid (1) (2) := 120; l_2d_grid (200) (206) := 200; IF l_2d_grid (1)(2) < 100 THEN ... END IF; END; /

If you want to define a two-dimensional array with a specific size, you should use s, because when you declare a you must also specify a maximum number of elements allowed in the . Here, for example, is the specification of a 100x100 element array: 

CREATE OR REPLACE PACKAGE twodim_varray IS TYPE data_t IS VARRAY (100) OF NUMBER; TYPE array_t IS VARRAY (100) OF data_t; END twodim_varray; /

Populating this array becomes very complicated, however, because before I can assign a value to an index in the array, that array must be initialized and then extended (the same will be true of nested tables). To do this, I must now expose the stark reality that the is not really a two-dimensional array—rather, it is a collection of collections. I must first extend one dimension. That dimension, a , defines an element that will in turn hold a (the inner collection— ). Before I can assign a value to that index, however, I must initialize the inner collection and extend it . Finally, I can assign a value. 

DECLARE l_2d_grid twodim_varray.array_t := twodim_varray.array_t(); BEGIN l_2d_grid.EXTEND; l_2d_grid(1) := twodim_varray.data_t(); l_2d_grid(1).EXTEND; l_2d_grid (1) (1) := 100; END; /

Because of these complications, you will almost certainly want to either 

  • Stick with associative arrays. It will greatly simplify your life when it comes to manipulating the values in multidimensional arrays.

  • Build an encapsulation to hide the code required to manipulate a -based multidimensional array. 

Regardless of which collection type you choose to emulate the multidimensional array, you will need to write your own programs to perform high-level operations on your array.

Finally, if you need to perform advanced operations on vectors and matrices, you might also consider using the package, new to Oracle Database 10g Release 2. This package exposes within PL/SQL a subset of the BLAS (Basic Linear Algebra Subprograms) and LAPACK (Linear Algebra PACKage) Version 3.0 operations on vectors and matrices. These structures are represented as s in .

Stripping the Unwanted

I'm using Oracle9i Database. How can I strip out unwanted characters from a string variable? For example, I would like to be able to call a function named stripped_string like this: 

DECLARE l_before VARCHAR2 (100) := 'This is my string'; l_after VARCHAR2 (100); BEGIN l_after := stripped_string (l_before, 'is'); DBMS_OUTPUT.put_line (l_after); END; / and set l_after to 'Th my trng'.

While Oracle9i Database does not provide a built-in function specifically to remove characters from a string, you can easily adapt to this purpose.

The function allows you to replace characters in an expression that appear in a from string to their corresponding characters in the to string.

Suppose that I want to replace all instances of the letter e in my name with the letter X. I will then execute this code: 

BEGIN DBMS_OUTPUT.PUT_LINE ( TRANSLATE ( 'Steven Feuerstein' , 'e', 'X') ); END; /

And see this result: 

StXvXn FXuXrstXin

Note that is case-sensitive. If I run this variation on the above block 

BEGIN DBMS_OUTPUT.PUT_LINE ( TRANSLATE ( 'Steven Feuerstein' , 'E', 'X') ); END; /

I see these results: 

Steven Feuerstein

From these demonstrations, you might quickly conclude that to remove all the e's from my name, I can simply call as follows: 

TRANSLATE ('Steven Feuerstein', 'e', NULL)


TRANSLATE ('Steven Feuerstein', 'e', '')

Alas, this will not work as desired. Both expressions will return , since the empty string is treated as a , and generally if you provide a argument to an Oracle function, it will return a (there are some exceptions to this rule, like the function).

To use as a character-stripper, then, I need to finesse the function a bit. For example, I can strip out e's from my name with this block of code: 

BEGIN DBMS_OUTPUT.PUT_LINE ( TRANSLATE ( 'Steven Feuerstein' , '#e', '#') ); END; /

Here is the resulting output: 

Stvn Furstin

Notice what I have done: I included in both the from string and the to string the same first character. But in the to string, that leading character is the only character. goes one by one through each character in the from string and replaces it with the character in the to string at the same position. Consequently, # is replaced by # and is replaced by . So all instances of disappear: has done the job for me.

Before you rush off to apply in this fashion, make sure you know how to best implement it as a character stripper. Consider the following block 

BEGIN DBMS_OUTPUT.PUT_LINE ( TRANSLATE ( 'Steven Feuerstein' , 'ee', 'e') ); END; /

and its output: 

Steven Feuerstein

did not, in this case, remove the e's from my name, because the leading character for replacement was also an , and that translation of to took precedence over the second translation of to . So you need to make sure that the leading character in both strings is not the same as any of the characters you wish to strip from the string.

Now, you could simply try to remember all this, but you would be much better off encapsulating all this information into a function so it can do the remembering and applying of these -related rules on your behalf.

Consider the function in Listing 1. This simple program offers the following advantages:

Code Listing 1: The STRIPPED_STRING function 

CREATE OR REPLACE FUNCTION stripped_string ( expression_in IN VARCHAR2 ,characters_in IN VARCHAR2 ,placeholder_in IN VARCHAR2 DEFAULT CHR(1) ) RETURN VARCHAR2 IS BEGIN RETURN TRANSLATE ( expression_in , placeholder_in || characters_in , placeholder_in ); END stripped_string; /


  • It hides the use of to perform the character stripping. You no longer need to be aware of how to use in this fashion.

  • It contains the knowledge about the need to provide a leading character in both from and to strings. By default, it uses the nonprinting ("Start of header" character) as the placeholder for -based stripping. It is very unlikely that you will ever have a string with in it, so you can usually call the function without specifying a placeholder character. If, however, you ever need to override that placeholder, you can do so by calling the function and specifying all three arguments.

Listing 2 includes examples of calls to .

Code Listing 2: Using STRIPPED_STRING with different inputs 

BEGIN DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', 'e')); DBMS_OUTPUT.put_line (stripped_string (NULL, 'e')); DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', 'e', NULL)); DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', NULL)); DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', 'e', 'e')); DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', 'e', 't')); DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', 'etn')); END; /

This process of information hiding is certainly useful for this exercise with . It is absolutely critical, however, with the more-complex programs you will write for your application. Take the time to encapsulate implementation of business rules and formulas in their own functions, such as . The resulting code will be much easier to debug and maintain.

I wrote the simple test script in Listing 3 to exercise .

Code Listing 3: Testing STRIPPED_STRING 

DECLARE l_success BOOLEAN DEFAULT TRUE; PROCEDURE report_failure (description_in IN VARCHAR2) IS BEGIN l_success := FALSE; DBMS_OUTPUT.put_line ('stripped_string failure!'); DBMS_OUTPUT.put_line (' ' || description_in); END report_failure; BEGIN IF stripped_string ('abc', 'b') != 'ac' THEN report_failure ('Default placeholder'); END IF; IF stripped_string ('abc', 'abc') IS NOT NULL THEN report_failure ('All characters removed'); END IF; IF stripped_string (NULL, 'abc') IS NOT NULL THEN report_failure ('NULL input string'); END IF; IF stripped_string ('abcdefb', 'b') != 'acdef' THEN report_failure ('Repeats of character'); END IF; IF stripped_string ('abc', 'a', '#') != 'bc' THEN report_failure ('# as placeholder'); END IF; IF stripped_string ('abc', NULL) != 'abc' THEN report_failure ('NULL character list'); END IF; IF l_success THEN DBMS_OUTPUT.put_line ('stripped_string passed its tests!'); END IF; END; /

Using Oracle Database 10g

If you were using Oracle Database 10g, there would be another way to do this: 

REGEXP_REPLACE. BEGIN -- Oracle Database 10g only! DBMS_OUTPUT.put_line ( REGEXP_REPLACE ( 'STEVEN FEUERSTEIN' , 'E*', NULL)); END; / Stvn Furstin

Regular expression functions rely on a set of metacharacters to determine their behavior. The asterisk (*) means "match zero or more occurrences," which is the behavior I desired to strip out all the e's.

What if the character you needed to strip was (*)? Use an escape character in the call to , as shown here: 


Listing 4 is a revision of the function, called , which uses instead of to do the string stripping.

Code Listing 4: The STRIPPED_STRING function using REGEXP_REPLACE 

CREATE OR REPLACE FUNCTION stripped_string_10g ( string_in IN VARCHAR2 ,strip_characters_in IN VARCHAR2 ) RETURN VARCHAR2 IS -- With REGEXP_REPLACE, each character to be replaced with NULL, -- must be followed by a "*". c_asterisk CONSTANT CHAR (1) := '*'; l_strip_characters VARCHAR2 (32767); l_length PLS_INTEGER; l_character VARCHAR2 (2); BEGIN l_length := LENGTH (strip_characters_in); IF l_length > 0 THEN FOR l_index IN 1 .. l_length LOOP l_character := SUBSTR (strip_characters_in, l_index, 1); IF l_character = c_asterisk THEN l_character := '\' || c_asterisk; END IF; l_strip_characters := l_strip_characters || l_character || c_asterisk; END LOOP; END IF; RETURN regexp_replace (string_in, l_strip_characters); END stripped_string; /

Does the complexity of using justify all this code (as well as the need to maintain it and the overhead of running it)? I would say no, that in this case you are better off learning the nuances of and applying it directly to your string to strip characters.

Can I Start a Timer?

I have a requirement to have a timer start after an insert into a table. After a short time, I would initiate another activity. How can I program that requirement in PL/SQL?

It's not hard to start a timer in your session. Check out the Timer or Stopwatch Object Type Definition in Downloads (at, which allows you to create an object type-based "stopwatch" so you can start and stop a timer from within PL/SQL.

Here is an example that uses this stopwatch timer:


DECLARE c_iterations CONSTANT PLS_INTEGER := 100; stopwatch_tmr tmr_t := NEW tmr_t ('Run My Test', c_iterations); BEGIN stopwatch_tmr.go; FOR indx IN 1 .. c_iterations LOOP run_my_test; END LOOP; stopwatch_tmr.stop; END; /

The tricky part of the question has to do with being able to detect that a certain amount of time has passed, which then triggers a new activity. PL/SQL executes in a single thread. If you want to "kick off" new activities (and then continue on with your current activity), you essentially need to send a message to another session, which then gets moving on your request.

You can use either Oracle's Advanced Queuing facility or the simpler package to implement messaging between sessions.

You might also want to consider or Oracle Database 10g's new . If you simply want to start a new activity in, say, 30 seconds, you could submit a job with a start time of 30 seconds in the future and no repeat execution of the job. The job manager would then wake up, check for the job, and kick it off, independently of the session from which the job was scheduled.

Steven Feuerstein ( is considered one of the world's leading experts on the Oracle PL/SQL language, having written 10 books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein serves as a senior technology advisor for Quest Software, and his projects include Qnxo, the world's first active mentoring product (


Leave a Reply

Your email address will not be published. Required fields are marked *