Hidden thought SQL question
I’m always amazed at how people ask questions about skill sets. A few weeks ago, I ran into a neat Oracle SQL question that hinges on whether the individual truly understands One Phase Commit (1PC) and Two Phase Commit (2PC) operations.
It’s a simple question that tests two levels of understanding. The question is:
- If you create table A and insert a row of data and subsequently you create table B and insert a row of data before issuing a ROLLBACK; statement, how many rows of data will you find in table A and table B?
Level 1 Understanding
Here’s the test script:
CREATE TABLE a1 ( text VARCHAR2(12) ); INSERT INTO a1 ( text ) VALUES ('Hello World!'); CREATE TABLE b1 ( text VARCHAR2(12) ); INSERT INTO b1 ( text ) VALUES ('Hello World!'); ROLLBACK; |
The answer is 1 row in table A1 and no row in table B1 because the second CREATE statement issues an implicit COMMIT. However, the INSERT statement to table B1 is a 2PC and the ROLLBACK statement undoes the first phase of the INSERT statement and removes the data. If you were using a sequence value in the INSERT statement, the sequence value would be consumed because it’s not replaced by a ROLLBACK statement.
Level 2 Understanding
Here’s the test script:
CREATE TABLE a2 AS (SELECT 'Hello World!' AS text); CREATE TABLE b2 AS (SELECT 'Hello World!' AS text); ROLLBACK; |
The answer is 1 row in table A2 and 1 row in table B2 because a CREATE statement using the AS clause subquery issues an implicit COMMIT on the data inserted from the subquery because its a 1PC transaction.
Most likely and interviewer would be looking for level one understanding but you can demonstrate mastery by sharing level two understanding. As always, I hope this helps those reading it.
Go Config & PostgreSQL
Finally, I got around to building a GO environment with PostgreSQL on Ubuntu. Next, I’ll need to sort out how to extend it to Oracle and MySQL. The only tedious part was figuring out where the current PostgreSQL ODBC driver was on GitHub.
The installation of GO has two options. You can install the version from the Linux Distro’s repo, or you can install the most current version from the GO website. The generic install on Ubuntu uses the following two commands:
sudo apt update sudo apt install -y golang-go |
This will install GO in the /usr/bin directory, which means you don’t need to add it manually to your $PATH environment variable in your .bashrc file.
Download from the GO website into your /home/username/Downloads directory and then run this command:
sudo tar -C /usr/local -xzf /home/student/Downloads/go1.24.0.linux-amd64.tar.gz |
If you haven’t installed the distro’s repo of the GO programming environment, you should add this to your .bashrc file:
# Added to PATH for Go export set PATH=$PATH:/usr/local/go/bin |
However, add the following if you have installed the Linux distorts GO repo and prefer to use the most current release available (a which -a go command displays the go program files in your current $PATH variable and you can call the first one in the list without an absolute or fully qualified file name but the second requires a fully qualified file name):
# Added to PATH for Go export set PATH=/usr/local/go/bin:$PATH |
Now, you should decide where you want to put your GO programs. I chose to create a base .go directory and then project directories beneath that. Within each project directory you should create bin, pkg, and src subdirectories, as shown below:
mkdir .go .go/hello .go/hello/bin .go/hello/pkg .go/hello/src |
You can write a “Hello World” program like the following in you ~.go/hello/src directory, which I’ve named hello-world.go for illustrative purposes:
// Declare package. package main // Import library. import "fmt" // Define main function using fmt library to print. func main() { fmt.Println("Hello World!") } |
Next, you should create a makefile, like this in the same ~.go/hello/src directory. If you’re new to the rules for creating a makefile, there can be no leading or trailing white spaces, and you should use before commands:
.DEFAULT_GOAL := build .PHONY: fmt vet build fmt: go fmt ./... vet: fmt go vet ./... build: vet go build -o ../bin/hello hello-world.go |
Before you run the make utility, you need to initialize the module with this syntax:
go mod init |
It will create the go.mod file. You will see something like the following when you display the content of the go.mod file.
module hello-world.go go 1.24.0 |
You have completed your project preparation. You run the make command to create the hello executable in the ~/.go/hello/bin directory.
Run the make utility:
make |
You can run it from your current directory with this command:
~/.go/hello/bin/hello |
It will return to console:
Hello World! |
Having built the GO development environment and a rudimentary starting program, it’s time to learn how to write a GO program that queries the PostgreSQL database. The first step is finding the correct PostgreSQL library in GitHub.com. A number of web sites post examples using the pq library but that’s no longer correct.
You need to download the pgx ODBC driver with the following GO command:
go get github.com/jackc/pgx@latest |
It will display the following to the console:
go: downloading github.com/jackc/pgx v3.6.2+incompatible go: downloading github.com/pkg/errors v0.9.1 go: downloading golang.org/x/crypto v0.35.0 go: downloading golang.org/x/text v0.22.0 go: added github.com/jackc/pgx v3.6.2+incompatible go: added github.com/pkg/errors v0.9.1 go: added golang.org/x/crypto v0.35.0 go: added golang.org/x/text v0.22.0 |
To use the standard database/sql approach you’ll also need to run this command:
go get github.com/jackc/pgx/v5/stdlib |
It will display the following to the console:
go: downloading github.com/jackc/pgx/v5 v5.7.2 go: downloading github.com/jackc/pgpassfile v1.0.0 go: downloading github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 go: downloading github.com/jackc/puddle/v2 v2.2.2 go: downloading golang.org/x/sync v0.11.0 go: added github.com/jackc/pgpassfile v1.0.0 go: added github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 go: added github.com/jackc/pgx/v5 v5.7.2 go: added github.com/jackc/puddle/v2 v2.2.2 |
Change back to your home directory and run this command to build a new connect project:
mkdir .go/postgres .go/postgres/connect .go/postgres/connect/bin .go/postgres/connect/pkg .go/postgres/connect/src |
Create postgres-connect.go program in the .go/postgres/connect/src directory:
package main import ( "database/sql" "fmt" "os" _ "github.com/jackc/pgx/v5/stdlib" ) func main() { // Open connection and defer connection close. db, err := sql.Open("pgx", "host=localhost user=student password=student dbname=videodb sslmode=disable") if err != nil { fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err) os.Exit(1) } defer db.Close() // Declare an output variable for the query. var version string err = db.QueryRow("SELECT version()").Scan(&version) if err != nil { fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err) os.Exit(1) } // Print database version. fmt.Println(version) } |
Next, you should create another makefile in the same ~.go/postgres/connect/src directory.
.DEFAULT_GOAL := build .PHONY: fmt vet build fmt: go fmt ./... vet: fmt go vet ./... build: vet go build -o ../bin/connect postgres-connect.go |
Before you run the make utility, you need to initialize the go.mod module file and check for dependencies in it. That requires the following two commands:
go mod init |
Which returns the following list of dependencies:
go: finding module for package github.com/jackc/pgx/v5/stdlib go: found github.com/jackc/pgx/v5/stdlib in github.com/jackc/pgx/v5 v5.7.2 |
There are two ways to fix this but the easiest is to run this command:
go mod tidy |
It adds the dependencies to the go.mod file. You will see something like the following when you display the content of the go.mod file.
module postgres-connect.go go 1.24.0 require github.com/jackc/pgx/v5 v5.7.2 require ( github.com/jackc/pgpassfile v1.0.0 // indirect github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect github.com/jackc/puddle/v2 v2.2.2 // indirect golang.org/x/crypto v0.31.0 // indirect golang.org/x/sync v0.10.0 // indirect golang.org/x/text v0.21.0 // indirect ) |
You should also note that the go mod tidy command created new go.sum file for the dependencies, which should look like the following:
github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c= github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM= github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg= github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 h1:iCEnooe7UlwOQYpKFhBabPMi4aNAfoODPEFNiAnClxo= github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM= github.com/jackc/pgx/v5 v5.7.2 h1:mLoDLV6sonKlvjIEsV56SkWNCnuNv531l94GaIzO+XI= github.com/jackc/pgx/v5 v5.7.2/go.mod h1:ncY89UGWxg82EykZUwSpUKEfccBGGYq1xjrOpsbsfGQ= github.com/jackc/puddle/v2 v2.2.2 h1:PR8nw+E/1w0GLuRFSmiioY6UooMp6KJv0/61nB7icHo= github.com/jackc/puddle/v2 v2.2.2/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4= github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM= github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4= github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME= github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI= github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg= github.com/stretchr/testify v1.8.1 h1:w7B6lhMri9wdJUVmEZPGGhZzrYTPvgJArz7wNPgYKsk= github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4= golang.org/x/crypto v0.31.0 h1:ihbySMvVjLAeSH1IbfcRTkD/iNscyz8rGzjF/E5hV6U= golang.org/x/crypto v0.31.0/go.mod h1:kDsLvtWBEx7MV9tJOj9bnXsPbxwJQ6csT/x4KIN4Ssk= golang.org/x/sync v0.10.0 h1:3NQrjDixjgGwUOCaF8w2+VYHv0Ve/vGYSbdkTa98gmQ= golang.org/x/sync v0.10.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk= golang.org/x/text v0.21.0 h1:zyQAAkrwaneQ066sspRyJaG9VNi/YJ1NfzcGB3hZ/qo= golang.org/x/text v0.21.0/go.mod h1:4IBbMaMmOPCJ8SecivzSH54+73PCFmPWxNTLm+vZkEQ= gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0= gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA= gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= |
Run the make utility:
make |
You can now run make to create the connect executable, which you can call with the following command:
~/.go/postgres/connect/bin/connect |
It will return to console:
PostgreSQL 14.17 (Ubuntu 14.17-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit |
Adding polars to Python3
Just a quick note to correct the Polars instruction, you should install using pip3 with the following command:
pip3 install polars |
You’ll get something like the following:
Defaulting to user installation because normal site-packages is not writeable
Collecting polars
Downloading polars-1.21.0-cp39-abi3-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (31.6 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 31.6/31.6 MB 11.5 MB/s eta 0:00:00
Installing collected packages: polars
Successfully installed polars-1.21.0 |
While I always add a python link to alias python3, that’s not possible with the pip3 utility because there is also a pip utility in the /usr/bin directory.
As always, I hope this helps somebody with the task at hand.
Using Python’s getopt
A couple of my students wanted me to write a switch and parameter handler for Python scripts. I wrote it just to show them it’s possible but I also show them how to do it correctly with the Python getopt library, which was soft-deprecated in Python 3.13 and replaced by the Python argparse library. The debate is which one I show you first in the blog.
This is the getops.py script that uses Python’s getopt library. There is a small trick to the options and long options values. You append a colon (:) to the option when it has a value, and append an equal (=) to the long option when it has a value.
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 | #!/usr/bin/python # Import libraries. import getopt, sys import mysql.connector from mysql.connector import errorcode # Define local function. def help(): # Declare display string. display = \ """ Program Help +---------------+-------------+-------------------+ | -h --help | | Help switch. | | -o --output | output_file | Output file name. | | -q --query | query_file | Query file name. | | -v --verbose | | Verbose switch. | +---------------+-------------+-------------------+""" # Return string. return display # ============================================================ # Set local variables for switch and parameter placeholders. # ============================================================ display = False log = [] output_file = '' query_file = '' verbose = False opts = "ho:q:v" long_opts = ["help","output=","query=","verbose"] # ============================================================ # Capture argument list minus the program name. # ============================================================ args = sys.argv[1:] # ============================================================ # Use a try-except block. # ============================================================ try: # Assign the results of the getopt function. params, values = getopt.getopt(args, opts, long_opts) # Loop through the parameters. for curr_param, curr_value in params: if curr_param in ("-h","--help"): print(help()) elif curr_param in ("-o","--output"): output_file = curr_value elif curr_param in ("-q","--query"): query_file = curr_value elif curr_param in ("-v","--verbose"): verbose = True # Append entry to log. log.append('[' + curr_param + '][' + curr_value + ']') # Print verbose parameter handling. if verbose: print(" Parameter Diagnostics\n-------------------------") for i in log: print(i) # Exception block. except getopt.GetoptError as e: # output error, and return with an error code print (str(e)) |
You can run the program in Linux or Unix with the following syntax provided that you’ve already set the parameters to 755. That means granting the file owner with read, write, and execute privileges, and group and other with read and execute privileges.
./getopts.py -h -o output.txt -q query.sql -v |
It would return the following:
Program Help +---------------+-------------+-------------------+ | -h --help | | Help switch. | | -o --output | output_file | Output file name. | | -q --query | query_file | Query file name. | | -v --verbose | | Verbose switch. | +---------------+-------------+-------------------+ Parameter Diagnostics ------------------------- [-h][] [-o][output.txt] [-q][query.sql] [-v][] |
If you didn’t notice, I also took the opportunity to write the help display in such a way that a maintenance programmer could add another switch or parameter easily. This way the programmer only needs to add a new row of text and add an elif statement with the new switch or parameter.
I think using Python’s getopt library is the cleanest and simplest way to implement switch and parameter handling, after all it’s the basis for so many C derived libraries. However, if you must write your own, below is an approach that would work:
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 | #!/usr/bin/python # Import libraries. import sys import mysql.connector from mysql.connector import errorcode # ============================================================ # Set local variables for switch and parameter placeholders. # ============================================================ help = False display = \ """ Program Help +---------------+-------------+-------------------+ | -h --help | | Help switch. | | -o --output | output_file | Output file name. | | -q --query | query_file | Query file name. | | -v --verbose | | Verbose switch. | +---------------+-------------+-------------------+""" log = [] output = '' query = '' verbose = False # ============================================================ # Capture argument list minus the program name. # ============================================================ args = sys.argv[1:] # ============================================================ # If one or more args exists and the first one is an # a string that can cast to an int, convert it to an int, # assign it to a variable, and ignore any other args # in the list. # ============================================================ if len(args) > 1 and args[0].isdigit(): powerIn = int(args[0]) # Check for switches and parameters. if isinstance(args,list) and len(args) >= 1: # Set the limit of switches and parameters. argc = len(args) # Enumerate through switches first and then parameters. for i in range(argc): if args[i][0] == '-': # Evaluate switches and ignore any parameter value. if args[i] in ['-h','--help']: help = True # Append entry to log. log.append('[' + str(args[i]) + ']') elif args[i] in ['-v','--verbose']: verbose = True # Append entry to log. log.append('[' + str(args[i]) + ']') # Evaluate parameters. elif i < argc and not args[i+1][0] == '-': if args[i] in ['-q','--query']: query = args[i+1] elif args[i] in ['-o','--output']: output = args[i+1] # Append entry to log. log.append('[' + str(args[i]) + '][' + args[i+1] + ']') else: continue continue # Print the help display when if help: print(display) # Print the parameter handling collected in the log variable. if verbose: for i in log: print(i) |
As you can see from the example, I didn’t give it too much effort. I think it should prove you should use the approach adopted by the general Python community.
SQL Calculations #1
This was principally written for my SQL students but I thought it might be useful to others. SQL calculation are performed row-by-row in the SELECT-list. In its simplest form without even touching a table, you can add two literal numbers like this:
1 | SELECT 2 + 2 AS result; |
It will display the result of the addition to the column alias result as a derived table, or the following result:
+--------+ | result | +--------+ | 4 | +--------+ 1 row in set (0.00 sec) |
Unfortunately, the use of literal values as shown above doesn’t really let you see how the calculation is made row-by-row because it only returns one row. You can rewrite the two literal values into one variable by using a Common Table Expressions (CTEs). The CTE creates an struct tuple with only one x element. Another way to describe what the CTE does would say, it creates a derived table named struct with a single x column in the SELECT-list.
The CTE runs first, then a subsequent query may use the CTE’s derived table results. Below is a query that uses the value in the struct.x derived table (or references the struct tuple’s x element) twice while assigning the value to a new column alias, labelled result. The FROM clause places the struct tuple in the queries namespace, which lets you reference it in the SELECT-list.
1 2 3 4 | WITH struct AS (SELECT 2 AS x) SELECT struct.x + struct.x AS result FROM struct; |
Like the literal example, it will display the result of the addition to the column alias result as a derived table of one row:
+--------+ | result | +--------+ | 4 | +--------+ 1 row in set (0.00 sec) |
Having laid a basis for a simple calculation in one row, let’s expand the example and demonstrate how to perform row-by-row calculations. The example requires introducing some new concepts. One uses the UNION ALL set operator to fabricate a CTE derived table with three rows. Another uses a comma within the WITH clause to create two derived tables or CTEs. The last uses the CROSS JOIN to add the single row CTE’s single y column to each of the rows returned by the multiple row CTE.
The CROSS JOIN is a Cartesian product, which multiplies the rows in one table against the rows in another table while adding the columns from each table. That means fabricating a table of one column and one row lets you put a variable into all the rows of another table or set of tables combined through an equijoin or non-equijoin operation.
The query below takes a struct1 derived table of one column and three rows and a struct2 derived table of one column and one row, then uses a CROSS JOIN to create a new derived table, which would be a table of two columns and three rows. The Cartesian product only provides the two columns that we will multiply to create new data.
The SELECT-list lets us fabricate a new column where we multiply the values of column x and column y to create a set of new results in column result.
1 2 3 4 5 6 7 8 9 10 | WITH struct1 AS (SELECT 1 AS x UNION ALL SELECT 2 AS x UNION ALL SELECT 3 AS x) , struct2 AS (SELECT 10 AS y) SELECT struct1.x AS x , struct2.y AS y , struct1.x * struct2.y AS result FROM struct1 CROSS JOIN struct2; |
The query returns the following results, which show the values used to calculate the result and the result:
+---+----+--------+ | x | y | result | +---+----+--------+ | 1 | 10 | 10 | | 2 | 10 | 20 | | 3 | 10 | 30 | +---+----+--------+ 3 rows in set (0.00 sec) |
As a rule, the columns x and y would not be displayed in the final derived table. You would only see the result columns’ values.
Let’s use an example from Alan Bwaulieu’s Learning SQL book with a twist. Rather than manually fabricating the ordinal numbers twice, let’s use the scope reference of a subsequent CTE to reference an earlier CTE. That would create two ten row tables of one column each, or a Cartesian product of a 100 row table with two columns. Then, let’s use the SELECT-list lets us fabricate only a new column, which will add 1 to the numbers 0 to 99 to give us the numbers 1 to a 100.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | WITH ones AS (SELECT 0 AS x UNION ALL SELECT 1 AS x UNION ALL SELECT 2 AS x UNION ALL SELECT 3 AS x UNION ALL SELECT 4 AS x UNION ALL SELECT 5 AS x UNION ALL SELECT 6 AS x UNION ALL SELECT 7 AS x UNION ALL SELECT 8 AS x UNION ALL SELECT 9 AS x ) , tens AS (SELECT x * 10 AS x FROM ones) SELECT ones.x + tens.x + 1 AS ordinal FROM ones CROSS JOIN tens ORDER BY ordinal; |
It returns the following result set:
+---------+ | ordinal | +---------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | ... | 98 | | 99 | | 100 | +---------+ 100 rows in set (0.00 sec) |
Moving on to more complex math, let’s create a numerals table with the result from our prior query. It will enable calculating the factors of exponents. The easiest way to create the table is shown below (only caveat is that it will build it with a biting rather than an int data type).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE numerals AS WITH ones AS (SELECT 0 AS x UNION ALL SELECT 1 AS x UNION ALL SELECT 2 AS x UNION ALL SELECT 3 AS x UNION ALL SELECT 4 AS x UNION ALL SELECT 5 AS x UNION ALL SELECT 6 AS x UNION ALL SELECT 7 AS x UNION ALL SELECT 8 AS x UNION ALL SELECT 9 AS x ) , tens AS (SELECT x * 10 AS x FROM ones) SELECT ones.x + tens.x + 1 AS ordinal FROM ones CROSS JOIN tens ORDER BY ordinal; |
Line #15 sets the column alias that determines the name of the column for the numerals table. It can be described after running the foregoing script in MySQL as:
+---------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------+------+-----+---------+-------+ | ordinal | bigint | NO | | 0 | | +---------+--------+------+-----+---------+-------+ 1 row in set (0.00 sec) |
The next query accepts a substitution variable into the WITH clause, which means an external program will call it. (Although, you could use a session level variable, which I would discourage.) This query returns the factors for any given exponent:
1 2 3 4 5 6 7 8 9 | WITH magic AS (SELECT %s AS vkey) SELECT CONCAT(magic.vkey,'^',LOG(magic.vkey,n.ordinal)) AS powers , n.ordinal AS result FROM numerals n CROSS JOIN magic WHERE MOD(n.ordinal,magic.vkey) = 0 AND LOG(magic.vkey,n.ordinal) REGEXP '^[0-9]*$' OR n.ordinal = 1 ORDER BY n.ordinal; |
FYI, in the WHERE clause the regular expression is guarantees only rows returning integer values, and the 1 returns the identity property of an exponent raised to the zero power.
Assuming you created the numerals table, put the foregoing query in a query.sql file (because I was to lazy to write the full parameter handling), and you run it in the same directory as this Python program, it’ll take any valid integer as a value.
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 | #!/usr/bin/python # ------------------------------------------------------------ # Name: power.py # Date: 19 Oct 2024 # ------------------------------------------------------------ # Purpose: # ------- # The program shows you how to provide a single agrument # to a query and print the formatted output. # # You can call the program: # # ./power.py 3 # # ------------------------------------------------------------ # Import libraries. import sys import mysql.connector from mysql.connector import errorcode # ============================================================ # Define a local padding function. # ============================================================ def pad(valueIn): # Define local variable. padding = '' # Convert single digit numbers to strings. if isinstance(valueIn,int) and len(str(valueIn)) == 1: padding = ' ' # Return padding space. return padding # ============================================================ # End local function defintion. # ============================================================ # Define any local variables. powerIn = 2 query = "" # ============================================================ # Capture argument list minus the program name. # ============================================================ arguments = sys.argv[1:] # ============================================================ # If one or more arguments exists and the first one is an # a string that can cast to an int, convert it to an int, # assign it to a variable, and ignore any other arguments # in the list. # ============================================================ if len(arguments) >= 1 and arguments[0].isdigit(): powerIn = int(arguments[0]) # ============================================================ # Use a try-catch block to read and parse a query from a # a file found in the same local directory as the Python # program. # ============================================================ try: file = open('query.sql','r') query = file.read().replace('\n',' ').replace(';','') file.close() except IOError: print("Could not read file:", fileName) # ============================================================ # Attempt connection in a try-catch block. # ============================================================ # -------------------------------------------------------- # Open connection, bind variable in query and format # query output before closing the cursor. # -------------------------------------------------------- try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Create cursor. cursor = cnx.cursor() # Execute cursor, and coerce string to tuple. cursor.execute(query, (powerIn,)) # Display the rows returned by the query. for (powers, result) in cursor: print((" {} is: {}").format(powers, pad(result) + str(result))) # Close cursor. cursor.close() # -------------------------------------------------------- # Handle MySQL exception # -------------------------------------------------------- except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # -------------------------------------------------------- # Close connection after try-catch completes. # -------------------------------------------------------- # Close the connection when the try block completes. else: cnx.close() |
If you forget to call it with a numeric parameter, it uses 2 as the default. You would call it as follows from a Linux prompt from the local directory:
./power.py |
It returns:
2^0 is: 1 2^1 is: 2 2^2 is: 4 2^3 is: 8 2^4 is: 16 2^5 is: 32 2^6 is: 64 |
If you call it with a numeric parameter, it uses the numeric value. You would call it as follows from a Linux prompt from the local directory:
./power.py 3 |
It returns:
3^0 is: 1 3^1 is: 3 3^2 is: 9 3^3 is: 27 3^4 is: 81 |
As always, I hope the post helps folks sort out how and why things work.
SQL Statement Management
It’s very difficult explaining to students new to relational databases how SQL works. There are many parts that seem intuitive and then there are others that confuse and confound.
For beginners, the idea that a SQL statement is simply a text string that you must dispatch to a SQL statement processing engine is new. That’s because they use an Integrated Development Environment (IDE) that hides, or abstracts the complexity, of how SQL executes.
I start my core SQL class by demonstrating how to run a text literal query without a FROM clause in MySQL Workbench, like this:
SELECT 'Hello World!' AS "Output"; |
After writing the query, I highlight everything except the semicolon and click the lightening bolt that dispatches the static string to the SQL statement engine. They see this result:
Then, I launch a mysql Monitor session and write the query with a semicolon to dispatch the SQL static string to the SQL statement engine:
SELECT 'Hello World!' AS "Output"; |
and, with a \g to dispatch the SQL static string to the SQL statement engine:
SELECT 'Hello World!' AS "Output"\g |
Both queries return the same output, as shown below:
+--------------+ | output | +--------------+ | Hello World! | +--------------+ 1 row in set (0.00 sec)
Rewriting the query with a \G to dispatch the SQL static string to the SQL statement engine:
SELECT 'Hello World!' AS "Output"\G |
Both queries return the following output:
*************************** 1. row *************************** output: Hello World! 1 row in set (0.00 sec)
The next step requires removing the MySQL Workbench and MySQL Monitor from the demonstration. Without either of those tools, a Python program can demonstrate how to run a static SQL string.
The query is now a string literal into a query.sql file. The Python program reads the query.sql file, dispatches the embedded query, and displays the query results.
This is the query.sql file is:
SELECT 'Hello World!' AS "output"; |
This is the query.py file is:
#!/usr/bin/python # Import libraries. import sys import mysql.connector from mysql.connector import errorcode # ============================================================ # Use a try-catch block to read and parse a query from a # a file found in the same local directory as the Python # program. # ============================================================ try: file = open('query.sql','r') query = file.read().replace('\n',' ').replace(';','') file.close() except IOError: print("Could not read file:", fileName) # ============================================================ # Attempt connection in a try-catch block. # ============================================================ # -------------------------------------------------------- # Open connection, bind variable in query and format # query output before closing the cursor. # -------------------------------------------------------- try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Create cursor. cursor = cnx.cursor() # Execute cursor, and coerce string to tuple. cursor.execute(query) # Display the rows returned by the query. for row in cursor: print(row[0]) # Close cursor. cursor.close() # -------------------------------------------------------- # Handle MySQL exception # -------------------------------------------------------- except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # -------------------------------------------------------- # Close connection after try-catch completes. # -------------------------------------------------------- # Close the connection when the try block completes. else: cnx.close() |
In Linux or Unix from the relative directory where both the query.sql and query.py files are located:
./query.py |
It returns:
Hello World!
These examples demonstrate that a query without variable substitution is only a static string. In all the cases, the static SQL strings are dispatched to the SQL engine by a terminator like a semicolon or through an ODBC library call that executes the static SQL string.
Troubleshoot Oracle Errors
It’s always a bit difficult to trap errors in SQL*Developer when you’re running scripts that do multiple things. As old as it is, using the SQL*Plus utility and spooling to log files is generally the fastest way to localize errors across multiple elements of scripts. Unfortunately, you must break up you components into local components, like a when you create a type, procedure, function, or package.
This is part of my solution to leverage in-depth testing of the Oracle Database 23ai Free container from an Ubuntu native platform. You can find this prior post shows you how to setup Oracle*Client for Ubuntu and connect to the Oracle Database 23ai Free container.
After you’ve done that, put the following oracle_errors Bash shell function into your testing context, or into your .bashrc file:
# Troubleshooting errors utility function. oracle_errors () { # Oracle Error prefixes qualify groups of error types, like # this subset of error prefixes used in the Bash function. # ============================================================ # JMS - Java Messaging Errors # JZN - JSON Errors # KUP - External Table Access Errors # LGI - File I/O Errors # OCI - Oracle Call Interface Errors # ORA - Oracle Database Errors # PCC - Oracle Precompiler Errors # PLS - Oracle PL/SQL Errors # PLW - Oracle PL/SQL Warnings # SP2 - Oracle SQL*Plus Errors # SQL - SQL Library Errors # TNS - SQL*Net (networking) Errors # ============================================================ # Define a array of Oracle error prefixes. prefixes=("jms" "jzn" "kup" "lgi" "oci" "ora" "pcc" "pls" "plw" "sp2" "sql" "tns") # Prepend the -e for the grep utility to use regular expression pattern matching; and # use the ^before the Oracle error prefixes to avoid returning lines that may # contain the prefix in a comment, like the word lookup contains the prefix kup. for str in ${prefixes[@]}; do patterns+=" -e ^${str}" done # Display output from a SQL*Plus show errors command written to a log file when # a procedure, function, object type, or package body fails to compile. This # prints the warning message followed by the line number displayed. patterns+=" -e ^warning" patterns+=" -e ^[0-9]/[0-9]" # Assign any file filter to the ext variable. ext=${1} # Assign the extension or simply use a wildcard for all files. if [ ! -z ${ext} ]; then ext="*.${ext}" else ext="*" fi # Assign the number of qualifying files to a variable. fileNum=$(ls -l ${ext} 2>/dev/null | grep -v ^l | wc -l) # Evaluate the number of qualifying files and process. if [ ${fileNum} -eq "0" ]; then echo "[0] files exist." elif [ ${fileNum} -eq "1" ]; then fileName=$(ls ${ext}) find `pwd` -type f | grep -in ${ext} ${patterns} | while IFS='\n' read list; do echo "${fileName}:${list}" done else find `pwd` -type f | grep -in ${ext} ${patterns} | while IFS='\n' read list; do echo "${list}" done fi # Clear ${patterns} variable. patterns="" } |
Now, let’s create a debug.txt test file to demonstrate how to use the oracle_errors, like:
ORA-12704: character SET mismatch PLS-00124: name OF EXCEPTION expected FOR FIRST arg IN exception_init PRAGMA SP2-00200: Environment error JMS-00402: Class NOT found JZN-00001: END OF input |
You can navigate to your logging directory and call the oracle_errors function, like:
oracle_errors txt |
It’ll return the following, which is file number, line number, and error code:
debug.txt:1:ORA-12704: character set mismatch debug.txt:2:PLS-00124: name of exception expected for first arg in exception_init pragma debug.txt:3:SP2-00200: Environment error debug.txt:4:JMS-00402: Class not found debug.txt:5:JZN-00001: End of input |
There are other Oracle error prefixes but the ones I’ve selected are the more common errors for Java, JavaScript, PL/SQL, Python, and SQL testing. You can add others if your use cases require them to the prefixes array. Just a note for those new to Bash shell scripting the “${variable_name}” is required for arrays.
For a more complete example, I created the following files for a trivial example of procedure overloading in PL/SQL:
- tables.sql – that creates two tables.
- spec.sql – that creates a package specification.
- body.sql – that implements a package specification.
- test.sql – that implements a test case using the package.
- integration.sql – that calls the the scripts in proper order.
The tables.sql, spec.sql, body.sql, and test.sql use the SQL*Plus spool command to write log files, like:
SPOOL spec.txt
-- Insert code here ...
SPOOL OFF |
The body.sql file includes SQL*Plus list and show errors commands, like:
SPOOL spec.txt
-- Insert code here ...
LIST
SHOW ERRORS
SPOOL OFF |
The integration.sql script calls the tables.sql, spec.sql, body.sql, and test.sql in order. Corrupting the spec.sql file by adding a stray “x” to one of the parameter names causes a cascade of errors. After running the integration.sql file with the introduced error, the Bash oracle_errors function returns:
body.txt:2:Warning: Package Body created with compilation errors. body.txt:148:4/13 PLS-00323: subprogram or cursor 'WARNER_BROTHER' is declared in a test.txt:4:ORA-06550: line 2, column 3: test.txt:5:PLS-00306: wrong number or types of arguments in call to 'WARNER_BROTHER' test.txt:6:ORA-06550: line 2, column 3: |
I hope that helps those learning how to program and perform integration testing in an Oracle Database.
sqlplus on Ubuntu
With the release of Oracle Database 23c Free came the ability to update components of the container’s base operating system. Naturally, I took full advantage of that to build my development machine on an Ubuntu 22.0.4 VMware instance with a Docker implementation of the Oracle Database 23c Free container.
Unfortunately, there were changes from that release to the release of Oracle Database 23ai Free. Specifically, Oracle disallows direct patching of their published container’s native Unbreakable Linux 8. It appears the restriction lies in licensing but I haven’t been able to get a clear answer. Oracle’s instructions also shifted from using Docker to using Podman, which reduces the development platform to a limited type of Database as a Service (DaaS) environment. Moreover, that means it requires more skill to leverage the Oracle Database 23ai Free container as a real developer environment by installing and configuring Oracle’s Client software on the host Ubuntu operating system. Then, you must create a host of shared directories to the container to use external files or test external libraries.
While Oracle’s invocation of proprietary control of their native OS is annoying, it’s not nearly as onerous as Apple’s decision to not offer an Intel chip for their MacBook Pro machines. I’ve a hunch Oracle will grant access to their Oracle 23ai Free container in the future but for now this article shows you how to get native SQL*Plus access working.
As to Apple, while I’ve fixed my older machines by upgrading my Intel-based MacBook Pro (i7) to native Ubuntu, it still annoying. Yes, Tim Cooke, I’d rather run Ubuntu than sell back a wonderful piece of hardware on the cheap to Apple. I also did the same upgrade to my iMac 5K with 32 GB of RAM but swapped the cheap hybrid drive for a 2TB SSD.
Now to the technical content that lets you natively develop using Oracle’s SQL*Plus on Ubuntu against the Oracle Database 23ai Free container. While I love SQL*Developer, it has significant limits when testing large blocks of code. Whereas, good techniques, sqlplus, and Bash shell can simplify code development and integration testing.
Here are the steps to get sqlplus working on Ubuntu for your Oracle Database 23ai Free container:
- You need to download the following two zip files from the Oracle Instant Client Downloads for Linux x86-64 (64-bit) website, which assumes an Intel x86 Chip Architecture:
- Open a terminal as your default Ubuntu user and do the following to assume the root superuser responsibility:
sudo sh
As the root user, create the following directory for the Oracle Client software:
mkdir /opt/oracle/instantclient_23_4
As the root user, copy the previously downloaded files to the /opt/oracle directory (this assumes your default user is name as the student user:
cp ~student/Downloads/instantclient*.zip /opt/oracle/.
As the root user, change directory with the cd command to the /opt/oracle directory and verify with the ls -al command that you have the following two files:
total 120968 drwxr-xr-x 4 root root 4096 Jul 3 14:29 . drwxr-xr-x 6 root root 4096 Jul 3 09:09 .. drwxr-xr-x 4 root root 4096 Jul 3 10:11 instantclient_23_4 -rw-r--r-- 1 root root 118377607 Jul 3 14:29 instantclient-basic-linux.x64-23.4.0.24.05.zip -rw-r--r-- 1 root root 5471693 Jul 3 14:29 instantclient-sqlplus-linux.x64-23.4.0.24.05.zip
As the root user, unzip the two zip files in the following order with the unzip command:
unzip instantclient-basic-linux.x64-23.4.0.24.05.zipDisplay detailed console log →
Archive: instantclient-basic-linux.x64-23.4.0.24.05.zip inflating: META-INF/MANIFEST.MF inflating: META-INF/ORACLE_C.SF inflating: META-INF/ORACLE_C.RSA inflating: instantclient_23_4/adrci inflating: instantclient_23_4/BASIC_LICENSE inflating: instantclient_23_4/BASIC_README inflating: instantclient_23_4/fips.so inflating: instantclient_23_4/genezi inflating: instantclient_23_4/legacy.so linking: instantclient_23_4/libclntshcore.so -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.12.1 -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.18.1 -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.19.1 -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.20.1 -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.21.1 -> libclntshcore.so.23.1 linking: instantclient_23_4/libclntshcore.so.22.1 -> libclntshcore.so.23.1 inflating: instantclient_23_4/libclntshcore.so.23.1 linking: instantclient_23_4/libclntsh.so -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.10.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.11.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.12.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.18.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.19.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.20.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.21.1 -> libclntsh.so.23.1 linking: instantclient_23_4/libclntsh.so.22.1 -> libclntsh.so.23.1 inflating: instantclient_23_4/libclntsh.so.23.1 inflating: instantclient_23_4/libnnz.so linking: instantclient_23_4/libocci.so -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.10.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.11.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.12.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.18.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.19.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.20.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.21.1 -> libocci.so.23.1 linking: instantclient_23_4/libocci.so.22.1 -> libocci.so.23.1 inflating: instantclient_23_4/libocci.so.23.1 inflating: instantclient_23_4/libociei.so inflating: instantclient_23_4/libocijdbc23.so inflating: instantclient_23_4/libtfojdbc1.so creating: instantclient_23_4/network/ inflating: instantclient_23_4/ojdbc11.jar inflating: instantclient_23_4/ojdbc8.jar inflating: instantclient_23_4/pkcs11.so inflating: instantclient_23_4/ucp11.jar inflating: instantclient_23_4/ucp.jar inflating: instantclient_23_4/uidrvci inflating: instantclient_23_4/xstreams.jar creating: instantclient_23_4/network/admin/ inflating: instantclient_23_4/network/admin/README finishing deferred symbolic links: instantclient_23_4/libclntshcore.so -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.12.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.18.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.19.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.20.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.21.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntshcore.so.22.1 -> libclntshcore.so.23.1 instantclient_23_4/libclntsh.so -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.10.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.11.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.12.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.18.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.19.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.20.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.21.1 -> libclntsh.so.23.1 instantclient_23_4/libclntsh.so.22.1 -> libclntsh.so.23.1 instantclient_23_4/libocci.so -> libocci.so.23.1 instantclient_23_4/libocci.so.10.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.11.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.12.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.18.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.19.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.20.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.21.1 -> libocci.so.23.1 instantclient_23_4/libocci.so.22.1 -> libocci.so.23.1and, then
unzip instantclient-sqlplus-linux.x64-23.4.0.24.05.zipDisplay detailed console log →
Archive: instantclient-sqlplus-linux.x64-23.4.0.24.05.zip replace META-INF/MANIFEST.MF? [y]es, [n]o, [A]ll, [N]one, [r]ename: y inflating: META-INF/MANIFEST.MF replace META-INF/ORACLE_C.SF? [y]es, [n]o, [A]ll, [N]one, [r]ename: y inflating: META-INF/ORACLE_C.SF replace META-INF/ORACLE_C.RSA? [y]es, [n]o, [A]ll, [N]one, [r]ename: y inflating: META-INF/ORACLE_C.RSA inflating: instantclient_23_4/glogin.sql inflating: instantclient_23_4/libsqlplusic.so inflating: instantclient_23_4/libsqlplus.so inflating: instantclient_23_4/sqlplus inflating: instantclient_23_4/SQLPLUS_LICENSE inflating: instantclient_23_4/SQLPLUS_README
- As the root user, run these two commands:
sudo sh -c "echo /opt/oracle/instantclient_23_4 > \ /etc/ld.so.conf.d/oracle-instantclient.conf" sudo ldconfig
Next, you’ll test the installation. As the root user, run these three commands, which you’ll later add to your standard Ubuntu user’s .bashrc file:
export ORACLE_HOME=/opt/oracle/instantclient_23_4 export LD_LIBRARY_PATH=$ORACLE_HOME export PATH=$PATH:$ORACLE_HOME
As the root user, you can now test whether you can start the Oracle SQL*Plus client with the following command:
sqlplus /nologIt should connect and return this:
SQL*Plus: RELEASE 23.0.0.0.0 - Production ON Wed Jul 3 10:12:33 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. ALL rights reserved. SQL>
If you get this type of error, either you didn’t install the Oracle instant client basic libraries or you installed an incompatible version:
sqlplus: error while loading shared libraries: libclntsh.so.23.1: cannot open shared object file: No such file or directory
If you got the error, you’ll need to readdress the installation of the Oracle instant client typically.
Another type of error can occur if you get ahead of these instructions and try to connect to the Oracle Database 23ai Free container with syntax like this:
sql> connect c##student/student@free
because you’ll most likely get an error like this:
ERROR: ORA-12162: TNS:net service name is incorrectly specified Help: https://docs.oracle.com/error-help/db/ora-12162/
The error occurs because you haven’t setup the Oracle Net Services, which is level 5 in the OSI (Open System Interconnection) Model. In Oracle-speak, that means you haven’t setup a tnsnames.ora file, failed to put the tnsnames.ora file in the right place, or failed to set the $TNS_ADMIN environment variable correctly.
- While there are many ways to setup a tnsnames.ora file, the best way is to follow Oracle’s recommended approaches. In the Oracle client approach you should put the tnsnames.ora file in the $ORACLE_HOME/network/admin directory and use the $TNS_ADMIN environment variable to point to it. Unfortunately, that approach doesn’t work when you’re installing the Oracle client software unless you want to play with mount points. It’s easiest to create a hidden directory in your sandbox user, which is student in this example.
As the root user, use the mkdir command to create the .oracle directory in your student user directory:
mkdir /home/student/.oracle
As the student user, navigate to the /home/student/.oracle directory and create the tnsnames.ora file with the following text:
# tnsnames.ora Network Configuration FILE: FREE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE) ) ) LISTENER_FREE = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.0)(PORT = 1521)) FREEPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB1) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_FREE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )
Exit the root user to your student user. As the student user set the $TNS_ADMIN environment variable like:
export TNS_ADMIN=$HOME/.oracle
Assuming you’ve already created a container user, like c##student, connect to sqlplus with the following syntax:
sqlplus c##student/student@freeYou should see the following when connection to an Oracle 23c Container:
SQL*Plus: Release 23.0.0.0.0 - Production on Wed Jul 3 15:05:10 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Wed Jul 03 2024 10:52:13 -06:00 Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL>
You should see the following when connection to an Oracle 23ai Container:
SQL*Plus: Release 23.0.0.0.0 - Production on Sat Jul 20 11:05:08 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Sat Jul 20 2024 10:41:38 -06:00 Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL>
- The last step adds all of the configuration settings into the .bashrc file. Before we do that, you may want to add the rlwrap utility library so you can use the up-arrow to navigate the sqlplus history. You install it as the root or sudo user on Ubuntu, like
apt install -y rlwrap
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following packages were automatically installed and are no longer required: libwpe-1.0-1 libwpebackend-fdo-1.0-1 Use 'sudo apt autoremove' to remove them. The following NEW packages will be installed: rlwrap 0 upgraded, 1 newly installed, 0 to remove and 5 not upgraded. Need to get 98.2 kB of archives. After this operation, 309 kB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 rlwrap amd64 0.43-1build3 [98.2 kB] Fetched 98.2 kB in 1s (160 kB/s) Selecting previously unselected package rlwrap. (Reading database ... 211287 files and directories currently installed.) Preparing to unpack .../rlwrap_0.43-1build3_amd64.deb ... Unpacking rlwrap (0.43-1build3) ... Setting up rlwrap (0.43-1build3) ... update-alternatives: using /usr/bin/rlwrap to provide /usr/bin/readline-editor ( readline-editor) in auto mode Processing triggers for man-db (2.10.2-1) ...
If you want to manually check what you’re removing, use the following command as the root user:
apt autoremove
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following packages will be REMOVED: libwpe-1.0-1 libwpebackend-fdo-1.0-1 0 upgraded, 0 newly installed, 2 to remove and 5 not upgraded. After this operation, 182 kB disk space will be freed. Do you want to continue? [Y/n] Y (Reading database ... 211329 files and directories currently installed.) Removing libwpebackend-fdo-1.0-1:amd64 (1.14.2-0ubuntu0.22.04.1) ... Removing libwpe-1.0-1:amd64 (1.14.0-0ubuntu0.22.04.1) ... Processing triggers for libc-bin (2.35-0ubuntu3.8) ...
- The last step requires that you put the environment variables into the student user’s .bashrc shell script, and add a sqlplus function to take advantage of the new libraries added to read your prior history inside the SQL*Plus command line.
You should edit the .bashrc file and add the following environment variables and sqlplus() function:
# Configure Oracle Client software. export ORACLE_HOME=/opt/oracle/instantclient_23_4 export LD_LIBRARY_PATH=$ORACLE_HOME export PATH=$PATH:$ORACLE_HOME export TNS_ADMIN=$HOME/.oracle # A user-defined function to wrap the sqlplus history. sqlplus () { # Discover the fully qualified program name. path=`which rlwrap 2>/dev/null` file='' # Parse the program name from the path. if [ -n ${path} ]; then file=${path##/*/} fi; # Wrap when there is a file and it is rewrap. if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then rlwrap $ORACLE_HOME/sqlplus "${@}" else echo "Command-line history unavailable: Install the rlwrap package." $ORACLE_HOME/sqlplus "${@}" fi }
You should remember that when you access sqlplus from the Ubuntu environment the TNS net service name is required. If you should forget to include it like this:
sqlplus c##student/studentYou’ll get the following error:
ERROR: ORA-12162: TNS:net service name is incorrectly specified Help: https://docs.oracle.com/error-help/db/ora-12162/
The correct way is:
sqlplus c##student/student@free
As always, I hope this helps those looking for a solution.
Updating Nested ADTs
The first part of this series showed how you can leverage Oracle’s SQL syntax with UDT columns and collection columns. It would be nice if Oracle gave you some SQL to work with the elements of ADT collections, but they don’t. After all, that’s why you have this article.
While you could change the setup of the prior example table, it’s easier to create a new customer table. The new customer table drops the address column. There’s also a new pizza table. The pizza table includes an ingredient ADT collection column, which by design holds a unique set of ingredients for each pizza.
Realistically, ADT collections of numbers, characters, and dates have little value by themselves. That’s because those data types typically don’t have much meaning. A set of unique strings can be useful for certain use cases.
You create the list ADT type with this syntax:
SQL> CREATE OR REPLACE 2 TYPE list IS TABLE OF VARCHAR2(20); 3 / |
You create the customer and pizza tables, and customer_s and pizza_s sequences with the following syntax:
SQL> CREATE TABLE customer 2 ( customer_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , CONSTRAINT pk_customer PRIMARY KEY (customer_id)); SQL> CREATE SEQUENCE customer_s; SQL> CREATE TABLE pizza 2 ( pizza_id NUMBER 3 , customer_id NUMBER 4 , pizza_size VARCHAR2(10) 5 , ingredients LIST 6 , CONSTRAINT pk_pizza PRIMARY KEY (pizza_id) 7 , CONSTRAINT ck_pizza_size 8 CHECK (pizza_size IN ('Mini','Small','Medium','Large','Very Large'))) 9 NESTED TABLE ingredients STORE AS ingredient_table; SQL> CREATE SEQUENCE pizza_s; |
The customer table only has scalar columns. The pizza table has the ingredient ADT collection column. Line 9 creates a nested ingredient_table for the ingredient ADT collection column.
There is a primary and foreign key relationship between the customer and pizza tables. That relationship between the tables requires that you insert rows into the customer table before you insert rows into the pizza table.
The sample script populates the customer table with characters from the Green Arrow television show, as follows:
Customer
ID # Last Name First Name
-------- ---------- ----------
1 Queen Oliver
2 Queen Thea
3 Queen Moira
4 Lance Dinah
5 Lance Quentin
6 Diggle John
7 Wilson Slade |
Next, you can insert three rows into the pizza table. Each has different ingredients in the ingredient ADT column.
The following is the syntax for the INSERT statements:
SQL> INSERT INTO pizza 2 VALUES 3 ( pizza_s.NEXTVAL 4 ,(SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'Quentin' AND c.last_name = 'Lance') 6 ,'Large' 7 , list('Cheese','Marinara Sauce','Sausage','Salami')); SQL> INSERT INTO pizza 2 VALUES 3 ( pizza_s.NEXTVAL 4 ,(SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'Thea' AND c.last_name = 'Queen') 6 ,'Medium' 7 , list('Cheese','Marinara Sauce','Canadian Bacon','Pineapple')); SQL> INSERT INTO pizza 2 VALUES 3 ( pizza_s.NEXTVAL 4 ,(SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'John' AND c.last_name = 'Diggle') 6 ,'Small' 7 , list('Cheese','BBQ Sauce','Chicken')); |
Querying results from tables with nested ADT columns provides interesting results. An ordinary query, like this:
SQL> COL pizza_id FORMAT 99999 HEADING "Pizza|ID #" SQL> COL pizza_size FORMAT A6 HEADING "Pizza|Size" SQL> COL ingredients FORMAT A64 HEADING "Ingredients" SQL> SELECT pizza_id 2 , pizza_size 3 , ingredients 4 FROM pizza; |
… returns the following results with a flattened object type:
Pizza Pizza
ID # Size Ingredients
------ ------ ----------------------------------------------------------------“
1 Large LIST('Cheese', 'Marinara Sauce', 'Sausage', 'Salami')
2 Medium LIST('Cheese', 'Marinara Sauce', 'Canadian Bacon', 'Pineapple')
3 Small LIST('Cheese', 'BBQ Sauce', 'Chicken') |
If you use a CROSS JOIN it multiplies each row times the number of items in the ADT collection column. The multiplication hides the results.
The best solution for displaying results from an ADT collection requires that you serialize the results. The following serialize_set PL/SQL function creates a serialized comma separated list:
SQL> CREATE OR REPLACE 2 FUNCTION serialize_set (pv_list LIST) RETURN VARCHAR2 IS 3 /* Declare a return string as large as you need. */ 4 lv_comma_string VARCHAR2(60); 5 BEGIN 6 /* Read list of values and serialize them in a string. */ 7 FOR i IN 1..pv_list.COUNT LOOP 8 IF NOT i = pv_list.COUNT THEN 9 lv_comma_string := lv_comma_string || pv_list(i) || ', '; 10 ELSE 11 lv_comma_string := lv_comma_string || pv_list(i); 12 END IF; 13 END LOOP; 14 RETURN lv_comma_string; 15 END serialize_set; |
You can now write a query that uses your PL/SQL function to format the ADT collection column values into a single row. The syntax for the query is:
SQL> SELECT pizza_id 2 , pizza_size 3 , serialize_set(ingredients) AS ingredients 4 FROM pizza; |
It returns:
Pizza Pizza
ID # Size Ingredients
------ ------ -----------------------------------------------------------
1 Large Cheese, Marinara Sauce, Sausage, Salami
2 Medium Cheese, Marinara Sauce, Canadian Bacon, Pineapple
3 Small Cheese, BBQ Sauce, Chicken |
At this point, you know how to create a table with an ADT collection column and how to insert values. The Oracle documentation says you can only replace the whole content of the ADT column in an UPDATE statement. That’s true in practice but not in principle.
The principal differs because you can write PL/SQL functions that add, change, or remove elements from the ADT collection that works in an UPDATE statement. The trick is quite simple. You achieve it by:
- Passing the current ADT collection as a IN-only mode parameter
- Passing any new parameters when you add or change elements
- Passing any old parameters when you change or remove elements
Now, you will learn how to create the add_elements, change_elements, and remove_elements PL/SQL functions. They let you use an UPDATE statement to add, change, or remove elements from an ADT collection column.
Adding ADT elements with an UPDATE statement
This section shows you how to add elements to an ADT collection column with an UPDATE statement. The add_elements PL/SQL function can add one or many elements to an ADT collection column. That’s possible because the new element or elements are passed to the function inside an ADT collection parameter.
The merit of this type of solution is that you only need one function to accomplish two tasks. The test cases show you how to pass one new element or a set of new elements.
An alternative solution would have you write two functions. One would accept a collection parameter and a variable length string, and the other would accept two collection parameters. Many developers might choose to do that because they would like to leverage overloading inside PL/SQL packages. You should ask yourself one question when you make the decision about your approach to this problem: Which is easier to maintain and use?
The following creates the add_elements PL/SQL function:
SQL> CREATE OR REPLACE 2 FUNCTION add_elements 3 ( pv_list LIST 4 , pv_element LIST ) RETURN LIST IS 5 /* Declare local return collection variable. */ 6 lv_list LIST; 7 BEGIN 8 /* Check for instantiated collection and initialize when necessary. */ 9 IF pv_list IS NULL THEN 10 lv_list := list(); 11 ELSE 12 /* Assign parameter collection to local collection variable. */ 13 lv_list := pv_list; 14 FOR i IN 1..pv_element.COUNT LOOP 15 /* Check to avoid duplicates, allocate memory and assign value. */ 16 IF NOT list(pv_element(i)) SUBMULTISET OF lv_list THEN 17 lv_list.EXTEND; 18 lv_list(lv_list.COUNT) := pv_element(i); 19 END IF; 20 END LOOP; 21 END IF; 22 23 /* Return new collection. */ 24 RETURN lv_list; 25 END add_elements; 26 / |
Line 3 and 4 define the two parameters of the add_elements function as ADT collections. Line 4 also designates the return type of the function, which is the same ADT collection.
Line 6 declares a local ADT collection variable. You need a local lv_list ADT collection variable because you want to accept two collections and merge them into the local ADT collection variable. Then, you return the local ADT collection variable as the function outcome.
Line 9 checks whether the pv_list parameter is null. Line 10 initializes the lv_list variable when it is null to avoid an unitialized error when you try to assign values to it. Line 13 assigns an initialized ADT collection column’s value to the local lv_list variable. Line 14 starts a loop through the ADT collection you want to add to the ingredient column’s list of values.
Line 16 use the SUBMULTISET set operator to ensure that only new add elements when they don’t already exist in the ingredient ADT collection column. Line 17 allocates memory space in the lv_list variable, and line 18 assigns a new element to it.
You could extend memory for the total count of elements but that would make the index assignment on line 18 more complex. Combining them increments the count of items and lets you use the count as the index value. Line 24 returns the local ADT collection and replaces the original ingredient column value.
The test case for the function should ensure that only unique values are assigned to the ingredient ADT collection column value. This can be done by a three-step test case. The test queries the values in the ADT collection column, updates them, and re-queries them.
The following query shows you the contents of the row:
SQL> SELECT pizza_id, pizza_size 2 , serialize_set(ingredients) AS ingredients 3 FROM pizza 4 WHERE customer_id = 5 ( SELECT customer_id FROM customer 6 WHERE first_name = 'Quentin' AND last_name = 'Lance' ); |
It returns:
Pizza Pizza
ID # Size Ingredients
------ ------ -----------------------------------------------------------
1 Large Cheese, Marinara Sauce, Sausage, Salami |
You can update the ADT collection column’s values with the following UPDATE statement. It attempts to add Sausage and Italian Sausage to the list of values. The function should add only Italian Sausage because Sausage already exists in the list of values. When you re-query the row you will see that the add_elements added only the element Italian Sausage.
You would use the following UPDATE statement:
SQL> UPDATE pizza 2 SET ingredients = 3 add_elements(ingredients,list('Italian Sausage','Sausage')) 4 WHERE customer_id = 5 (SELECT customer_id FROM customer 6 WHERE first_name = 'Quentin' AND last_name = 'Lance'); |
Line 3 calls the add_elements PL/SQL function with the ingredient ADT collection column’s value as the first parameter. The second parameter is a dynamically created list of the elements. It contains the element or elements you want to add to the ingredient column’s values.
Re-querying the row, you should see that the UPDATE statement added only the Italian Sausage element to the row. You should see the following output:
Pizza Pizza
ID # Size Ingredients
------ ------ -----------------------------------------------------------
1 Large Cheese, Marinara Sauce, Sausage, Salami, Italian Sausage' |
As you can see, the call to the add_elements function adds only Italian Sausage to the list of values in the ingredient column, while a comma delimited list of single quote delimited strings allows you to add multiple elements. You add one element by making it the only single quote delimited item in the list constructor call.
Updating ADT elements with an UPDATE statement
This section shows you how to change elements in an ADT collection column with an UPDATE statement. The change_elements PL/SQL function can change one to many elements in an ADT collection column. That’s possible because the change element or elements are passed to the function inside ADT collection parameters.
Unlike the add_elements function, the change_elements function requires an ADT collection parameter and a UDT collection element. The UDT collection needs to hold an old and new value.
The alternative approach would require you to try and synchronize two ADT collection value sets. One would hold all the old values and the other would hold all the new values, and they would both need to be synchronized in mirrored positional order.
You define a pair UDT object type such as the following:
SQL> CREATE OR REPLACE 2 TYPE pair IS OBJECT 3 ( old VARCHAR2(20) 4 , NEW VARCHAR2(20)); 5 / |
Next, you define a change UDT collection type:
SQL> CREATE OR REPLACE 2 TYPE change IS TABLE OF pair; 3 / |
You define the change_element function as shown below:
SQL> CREATE OR REPLACE 2 FUNCTION change_elements 3 ( pv_list LIST 4 , pv_element CHANGE ) RETURN LIST IS 5 /* Declare local return collection variable. */ 6 lv_list LIST; 7 BEGIN 8 /* Check for instantiated collection and initialize when necessary. */ 9 IF pv_list IS NULL THEN 10 lv_list := list(); 11 ELSE 12 /* Assign parameter collection to local collection variable. */ 13 lv_list := pv_list; 14 FOR i IN 1..pv_element.COUNT LOOP 15 /* Check to avoid duplicates, allocate memory and assign value. */ 16 IF NOT list(pv_element(i).old) SUBMULTISET OF lv_list THEN 17 lv_list.EXTEND; 18 lv_list(lv_list.COUNT) := pv_element(i).NEW; 19 END IF; 20 END LOOP; 21 END IF; 22 23 /* Return new collection. */ 24 RETURN lv_list; 25 END change_elements; 26 / |
Line 3 and 4 define the two parameters of the change_elements function. The first pv_list parameter uses the list ADT collection type and the list type that matches the ingredient column’s data type. Line 4 defines a parameter that uses the change UDT collection type, which is a collection of the pair UDT type.
Line 6 declares a local ADT collection variable, such as the add_elements function. The lv_list variable also serves the same purpose as it does in the add_elements function.
Line 9 checks whether the pv_list parameter is null. Line 10 initializes the lv_list variable when it is null to avoid an unitialized error when you try to assign values to it. Line 13 assigns an initialized ADT collection column’s value to the local lv_list variable. Line 14 starts a loop through the ADT collection you want to add to the ingredient column’s list of values.
Line 16 uses the SUBMULTISET set operator to ensure that the old element exists in the ingredient ADT collection column. Line 17 allocates memory space in the lv_list variable, and line 18 assigns the new element to it.
The change_elements function couples the memory allocation with the assignment of new values. Line 24 returns the local ADT collection and replaces the original ingredient column value.
The test case shows you how to pass one old and one new element or a set of old and new elements. The initial query shows you the data before the update:
SQL> SELECT pizza_id, pizza_size 2 , serialize_set(ingredients) AS ingredients 3 FROM pizza 4 WHERE customer_id = 5 (SELECT customer_id FROM customer 6 WHERE first_name = 'Thea' AND last_name = 'Queen'); |
It returns:
Pizza Pizza
ID # Size Ingredients
------ ------ -----------------------------------------------------------
2 Medium Cheese, Marinara Sauce, Canadian Bacon |
You now update the row with the following query:
SQL> UPDATE pizza 2 SET ingredients = 3 change_elements(ingredients 4 ,change(pair(old => 'Italian Sausage' 5 ,NEW => 'Linguica'))) 6 WHERE customer_id = 7 ( SELECT customer_id FROM customer 8 WHERE first_name = 'Thea' AND last_name = 'Queen' ); |
When you re-query the row, it shows you the following:
Pizza Pizza
ID # Size Ingredients
------ ------ -----------------------------------------------------------
2 Medium Cheese, Marinara Sauce, Canadian Bacon, Linguica |
As you can see, the call to the change_elements function changes onlyItalian Sausage to Linguica in the list of values in the ingredient column, while a comma delimited list of pair UDT values allows you to change multiple elements. You change one element by making it the only pair UDT in the change constructor call.
Removing ADT elements with an UPDATE statement
This section shows you how to remove elements from an ADT collection column with an UPDATE statement. The remove_elements PL/SQL function can remove one to many elements from an ADT collection column.
The remove_elements function works much like the add_elements function. It uses the same ADT collections as the add_elements function.
The code for the remove_elements function is:
SQL> CREATE OR REPLACE 2 FUNCTION remove_elements 3 ( pv_list LIST 4 , pv_elements LIST ) RETURN LIST IS 5 /* Declare local return collection variable. */ 6 lv_list LIST; 7 BEGIN 8 /* Check for instantiation and element membership. */ 9 IF NOT (pv_list IS NULL AND pv_elements IS NULL) AND 10 (pv_list.COUNT > 0 AND pv_elements.COUNT > 0) THEN 11 /* Assign parameters to local variables. */ 12 lv_list := pv_list; 13 /* Remove any elements from a collection. */ 14 FOR i IN 1..lv_list.COUNT LOOP 15 FOR j IN 1..pv_elements.COUNT LOOP 16 IF lv_list(i) = pv_elements(j) THEN 17 lv_list.DELETE(i); 18 EXIT; 19 END IF; 20 END LOOP; 21 END LOOP; 22 END IF; 23 24 /* Return modified collection. */ 25 RETURN lv_list; 26 END remove_elements; 27 / |
Lines 3, 4, and 6 work like the add_elements function. Lines 9 and 10 differ because they check for initialized collections that hold at least one element each. Line 12 mimics the behavior of line 13 in the add_elements function. Lines 14 through 16 implements a nested loop and filtering IF-statement. The IF-statement checks for a valid element to remove from the ingredient ADT column’s list of values.
Line 17 removes an element from the list. Line 18 exits the inner loop to skip the evaluation of other non-matches. It’s possible to do this because the add_elements and change_elements functions ensure a unique list of string values in the ingredient ADT collection.
The test case for the remove_elements function works like the earlier tests. You query the row that you will update to check its values; for instance:
SQL> SELECT pizza_id, pizza_size 2 , serialize_set(ingredients) AS ingredients 3 FROM pizza 4 WHERE customer_id = 5 (SELECT customer_id FROM customer 6 WHERE first_name = 'Thea' AND last_name = 'Queen'); |
It should return:
Pizza Pizza
ID # Size Ingredients
------ ------ ----------------------------------------------------------------
2 Medium Cheese, Marinara Sauce, Canadian Bacon, Linguica |
You would remove an element from the ingredient ADT collection column with the following UPDATE statement:
SQL> UPDATE pizza 2 SET ingredients = 3 remove_elements(ingredients,list('Canadian Bacon')) 4 WHERE customer_id = 5 ( SELECT customer_id FROM customer 6 WHERE first_name = 'Thea' AND last_name = 'Queen' ); |
When you re-query the row, you should see that Canadian Bacon is no longer an element in the ingredient ADT collection column. Like this:
Pizza Pizza
ID # Size Ingredients
------ ------ ----------------------------------------------------------------
2 Medium Cheese, Marinara Sauce, Linguica |
This two article series has shown you the differences between working with ADT and UDT collection. It has also shown you how to create PL/SQL functions to enable you to add, change, and remove elements from ADT column inside an UPDATE statement.
The next step would be for you to put the serialize_set, add_elements, change_elements, and remove_elements functions into an adt package. That package would look like:
SQL> CREATE OR REPLACE 2 PACKAGE adt IS 3 4 FUNCTION add_elements 5 ( pv_list LIST 6 , pv_element LIST ) RETURN LIST; 7 8 FUNCTION change_elements 9 ( pv_list LIST 10 , pv_element CHANGE ) RETURN LIST; 11 12 FUNCTION remove_elements 13 ( pv_list LIST 14 , pv_elements LIST ) RETURN LIST; 15 16 FUNCTION serialize_set 17 (pv_list LIST) RETURN VARCHAR2; 18 19 END adt; 20 / |
Beyond writing an ADT package to manage a list of variable length strings, you have the opportunity to extend behaviors further through overloading. Overloading lets you define functions that use the same name with different parameter lists.
For example, you could define the LIST_D, LIST_N, and LIST_S as SQL ADT where they would implement ADTs of dates, numbers, and strings respectively. Then, you would write three versions of the preceding four functions. Each set of functions would work with one of the type specific ADTs, and provide you with a powerful utility package to add, change, remove, and serialize the values of date, number, and string ADTs.
When you put all the related functions into a package you simplify access and organize for reusability. That way you have all the tools you need inside a single adt package to write advanced UPDATE statements against ADT nested tables.
Updating Nested Tables
This two-part series covers how you update User-Defined Types (UDTs) and Attribute Data Types (ADTs). There are two varieties of UDTs. One is a column of a UDT object type and the other a UDT collection of a UDT object type.
You update nested UDT columns by leveraging the TABLE function. The TABLE function lets you create a result set, and access a UDT object or collection column. You need to combine the TABLE function and a CROSS JOIN to update elements of a UDT collection column.
ADTs are collections of a scalar data types. Oracle’s scalar data types are DATE, NUMBER, CHAR and VARCHAR2 (or, variable length strings). ADTs are unique and from some developer’s perspective difficult to work with.
The first article in this series shows you how to work with a UDT object type column and a UDT collection type. The second article will show you how to work with an ADT collection type.
PL/SQL uses ADT collections all the time. PL/SQL also uses User-Defined Types (UDTs) collections all the time. UDTs can be record or object types, or collections of records and objects. Record types are limited, and only work inside a PL/SQL scope. Object types are less limited and you can use them in a SQL or PL/SQL scope.
Object types come in two flavors. One acts as a typical record structure and has no methods and the other acts like an object type in any object-oriented programming language (OOPL). This article refers only to object types like typical record structures. That means when you read ADTs you should think of a SQL collection of a scalar data type, and when you read UDTs you should think of a SQL collection of an object type without methods.
You can create tables that hold nested tables. Nested tables can use a SQL ADT or UDT data type. Inserting data into nested tables is straightforward when you understand the syntax, but updating nested tables can be complex. The complexity exists because Oracle treats nested tables of ADTs differently than UDTs. My article series will show you how to simplify updating ADT columns.
That’s why it has two parts:
- How you insert and update rows with UDT columns and collection columns
- How you insert and update rows with ADT collection columns
If you’re asking yourself why there isn’t a section for deleting rows, that’s simple. You delete them the same way as you would any other row, using the DELETE statement.
How you insert and update rows with UDT columns and collection columns
This section shows you how to create a table with a UDT column and a UDT collection column. It also shows you how to insert and update the embedded columns.
You insert into any ordinary UDT column by prefacing the data with a constructor name. A constructor name is the same as a UDT name. The following creates an address_type UDT that you will use inside a customer table:
SQL> CREATE OR REPLACE 2 TYPE address_type IS OBJECT 3 ( street VARCHAR2(20) 4 , city VARCHAR2(30) 5 , state VARCHAR2(2) 6 , zip VARCHAR2(5)); 7 / |
You should take note that the address_type UDT doesn’t have any methods. All object types without methods have a default constructor. The default constructor follows the same rules as tables in the database.
Create the sample customer table with an address column that uses the address_type UDT as its data type; for instance:
SQL> CREATE TABLE customer 2 ( customer_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , address ADDRESS_TYPE 6 , CONSTRAINT pk_customer PRIMARY KEY (customer_id)); |
Line 5 defines the address column with the address_type UDT. You insert a row with an embedded address_type data record as follows:
SQL> INSERT 2 INTO customer 3 VALUES 4 ( customer_s.NEXTVAL 5 ,'Oliver' 6 ,'Queen' 7 , address_type( street => '1 Park Place' 8 , city => 'Starling City' 9 , state => 'NY' 10 , zip => '10001')); |
Lines 7 through 10 includes the constructor call to the address_type UDT. The address_type constructor uses named notation rather than positional notation. You should always try to use named notation for object type constructor calls.
Updating an element of a UDT object structure is straightforward, because you simply refer to the column and a member of the UDT object structure. The syntax for that type of UPDATE statement follows:
SQL> UPDATE customer c 2 SET c.address.state = 'NJ' 3 WHERE c.first_name = 'Oliver' 4 AND c.last_name = 'Queen'; |
The address_type UDT works for an object structure but not for a UDT collection. You need to add a column to differentiate between rows of the nested collection. You can redefine the address_type UDT as follows:
SQL> CREATE OR REPLACE 2 TYPE address_type IS OBJECT 3 ( status VARCHAR2(8) 4 , street VARCHAR2(20) 5 , city VARCHAR2(30) 6 , state VARCHAR2(2) 7 , zip VARCHAR2(5)); 8 / |
After creating the UDT object type, you need to create an address_table UDT collection of the address_type UDT object type. You use the following syntax to create the SQL collection:
SQL> CREATE OR REPLACE 2 TYPE address_table IS TABLE OF address_type; 3 / |
Having both the UDT object and collection types, you can drop and create the customer table with the following syntax:
SQL> CREATE TABLE customer 2 ( customer_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , address ADDRESS_TABLE 6 , CONSTRAINT pk_customer PRIMARY KEY (customer_id)) 7 NESTED TABLE address STORE AS address_tab; |
Line 5 defines the address column as a UDT collection. Line 7 instructs how to store the UDT collection as a nested table. You designate the address column as the nested table and store it as an address_tab table. You can access the nested table only through its container, which is the customer table.
You can insert rows into the customer table with the following syntax. This example stores a single row with two elements of the address_type in the nested table:
SQL> INSERT 2 INTO customer 3 VALUES 4 ( customer_s.NEXTVAL 5 ,'Oliver' 6 ,'Queen' 7 , address_table( 8 address_type( status => 'Obsolete' 9 , street => '1 Park Place' 10 , city => 'Starling City' 11 , state => 'NY' 12 , zip => '10001') 13 , address_type( status => 'Current' 14 , street => '1 Dockland Street' 15 , city => 'Starling City' 16 , state => 'NY' 17 , zip => '10001'))); |
Lines 7 through 17 have two constructor calls for the address_type UDT object type inside the address_table UDT collection. After you insert an address_table UDT collection, you can query an element by using the SQL built-in TABLE function and a CROSS JOIN. The TABLE function returns a SQL result set. The CROSS JOIN lets you create cross product that you can filter inside the WHERE clause.
A CROSS JOIN between two tables or a table and result set from a nested table matches every row in the customer table with every row in the nested table. A best practice would include a WHERE clause that filters the nested table to a single row in the result set.
The syntax for such a query is complex, and follows below:
SQL> COL first_name FORMAT A8 HEADING "First|Name" SQL> COL last_name FORMAT A8 HEADING "Last|Name" SQL> COL street FORMAT A20 HEADING "Street" SQL> COL city FORMAT A14 HEADING "City" SQL> COL state FORMAT A5 HEADING "State" SQL> SELECT c.first_name 2 , c.last_name 3 , a.street 4 , a.city 5 , a.state 6 FROM customer c CROSS JOIN TABLE(c.address) a 7 WHERE a.status = 'Current'; |
As mentioned, the TABLE function on line 6 translates the UDT collection into a SQL result set, which acts as a temporary table. The alias a becomes the name of the temporary table. Lines 3, 4, 5, and 7 all reference the temporary table.
The query should return the following for the customer and their current address value:
First Last Name Name Street City State -------- -------- -------------------- -------------- ----- Oliver Queen 1 Dockland Street Starling City NY |
Oracle thought through the fact that you should be able to update UDT collections. The same TABLE function lets you update elements in the nested table. You can update the elements in nested UDT tables provided you create a unique key, such as a natural key or primary key. Oracle’s syntax doesn’t support constraints on nested tables, which means you need to implement it by design and protect by carefully controlling inserts and updates to the nested table.
You can update the state value of the current address with the following UPDATE statement:
SQL> UPDATE TABLE(SELECT c.address 2 FROM customer c 3 WHERE c.first_name = 'Oliver' 4 AND c.last_name = 'Queen') a 5 SET a.state = 'NJ' 6 WHERE a.status = 'Current'; |
Line 5 sets the current state value in the address_table UDT nested table. Line 6 filters the nested table to the current address element. You need to ensure that any UDT object type holds a member attribute or set of member attributes that holds a unique value. That’s because you need to ensure that there’s a way to find a unique element within a UDT collection. If you require the table, you should see the change inside the nested table.
Oracle does not provide equivalent syntax for such a change in an ADT collection type. The second article in this series show you how to implement PL/SQL functions to solve that problem.
Subscribe via RSS