Archive for the ‘DevOps’ Category
Oracle Partitioned Tables
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') , ... ) |
Logging Table Function
It is interesting when somebody remembers a presentation from 10 years ago. They asked if it was possible in PL/pgSQL to write an autonomous procedure to log data when calling a table view function. The answer is two fold. PL/pgSQL doesn’t support autonomous functions or procedures like the Oracle database but it doesn’t need to because unless you invoke a transaction it auto commits writes.
Logging table functions are important for security auditing and compliance management against laws, like SOX, HIPAA, and FERPA. All too many systems lack the basic ability to audit who queries records without raising an error and blocking the access. That means the bad actor or actress gains the ability to probe the system for weaknesses before determining an attack vector. It’s often better to capture the unauthorized access and take direct action to protect both the the data and systems.
While the example lets an unauthorized person access the information in the first version of the student_query, it blocks access by reporting no rows returned in the latter. Both versions of the query log the data and thereby collect the evidence necessary to act against the hack.
This blog post shows you how to write it and test it. Follow the following steps:
- Create the necessary tables and data to work with a logging PL/pgSQL table view function:
/* Conditionally drop and create table. */ DROP TABLE IF EXISTS student; CREATE TABLE student ( student_id SERIAL , first_name VARCHAR(20) , last_name VARCHAR(20) , hogwarts_house VARCHAR(10)); /* Conditionally drop and create table. */ DROP TABLE IF EXISTS logger; CREATE TABLE logger ( logger_id SERIAL , app_user VARCHAR(30) , queried_student VARCHAR(30) , query_time TIMESTAMP ); /* Insert one record into table. */ INSERT INTO student ( first_name, last_name, hogwarts_house ) VALUES ( 'Harry', 'Potter', 'Gryffindor' ) ,( 'Hermione', 'Granger', 'Gryffindor' ) ,( 'Ronald', 'Weasily', 'Gryffindor' ) ,( 'Draco', 'Malfoy', 'Slytherin' ) ,( 'Vincent', 'Crabbe', 'Slytherin' ) ,( 'Susan', 'Bones', 'Hufflepuff' ) ,( 'Hannah', 'Abbott', 'Hufflepuff' ) ,( 'Luna', 'Lovegood', 'Ravenclaw' ) ,( 'Cho', 'Chang', 'Ravenclaw' ) ,( 'Gilderoy', 'Lockhart', 'Ravenclaw' );
- While not necessary if you’re very familiar with PL/pgSQL, it may be helpful to review:
- The SET command that lets you assign a value to a session-level variable, which you can later use in a PL/pgSQL block.
- The SELECT-INTO statement in a DO-block.
Here’s a test script that demonstrates both:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
/* Set a session-level variable. */ SET credential.app_user = 'Draco Malfoy'; /* Secure the value from a session-level variable. */ SELECT current_setting('credential.app_user'); /* DO $$ DECLARE input VARCHAR(30) := 'Hermione'; output VARCHAR(30); BEGIN /* Sample for partial name construction of full name. */ SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name INTO output FROM student s WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||input||'%'; /* Show result of local assignment via a query. */ RAISE NOTICE '[%][%]', current_setting('credential.app_user'), output; END; $$;
There’s an important parsing trick to this sample program. It uses the LIKE operator rather than the SIMILAR TO operator because the parser fails to recognize the SIMILAR TO operator.
The DO-block returns the following output:
NOTICE: [Draco Malfoy][Hermione Granger]
- This creates the student_query logging table function, which takes a partial portion of a students first and last name to return the student information. While the example only returns the name and the Hogwarts House it lays a foundation for a more complete solution.
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
CREATE OR REPLACE FUNCTION student_query (partial_name VARCHAR) RETURNS TABLE ( first_naem VARCHAR(20) , last_name VARCHAR(20) , hogwarts_house VARCHAR(10) ) AS $$ DECLARE queried VARCHAR; by_whome VARCHAR; BEGIN /* Query separately because embedding in insert statement fails. */ SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name FROM student s INTO queried WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%'; /* Log the query with the credentials of the user. */ INSERT INTO logger ( app_user , queried_student , query_time ) VALUES ( current_setting('credential.app_user') , queried , NOW()); /* Return the result set without disclosing the query was recorded. */ RETURN QUERY SELECT s.first_name , s.last_name , s.hogwarts_house FROM student s WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%'; END; $$ LANGUAGE plpgsql;
- You can test the function by calling it, like this:
SELECT * FROM student_query('Hermione');
It displays:
first_naem | last_name | hogwarts_house ------------+-----------+---------------- Hermione | Granger | Gryffindor (1 row)
You can check the logging table and discover who looked up another student’s records.
SELECT * FROM logger;
It displays:
logger_id | app_user | queried_student | query_time -----------+--------------+------------------+---------------------------- 1 | Draco Malfoy | Hermione Granger | 2022-05-29 22:51:50.398987 (1 row) - Assuming you’ve built an authorized_user function that returns a Boolean, you can add a call to it in the WHERE clause. For simplicity, let’s implement the function to deny all users, like:
1 2 3 4 5 6 7 8 9 10
CREATE OR REPLACE FUNCTION authorized_user (user_name VARCHAR) RETURNS BOOLEAN AS $$ DECLARE lv_retval BOOLEAN := FALSE; BEGIN RETURN lv_retval; END; $$ LANGUAGE plpgsql;
You can now replace the query on lines 28 through 32 with the new one below. The added clause on line 33 denies access to unauthorized users because there aren’t any.
28 29 30 31 32 33
SELECT s.first_name , s.last_name , s.hogwarts_house FROM student s WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%' AND authorized_user(current_setting('credential.app_user'));
While it returns:
first_naem | last_name | hogwarts_house ------------+-----------+---------------- (0 rows)
The logger table shows two entries. One for the query that returned a value and one for the version that didn’t.
logger_id | app_user | queried_student | query_time -----------+--------------+------------------+---------------------------- 1 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:39.82063 2 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:40.736945 (2 rows)In both cases the bad actor Draco Malfoy’s unauthorized access is captured and he was denied any information without alerting him to the security precaution in a logging table function.
As always, I hope this helps those looking for this type of solution.
PostgreSQL Table Function
A quick tutorial on how to write a PL/pgSQL Table function. The functions is simple. It returns the list of conquistadors that were originally German. It does that by filtering on the lang column in the table. For example, you use ‘de‘ for German.
I’ll stage this with the same conquistador table used in the last post. Don’t forget to use the chcp command to the Active Console Code Page to 4-byte Unicode before you run the script file, like:
chcp 65001 |
Then, connect to the psql shell and run the following script file:
/* Conditionally drop the conquistador table. */ DROP TABLE IF EXISTS conquistador; /* Create the conquistador table. */ CREATE TABLE conquistador ( conquistador_id SERIAL , conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30) , lang VARCHAR(2)); /* Insert some conquistadors into the table. */ INSERT INTO conquistador ( conquistador , actual_name , nationality , lang ) VALUES ('Juan de Fuca','Ioánnis Fokás','Greek','el') ,('Nicolás de Federmán','Nikolaus Federmann','German','de') ,('Sebastián Caboto','Sebastiano Caboto','Venetian','it') ,('Jorge de la Espira','Georg von Speyer','German','de') ,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it') ,('Wenceslao Linck','Wenceslaus Linck','Bohemian','cs') ,('Fernando Consag','Ferdinand Konšcak','Croatian','sr') ,('Américo Vespucio','Amerigo Vespucci','Italian','it') ,('Alejo García','Aleixo Garcia','Portuguese','pt'); |
Now, you can build another script file to create the getConquistador function, like:
/* Drop the funciton conditionally. */ DROP FUNCTION IF EXISTS getConquistador; |
Create the getConquistador function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE FUNCTION getConquistador (IN lang_in VARCHAR(2)) RETURNS TABLE ( conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30)) AS $$ BEGIN RETURN QUERY SELECT c.conquistador , c.actual_name , c.nationality FROM conquistador c WHERE c.lang = lang_in; END; $$ LANGUAGE plpgsql; |
Then, you can test it like:
SELECT * FROM getConquistador('de'); |
It will return the following:
conquistador | actual_name | nationality -----------------------+--------------------+------------- Nicolás de Federmán | Nikolaus Federmann | German Jorge de la Espira | Georg von Speyer | German (2 rows) |
As always, I hope this helps with a technique that’s useful.
PL/SQL List to Struct
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:
- They handle lists of standard types in a difference C++ class than they do UDT types.
- They rigidly adhere to Interface Definition Language (IDL) principles.
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
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.
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.
Subscribe via RSS