MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘pl/sql’ Category

Oracle Unit Test

without comments

A unit test script may contain SQL or PL/SQL statements or it may call another script file that contains SQL or PL/SQL statements. Moreover, a script file is a way to bundle several activities into a single file because most unit test programs typically run two or more instructions as unit tests.

Unconditional Script File

You can write a simple unit test like the example program provided in the Lab 1 Help Section, which includes conditional logic. However, you can write a simpler script that is unconditional and raises exceptions when preconditions do not exist.

The following script file creates a one table and one_s sequence. The DROP TABLE and DROP SEQUENCE statements have the same precondition, which is that the table or sequence must previously exist.

-- Drop table one.
DROP TABLE one;
 
-- Crete table one.
CREATE TABLE one
( one_id    NUMBER
, one_text  VARCHAR2(10));
 
-- Drop sequence one_s.
DROP SEQUENCE one_s;
 
-- Create sequence one_s.
CREATE SEQUENCE one_s;

After writing the script file, you can save it in the lab2 subdirectory as the unconditional.sql file. After you login to the SQL*Plus environment from the lab2 subdirectory. You call the unconditional.sql script file from inside the SQL*Plus environment with the following syntax:

@unconditional.sql

It will display the following output, which raises an exception when the one table or one_s sequence does not already exist in the schema or database:

DROP TABLE one
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
Table created.
 
DROP SEQUENCE one_s
              *
ERROR at line 1:
ORA-02289: sequence does not exist
 
Sequence created.

An unconditional script raises exceptions when a precondition of the statement does not exist. The precondition is not limited to objects, like the table or sequence; and the precondition may be specific data in one or several rows of one or several tables. You can avoid raising conditional errors by writing conditional scripts.

Conditional Script File

A conditional script file contains statements that check for a precondition before running a statement, which effectively promotes their embedded statements to a lambda function. The following logic recreates the logic of the unconditional.sql script file as a conditional script file:

-- Conditionally drop a table and sequence.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('ONE','ONE_S')
            ORDER BY object_type ) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
-- Crete table one.
CREATE TABLE one
( one_id    NUMBER
, one_text  VARCHAR2(10));
 
-- Create sequence one_s.
CREATE SEQUENCE one_s;

You can save this script in the lab2 subdirectory as conditional.sql and then unit test it in SQL*Plus. You must manually drop the one table and one_s sequence before running the conditional.sql script to test the preconditions.

You will see that the conditional.sql script does not raise an exception because the one table or one_s sequence is missing. It should generate output to the console, like this:

PL/SQL procedure successfully completed.
 
Table created.
 
Sequence created.

As a rule, you should always write conditional script files. Unconditional script files throw meaningless errors, which may cause your good code to fail a deployment test that requires error free code.

Written by maclochlainn

April 5th, 2022 at 1:59 pm

Oracle’s Sparse Lists

without comments

Oracle’s PL/SQL Programming Language is really quite nice. I’ve written 8 books on it and still have fun coding in it. One nasty little detail about Oracle’s lists, introduced in Oracle 8 as PL/SQL Tables according their documentation, is they rely on sequential numeric indexes. Unfortunately, Oracle lists support a DELETE method, which can create gaps in the sequential indexes.

Oracle calls a sequence without gaps densely populated and a sequence with gaps sparsely populated. This can cause problems when PL/SQL code inadvertently removes elements at the beginning, end, or somewhere in the middle of the list. That’s because a program can then pass the sparsely populated list as a parameter to another stored function or procedure where the developer may traverse the list in a for-loop. That traversal may raise an exception in a for-loop, like this when it has gaps in the index sequence:

DECLARE
*
ERROR AT line 1:
ORA-01403: no data found
ORA-06512: AT line 20

Oracle’s myriad built-in libraries don’t offer a function to compact a sparsely populated list into a densely populated list. This post provides a compact stored procedure that converts a sparsely populated list to a densely populated list.

The first step to using the compact stored procedure requires that you create an object type in SQL, like this list of 20-character strings:

DROP TYPE list;
CREATE OR REPLACE
  TYPE list IS TABLE OF VARCHAR2(20);
/

Now, you can implement the compact stored procedure by passing the User-Defined Type as it’s sole parameter.

CREATE OR REPLACE
  PROCEDURE compact ( sparse IN OUT LIST ) IS
    /* Declare local variables. */
    iterator  NUMBER;           -- Leave iterator as null.
 
    /* Declare new list. */
    dense     LIST := list();
  BEGIN
    /*
      Initialize the iterator with the starting value, which is
      necessary because the first element of the original list
      could have been deleted in earlier operations. Setting the
      initial iterator value to the first numeric index value
      ensures you start at the lowest available index value.
    */
    iterator := sparse.FIRST;
 
    /* Convert sparsely populated list to densely populated. */
    WHILE (iterator <= sparse.LAST) LOOP
      dense.EXTEND;
      dense(dense.COUNT) := sparse(iterator);
      iterator := sparse.NEXT(iterator);
    END LOOP;
 
    /* Replace the input parameter with the compacted list. */
    sparse := dense;
  END;
/

Before we test the compact stored procedure, let’s create deleteElement stored procedure for our testing:

CREATE OR REPLACE
  PROCEDURE deleteElement ( sparse   IN OUT LIST
                          , element  IN     NUMBER ) IS
  BEGIN
    /* Delete a value. */
    sparse.DELETE(element);
  END;
/

Now, let’s use an anonymous block to test compacting a sparsely populated list into a densely populated list. The test program will remove the first, last, and one element in the middle before printing the sparsely populated list’s index and string values. This test will show you gaps in the remaining non-sequential index values.

After you see the gaps, the test program compacts the remaining list values into a new densely populated list. It then prints the new index values with the data values.

DECLARE
  /* Declare a four item list. */
  lv_strings  LIST := list('one','two','three','four','five','six','seven');
BEGIN
  /* Check size of list. */
  dbms_output.put_line('Print initial list size:  ['||lv_strings.COUNT||']');
  dbms_output.put_line('===================================');
 
  /* Delete a value. */
  deleteElement(lv_strings,lv_strings.FIRST);
  deleteElement(lv_strings,3);
  deleteElement(lv_strings,lv_strings.LAST);
 
  /* Check size of list. */
  dbms_output.put_line('Print modified list size: ['||lv_strings.COUNT||']');
  dbms_output.put_line('Print max index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']');
  dbms_output.put_line('===================================');
  FOR i IN 1..lv_strings.LAST LOOP
    IF lv_strings.EXISTS(i) THEN
      dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']');
    END IF;
  END LOOP;
 
  /* Call a procedure by passing current sparse collection and
     the procedure returns dense collection. */
  dbms_output.put_line('===================================');
  dbms_output.put_line('Compacting list.');
  compact(lv_strings);
  dbms_output.put_line('===================================');
 
  /* Print the new maximum index value and list size. */
  dbms_output.put_line('Print new index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']');
  dbms_output.put_line('===================================');
  FOR i IN 1..lv_strings.COUNT LOOP
    dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']');
  END LOOP;
  dbms_output.put_line('===================================');
END;
/

It produces output, like:

Print initial list size:  [7]
===================================
Print modified list size: [4]
Print max index and size: [6][4]
===================================
List list index and item: [2][two]
List list index and item: [4][four]
List list index and item: [5][five]
List list index and item: [6][six]
===================================
Compacting list.
===================================
Print new index and size: [4][4]
===================================
List list index and item: [1][two]
List list index and item: [2][four]
List list index and item: [3][five]
List list index and item: [4][six]
===================================

You can extend this concept by creating User-Defined Types with multiple attributes, which are essentially lists of tuples (to draw on Pythonic lingo).

Written by maclochlainn

October 4th, 2021 at 11:49 pm

Design Database Triggers

without comments

Designing and implementing database triggers is always interesting and sometimes not easy. I believe most of the difficulty comes from not implementing the triggers in a way that lets you perform single use case testing. For example, a trigger typically fires as a result of an INSERT, UPDATE, or DELETE statement. That means you can’t test the trigger’s logic independently from the SQL statement.

This post shows you how to implement an Oracle Database trigger that ensures a last_name field always has a hyphen when it is composed of two surnames. It also shows you how to build debugging directly into the trigger with Oracle’s conditional compilation logic (covered in my Oracle Database 12c PL/SQL Programming book on pages 170-171) while writing the debug comments to a debug logging table.

The example works through the design in stages. To begin the process, you need to define a zeta table and zeta_s sequence (no magic in the table or sequence names).

-- Create the zeta demo table.
CREATE TABLE zeta
( zeta_id     NUMBER
, last_name   VARCHAR2(30));
 
-- Create the zeta_s demo sequence.
CREATE SEQUENCE zeta_s;

Next, you write a basic on insert row-level (or, row-by-row) trigger. The following white_space trigger only fires when the last_name column value contains a whitespace between two components of a last name.

The code follows below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*
|| Create an on insert trigger to implement the desired
|| logic, which replaces a whitespace between two portions
|| of a last_name column.
*/
CREATE OR REPLACE
  TRIGGER white_space
  BEFORE INSERT ON zeta
  FOR EACH ROW
  WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END white_space;
/

You can now test the white_space trigger with these two INSERT statements:

-- Two test insert statements.
INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Baron-Schwartz' );
 
INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Zeta Jones' );

After running the two INSERT statements, you can query the last_name from the zeta table and verify that there’s always a hyphen between the two components of the last name, like:

SELECT * FROM zeta;

It should display:

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta-Jones

However, the business logic is violated when you run an UPDATE statement, like:

-- Update data and break the business rule.
UPDATE zeta
SET    last_name = 'Zeta Jones'
WHERE  last_name = 'Zeta-Jones';

A fresh query like

SELECT * FROM zeta;

Should display the following, which allowed an UPDATE statement to put in a non-conforming last name value:

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta Jones

You need to expand the role of your white_space trigger to prevent this undesired outcome by enabling it to fire on an insert or update event. You do that by adding ON UPDATE to line 8 below. The modified white_space trigger for both SQL events is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*
|| Create an on insert or update trigger to implement the
|| desired logic, which replaces a whitespace between two 
|| portions of a last_name column.
*/
CREATE OR REPLACE
  TRIGGER white_space
  BEFORE INSERT OR UPDATE ON zeta
  FOR EACH ROW
  WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END white_space;
/

Having made the change on line 8 above, you can now retest the white_space trigger with the following UPDATE statement. You should note that the WHERE clause uses a whitespace because the last UPDATE statement with the INSERT-only white_space trigger allowed its change.

UPDATE zeta
SET    last_name = 'Zeta-Jones'
WHERE  last_name = 'Zeta Jones';

Re-query the zeta table:

SELECT * FROM zeta;

It should display the following values that meet the business rule:

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta Jones

The modified white_space trigger doesn’t let us capture debug information and it doesn’t let us see whether the SQL event is an INSERT or UPDATE statement. It also fails to differentiate between outcomes from an INSERT and UPDATE event.

You can fix this by:

  • Creating a debug_log table that captures debugging information.
  • Creating a debug_procedure to format diagnostic strings.
  • Using the Data Manipulation Language (DML) Event Functions (covered in my Oracle Database 12c PL/SQL Programming book’s Table 12-3 on page 533) to track whether the event is an INSERT or UPDATE statement.

The three steps to make the trigger capable of different outcomes and debugging are:

  1. The following creates a debug_log table:

    -- Create the debug_log table.
    CREATE TABLE debug_log
    ( message  VARCHAR2(78));
  2. The following creates an a debug procedure:

    -- Create a debug logging procedure.
    CREATE OR REPLACE
      PROCEDURE debug ( event     VARCHAR2 := 'Unknown'
                      , location  VARCHAR2
                      , COLUMN    VARCHAR2 ) IS
        /* Local message variable. */
        lv_message  VARCHAR2(78);
     
        /* Set procedure as an autonomous transaction. */ 
        PRAGMA AUTONOMOUS_TRANSACTION;
      BEGIN
        /* Build, insert, and commit message in log. */
        lv_message := event || ' event at ' || location || ' on column [' || COLUMN || ']';
        INSERT INTO debug_log ( message ) VALUES ( lv_message );
        COMMIT;
      END;
    /
  3. The following creates an a replacement white_space trigger equipped with event tracking and conditional compilation debug calls to the debug_log table:

    You actually need to change the session before compiling this trigger with the following command so that the conditional compilation instructions work:

    ALTER SESSION SET PLSQL_CCFLAGS = 'DEBUG:1';

    Then, create the white_space trigger from the following code:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    
    -- Create a debug logging procedure.
    CREATE OR REPLACE
      TRIGGER white_space
      BEFORE INSERT OR UPDATE ON zeta
      FOR EACH ROW
      WHEN (REGEXP_LIKE(NEW.last_name,' '))
    DECLARE
      lv_event  VARCHAR2(9);
    BEGIN
      /* Conditional debugging. */
      $IF $$DEBUG = 1 $THEN
        debug( location     => 'before IF statement'
             , column_value => ':new.last_name' );
      $END
     
      IF INSERTING THEN
        lv_event := 'Inserting';
     
        /* Conditional debugging. */
        $IF $$DEBUG = 1 $THEN
          debug( event        => lv_event
               , location     => 'after IF statement'
               , column_value => ':new.last_name' );
        $END
     
        :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
      ELSIF UPDATING THEN
        lv_event := 'Updating';
     
        /* Conditional debugging. */
        $IF $$DEBUG = 1 $THEN
          debug( event        => lv_event
               , location     => 'after ELSIF statement'
               , column_value => ':new.last_name' );
        $END
     
        RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.');
      END IF;
        /* Conditional debugging. */
        $IF $$DEBUG = 1 $THEN
          debug( location     => 'after END IF statement'
               , column_value => ':new.last_name' );
        $END
    END white_space;
    /

A new test case for the modified white_space trigger uses an INSERT and UPDATE statement, like:

INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Pinkett Smith' );
 
UPDATE zeta
SET    last_name = 'Pinkett Smith'
WHERE  last_name = 'Pinkett-Smith';

The UPDATE statement violates the business rule and the new white_space trigger throws an error when an attempt is made to update the last_name with two names separated by a whitespace. The UPDATE statement raises the following error stack:

UPDATE zeta
       *
ERROR AT line 1:
ORA-20001: Whitespace replaced WITH hyphen.
ORA-06512: AT "STUDENT.WHITE_SPACE", line 31
ORA-04088: error during execution OF TRIGGER 'STUDENT.WHITE_SPACE'

Re-query the zeta table:

SELECT * FROM zeta;

It should display the following values that meet the business rule. The new third row in the table came from the INSERT statement in the test case.

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta-Jones
         3 Pinkett-Smith

Unfortunately, there’s a lot of debugging clutter in the white_space trigger. The other downside is it requires testing from INSERT and UPDATE statements rather than simple anonymous block. You can fix that by doing two things:

  1. Remove the body of the trigger to an autonomous zeta_function.
  2. Put a logic router in the trigger with a call to the autonomous zeta_function.

Here’s the script to create the zeta_function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
CREATE OR REPLACE
  FUNCTION zeta_function 
  ( column_value  VARCHAR2
  , event         VARCHAR2 ) RETURN VARCHAR2 IS
 
    /* Return value. */
    lv_retval  VARCHAR2(30) := column_value;
 
    /* Set function as an autonomous transaction. */ 
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
 
    /* Conditional debugging. */
    $IF $$DEBUG = 1 $THEN
      debug( location     => 'before IF statement'
           , column_value => ':new.column_value' );
    $END
 
    /* Check if event is INSERT statement. */
    IF event = 'INSERTING' THEN
 
      /* Conditional debugging. */
      $IF $$DEBUG = 1 $THEN
        debug( event        =>  INITCAP(event)
             , location     => 'after IF statement'
             , column_value => ':new.column_value' );
      $END
 
      /* Replace a whitespace with a hyphen. */
      lv_retval := REGEXP_REPLACE(column_value,' ','-',1,1);
 
    /* Check if event is UPDATE statement. */
    ELSIF event = 'UPDATING' THEN
 
      /* Conditional debugging. */
      $IF $$DEBUG = 1 $THEN
        debug( event        =>  INITCAP(event)
             , location     => 'after ELSIF statement'
             , column_value => ':new.column_value' );
      $END
 
      /* Raise error to state policy allows no changes. */
      RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.');
 
    END IF;
 
      /* Conditional debugging. */
      $IF $$DEBUG = 1 $THEN
        debug( location     => 'after END IF statement'
             , column_value => ':new.column_value' );
      $END
 
    /* Return modified column for insert or original column for update. */
    RETURN lv_retval;
  END zeta_function;
/

The refactored white_space trigger follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE
  TRIGGER white_space
  BEFORE INSERT OR UPDATE ON zeta
  FOR EACH ROW
  WHEN (REGEXP_LIKE(NEW.last_name,' '))
DECLARE
  lv_event  VARCHAR2(9);
BEGIN
  /* Set evaluation event. */
  IF    INSERTING THEN lv_event := 'INSERTING';
  ELSIF UPDATING  THEN lv_event := 'UPDATING';
  END IF;
 
  /*
  || Assign the result of the formatted string to the 
  || new last_name value.
  */
  :NEW.last_name := zeta_function( event        => lv_event
                                 , column_value => :NEW.last_name);
END white_space;
/

A new test case for the modified white_space trigger uses an INSERT and UPDATE statement with some new values.

INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Day Lewis' );
 
UPDATE zeta
SET    last_name = 'Day Lewis'
WHERE  last_name = 'Day-Lewis';

The UPDATE statement continues to violate the business rule and the modified white_space trigger throws a different error stack. The new error stack includes the zeta_function because that’s where you throw the error. It is caught and re-thrown by the white_space trigger.

UPDATE zeta
       *
ERROR AT line 1:
ORA-20001: Whitespace replaced WITH hyphen.
ORA-06512: AT "STUDENT.ZETA_FUNCTION", line 47
ORA-06512: AT "STUDENT.WHITE_SPACE", line 13
ORA-04088: error during execution OF TRIGGER 'STUDENT.WHITE_SPACE'

Re-query the zeta table:

SELECT * FROM zeta;

It should display the following values that meet the business rule. The new third row in the table came from the INSERT statement in the test case.

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta-Jones
         3 Pinkett-Smith
         4 Day-Lewis

Now, you can query the debug_log table and see the debug messages that you captured from testing the INSERT and UPDATE statements. You get three messages from the INSERT statement test and only two from the UPDATE statement test.

MESSAGE
------------------------------------------------------------------
Unknown event at before IF statement on column [:new.last_name]
Inserting event at after IF statement on column [:new.last_name]
Unknown event at after END IF statement on column [:new.last_name]
Unknown event at before IF statement on column [:new.last_name]
Updating event at after ELSIF statement on column [:new.last_name]

As always, I hope this helps people see new ways to solve problems.

Written by maclochlainn

June 30th, 2021 at 11:30 pm

PL/SQL Mimic Iterator

without comments

There’s no formal iterator in PL/SQL but you do have the ability of navigating a list or array with Oracle’s Collection API. For example, the following navigates a sparsely indexed collection from the lowest to the highest index value while skipping a missing index value:

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Declare the collection. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Create a gap in the densely populated index. */
  lv_list.DELETE(2);
 
  /* Mimic an iterator in the loop. */
  CURRENT := lv_list.FIRST;
  WHILE NOT (CURRENT > lv_list.LAST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
END;
/

The next one, navigates a sparsely indexed collection from the highest to the lowest index value while skipping a missing index value:

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Declare the collection. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Create a gap in the densely populated index. */
  lv_list.DELETE(2);
 
  /* Mimic an iterator in the loop. */
  CURRENT := lv_list.LAST;
  WHILE NOT (CURRENT < lv_list.FIRST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.PRIOR(CURRENT);
  END LOOP;
END;
/

However, the next example is the most valuable because it applies to a PL/SQL associative array indexed by string values. You should note that the string indexes are organized in ascending order and assigned in the execution section of the program. This differs from the earlier examples where the values are assigned by constructors in the declaration section.

There’s no need to delete an element from the associative array because the string-based indexes are already sparsely constructed. A densely populated character index sequence is possible but not very useful, which is probably why there aren’t any examples of it.

Moreover, the following example is how you navigate a dictionary, which is known as an associative array in Oracle parlance (special words to describe PL/SQL structures). Unfortunately, associative arrays lack any utilities like Python’s key() method for dictionaries.

DECLARE
  /* Create a local associative array type. */
  TYPE list IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
 
  /* Define a variable of the associative array type. */
  lv_list  LIST; --  := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  VARCHAR2(5);
BEGIN
  /* Assign values to an associative array (PL/SQL structure). */
  lv_list('One') := 'Moe';
  lv_list('Two') := 'Shemp';
  lv_list('Three') := 'Larry';
  lv_list('Four') := 'Curly';
 
  /* Mimic iterator. */
  CURRENT := lv_list.FIRST;
  dbms_output.put_line('Debug '||CURRENT);
  WHILE NOT (CURRENT < lv_list.LAST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
END;
/

As always, I hope this example helps somebody solve a real world problem.

Written by maclochlainn

May 14th, 2021 at 4:50 pm

What Identifier?

without comments

It’s always interesting to see students find the little nuances that SQL*Plus can generate. One of the first things we cover is the concept of calling PL/SQL interactively versus through an embedded call. The easiest and first exercise simply uses an insecure call like:

sqlplus -s student/student @call.sql

to the call.sql program:

SQL> DECLARE
  2    lv_input  VARCHAR2(20);
  3  BEGIN
  4    lv_input := '&1';
  5    dbms_output.put_line('['||lv_input||']');
  6  END;
  7  /

It prints the following to console:

Enter value for 1: machine
old   4:   lv_input := '&1';
new   4:   lv_input := 'machine';
[machine]
 
PL/SQL procedure successfully completed.

Then, we change the '&1' parameter variable to '&mystery' and retest the program, which prints the following to the console:

Enter value for mystery: machine
old   4:   lv_input := '&mystery';
new   4:   lv_input := 'machine';
[machine]
 
PL/SQL procedure successfully completed.

After showing a numeric and string input parameter, we remove the quotation from the lv_input input parameter and raise the following error:

Enter value for mystery: machine
old   4:   lv_input := &mystery;
new   4:   lv_input := machine;
  lv_input := machine;
              *
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00201: identifier 'MACHINE' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

The point of the exercise is to spell out that the default input value is numeric and that if you pass a string it becomes an identifier in the scope of the program. So, we rewrite the call.sql program file by adding a machine variable, like:

SQL> DECLARE
  2    lv_input  VARCHAR2(20);
  3    machine   VARCHAR2(20) := 'Mystery Machine';
  4  BEGIN
  5    lv_input := &mystery;
  6    dbms_output.put_line('['||lv_input||']');
  7  END;
  8  /

It prints the following:

Enter value for mystery: machine
old   5:   lv_input := &mystery;
new   5:   lv_input := machine;
[Mystery Machine]
 
PL/SQL procedure successfully completed.

The parameter name becomes an identifier and maps to the variable machine. That mapping means it prints the value of the machine variable.

While this is what we’d call a terminal use case, it is a fun way to illustrate an odd PL/SQL behavior. As always, I hope its interesting for those who read it.

Written by maclochlainn

April 26th, 2021 at 12:47 pm

PL/SQL Inheritance Failure

without comments

PL/SQL is a great programming language as far as it goes but it lacks true type inheritance for its collections. While you can create an object type and subtype, you can’t work with collections of those types the same way. PL/SQL object type inheritance, unlike the Java class hierarchy and parallel array class hierarchy, only supports a class hierarchy. Effectively, that means:

  • You can pass a subtype as a call parameter, or argument, to a parent data type in a function, procedure, or method signature, but
  • You can’t pass a collection of a subtype as a call parameter, or argument, to a collection of parent type in a function, procedure, or method signature.

The limitation occurs because collections have their own data type, which is fixed when you create them. Worse yet, because Oracle has never seen fit to fix their two underlying code trees (23 years and counting since Oracle 8i), you have two types of collections using two distinct C/C++ libraries. You define collections of Attribute Data Types (ATDs) when you create a collection of a standard scalar data type, like NUMBER, VARCHAR2, or DATE. You define collection of User-Defined Data Types (UTDs) when you create a collection of a SQL UDT or PL/SQL-only RECORD data type. The former uses one C/C++ library and the latter another.

Now, Oracle even make the differences between Java and PL/SQL more complex because it treats collections known as tables, really lists in most programming languages, differently than varrays, or arrays. You create a TABLE collection, or list, when you create a table of a scalar or UDT data type. There are two options when you create these object types, and they are:

  • You create an empty collection with a no element constructor, which means you’ll need to allocate memory before assigning element values later in your program.
  • You create a populated collection with a comma-delimited list of elements.

Both approaches give you a list of elements with a densely populated index. A “densely populated index” is Oracle’s jargon for how they characterize a 1-based sequence of integers without any gaps (e.g., 1, 2, 3, …). The initial construction works the same way whether you create a TABLE or VARRAY collection type. Unfortunately, after you’ve built the collection behaviors change. If you use Oracle’s Collection API to delete one or more items from a TABLE collection type, you create gaps in the index’s sequence of values. That means you must use special logic to navigate across a TABLE collection type to ensure it doesn’t fail when encountering a gap in the numeric sequence.

For example, here’s a FOR-LOOP without the logic to vouchsafe a uninterrupted set of sequence values incrementing by a counter of 1 element at a time:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Statically allocate memory and assign values
     to for elements. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
BEGIN
  /* Remove the second element, Shemp, from the 
     collection of variable length strings. */
  lv_list.DELETE(2);
 
  /* Loop through the target with a for loop, which
     depends on densely populated index values. */
  FOR i IN 1..lv_list.COUNT LOOP
    dbms_output.put_line('['||lv_list(i)||']');
  END LOOP;
END;
/

The program fails when it tries to read the second element of the table collection, which was previously removed. It raises the following error message after print the first element of the table collection:

[Moe]
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 16

Conveniently, Oracle’s Collection API provides an EXISTS method that we can use to check for the presence of an index’s value. Modifying line 16 by wrapping it in an IF-statement fixes one problem but identifies another:

15
16
17
18
19
  FOR i IN 1..lv_list.COUNT LOOP
    IF lv_list.EXISTS(i) THEN
      dbms_output.put_line('['||lv_list(i)||']');
    END IF;
  END LOOP;

The program no longer fails on a missing index value, or index gap, but it returns fewer lines of output than you might expect.

That’s because the Oracle Collection API’s COUNT method returns the number of elements currently allocated in memory not the number of original elements. We learn that when we deleted the second element, Oracle deleted the memory allocated for it as well. This is the type of behavior you might expect for a singly linked list. It prints:

[Moe]
[Larry]

One more change is required to count past and to the highest index value. One line 15, change the COUNT method call to the LAST method call, which returns the highest index value.

15
16
17
18
19
  FOR i IN 1..lv_list.LAST LOOP
    IF lv_list.EXISTS(i) THEN
      dbms_output.put_line('['||lv_list(i)||']');
    END IF;
  END LOOP;

It now prints the three stooges we would expect to see:

[Moe]
[Larry]
[Curly]

Realistically, a FOR-LOOP is not the best control structure for a collection. You should use a WHILE-LOOP and treat the incrementing value as an iterator rather than sequence index value. An iterator doesn’t worry about gaps in the sequence, it simply moves to the next element in the singly linked list. Here’s an example that uses the iterator approach with a WHILE-LOOP:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Statically allocate memory and assign values
     to for elements. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Remove the second element, Shemp, from the 
     collection of variable length strings. */
  lv_list.DELETE(2);
 
  /* Loop through the target with a while loop, which
     doesn't depend on densely populated index values
     by setting the starting index value and increment
     as if with an iterator. */
  CURRENT := lv_list.FIRST;
  WHILE NOT (CURRENT > lv_list.LAST) LOOP
    dbms_output.put_line('['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
END;
/

The iterator approach prints the elements as:

[Moe]
[Larry]
[Curly]

You can reverse the process with the following changes to lines 20-24:

20
21
22
23
24
  CURRENT := lv_list.LAST;
  WHILE NOT (CURRENT < lv_list.FIRST) LOOP
    dbms_output.put_line('['||lv_list(CURRENT)||']');
    CURRENT := lv_list.PRIOR(CURRENT);
  END LOOP;

It prints the list backwards:

[Curly]
[Larry]
[Moe]

After covering the issues with sparsely populated, those with gaps in the sequence of indexes values, table collections, let’s examine how you must work around PL/SQL’s lack of a parallel array class hierarchy. The solution lies in combining two programming concepts:

  • A function to pack the sparsely populated table collection into a densely populated one, and
  • A package with overloaded functions that pack different table collections.

To develop the test case, let’s use an ADT collection because it’s the simplest to work with. The following creates a table collection of a thirty character long scalar string:

1
2
3
CREATE OR REPLACE
  TYPE list IS TABLE OF VARCHAR2(30);
/

The following pack function takes a table collection of the thirty character long scalar string, evaluates the string for missing elements, and packs the existing elements into a densely populated list:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE
  FUNCTION pack
  ( pv_list  LIST ) RETURN list IS
 
  /* Declare a new list. */
  lv_new  LIST  := list();
BEGIN
  /* Read, check, and pack an old list into a new one. */
  FOR i IN 1..pv_list.LAST LOOP
    IF pv_list.EXISTS(i) THEN
      lv_new.EXTEND;
      lv_new(lv_new.COUNT) := pv_list(i);
    END IF;
  END LOOP;
  RETURN lv_new;
END;
/

This anonymous block tests the pack function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
  /* Declare a list value. */
  lv_test  LIST := list('Moe','Shemp','Larry','Curly');
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.COUNT LOOP
    dbms_output.put_line('['||lv_test(i)||']');
  END LOOP;
END;
/

It prints the expected three string values:

[Moe]
[Larry]
[Curly]

Now, let’s expand the example to build an overloaded package. The first step requires building a base_t object type and a table collection of the object type, like:

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE
  TYPE base_t IS OBJECT
  ( oid  NUMBER )
  INSTANTIABLE NOT FINAL;
/
 
CREATE OR REPLACE
  TYPE base_list IS TABLE OF base_t;
/

Next, you create a book_t subtype of the base_t object type and a book_list table collection of the book_t subtype, like:

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE
  TYPE book_t UNDER base_t
  ( title  VARCHAR2(30)
  , COST   NUMBER);
/
 
CREATE OR REPLACE
  TYPE book_list IS TABLE OF book_t;
/

We can test the base_t and book_t default constructors with the following SQL*Plus formatting and SQL query:

COL oid   FORMAT 999
COL title FORMAT A20
COL COST  FORMAT 99.99
SELECT *
FROM   TABLE(book_list(book_t(1,'Neuromancer',15.30)
                      ,book_t(2,'Count Zero',7.99)
                      ,book_t(3,'Mona Lisa Overdrive',7.99)
                      ,book_t(4,'Burning Chrome',8.89)));

It prints the following output:

 OID TITLE                  COST
---- -------------------- ------
   1 Neuromancer           15.30
   2 Count Zero             7.99
   3 Mona Lisa Overdrive    7.99
   4 Burning Chrome         8.89

The following is an overloaded package specification:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE
  PACKAGE packer IS
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  LIST ) RETURN list;
 
  /* A UDT list of base objects. */
  FUNCTION pack
  ( pv_list  BASE_LIST ) RETURN base_list;
 
  /* A UDT list of subtype objects. */
  FUNCTION pack
  ( pv_list  BOOK_LIST ) RETURN book_list;
 
END;
/

After you create the package specification, you need to provide the implementation. This is typical in any programming language that supports Interface Description Language (IDL). A package body provides the implementation for the package specification. The package body follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
CREATE OR REPLACE
  PACKAGE BODY packer IS
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  LIST ) RETURN list IS
 
    /* Declare a new list. */
    lv_new  LIST  := list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  BASE_LIST ) RETURN base_list IS
 
    /* Declare a new list. */
    lv_new  BASE_LIST  := base_list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  BOOK_LIST ) RETURN book_list IS
 
    /* Declare a new list. */
    lv_new  BOOK_LIST  := book_list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
END packer;
/

The test case for the base_list object type is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE
  lv_test  BASE_LIST :=
    base_list(base_t(1),base_t(2)
             ,base_t(3),base_t(4));
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := packer.pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.LAST LOOP
    dbms_output.put_line('['||lv_test(i).oid||']');
  END LOOP;
END;
/

It prints the following output:

[1]
[3]
[4]

The test case for the book_list object type is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DECLARE
  lv_test  BOOK_LIST := 
    book_list(book_t(1,'Neuromancer',15.30)
             ,book_t(2,'Count Zero',7.99)
             ,book_t(3,'Mona Lisa Overdrive',7.99)
             ,book_t(4,'Burning Chrome',8.89));
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := packer.pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.LAST LOOP
    dbms_output.put_line( '['||lv_test(i).oid||']'
                        ||'['||lv_test(i).title||']'
                        ||'['||lv_test(i).COST||']');
  END LOOP;
END;
/

It prints the following output:

[1][Neuromancer][15.3]
[3][Mona Lisa Overdrive][7.99]
[4][Burning Chrome][8.89]

In conclusion, you would not have to write overloaded methods for every list if PL/SQL supported class hierarchy and parallel array class hierarchy like Java. Unfortunately, it doesn’t and likely won’t in the future. You can pack table collections as a safety measure when they’re passed as parameters to other functions, procedures, or methods with the code above.

As always, I hope this helps those looking for a solution.

Written by maclochlainn

February 1st, 2021 at 12:08 am

PL/SQL Coupled Loops

without comments

The purpose of this example shows you how to navigate a list with a sparsely populated index. This can occur when one element has been removed after the list was initialized. Unlike Oracle’s VARRAY (array), removing an element from a TABLE or list does not re-index the elements of the list.

This example also shows you how to coupled lists. The outer loop increments, notwithstanding the gap in index values, while the inner loop decrements. The upper range of the inner loop is set by the index value of the outer loop.

The example program uses an abbreviated version of the Twelve Days of Christmas, and I’ve tried to put teaching notes throughout the example file.

DECLARE
  /* Create a single column collection that is a list strings
     less than 8 characters in length and another of strings
     less than 20 characters in length. */
  TYPE DAY   IS TABLE OF VARCHAR2(8);
  TYPE verse IS TABLE OF VARCHAR2(20);
 
  /* Create variables that use the user-defined types:
  || =================================================
  ||  1. We give the variable a name of lv_day and lv_verse.
  ||  2. We assign a user-defined ADT (Attribute Data Type) collection.
  ||  3. We assign a list of value to the constructor of the list, which
  ||     allocates memory for each item in the comma-delimited list of
  ||     string.
  */
  lv_day   DAY   := DAY('first','second','third','fourth','fifth');
  lv_verse VERSE := verse('Partridge','Turtle Doves','French Hen'
                         ,'Calling Birds','Gold Rings');
 
BEGIN
  /*
  ||  Remove an element from each of the two lists, which makes the two
  ||  lists sparsely indexed. A sparsely indexed list has gaps in the
  ||  sequential index of the list.
  */
 
  lv_day.DELETE(3);
 
  /*
  ||   Loop through the list of days:
  ||  ===================================================j
  ||   1. A list created by a comma-delimited list is densely populated,
  ||      which means it has no gaps in the sequence of indexes.
  ||   2. A list created by any means that is subsequently accessed
  ||      and has one or more items removed is sparsely populated,
  ||      which means it may have gaps in the sequence of indexes.
  ||   3. A FOR loop anticipates densely populated indexes and fails
  ||      when trying to read a missing index, which is why you should
  ||      use an IF statement to check for the element of a list before
  ||      accessing it.
  ||   4. A COUNT method returns the number of elements allocated memory
  ||      in a list of values and the LAST method returns the highest
  ||      index value. The index value is alway an integer for user-defined
  ||      ADT (Attribute Data Type) collections, but may be a string for
  ||      an associative array or a PL/SQL list indexed by a string.
  ||   5. Removing an element from a list does not change the other
  ||      index values but does if you create an array (or varray), which
  ||      means COUNT OR LAST may cause the same type of error for a list
  ||      with a missing element.
  */
 
  FOR i IN 1..lv_day.LAST LOOP
 
    /*
    ||  Verify the index is valid.
    || ====================================================
    ||  You check whether the element is present in the
    ||  list.
    */
 
    IF lv_day.EXISTS(i) THEN
 
      /* Print the beginning of the stanza. */
      dbms_output.put_line('On the ['||lv_day(i)||'] of Christmas ...');
 
      /* Print the song. */
      FOR j IN REVERSE 1..i LOOP
        /* Check if the day exists. */
        IF lv_verse.EXISTS(j) THEN
          /* All but first and last verses. */
          IF j > 1 THEN
            dbms_output.put_line('-   ['||lv_verse(j)||']');
          /* The last verse. */
          ELSIF i = j THEN
            dbms_output.put_line('- A ['||lv_verse(j)||']'||CHR(10));
          /* Last verse. */
          ELSE
            dbms_output.put_line('and a ['||lv_verse(j)||']'||CHR(10));
          END IF;
        END IF;
      END LOOP;
    ELSE
      CONTINUE;
    END IF;
  END LOOP;
END;
/

As always, I hope it helps you solve problems in the real world.

Written by maclochlainn

January 27th, 2021 at 9:09 pm

Create Student User

without comments

It’s amazing how old some of my students’ computers are. The oldest with least memory are the foreign students. Fortunately, I kept copies of the old Oracle Database 10g XE. I give it to some students who need to run the smallest possible option. Then, again I have students who get emotional about having to use Unix or Linux as an operating system, which means I now also support Oracle Database 18c.

Anyway, I had to write a script that would support building a small 200 MB student schema in any of the Express Edition databases from 10g to 18c. Here’s the script for those who would like to use it. It sets up a student schema for Oracle Database 10g and 11g databases and a c##student schema for Oracle’s Containized Database 12c and 18c.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
DECLARE
 /* Control variable. */
 container  BOOLEAN := FALSE;
 
 /* Weakly structured system reference cursor. */
 container_sql  SYS_REFCURSOR;
 
 /* Constant required for pre-container databases to avoid a
    a compile time error. */
 sql_statement  CONSTANT VARCHAR2(50) :=
                  'SELECT cdb FROM v$database WHERE cdb = ''YES''';
BEGIN
 /* Check if the current user is the superuser. */
 FOR i IN (SELECT USER FROM dual) LOOP
 
   /* Perform tasks as superuser. */
   IF i.USER = 'SYSTEM' THEN
     /* Check for a container-enabled column, which enables this to
        work in both pre-container Oracle databases, like 10g and 11g. */
     FOR j IN (SELECT DISTINCT column_name
               FROM   dba_tab_columns
               WHERE  column_name = 'CDB') LOOP
 
       /* Check for a container database, set control variable
          and exit when found. */
       OPEN container_sql FOR sql_statement;
       LOOP
         container := TRUE;
         EXIT WHEN container_sql%FOUND;
       END LOOP;
     END LOOP;
 
      /* Conditionally drop existing user and role. */   
      IF container THEN
        /* Conditionally drop a container user. */
        FOR j IN (SELECT username
                  FROM   dba_users
                  WHERE  username = 'C##STUDENT') LOOP
          EXECUTE IMMEDIATE 'DROP USER c##student CASCADE';
        END LOOP;
 
        /* Conditionally rop the container c##studentrole role. */
        FOR j IN (SELECT ROLE
                  FROM   dba_roles
                  WHERE  ROLE = 'C##STUDENTROLE') LOOP
          EXECUTE IMMEDIATE 'DROP ROLE c##studentrole';
        END LOOP;
 
        /* Create a container user with 200 MB of space. */
        EXECUTE IMMEDIATE 'CREATE USER c##student'||CHR(10)
        ||                'IDENTIFIED BY student'||CHR(10)
        ||                'DEFAULT TABLESPACE users'||CHR(10)
        ||                'QUOTA 200M ON users'||CHR(10)
        ||                'TEMPORARY TABLESPACE temp';
 
        /* Create a container role. */
        EXECUTE IMMEDIATE 'CREATE ROLE c##studentrole CONTAINER = ALL';
 
        /* Grant privileges to a container user. */
        EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10)
        ||                'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10)
        ||                'CREATE SESSION, CREATE TABLE,'||CHR(10)
        ||                'CREATE TRIGGER, CREATE TYPE,'||CHR(10)
        ||                'CREATE VIEW TO c##studentrole';
 
        /* Grant role to user. */
        EXECUTE IMMEDIATE 'GRANT c##studentrole TO c##student';
      ELSE
        /* Conditonally drop the non-container database user. */
        FOR j IN (SELECT username
                  FROM   dba_users
                  WHERE  username = 'STUDENT') LOOP
          EXECUTE IMMEDIATE 'DROP USER student CASCADE';
        END LOOP;
 
        /* Create the student database. */
        EXECUTE IMMEDIATE 'CREATE USER student'||CHR(10)
        ||                'IDENTIFIED BY student'||CHR(10)
        ||                'DEFAULT TABLESPACE users'||CHR(10)
        ||                'QUOTA 200M ON users'||CHR(10)
        ||                'TEMPORARY TABLESPACE temp';
 
        /* Grant necessary privileges to the student database. */
        EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10)
        ||                'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10)
        ||                'CREATE SESSION, CREATE TABLE,'||CHR(10)
        ||                'CREATE TRIGGER, CREATE TYPE,'||CHR(10)
        ||                'CREATE VIEW TO student';
       END IF;
    ELSE
      /* Print an message that the user lacks privilegs. */
      dbms_output.put_line('You must be the SYSTEM user to drop and create a user.');
    END IF;
  END LOOP;
END;
/

As always, I hope this helps those looking for a solution beyond Quest’s Toad for Oracle, APEX, or SQL Developer. Let me know if you like it.

Written by maclochlainn

May 4th, 2020 at 8:01 pm

Postgres Print Debug Notes

without comments

A student asked how you print output from PL/pgSQL blocks. The student wanted to know if there was something like the following in Oracle’s PL/SQL programming language:

dbms_output.put_line('some string');

or, in Java programming the:

System.out.println("some string");

The RAISE NOTICE is the equivalent to these in Postgres PL/pgSQL, as shown in the following anonymous block:

do $$
BEGIN
  raise notice 'Hello World!';
END;
$$;

It prints:

NOTICE:  Hello World!

You can write a hello_world function as a named PL/pgSQL block:

CREATE FUNCTION hello_world()
RETURNS text AS
$$
DECLARE
  output  VARCHAR(20);
BEGIN
  /* Query the string into a local variable. */
  SELECT 'Hello World!' INTO output;
 
  /* Return the output text variable. */
  RETURN output;
END
$$ LANGUAGE plpgsql;

You can call it with the following:

SELECT hello_world();

It prints:

 hello_world  
--------------
 Hello World!
(1 row)

Here’s a full test case with stored procedure in PL/pgSQL:

-- Drop the msg table.
DROP TABLE msg;
 
-- Create the msg table.
CREATE TABLE msg
( comment  VARCHAR(400) );
 
-- Transaction Management Example.
DROP PROCEDURE IF EXISTS testing
( IN pv_one                 VARCHAR(30)
, IN pv_two                 VARCHAR(10));
 
-- Transaction Management Example.
CREATE OR REPLACE PROCEDURE testing
( IN pv_one                 VARCHAR(30)
, IN pv_two                 VARCHAR(10)) AS
$$
DECLARE
  /* Declare error handling variables. */
  err_num      TEXT;
  err_msg      INTEGER;
BEGIN
  /* Log actdual parameter values. */
  INSERT INTO msg VALUES (pv_one||'.'||pv_two);
 
EXCEPTION
  WHEN OTHERS THEN
    err_num := SQLSTATE;
    err_msg := SUBSTR(SQLERRM,1,100);
    RAISE NOTICE 'Trapped Error: %', err_msg;
END
$$ LANGUAGE plpgsql;
 
do $$
DECLARE
  lv_one VARCHAR(30) := 'INDIVIDUAL';
  lv_two VARCHAR(19) := 'R11-514-34';
BEGIN
  RAISE NOTICE '[%]', lv_one;
  RAISE NOTICE '[%]', lv_two;
  CALL testing( pv_one := lv_one, pv_two := lv_two );
END
$$;
 
-- Query any logged results.
SELECT * FROM msg;

It prints:

DROP TABLE
CREATE TABLE
DROP PROCEDURE
CREATE PROCEDURE
psql:fixed.sql:61: NOTICE:  [INDIVIDUAL]
psql:fixed.sql:61: NOTICE:  [R11-514-34]
DO
        comment        
-----------------------
 INDIVIDUAL.R11-514-34
(1 row)

I hope this helps those looking for a solution.

Written by maclochlainn

October 12th, 2019 at 5:03 pm

Misleading ORA- Message

without comments

Oracle error messages are more or less the best in the industry but time-to-time they saddle you with a bad or misleading message. For example, I was running one of the code modules from my Oracle Database 12c PL/SQL Programming book for a class exercise and got this error message:

BEGIN
*
ERROR AT line 1:
ORA-22288: FILE OR LOB operation  failed
ORA-06512: AT "STUDENT.LOAD_CLOB_FROM_FILE", line 71
ORA-06512: AT line 11

Oddly enough, it was simple to identify generally. It failed on a call to the DBMS_LOB.LOADCLOBFROMFILE procedure. However, the better question is why did it fail because the virtual directory resolved and the permissions worked.

The first test was to try another file, which worked perfectly with the same code. That meant it had to be something with the physical file. I took a look and sure enough I found a character set problem, like the following:

… he reveals that the Nazgûl, or Ringwraiths, have left Mordor to capture the Ring and kill whoever carries it.

and,

The group flees to the elvish realm of Lothlórien …

The “û” and “ó” characters were incompatible with the default NLS_LANG setting of the database and a CLOB limits the use of non-standard character sets. It’s ashamed that Oracle didn’t through a character set error, which would have expedited resolution of the problem.

As always, I hope this helps those looking for solutions.

Written by maclochlainn

August 17th, 2019 at 4:52 pm