Archive for May, 2022
Oracle DSN Security
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/pgSQL Coupled Loops
I love a challenge. A loyal Oracle PL/SQL developer said PL/pgSQL couldn’t support coupled loops and user-defined lists. Part true and part false. It’s true PL/pgSQL couldn’t support user-defined lists because it supports arrays. It’s false because PL/pgSQL supports an ARRAY_APPEND function that lets you manage arrays like Java’s ArrayList class.
Anyway, without further ado. You only need to create one data type because PL/pgSQL supports natural array syntax, like Java, C#, and other languages and doesn’t adhere rigidly to the Information Definition Language (IDL) standard that Oracle imposes. Oracle requires creating an Attribute Data Type (ADT) for the string collections, which you can avoid in PL/pgSQL.
You do need to create a record structure type, like:
/* Create a lyric object type. */ CREATE TYPE lyric AS ( day VARCHAR(8) , gift VARCHAR(24)); |
You can build a function to accept an array of strings and an array of record structures that returns a new array constructed from parts of the two input arrays. The function also compares and matches the two arrays before returning an array that combines strings for a songs lyrics. While the example uses the ever boring 12 Days of Christmas, I’d love another for examples. It just needs to use this type of repetitive structure. If you have one that you would like to share let me know.
The twelve_days function is:
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 | CREATE FUNCTION twelve_days ( IN pv_days VARCHAR(8)[] , IN pv_gifts LYRIC[] ) RETURNS VARCHAR[] AS $$ DECLARE /* Initialize the collection of lyrics. */ lv_retval VARCHAR(36)[114]; BEGIN /* Read forward through the days. */ FOR i IN 1..ARRAY_LENGTH(pv_days,1) LOOP lv_retval := ARRAY_APPEND(lv_retval,('On the ' || pv_days[i] || ' day of Christmas')::text); lv_retval := ARRAY_APPEND(lv_retval,('my true love sent to me:')::text); /* Read backward through the lyrics based on the ascending value of the day. */ FOR j IN REVERSE i..1 LOOP IF i = 1 THEN lv_retval := ARRAY_APPEND(lv_retval,('-'||'A'||' '|| pv_gifts[j].gift)::text); ELSIF j <= i THEN lv_retval := ARRAY_APPEND(lv_retval,('-'|| pv_gifts[j].day ||' '|| pv_gifts[j].gift )::text); END IF; END LOOP; /* A line break by verse. */ lv_retval := ARRAY_APPEND(lv_retval,' '::text); END LOOP; /* Return the song's lyrics. */ RETURN lv_retval; END; $$ LANGUAGE plpgsql; |
Then, you can test it with this query:
SELECT UNNEST(twelve_days(ARRAY['first','second','third','fourth' ,'fifth','sixth','seventh','eighth' ,'nineth','tenth','eleventh','twelfth'] ,ARRAY[('and a','Partridge in a pear tree')::lyric ,('Two','Turtle doves')::lyric ,('Three','French hens')::lyric ,('Four','Calling birds')::lyric ,('Five','Golden rings')::lyric ,('Six','Geese a laying')::lyric ,('Seven','Swans a swimming')::lyric ,('Eight','Maids a milking')::lyric ,('Nine','Ladies dancing')::lyric ,('Ten','Lords a leaping')::lyric ,('Eleven','Pipers piping')::lyric ,('Twelve','Drummers drumming')::lyric])) AS "12-Days of Christmas"; |
It prints:
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 |
So, I believe that I met the challenge and hopefully provided a concrete example of some syntax that seems to be missing from most of the typical places.
PL/SQL List Function
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.
MySQL Windows DSN
Almost a Ripley’s Believe It or Not. An prior data science student told me that his new IT department setup a Windows component that let him connect his Excel Spreadsheets to their production MySQL database without a password. Intrigued, I asked if it was a MySQL Connector/ODBC Data Source Configuration, or DSN (Data Source Name)?
He wasn’t sure, so I asked him to connect to PowerShell and run the following command:
Get-Item -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MySQL |
It returned something like this (substituting output from one of my test systems):
Hive: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
Name Property
---- --------
MySQL Driver : C:\Program Files\MySQL\Connector ODBC 8.0\myodbc8w.dll
DESCRIPTION : MySQL ODBC Connector
SERVER : localhost
UID : student
PWD : student
DATABASE : studentdb
PORT : 3306 |
The student was stunned and concerned he was compromising his employer’s system security. I suggested he share the information with his IT department so they could provide a different approach for his access to the production database. His IT department immediately agreed. Unfortunately, he’s bummed he can’t simply access the data through Excel.
I told him they were welcome to use the MySQL Connect Dialog PowerShell solution that I wrote. It creates a minimal MySQL DSN and requires a manual password entry through the PowerShell Dialog box. I also suggested that they look into the PowerShell Excel Module.
I also suggested they develop a query only copy of the production database, or shift access to a data warehouse. Needless to say, it wasn’t a large corporation.
As always, I hope this helps others.
Magic WITH Clause
Magic WITH Clause
Learning Outcomes
- Learn how to use the
WITHclause. - Learn how to join the results of two
WITHclauses.
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.
Script Code →
The following script includes tables that will not conflict with other lab assignments. It creates the war, ace, and country tables; war_s, ace_s, and country_s sequences; and the WITH enabled query from this blog page.
DROP TABLE war; CREATE TABLE war ( war_id NUMBER , war_name VARCHAR2(30)); DROP SEQUENCE war_s; CREATE SEQUENCE war_s START WITH 1001; DROP TABLE ace; CREATE TABLE ace ( ace_id NUMBER , ace_name VARCHAR2(30) , country_id NUMBER , war_id NUMBER); DROP SEQUENCE ace_s; CREATE SEQUENCE ace_s START WITH 1001; DROP TABLE country; CREATE TABLE country ( country_id NUMBER , country_name VARCHAR2(20)); DROP SEQUENCE country_s; CREATE SEQUENCE country_s START WITH 1001; /* || Insert two rows for wars. */ INSERT INTO war ( war_id , war_name ) VALUES ( war_s.NEXTVAL ,'World War I'); INSERT INTO war ( war_id , war_name ) VALUES ( war_s.NEXTVAL ,'World War II'); /* || Insert ten rows for countries. */ INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'Germany'); INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'Italy'); INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'Poland'); INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'United Kingdom'); INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'France'); INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'Finland'); INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'Soviet Union'); INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'America'); INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'Austrailia'); INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'New Zealand'); INSERT INTO country ( country_id , country_name ) VALUES ( country_s.NEXTVAL ,'Canada'); /* || Insert twenty rows for aerial combat aces. */ INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Manfred von Richtofen' ,(SELECT country_id FROM country WHERE country_name = 'Germany') ,(SELECT war_id FROM war WHERE war_name = 'World War I')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Eddie Rickenbacker' ,(SELECT country_id FROM country WHERE country_name = 'America') ,(SELECT war_id FROM war WHERE war_name = 'World War I')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Albert Ball' ,(SELECT country_id FROM country WHERE country_name = 'United Kingdom') ,(SELECT war_id FROM war WHERE war_name = 'World War I')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Werner Voss' ,(SELECT country_id FROM country WHERE country_name = 'Germany') ,(SELECT war_id FROM war WHERE war_name = 'World War I')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'William Bishop' ,(SELECT country_id FROM country WHERE country_name = 'Canada') ,(SELECT war_id FROM war WHERE war_name = 'World War I')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Georges Guynemer' ,(SELECT country_id FROM country WHERE country_name = 'France') ,(SELECT war_id FROM war WHERE war_name = 'World War I')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Keith Caldwell' ,(SELECT country_id FROM country WHERE country_name = 'New Zealand') ,(SELECT war_id FROM war WHERE war_name = 'World War I')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Robert Alexander Little' ,(SELECT country_id FROM country WHERE country_name = 'Austrailia') ,(SELECT war_id FROM war WHERE war_name = 'World War I')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'William Terry Badham' ,(SELECT country_id FROM country WHERE country_name = 'America') ,(SELECT war_id FROM war WHERE war_name = 'World War I')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Charles John Biddle' ,(SELECT country_id FROM country WHERE country_name = 'America') ,(SELECT war_id FROM war WHERE war_name = 'World War I')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Erich Rudorffer' ,(SELECT country_id FROM country WHERE country_name = 'Germany') ,(SELECT war_id FROM war WHERE war_name = 'World War II')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Thomas McGuire' ,(SELECT country_id FROM country WHERE country_name = 'America') ,(SELECT war_id FROM war WHERE war_name = 'World War II')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Pat Pattle' ,(SELECT country_id FROM country WHERE country_name = 'United Kingdom') ,(SELECT war_id FROM war WHERE war_name = 'World War II')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Ivan Kozhedub' ,(SELECT country_id FROM country WHERE country_name = 'Soviet Union') ,(SELECT war_id FROM war WHERE war_name = 'World War II')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Edward F Charles' ,(SELECT country_id FROM country WHERE country_name = 'Canada') ,(SELECT war_id FROM war WHERE war_name = 'World War II')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Stanislaw Skalski' ,(SELECT country_id FROM country WHERE country_name = 'Poland') ,(SELECT war_id FROM war WHERE war_name = 'World War II')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Teresio Vittorio' ,(SELECT country_id FROM country WHERE country_name = 'Italy') ,(SELECT war_id FROM war WHERE war_name = 'World War II')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Richard Bong' ,(SELECT country_id FROM country WHERE country_name = 'America') ,(SELECT war_id FROM war WHERE war_name = 'World War II')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Ilmari Juutilainen' ,(SELECT country_id FROM country WHERE country_name = 'Finland') ,(SELECT war_id FROM war WHERE war_name = 'World War II')); INSERT INTO ace ( ace_id , ace_name , country_id , war_id ) VALUES ( ace_s.NEXTVAL ,'Heinrich Ehrler' ,(SELECT country_id FROM country WHERE country_name = 'Germany') ,(SELECT war_id FROM war WHERE war_name = 'World War II')); /* || Commit the records. */ COMMIT; /* || Query from statement scoped views. */ 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; |
You can use the base query as a starting place to experiment with the WITH clause in a query.
Fedora for macOS ARM64
I’m always updating VMs, and I was gratified to notice that there’s a Fedora arm64 ISO. If you’re interested in it, you can download the Live Workstation from here or the Fedora Server from here.
Unfortunately, I only have macOS running on i7 and i9 Intel Processors. It would be great to hear back how it goes for somebody one of the new Apple M1 chip.
I typically install the workstation version because it meets my needs to run MySQL and other native Linux development tools. However, the server version is also available. Fedora is a wonderful option, as a small footprint for testing things on my MacBookPro.
Java Gregorian Date
One of my students asked for an example of how to work with a Gregorian date and timezones in Java. I dug out an old example file from when I taught Java at Regis University.
The code follows:
/* || Program name: MyGregorian.java || Created by: Michael McLaughlin || Creation date: 10/07/02 || History: || ---------------------------------------------------------------------- || Date Author Purpose || -------- ---------------------- --------------------------------- || dd/mm/yy {Name} {Brief statement of change.} || ---------------------------------------------------------------------- || Execution method: Static class demonstrating timezone setting. || Program purpose: Designed as a stand alone program. */ // Class imports. import java.util.Calendar; import java.util.GregorianCalendar; import java.util.TimeZone; // Define MyGregorian class. public class MyGregorian { // Testing static main() method. public static void main(String args[]) { // Set an initial variable. String initial = ""; System.out.println("======================================================"); System.out.println("Value of [user.timezone]: [" + (initial = (null != System.getProperty("user.timezone")) ? "Unset" : System.getProperty("user.timezone")) + "]"); System.out.println("======================================================"); GregorianCalendar gc = (GregorianCalendar) Calendar.getInstance(); System.out.println("Calendar Date: [" + gc.getTime() + "]"); gc.add(GregorianCalendar.MONTH,1); System.out.println("Calendar Date: [" + gc.getTime() + "]"); System.out.println("======================================================"); System.out.println("Value of [user.timezone]: [" + System.getProperty("user.timezone") + "]"); System.out.println("Value of [user.timezone]: [" + System.setProperty("user.timezone","") + "]"); gc.add(GregorianCalendar.MONTH,1); System.out.println("Calendar Date: [" + gc.getTime() + "]"); System.out.println("======================================================"); Calendar c = Calendar.getInstance(); System.out.println("Calendar Date: [" + c.getTime() + "]"); // Move the date ahead one month, hour and minute. c.add(Calendar.MONTH,1); System.out.println("Calendar Date: [" + c.getTime() + "]"); System.out.println("======================================================"); } // End of testing static main() method. } // End of MyGregorian class. |
It prints to console:
====================================================== Value of [user.timezone]: [Unset] ====================================================== Calendar Date: [Thu May 05 23:43:42 MDT 2022] Calendar Date: [Sun Jun 05 23:43:42 MDT 2022] ====================================================== Value of [user.timezone]: [America/Denver] Value of [user.timezone]: [America/Denver] Calendar Date: [Tue Jul 05 23:43:42 MDT 2022] ====================================================== Calendar Date: [Thu May 05 23:43:42 MDT 2022] Calendar Date: [Sun Jun 05 23:43:42 MDT 2022] ====================================================== |
As always, I hope this helps those who need to see and example to work with Gregorian dates.
Bash Debug Function
My students working in Linux would have a series of labs to negotiate and I’d have them log the activities of their Oracle SQL scripts. Many of them would suffer quite a bit because they didn’t know how to find the errors in the log files.
I wrote this SQL function for them to put in their .bashrc files. It searches all the .txt files for errors and organizes them by log file, line number, and descriptive error message.
errors () { label="File Name:Line Number:Error Code"; list=`ls ./*.$1 | wc -l`; if [[ ${list} -eq 1 ]]; then echo ${label}; echo "----------------------------------------"; filename=`ls *.txt`; echo ${filename}:`find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\-`; else if [[ ${list} -gt 1 ]]; then echo ${label}; echo "----------------------------------------"; find . -type f | grep --color=auto -in *.txt -e ora\- -e pls\- -e sp2\-; fi; fi } |
I hope it helps others now too.
What’s up on M1 Chip?
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.
Subscribe via RSS