MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle Developer’ tag

Oracle PLS-00103 Gotcha

without comments

Teaching PL/SQL can be fun and sometimes challenging when you need to troubleshoot a student error. Take the Oracle PLS-00103 error can be very annoying when it return like this:

24/5     PLS-00103: Encountered the symbol "LV_CURRENT_DATE" WHEN
         expecting one OF the following:
         language

Then, you look at the code and see:

22
23
24
25
   , pv_user_id             NUMBER ) IS
 
    /* Declare local constants. */
    lv_current_date      DATE := TRUNC(SYSDATE);

Obviously, there’s nothing wrong on the line number that the error message pointed. Now, here’s where it gets interesting because of a natural human failing. The student thought they had something wrong with declaring the variable and tested as stand alone procedure and anonymous block. Naturally, they were second guessing what they knew about the PL/SQL.

That’s when years of experience with PL/SQL kicks in to solve the problem. The trick is recognizing two things:

  1. The error message points to the first line of code in a package body.
  2. The error is pointing to the first character on the line after the error.

That meant that the package body was incorrectly defined. A quick check to the beginning of the package body showed:

1
2
3
4
5
6
CREATE OR REPLACE
  PACKAGE account_creation AS
 
  PROCEDURE insert_contact
  ( pv_first_name          VARCHAR2
  , pv_middle_name         VARCHAR2 := NULL

The student failed to designate the package as an implementation by omitting the keyword BODY from line 2. The proper definition of the package body should be:

1
2
3
4
5
6
CREATE OR REPLACE
  PACKAGE BODY account_creation AS
 
  PROCEDURE insert_contact
  ( pv_first_name          VARCHAR2
  , pv_middle_name         VARCHAR2 := NULL

That’s the resolution for the error message. I wrote this because I checked if they should have been able to find a helpful article with a google search. I discovered that there wasn’t an answer like this that came up after 10 minutes of various searches.

As always, I hope this helps those writing PL/SQL.

Written by maclochlainn

October 3rd, 2022 at 12:11 am

PL/SQL Overloading

without comments

So, I wrote an updated example of my grandma and tweetie_bird for my students. It demonstrates overloading with the smallest parameter lists possible across a transaction of two tables. It also shows how one version of the procedure can call another version of the procedure.

The tables are created with the following:

/* Conditionally drop grandma table and grandma_s sequence. */
BEGIN
  FOR i IN (SELECT object_name
            ,      object_type
            FROM   user_objects
            WHERE  object_name IN ('GRANDMA','GRANDMA_SEQ')) LOOP
    IF i.object_type = 'TABLE' THEN
      /* Use the cascade constraints to drop the dependent constraint. */
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
/* Create the table. */
CREATE TABLE GRANDMA
( grandma_id     NUMBER       CONSTRAINT grandma_nn1 NOT NULL
, grandma_house  VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL
, created_by     NUMBER       CONSTRAINT grandma_nn3 NOT NULL
, CONSTRAINT grandma_pk       PRIMARY KEY (grandma_id)
);
 
/* Create the sequence. */
CREATE SEQUENCE grandma_seq;
 
/* Conditionally drop a table and sequence. */
BEGIN
  FOR i IN (SELECT object_name
            ,      object_type
            FROM   user_objects
            WHERE  object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ')) 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;
/
 
/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        CONSTRAINT tweetie_bird_nn1 NOT NULL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn2 NOT NULL
, grandma_id          NUMBER        CONSTRAINT tweetie_bird_nn3 NOT NULL
, created_by          NUMBER        CONSTRAINT tweetie_bird_nn4 NOT NULL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);
 
/* Create sequence. */
CREATE SEQUENCE tweetie_bird_seq;

The sylvester package specification holds the two overloaded procedures, like:

CREATE OR REPLACE
  PACKAGE sylvester IS
 
  /* Three variable length strings. */
  PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2
  , pv_system_user_name    VARCHAR2  );
 
  /* Two variable length strings and a number. */  
  PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2
  , pv_system_user_id      NUMBER   );
 
END sylvester;
/

The sylvester package implements two warner_brother procedures. One takes the system user’s ID and the other takes the system user’s name. The procedure that accepts the system user name queries the system_user table with the system_user_name to get the system_user_id column and then calls the other version of itself. This demonstrates how you only write logic once when overloading and let one version call the other with the added information.

Here’s the sylvester package body code:

CREATE OR REPLACE
  PACKAGE BODY sylvester IS
 
  /* Procedure warner_brother with user name. */
  PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2
  , pv_system_user_id      NUMBER  ) IS
 
    /* Declare a local variable for an existing grandma_id. */
    lv_grandma_id   NUMBER;
 
    FUNCTION get_grandma_id
    ( pv_grandma_house  VARCHAR2 ) RETURN NUMBER IS
 
      /* Initialized local return variable. */
      lv_retval  NUMBER := 0;  -- Default value is 0.
 
      /* A cursor that lookups up a grandma's ID by their name. */
      CURSOR find_grandma_id
      ( cv_grandma_house  VARCHAR2 ) IS
        SELECT grandma_id
        FROM   grandma
        WHERE  grandma_house = cv_grandma_house;
 
    BEGIN   
      /* Assign a grandma_id as the return value when a row exists. */
      FOR i IN find_grandma_id(pv_grandma_house) LOOP
        lv_retval := i.grandma_id;
      END LOOP;
 
      /* Return 0 when no row found and the grandma_id when a row is found. */
      RETURN lv_retval;
    END get_grandma_id;
 
  BEGIN
    /* Set the savepoint. */
    SAVEPOINT starting;
 
    /*
     *  Identify whether a member account exists and assign it's value
     *  to a local variable.
     */
    lv_grandma_id := get_grandma_id(pv_grandma_house);
 
    /*
     *  Conditionally insert a new member account into the member table
     *  only when a member account does not exist.
     */
    IF lv_grandma_id = 0 THEN
 
      /* Insert grandma. */
      INSERT INTO grandma
      ( grandma_id
      , grandma_house
      , created_by )
      VALUES
      ( grandma_seq.NEXTVAL
      , pv_grandma_house
      , pv_system_user_id  );
 
      /* Assign grandma_seq.currval to local variable. */
      lv_grandma_id := grandma_seq.CURRVAL;
 
    END IF;
 
    /* Insert tweetie bird. */
    INSERT INTO tweetie_bird
    ( tweetie_bird_id
    , tweetie_bird_house 
    , grandma_id
    , created_by )
    VALUES
    ( tweetie_bird_seq.NEXTVAL
    , pv_tweetie_bird_house
    , lv_grandma_id
    , pv_system_user_id );
 
    /* If the program gets here, both insert statements work. Commit it. */
    COMMIT;
 
  EXCEPTION
    /* When anything is broken do this. */
    WHEN OTHERS THEN
      /* Until any partial results. */
      ROLLBACK TO starting;
  END;
 
  PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2
  , pv_system_user_name    VARCHAR2  ) IS
 
    /* Define a local variable. */
	lv_system_user_id  NUMBER := 0;
 
    FUNCTION get_system_user_id
    ( pv_system_user_name  VARCHAR2 ) RETURN NUMBER IS
 
      /* Initialized local return variable. */
      lv_retval  NUMBER := 0;  -- Default value is 0.
 
      /* A cursor that lookups up a grandma's ID by their name. */
      CURSOR find_system_user_id
      ( cv_system_user_id  VARCHAR2 ) IS
        SELECT system_user_id
        FROM   system_user
        WHERE  system_user_name = pv_system_user_name;
 
    BEGIN   
      /* Assign a grandma_id as the return value when a row exists. */
      FOR i IN find_system_user_id(pv_system_user_name) LOOP
        lv_retval := i.system_user_id;
      END LOOP;
 
      /* Return 0 when no row found and the grandma_id when a row is found. */
      RETURN lv_retval;
    END get_system_user_id;
 
  BEGIN
 
    /* Convert a system_user_name to system_user_id. */
	lv_system_user_id := get_system_user_id(pv_system_user_name);
 
	/* Call the warner_brother procedure. */
	warner_brother
    ( pv_grandma_house      => pv_grandma_house
    , pv_tweetie_bird_house => pv_tweetie_bird_house
    , pv_system_user_id     => lv_system_user_id  );
 
  EXCEPTION
    /* When anything is broken do this. */
    WHEN OTHERS THEN
      /* Until any partial results. */
      ROLLBACK TO starting;
  END;  
 
END sylvester;
/

The following anonymous block test case works with the code:

BEGIN
  sylvester.warner_brother( pv_grandma_house      => 'Blue House'
                          , pv_tweetie_bird_house => 'Cage'
				          , pv_system_user_name   => 'DBA 3' );
  sylvester.warner_brother( pv_grandma_house      => 'Blue House'
                          , pv_tweetie_bird_house => 'Tree House'
				          , pv_system_user_id     =>  4 );
END;
/

You can now query the results with this SQL*PLus formatting and query:

/* Query results from warner_brother procedure. */
COL grandma_id          FORMAT 9999999  HEADING "Grandma|ID #"
COL grandma_house       FORMAT A14      HEADING "Grandma House"
COL created_by          FORMAT 9999999  HEADING "Created|By"
COL tweetie_bird_id     FORMAT 9999999  HEADING "Tweetie|Bird ID"
COL tweetie_bird_house  FORMAT A18      HEADING "Tweetie Bird House"
SELECT *
FROM   grandma g INNER JOIN tweetie_bird tb
ON     g.grandma_id = tb.grandma_id;

You should see the following data:

 Grandma                 Created  Tweetie                     Grandma  Created
    ID # Grandma House        By  Bird ID Tweetie Bird House     ID #       By
-------- -------------- -------- -------- ------------------ -------- --------
       1 Blue House            3        1 Cage                      1        3
       1 Blue House            3        2 Tree House                1        4

As always, I hope complete code samples help solve real problems.

Written by maclochlainn

September 29th, 2022 at 9:24 pm

Oracle Partitioned Tables

without comments

Oracle Partitioned Tables

Learning Outcomes

  • Learn about List Partitioning.
  • Learn about Range Partitioning.
  • Learn about Hash Partitioning.
  • Learn about Composite Partitioning.

Lesson Material

Partitioning is the process of breaking up a data source into a series of data sources. Partitioned tables are faster to access and transact against. Partitioning data becomes necessary as the amount of data grows in any table. It speeds the search to find rows and insert, update, or delete rows.

Oracle Database 21c supports four types of table partitioning: list, range, hash, and composite partitioning.

List Partitioning

A list partition works by identifying a column that contains a value, such as a STATE column in an ADDRESS table. Partitioning clauses follow the list of columns and constraints.

A list partition could use a STATE column, like the following (the complete example is avoided to conserve space, and the three dots represent the balance of partitions not shown):

CREATE TABLE franchise
( franchise_id    NUMBER CONSTRAINT pk_franchise PRIMARY KEY
, franchise_name  VARCHAR(20)
, city            VARCHAR(20)
, state           VARCHAR(20))
PARTITION BY LIST(state)
( PARTITION offshore VALUES('Alaska', 'Hawaii')
, PARTITION west VALUES('California', 'Oregon', 'Washington')
, PARTITION desert VALUES ('Arizona','New Mexico')
, PARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming')
, ... );

This can be used with other values such as ZIP codes with great effect, but the maintenance of list partitioning can be considered costly. Cost occurs when the list of values changes over time. Infrequent change means low cost, while frequent change means high costs. In the latter case, you should consider other partitioning strategies. Although an Oracle database supports partitioning on a variable-length string, MySQL performs list partitioning only on integer columns.

Range Partitioning

Range partitioning is very helpful on any column that contains a continuous metric, such as dates or time. It works by stating a minimum set that is less than a certain value, and then a group of sets of higher values until you reach the top most set of values. This type of partition helps you improve performance by letting you search ranges rather than complete data sets. Range partitioning is also available in MySQL.

A range example based on dates could look like this:

PARTITION BY RANGE(rental_date)
( PARTITION rental_jan2011
  VALUES LESS THAN TO_DATE('31-JAN-11','DD-MON-YY')
, PARTITION rental_feb2011
  VALUES LESS THAN TO_DATE('28-FEB-11','DD-MON-YY')
, PARTITION rental_mar2011
  VALUES LESS THAN TO_DATE('31-MAR-11','DD-MON-YY')
, ... );

The problem with this type of partitioning, however, is that the new months require constant management. Many North American businesses simply add partitions for all months in the year as an annual maintenance task during the holidays in November or December. Companies that opt for bigger range increments reap search and access benefits from range partitioning, while minimizing ongoing maintenance expenses.

Hash Partitioning

Hash partitioning is much easier to implement than list or range partitioning. Many DBAs favor it because it avoids the manual maintenance of list and range partitioning. Oracle Database 21c documentation recommends that you implement a hash for the following reasons:

  • There is no concrete knowledge about how much data maps to a partitioning range.
  • The sizes of partitions are unknown at the outset and difficult to balance as data is added to the database.
  • A range partition might cluster data in an ineffective way.

This next statement creates eight partitions and stores them respectively in one of the eight tablespaces. The hash partition manages nodes and attempts to balance the distribution of rows across the nodes.

PARTITION BY HASH(store)
PARTITIONS 8
STORE IN (tablespace1, tablespace2, tablespace3, tablespace4
         ,tablespace5, tablespace6, tablespace7, tablespace8);

As you can imagine the maintenance for this type of partitioning is low. Some DBAs choose this method to get an initial sizing before adopting a list or range partitioning plan. Maximizing the physical resources of the machine ultimately rests with the DBAs who manage the system. Developers need to stand ready to assist DBAs with analysis and syntax support.

Composite Partitioning

Composite partitioning requires a partition and subpartition. The composites are combinations of two types of partitioning—typically, list and range partitioning, or range and hash composite partitioning. Which of these you should choose depends on a few considerations. List and range composite partitioning is done for historical information and is well suited for data warehouses. This method lets you partition on unordered or unrelated column values.

A composite partition like this uses the range as the partition and the list as the subpartition, like the following:

PARTITION BY RANGE (rental_date)
 SUBPARTITION BY LIST (state)
 (PARTITION FQ1_1999 VALUES LESS THAN (TO_DATE('1-APR-2011','DD-MON-YYYY'))
  (SUBPARTITION offshore VALUES('Alaska', 'Hawaii')
  , SUBPARTITION west VALUES('California', 'Oregon', 'Washington')
  , SUBPARTITION desert VALUES ('Arizona','New Mexico')
  , SUBPARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming')
  , ... )
,(PARTITION FQ2_1999 VALUES LESS THAN (TO_DATE('1-APR-2011','DD-MON-YYYY'))
  (SUBPARTITION offshore VALUES('Alaska', 'Hawaii')
  , SUBPARTITION west VALUES('California', 'Oregon', 'Washington')
  , SUBPARTITION desert VALUES ('Arizona','New Mexico')
  , SUBPARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming')
  , ... )
, ... )

Range and hash composite partitioning is done for historical information when you also need to stripe data. Striping is the process of creating an attribute in a table that acts as a natural subtype or separator of data. Users typically view data sets of one subtype, which means organizing the data by stripes (subtypes) can speed access based on user access patterns.

Range is typically the partition and the hash is the subpartition in this composite partitioning schema. The syntax for this type of partition is shown next:

PARTITION BY RANGE (rental_date)
 SUBPARTITION BY HASH(store)
  SUBPARTITIONS 8 STORE IN (tablespace1, tablespace2, tablespace3
                           ,tablespace4, tablespace5, tablespace6
                           ,tablespace7, tablespace8)
   ( PARTITION rental_jan2011
     VALUES LESS THAN TO_DATE('31-JAN-11','DD-MON-YY')
   , PARTITION rental_feb2011
     VALUES LESS THAN TO_DATE('28-FEB-11','DD-MON-YY')
   , PARTITION rental_mar2011
     VALUES LESS THAN TO_DATE('31-MAR-11','DD-MON-YY')
   , ... )

Written by maclochlainn

May 30th, 2022 at 9:38 pm

PL/SQL List to Struct

without comments

Every now and then, I get questions from folks about how to tune in-memory elements of their PL/SQL programs. This blog post address one of those core issues that some PL/SQL programmers avoid.

Specifically, it addresses how to convert a list of values into a structure (in C/C++ its a struct, in Java its an ArrayList, and PL/SQL it’s a table of scalar or object types). Oracle lingo hides the similarity by calling either an Attribute Definition Type (ADT) or User-Defined Type (UDT). The difference in the Oracle space is that an ADT deals with a type defined in DBMS_STANDARD package, which is more or less like a primitive type in Java.

Oracle does this for two reasons:

The cast_strings function converts a list of strings into a record data structure. It lets the list of strings have either a densely or sparsely populated list of values, and it calls the verify_date function to identify a DATE data type and regular expressions to identify numbers and strings.

You need to build a UDT object type and lists of both ADT and UDT data types.

/* Create a table of strings. */
CREATE OR REPLACE
  TYPE tre AS TABLE OF VARCHAR2(20);
/
 
/* Create a structure of a date, number, and string. */
CREATE OR REPLACE
  TYPE struct IS OBJECT
  ( xdate     DATE
  , xnumber  NUMBER
  , xstring  VARCHAR2(20));
/
 
/* Create a table of tre type. */
CREATE OR REPLACE
  TYPE structs IS TABLE OF struct;
/

The cast_strings function is defined below:

CREATE OR REPLACE
  FUNCTION cast_strings
  ( pv_list  TRE ) RETURN struct IS
 
  /* Declare a UDT and initialize an empty struct variable. */
  lv_retval  STRUCT := struct( xdate => NULL
                             , xnumber => NULL
					         , xstring => NULL); 
  BEGIN  
    /* Loop through list of values to find only the numbers. */
    FOR i IN 1..pv_list.LAST LOOP
      /* Ensure that a sparsely populated list can't fail. */
      IF pv_list.EXISTS(i) THEN
        /* Order if number evaluation before string evaluation. */
        CASE
          WHEN lv_retval.xnumber IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:digit:]]*$') THEN
            lv_retval.xnumber := pv_list(i);
          WHEN verify_date(pv_list(i)) THEN
            IF lv_retval.xdate IS NULL THEN
              lv_retval.xdate := pv_list(i);
            ELSE
              lv_retval.xdate := NULL;
            END IF;
          WHEN lv_retval.xstring IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:alnum:]]*$') THEN
            lv_retval.xstring := pv_list(i);
          ELSE
            NULL;
        END CASE;
      END IF;
    END LOOP;
 
    /* Print the results. */
    RETURN lv_retval;
  END;
/

There are three test cases for this function:

  • The first use-case checks whether the input parameter is a sparsely or densely populated list:

    DECLARE
      /* Declare an input variable of three or more elements. */
      lv_list    TRE := tre('Berlin','25','09-May-1945','45');
     
      /* Declare a variable to hold the compound type values. */
      lv_struct  STRUCT;
    BEGIN
      /* Make the set sparsely populated. */
      lv_list.DELETE(2);
     
      /* Test the cast_strings function. */
      lv_struct := cast_strings(lv_list);
     
      /* Print the values of the compound variable. */
      dbms_output.put_line(CHR(10));
      dbms_output.put_line('xstring ['||lv_struct.xstring||']');
      dbms_output.put_line('xdate   ['||TO_CHAR(lv_struct.xdate,'DD-MON-YYYY')||']');
      dbms_output.put_line('xnumber ['||lv_struct.xnumber||']');
    END;
    /

    It should return:

    xstring [Berlin]
    xdate   [09-MAY-1945]
    xnumber [45]

    The program defines two numbers and deletes the first number, which is why it prints the second number.

  • The second use-case checks with a list of only one element:

    SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate
    ,      xnumber
    ,      xstring
    FROM   TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945'))));

    It should return:

    XDATE                   XNUMBER XSTRING
    -------------------- ---------- --------------------
    25-NOV-1945                  25 catch22

    The program returns a structure with values converted into their appropriate data type.

  • The third use-case checks with a list of two elements:

    SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate
    ,      xnumber
    ,      xstring
    FROM   TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945'))
                        ,cast_strings(tre('31-APR-2017','1918','areodromes'))));

    It should return:

    XDATE                   XNUMBER XSTRING
    -------------------- ---------- --------------------
    25-NOV-1945                  25 catch22
                               1918 areodromes

    The program defines calls the cast_strings with a valid set of values and an invalid set of values. The invalid set of values contains a bad date in the set of values.

As always, I hope this helps those looking for how to solve this type of problem.

PL/SQL CASE Not Found

without comments

I was working on some test cases for my students and changing the behavior of a verify_date function that I wrote years ago to validate and returns valid dates when they’re passed as strings. The original program returned today’s date when the date was invalid.

The new function returns a BOOLEAN value of false by default and true when the string validates as a date. Unfortunately, I introduced a mistake that didn’t use to exist in Oracle 11g, which was the version when I wrote the original function.

The test cases in Oracle 21c raises the following error when an invalid date is passed to the CASE statement by the cast_strings function that calls the new verify_date function:

FROM   TABLE(structs(cast_strings(tre('31-APR-2017','1917','dirk'))))
                     *
ERROR AT line 2:
ORA-06592: CASE NOT found WHILE executing CASE statement
ORA-06512: AT "C##STUDENT.VERIFY_DATE", line 30
ORA-06512: AT "C##STUDENT.CAST_STRINGS", line 18

As you can see, the test case uses ’31-APR-2017′ as an incorrect date to verify the use-case. The error occurred because the ELSE clause in the CASE statement wasn’t provided. Previously, the ELSE clause was optional and setting the lv_retval return variable to FALSE in the DECLARE block made it unnecessary.

The fixed code 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
CREATE OR REPLACE
  FUNCTION verify_date
  ( pv_date_in  VARCHAR2) RETURN BOOLEAN IS
 
  /* Local variable to ensure case-insensitive comparison. */
  lv_date_in  VARCHAR2(11);
 
  /* Local return variable. */
  lv_date  BOOLEAN := FALSE;
BEGIN
  /* Convert string input to uppercase month. */
  lv_date_in := UPPER(pv_date_in);
 
  /* Check for a DD-MON-RR or DD-MON-YYYY string. */
  IF REGEXP_LIKE(lv_date_in,'^[0-9]{2,2}-[ADFJMNOS][ACEOPU][BCGLNPRTVY]-([0-9]{2,2}|[0-9]{4,4})$') THEN
    /* Case statement checks for 28 or 29, 30, or 31 day month. */
    CASE
      /* Valid 31 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) IN ('JAN','MAR','MAY','JUL','AUG','OCT','DEC') AND
           TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 31 THEN 
        lv_date := TRUE;
      /* Valid 30 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) IN ('APR','JUN','SEP','NOV') AND
           TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 30 THEN 
        lv_date := TRUE;
      /* Valid 28 or 29 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) = 'FEB' THEN
        /* Verify 2-digit or 4-digit year. */
        IF (LENGTH(pv_date_in) = 9 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,2)) + 2000,4) = 0 OR
            LENGTH(pv_date_in) = 11 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,4)),4) = 0) AND
            TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 29 THEN
          lv_date := TRUE;
        ELSE /* Not a leap year. */
          IF TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 28 THEN
            lv_date := TRUE;
          END IF;
        END IF;
      ELSE
        NULL;
    END CASE;
  END IF;
  /* Return date. */
  RETURN lv_date;
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN lv_date;
END;
/

The new ELSE clause in on lines 31 and 32, and the converted function works. I also added a local lv_date_in variable to hold an uppercase version of an input string to: ensure a case-insensitive comparison of the month value, and avoid a having to pass the input as an IN OUT mode parameter. Typically, I leave off exception handlers because mistyping or copying for newer programmers becomes easier, but in this case I added an exception handler for strings that are larger than 11-characters.

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

Written by maclochlainn

May 22nd, 2022 at 5:41 pm

Oracle DSN Security

without comments

Oracle disallows entry of a password value when configuring the ODBC’s Windows Data Source Name (DSN) configurations. As you can see from the dialog’s options:

So, I check the Oracle ODBC’s property list with the following PowerShell command:

Get-Item -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Oracle | Select-Object

It returned:

Oracle                         Driver                 : C:\app\mclaughlinm\product\18.0.0\dbhomeXE\BIN\SQORA32.DLL
                               DisableRULEHint        : T
                               Attributes             : W
                               SQLTranslateErrors     : F
                               LobPrefetchSize        : 8192
                               AggregateSQLType       : FLOAT
                               MaxTokenSize           : 8192
                               FetchBufferSize        : 64000
                               NumericSetting         : NLS
                               ForceWCHAR             : F
                               FailoverDelay          : 10
                               FailoverRetryCount     : 10
                               MetadataIdDefault      : F
                               BindAsFLOAT            : F
                               BindAsDATE             : F
                               CloseCursor            : F
                               EXECSchemaOpt          :
                               EXECSyntax             : F
                               Application Attributes : T
                               QueryTimeout           : T
                               CacheBufferSize        : 20
                               StatementCache         : F
                               ResultSets             : T
                               MaxLargeData           : 0
                               UseOCIDescribeAny      : F
                               Failover               : T
                               Lobs                   : T
                               DisableMTS             : T
                               DisableDPM             : F
                               BatchAutocommitMode    : IfAllSuccessful
                               Description            : Oracle ODBC
                               ServerName             : xe
                               Password               : 
                               UserID                 : c##student
                               DSN                    : Oracle

Then, I used this PowerShell command to set the Password property:

Set-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Oracle -Name "Password" -Value 'student'

After setting the Password property’s value, I queried it with the following PowerShell command:

Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Oracle | Select-Object -Property "Password"

It returns:

Password : student

After manually setting the Oracle ODBC DSN’s password value you can now connect without providing a password at runtime. It also means anybody who hacks the Windows environment can access the password through trivial PowerShell command.

I hope this alerts readers to a potential security risk when you use Oracle DSNs.

PL/SQL List Function

without comments

Students wanted to see how to write PL/SQL functions that accept, process, and return lists of values. I thought it would be cool to also demonstrate coupling of loop behaviors and wrote the example using the 12-Days of Christmas lyrics.

The twelve_days function accepts two different collections. One is an Attribute Data Type (ADT) and the other a User-Defined Type (UDT). An ADT is based on a scalar data type, and a UDT is based on an object type. Object types are basically data structures, and they support both positional and named notation for variable assignments.

The twelve_days function returns a list of string, which is an ADT of the VARCHAR2 data type. Creating the ADT types is easy and a single step, like:

/* Create a days object type. */
CREATE OR REPLACE
  TYPE days IS TABLE OF VARCHAR2(8);
/
 
/* Create a string object type. */
CREATE OR REPLACE
  TYPE song IS TABLE OF VARCHAR2(36);
/

Creating the UDT is more complex and requires two steps. You need to create the UDT object type, or structure, and then the list based on the UDT object type, like:

/* Create a lyric object type. */
CREATE OR REPLACE
  TYPE lyric IS OBJECT
  ( DAY   VARCHAR2(8)
  , gift  VARCHAR2(24));
/
 
/* Create a lyrics object type. */
CREATE OR REPLACE
  TYPE lyrics IS TABLE OF LYRIC;
/

Now, you can create the twelve_days function that uses these ADT and UDT types, like:

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
CREATE OR REPLACE
  FUNCTION twelve_days
  ( pv_days   DAYS
  , pv_gifts  LYRICS ) RETURN song IS
 
  /* Initialize the collection of lyrics. */
  lv_retval  SONG := song();
 
  /* Local procedure to add to the song. */
  PROCEDURE ADD
  ( pv_input  VARCHAR2 ) IS
  BEGIN
    lv_retval.EXTEND;
    lv_retval(lv_retval.COUNT) := pv_input;
  END ADD;
 
BEGIN
  /* Read forward through the days. */
  FOR i IN 1..pv_days.COUNT LOOP
    ADD('On the ' || pv_days(i) || ' day of Christmas');
    ADD('my true love sent to me:');
 
    /* Read backward through the lyrics based on the ascending value of the day. */
    FOR j IN REVERSE 1..i LOOP
      IF i = 1 THEN
        ADD('-'||'A'||' '||pv_gifts(j).gift);
      ELSE
        ADD('-'||pv_gifts(j).DAY||' '||pv_gifts(j).gift);
      END IF;
    END LOOP;
 
    /* A line break by verse. */
    ADD(CHR(13));
  END LOOP;
 
  /* Return the song's lyrics. */
  RETURN lv_retval;
END;
/

You may notice the local add procedure on lines 10 thru 15. It lets you perform the two tasks required for populating an element in a SQL object type list in one line in the main body of the twelve_days function.

The add procedure first uses the EXTEND function to allocate space before assigning the input value to the newly allocated element in the list. Next, you can call the function inside the following SQL query:

SELECT column_value AS "12-Days of Christmas"
FROM   TABLE(twelve_days(days('first','second','third','fourth'
                             ,'fifth','sixth','seventh','eighth'
                             ,'nineth','tenth','eleventh','twelfth')
                        ,lyrics(lyric(DAY => 'and a', gift => 'Partridge in a pear tree')
                               ,lyric(DAY => 'Two',   gift => 'Turtle doves')
                               ,lyric(DAY => 'Three', gift => 'French hens')
                               ,lyric(DAY => 'Four',  gift => 'Calling birds')
                               ,lyric(DAY => 'Five',  gift => 'Golden rings' )
                               ,lyric(DAY => 'Six',   gift => 'Geese a laying')
                               ,lyric(DAY => 'Seven', gift => 'Swans a swimming')
                               ,lyric(DAY => 'Eight', gift => 'Maids a milking')
                               ,lyric(DAY => 'Nine',  gift => 'Ladies dancing')
                               ,lyric(DAY => 'Ten',   gift => 'Lords a leaping')
                               ,lyric(DAY => 'Eleven',gift => 'Pipers piping')
                               ,lyric(DAY => 'Twelve',gift => 'Drummers drumming'))));

It will print:

12-Days of Christmas
------------------------------------
On the first day of Christmas
my true love sent to me:
-A Partridge in a pear tree
 
On the second day of Christmas
my true love sent to me:
-Two Turtle doves
-and a Partridge in a pear tree
 
On the third day of Christmas
my true love sent to me:
-Three French hens
-Two Turtle doves
-and a Partridge in a pear tree
 
... redacted for space ...
 
On the twelfth day of Christmas
my true love sent to me:
-Twelve Drummers drumming
-Eleven Pipers piping
-Ten Lords a leaping
-Nine Ladies dancing
-Eight Maids a milking
-Seven Swans a swimming
-Six Geese a laying
-Five Golden rings
-Four Calling birds
-Three French hens
-Two Turtle doves
-and a Partridge in a pear tree

As always, I hope the example helps those looking for a solution to this type of problem.

Written by maclochlainn

May 13th, 2022 at 12:57 am

Magic WITH Clause

without comments

Magic WITH Clause

Learning Outcomes

  • Learn how to use the WITH clause.
  • Learn how to join the results of two WITH clauses.

Lesson Materials

The idea of modularity is important in every programming environment. SQL is no different than other programming languages in that regard. SQL-92 introduced the ability to save queries as views. Views are effectively modular views of data.

A view is a named query that is stored inside the data dictionary. The contents of the view change as the data in the tables that are part of the view changes.

SQL:1999 added the WITH clause, which defines statement scoped views. Statement scoped views are named queries, or queries named as views, only in the scope of a query where they are defined.

The simplest prototype for a WITH clause that contains a statement scoped view is:

WITH query_name
[(column1, column2, ...)] AS
 (SELECT column1, column2, ...)
  SELECT column1, column2, ...
  FROM   table_name tn INNER JOIN query_name qn
  ON     tn.column_name = qn.column_name 
  WHERE  qn.column_name = 'Some literal';

You should note that the list of columns after the query name is an optional list. The list of columns must match the SELECT-list, which is the set of comma delimited columns of the SELECT clause.

A more complete prototype for a WITH clause shows you how it can contain two or more statement scoped views. That prototype is:

WITH query_name
[(column1, column2, ...)] AS
 (SELECT column1, column2, ...)
, query_name2
[(column1, column2, ...)] AS
 (SELECT column1, column2, ...)
SELECT column1, column2, ...
FROM   table_name tn INNER JOIN query_name1 qn1
ON     tn.column_name = qn1.column_name INNER JOIN query_name2 qn2
ON     qn1.column_name = qn2.column_name;
WHERE  qn1.column_name = 'Some literal';

The WITH clause has several advantages over embedded view in the FROM clause or subqueries in various parts of a query or SQL statement. The largest advantage is that a WITH clause is a named subquery and you can reference it from multiple locations in a query; whereas, embedded subqueries are unnamed blocks of code and often results in replicating a single subquery in multiple locations.

A small model of three tables lets you test a WITH clause in the scope of a query. It creates a war, country, and ace tables. The tables are defined as:

WAR

Name                             NULL?    TYPE
-------------------------------- -------- ----------------
WAR_ID                                    NUMBER
WAR_NAME                                  VARCHAR2(30)

COUNTRY

Name                             NULL?    TYPE
-------------------------------- -------- ----------------
COUNTRY_ID                                NUMBER
COUNTRY_NAME                              VARCHAR2(20)

ACE

Name                             NULL?    TYPE
-------------------------------- -------- ----------------
ACE_ID                                    NUMBER
ACE_NAME                                  VARCHAR2(30)
COUNTRY_ID                                NUMBER
WAR_ID                                    NUMBER

The following WITH clause includes two statement scoped views. One statement scoped view queries results form a single table while the other queries results from a join between the country and ace tables.

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
CLEAR COLUMNS
CLEAR BREAKS
 
BREAK ON REPORT
BREAK ON war_name SKIP PAGE
 
COL ace_id        FORMAT 9999 HEADING "Ace|ID #"
COL ace_name      FORMAT A24  HEADING "Ace Name"
COL war_name      FORMAT A12  HEADING "War Name"
COL country_name  FORMAT A14  HEADING "Country Name"
WITH wars (war_id, war_name) AS
 (SELECT w.war_id, war_name
  FROM   war w )
, aces (ace_id, ace_name, country_name, war_id) AS
 (SELECT   a.ace_id
  ,        a.ace_name
  ,        c.country_name
  ,        a.war_id
  FROM     ace a INNER JOIN country c
  ON       a.country_id = c.country_id)
SELECT   a.ace_id
,        a.ace_name
,        w.war_name
,        a.country_name
FROM     aces a INNER JOIN wars w
ON       a.war_id = w.war_id
ORDER BY war_name
,        CASE
           WHEN REGEXP_INSTR(ace_name,' ',1,2,1) > 0 THEN
             SUBSTR(ace_name,REGEXP_INSTR(ace_name,' ',1,2,1),LENGTH(ace_name) - REGEXP_INSTR(ace_name,' ',1,2,0))
           WHEN REGEXP_INSTR(ace_name,' ',1,1,1) > 0 THEN
             SUBSTR(ace_name,REGEXP_INSTR(ace_name,' ',1,1,1),LENGTH(ace_name))
         END;

wars is the first statement scoped view of the war table. aces is the second statement scoped view of the inner join between the ace and country tables. You should note that aces statement scoped view has access to the wars scoped view, and the master SELECT statement has scope access to both statement scoped views and any tables in its schema.

The query returns the following with the help of SQL*Plus formatting BREAK statements:

  Ace
 ID # Ace Name		       War Name     Country Name
----- ------------------------ ------------ --------------
 1009 William Terry Badham     World War I  America
 1003 Albert Ball			    United Kingdom
 1010 Charles John Biddle		    America
 1005 William Bishop			    Canada
 1007 Keith Caldwell			    New Zealand
 1006 Georges Guynemer			    France
 1008 Robert Alexander Little		    Austrailia
 1001 Manfred von Richtofen		    Germany
 1002 Eddie Rickenbacker		    America
 1004 Werner Voss			    Germany
 
  Ace
 ID # Ace Name		       War Name     Country Name
----- ------------------------ ------------ --------------
 1018 Richard Bong	       World War II America
 1015 Edward F Charles			    Canada
 1020 Heinrich Ehrler			    Germany
 1019 Ilmari Juutilainen		    Finland
 1014 Ivan Kozhedub			    Soviet Union
 1012 Thomas McGuire			    America
 1013 Pat Pattle			    United Kingdom
 1011 Erich Rudorffer			    Germany
 1016 Stanislaw Skalski 		    Poland
 1017 Teresio Vittorio			    Italy
 
20 rows selected.

The WITH clause is the most effective solution when you have a result set that needs to be consistently used in two or more places in a master query. That’s because the result set becomes a named statement scoped view.

Script Code

Click the Script Code link to open the test case seeding script inside the current webpage.

Written by maclochlainn

May 12th, 2022 at 7:01 pm

What’s up on M1 Chip?

with one comment

I’ve been trying to sort out what’s up on Oracle’s support of Apple’s M1 (arm64) chip. It really is a niche area. It only applies to those of us who work on a macOS machines with Oracle Database technology; and then only when we want to install a version of Oracle Database 21c or newer in Windows OS for testing. Since bootcamp is now gone, these are only virtualized solutions through a full virtualization product or containerized with Docker of Kubernetes.

The Best Virtual Machine Software for Mac 2022 (4/11/2022) article lets us know that only Parallels fully supports Windows virtualization on the ARM64 chip. Then, there’s the restriction that you must use Monterey or Big Sur (macOS) and Windows 11 arm64.

Instructions were published on On how to run Windows 11 on an Apple M1 a couple weeks ago. They use the free UTM App from the Apple Store and provide the download site for the Windows Insider Program. You can’t get a copy of Windows 11 arm64 without becoming part of the Windows Insider Program.

The next step would be to try and install Oracle Database 21c on Windows 11 arm64, which may or may not work. At least, I haven’t tested it yet and can’t make the promise that it works. After all, I doubt it will work because the Oracle Database 21c documentation says it only supports x64 (or Intel) architecture.

If anybody knows what Oracle has decided, will decide or even their current thinking on the issue, please make a comment.

Written by maclochlainn

May 1st, 2022 at 11:56 pm

Oracle ODBC DSN

without comments

As I move forward with trying to build an easy to use framework for data analysts who use multiple database backends and work on Windows OS, here’s a complete script that lets you run any query stored in a file to return a CSV file. It makes the assumption that you opted to put the user ID and password in the Windows ODBC DSN, and only provides the ODBC DSN name to make the connection to the ODBC library and database.

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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
# A local function for verbose reporting.
function Get-Message ($param, $value = $null) {
  if (!($value)) {
    Write-Host "Evaluate swtich    [" $param "]" } 	  
  else {
    Write-Host "Evaluate parameter [" $param "] and [" $value "]" } 
}
 
# Read SQLStatement file and minimally parse it.
function Get-SQLStatement ($sqlStatement) {
  # Set localvariable for return string value.
  $statement = ""
 
  # Read a file line-by-line.
  foreach ($line in Get-Content $sqlStatement) {
    # Use regular expression to replace multiple whitespace.
    $line = $line -replace '\s+', ' '
 
    # Add a whitespace to avoid joining keywords from different lines;
    # and remove trailing semicolons which are unneeded.
    if (!($line.endswith(";"))) {
      $statement += $line + " " }
    else {
      $statement += $line.trimend(";") }
  }
  # Returned minimally parsed statement.
  return $statement
}
 
# Set default type of SQL statement value to a query.
$stmt = "select"
 
# Set a variable to hold a SQL statement from a file.
$query = ""
 
# Set default values for SQL input and output files.
$outFile = "output.csv"
$sqlFile = "query.sql"
 
# Set default path to: %USERPROFILE%\AppData\Local\Temp folder, but ir 
# the tilde (~) in lieu of the %USERPROFILE% environment variable value.
$path = "~\AppData\Local\Temp"
 
# Set a verbose switch.
$verbose = $false
 
# Wrap the Parameter call to avoid a type casting warning.
try {
  param (
    [Parameter(Mandatory)][hashtable]$args
  )
}
catch {}
 
# Check for switches and parameters with arguments.
for ($i = 0; $i -lt $args.count; $i += 1) {
  if (($args[$i].startswith("-")) -and ($args[$i + 1].startswith("-"))) {
    if ($args[$i] = "-v") {
      $verbose = $true }
      # Print to verbose console.
    if ($verbose) { Get-Message $args[$i] }}
  elseif ($args[$i].startswith("-")) {
    # Print to verbose console.
    if ($verbose) { Get-Message $args[$i] $args[$i + 1] }
 
    # Evaluate and take action on parameters and values.
    if ($args[$i] -eq "-o") {
      $outfile = $args[$i + 1] }
    elseif ($args[$i] -eq "-q") {
      $sqlFile = $args[$i + 1] }
    elseif ($args[$i] -eq "-p") {
      $path = $args[$i + 1] }
  }
}
 
# Set a PowerShell Virtual Drive.
New-PSDrive -Name folder -PSProvider FileSystem -Description 'Forder Location' `
            -Root $path | Out-Null
 
# Remove the file only when it exists.
if (Test-Path folder:$outFile) {
  Remove-Item -Path folder:$outFile }
 
# Read SQL file into minimally parsed string.
if (Test-Path folder:$sqlFile) {
  $query = Get-SQLStatement $sqlFile }
 
# Set a ODBC DSN connection string.
$ConnectionString = 'DSN=OracleGeneric'
 
# Set an Oracle Command Object for a query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = $query;
 
  # Attempt to read SQL command.
  try {
    $row = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($row.Read()) {
      # Initialize output for each row.
      $output = ""
 
      # Navigate across all columns (only two in this example).
      for ($column = 0; $column -lt $row.FieldCount; $column += 1) {
        # Mechanic for comma-delimit between last and first name.  
        if ($output.length -eq 0) { 
          $output += $row[$column] }
        else {
          $output += ", " + $row[$column] }
      }
      # Write the output from the database to a file.
      Add-Content -Value $output -Path folder:$outFile
    }
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $row.Close() }
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

You can use a command-line call like this:

powershell ./OracleContact.ps1 -v -o output.csv -q script.sql -p .

It produces the following verbose output to the console:

Evaluate swtich    [ -v ]
Evaluate parameter [ -o ] and [ output.csv ]
Evaluate parameter [ -q ] and [ script.sql ]
Evaluate parameter [ -p ] and [ . ]

You can suppress printing to the console by eliminating the -v switch from the parameter list.

As always, I hope this helps those looking for a solution to less tedious interactions with the Oracle database.