Archive for the ‘sql’ Category
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 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.
Oracle23ai Ubuntu Install
What to do with a Late 2015 iMac with an i7 Quad CPU running at 3.4 GHz, 32 GB or RAM, a 5K Display and an almost warn out hybrid 1 TB hard disk? You could sell it to Apple for pennies, but why enrich them. I opted to upgrade it with an OWC kit that had a 2 TB SSD Disk. Then, I installed Ubuntu 22.0.4 and built a DaaS (Database as a Service) machine with Oracle Database 23ai in a Docker container, and MySQL 8 and PostgreSQL 14 natively.
I’ve posted on installing MySQL 8 and PostgreSQL 14 on Ubuntu before when I repurposed my late 2014 MacBook Pro. This post covers the installation of Docker and Oracle Database 23ai.
Install Docker
Contrary to the instructions, you should do the following as a sudoer user:
sudo apt install -y docker.io |
Install all dependency packages using the following command:
sudo snap install docker |
You should see the following:
docker 20.10.24 from Canonical✓ installed |
You can verify the Docker install with the following command:
sudo docker --version |
It should show something like this:
Docker version 24.0.5, build 24.0.5-0ubuntu1~22.04.1 |
You can check the pulled containers with the following command but at this point there should be no pulled containers.
sudo docker images |
At this point, a docker group already exists but you need to add your user to the docker group with the following command:
sudo usermod -aG docker $USER |
Using the Docker Commands:
- To activate the logging, utilize the -f parameter.
- To divide JSON, use Docker by default; to extract individual keys, use JQ.
- In your Container file, there are quite a few areas where commands may be specified.
- Posting to the volumes could be more effective while the picture is being built.
- Docker offers a highly efficient way to create an alias for its own built-in commands. This makes it easier to set up and handle lengthy and enormous orders. These alias values are stored in the directories /.bashrc or and /.bash_aliases.
- Docker offers further assistance to remove unused code fragments from the installation of the container.
- Docker always favors reading statements from the container file that have not changed. Therefore, time savings may be realized by arranging what is shown in the container file in a way that ensures the elements that are susceptible to change are shown towards the end of the document and those that are most likely to undergo change are shown at the top.
Install Oracle Database 23ai Free in a Docker container
Use the following command to pull and install the Oracle Database 23ai container:
sudo docker run --name oracle23ai -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=cangetin container-registry.oracle.com/database/free:latest |
After installing the Oracle Database 23ai Free container, you can access it as the root user by default with this syntax:
docker exec -it -u root oracle23ai bash |
At the root prompt, you can connect to the system schema with the following command:
sqlplus system/cangetin@FREE |
You should see the following:
SQL*Plus: RELEASE 23.0.0.0.0 - Production ON Thu May 9 03:56:57 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. ALL rights reserved. LAST SUCCESSFUL login TIME: Wed Apr 24 2024 21:23:00 +00: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> |
Create a c##student as a sandbox user:
After you create and provision the Oracle Database 21ai Free, you can create a c##student sand-boxed user with the following two step process.
- Create a c##student Oracle user account with the following command as the system user:
CREATE USER c##student IDENTIFIED BY student DEFAULT TABLESPACE users QUOTA 200M ON users TEMPORARY TABLESPACE temp;
- Grant necessary privileges to the newly created c##student user:
GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO c##student;
- Connect to the sandboxed user with the following syntax (by the way it’s a pluggable user account as qualified in Oracle Database 12c forward):
SQL> CONNECT c##student/student@FREE
or, disconnect and reconnect with this syntax:
sqlplus system/cangetin@FREE
Set Docker Oracle 23ai to start always
Assuming that your container name was oracle23ai, as qualified above, you can run the following command to automatically restart the Docker container:
docker update --restart=always `docker ps -aqf "name=oracle23ai"` |
The docker command inside the backquotes uses the Docker instance’s name to return the Docker container_id value, which can also be seen when you run the following command:
docker ps |
which returns:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES b211f494e692 container-registry.oracle.com/database/free:latest "/bin/bash -c $ORACL…" 13 days ago Up 18 minutes (healthy) 0.0.0.0:1521->1521/tcp, :::1521->1521/tcp, 0.0.0.0:5500->5500/tcp, :::5500->5500/tcp oracle23ai |
The Docker container_id value is required when you perform a Docker update operation.
Configuring your Docker Oracle 23ai environment
Unless you like memorizing the Docker command-line, you may automate connecting as the root user or add a sand boxed user. The root user typically has more power than you need to perform ordinary development and use-case testing tasks.
A sand boxed user has narrow access, can’t start and stop the database instance or perform Oracle Datasbase 23ai administration. In this segment, you’ll learn how to create a couple local Bash functions to simplify your use of the Oracle Database 23ai container; and how to extend the configuration of Oracle’s Docker container:
- Adding a student user to the Docker container and configuring it to access the Oracle Database 23ai locally from within the Docker container using a direct sqlplus connection.
- Configuring the Docker container to support external files and leverage a shared directory with your base operating system.
Automating Docker instance connections:
The following shows you how to add a local Bash function to automate access to the Docker container from the Linux command-line. You put the following Bash function in your base Linux operating system’s user .bashrc file:
- Create the following Bash function:
# User defined function to launch Oracle 23 ai container # as the root user. admin () { # Discover the fully qualified program name. path=`which docker 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} = "docker" ]]; then python -c "import subprocess; subprocess.run(['docker exec -it --user root oracle23ai bash'], shell=True)" else echo "Docker is unavailable: Install the docker package." fi }
- After you source the .bashrc file or simply reconnect as to the terminal as your user, which resources the .bashrc file, you can access the oracle23ai Docker instance with this command:
admin
It will display a new prompt with the root user and the Docker container_id value, like:
[root@b211f494e692 oracle]#
You can exit the Docker container by typing exit at the Linux command line. If you curious what version of Linux you’re using inside the Docker instance, you can’t use the uname command because it returns the hosting Linux distribution (distro). You must use the following when inside the Docker instance:
cat /etc/os-release
or, outside the Docker instance you can use the following docker command:
docker exec oracle23ai cat /etc/os-release
Either way, for an Oracle Database 23ai container, it should return:
NAME="Oracle Linux Server" VERSION="8.9" ID="ol" ID_LIKE="fedora" VARIANT="Server" VARIANT_ID="server" VERSION_ID="8.9" PLATFORM_ID="platform:el8" PRETTY_NAME="Oracle Linux Server 8.9" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:8:9:server" HOME_URL="https://linux.oracle.com/" BUG_REPORT_URL="https://github.com/oracle/oracle-linux" ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8" ORACLE_BUGZILLA_PRODUCT_VERSION=8.9 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=8.9
Unfortunately, Oracle has appeared to block updates to the Oracle Unbreakable Linux 8 instance inside the container, which makes native SQL*Plus use more difficult. That’s because you’ll need to install the Oracle SQL*Plus client in the hosting Operating System.
I’ve written a separate blog post that instructs you on how to install and use Oracle SQL*Plus client on Ubuntu.
Install SQL Developer in the base Linux operating system
The first steps are installing the Java Runtime Environment and Java Development Kit, and then downloading, installing and configuring SQL Developer. These are the required steps:
- Install the Java Runtime Environment:
sudo apt install default-jre
The log file for this is:
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: ca-certificates-java default-jre-headless fonts-dejavu-extra java-common libatk-wrapper-java libatk-wrapper-java-jni openjdk-11-jre openjdk-11-jre-headless Suggested packages: fonts-ipafont-gothic fonts-ipafont-mincho fonts-wqy-microhei | fonts-wqy-zenhei The following NEW packages will be installed: ca-certificates-java default-jre default-jre-headless fonts-dejavu-extra java-common libatk-wrapper-java libatk-wrapper-java-jni openjdk-11-jre openjdk-11-jre-headless 0 upgraded, 9 newly installed, 0 to remove and 4 not upgraded. Need to get 44.9 MB of archives. After this operation, 185 MB of additional disk space will be used. Do you want to continue? [Y/n] Y Get:1 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 java-common all 0.72build2 [6,782 B] Get:2 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 openjdk-11-jre-headless amd64 11.0.21+9-0ubuntu1~22.04 [42.5 MB] Get:3 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 default-jre-headless amd64 2:1.11-72build2 [3,042 B] Get:4 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 ca-certificates-java all 20190909ubuntu1.2 [12.1 kB] Get:5 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 openjdk-11-jre amd64 11.0.21+9-0ubuntu1~22.04 [214 kB] Get:6 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 default-jre amd64 2:1.11-72build2 [896 B] Get:7 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 fonts-dejavu-extra all 2.37-2build1 [2,041 kB] Get:8 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libatk-wrapper-java all 0.38.0-5build1 [53.1 kB] Get:9 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libatk-wrapper-java-jni amd64 0.38.0-5build1 [49.0 kB] Fetched 44.9 MB in 14s (3,270 kB/s) Selecting previously unselected package java-common. (Reading database ... 203118 files and directories currently installed.) Preparing to unpack .../0-java-common_0.72build2_all.deb ... Unpacking java-common (0.72build2) ... Selecting previously unselected package openjdk-11-jre-headless:amd64. Preparing to unpack .../1-openjdk-11-jre-headless_11.0.21+9-0ubuntu1~22.04_amd64 .deb ... Unpacking openjdk-11-jre-headless:amd64 (11.0.21+9-0ubuntu1~22.04) ... Selecting previously unselected package default-jre-headless. Preparing to unpack .../2-default-jre-headless_2%3a1.11-72build2_amd64.deb ... Unpacking default-jre-headless (2:1.11-72build2) ... Selecting previously unselected package ca-certificates-java. Preparing to unpack .../3-ca-certificates-java_20190909ubuntu1.2_all.deb ... Unpacking ca-certificates-java (20190909ubuntu1.2) ... Selecting previously unselected package openjdk-11-jre:amd64. Preparing to unpack .../4-openjdk-11-jre_11.0.21+9-0ubuntu1~22.04_amd64.deb ... Unpacking openjdk-11-jre:amd64 (11.0.21+9-0ubuntu1~22.04) ... Selecting previously unselected package default-jre. Preparing to unpack .../5-default-jre_2%3a1.11-72build2_amd64.deb ... Unpacking default-jre (2:1.11-72build2) ... Selecting previously unselected package fonts-dejavu-extra. Preparing to unpack .../6-fonts-dejavu-extra_2.37-2build1_all.deb ... Unpacking fonts-dejavu-extra (2.37-2build1) ... Selecting previously unselected package libatk-wrapper-java. Preparing to unpack .../7-libatk-wrapper-java_0.38.0-5build1_all.deb ... Unpacking libatk-wrapper-java (0.38.0-5build1) ... Selecting previously unselected package libatk-wrapper-java-jni:amd64. Preparing to unpack .../8-libatk-wrapper-java-jni_0.38.0-5build1_amd64.deb ... Unpacking libatk-wrapper-java-jni:amd64 (0.38.0-5build1) ... Setting up java-common (0.72build2) ... Setting up fonts-dejavu-extra (2.37-2build1) ... Setting up libatk-wrapper-java (0.38.0-5build1) ... Setting up libatk-wrapper-java-jni:amd64 (0.38.0-5build1) ... Setting up default-jre-headless (2:1.11-72build2) ... Setting up openjdk-11-jre-headless:amd64 (11.0.21+9-0ubuntu1~22.04) ... update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/java to provid e /usr/bin/java (java) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jjs to provide /usr/bin/jjs (jjs) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/keytool to pro vide /usr/bin/keytool (keytool) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/rmid to provid e /usr/bin/rmid (rmid) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/rmiregistry to provide /usr/bin/rmiregistry (rmiregistry) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/pack200 to pro vide /usr/bin/pack200 (pack200) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/unpack200 to p rovide /usr/bin/unpack200 (unpack200) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/lib/jexec to provi de /usr/bin/jexec (jexec) in auto mode Setting up openjdk-11-jre:amd64 (11.0.21+9-0ubuntu1~22.04) ... Setting up default-jre (2:1.11-72build2) ... Setting up ca-certificates-java (20190909ubuntu1.2) ... head: cannot open '/etc/ssl/certs/java/cacerts' for reading: No such file or dir ectory Adding debian:QuoVadis_Root_CA_1_G3.pem Adding debian:GlobalSign_Root_E46.pem Adding debian:T-TeleSec_GlobalRoot_Class_3.pem Adding debian:Certum_Trusted_Network_CA.pem Adding debian:Buypass_Class_2_Root_CA.pem Adding debian:NetLock_Arany_=Class_Gold=_FÅ‘tanúsÃtvány.pem Adding debian:e-Szigno_Root_CA_2017.pem Adding debian:emSign_Root_CA_-_G1.pem Adding debian:D-TRUST_BR_Root_CA_1_2020.pem Adding debian:Hongkong_Post_Root_CA_3.pem Adding debian:GlobalSign_ECC_Root_CA_-_R4.pem Adding debian:NAVER_Global_Root_Certification_Authority.pem Adding debian:UCA_Extended_Validation_Root.pem Adding debian:AffirmTrust_Premium.pem Adding debian:Entrust_Root_Certification_Authority.pem Adding debian:DigiCert_Trusted_Root_G4.pem Adding debian:CFCA_EV_ROOT.pem Adding debian:ePKI_Root_Certification_Authority.pem Adding debian:Hellenic_Academic_and_Research_Institutions_ECC_RootCA_2015.pem Adding debian:HARICA_TLS_RSA_Root_CA_2021.pem Adding debian:GlobalSign_Root_CA_-_R6.pem Adding debian:TWCA_Global_Root_CA.pem Adding debian:Trustwave_Global_ECC_P384_Certification_Authority.pem Adding debian:ISRG_Root_X1.pem Adding debian:Starfield_Services_Root_Certificate_Authority_-_G2.pem Adding debian:QuoVadis_Root_CA_3.pem Adding debian:Security_Communication_Root_CA.pem Adding debian:DigiCert_TLS_RSA4096_Root_G5.pem Adding debian:Entrust_Root_Certification_Authority_-_EC1.pem Adding debian:Security_Communication_RootCA3.pem Adding debian:TeliaSonera_Root_CA_v1.pem Adding debian:vTrus_ECC_Root_CA.pem Adding debian:AC_RAIZ_FNMT-RCM_SERVIDORES_SEGUROS.pem Adding debian:Certum_EC-384_CA.pem Adding debian:Microsec_e-Szigno_Root_CA_2009.pem Adding debian:ssl-cert-snakeoil.pem Adding debian:USERTrust_ECC_Certification_Authority.pem Adding debian:CA_Disig_Root_R2.pem Adding debian:Certum_Trusted_Network_CA_2.pem Adding debian:ACCVRAIZ1.pem Adding debian:TunTrust_Root_CA.pem Adding debian:Buypass_Class_3_Root_CA.pem Adding debian:D-TRUST_Root_Class_3_CA_2_2009.pem Adding debian:Security_Communication_ECC_RootCA1.pem Adding debian:GTS_Root_R2.pem Adding debian:Certigna.pem Adding debian:SSL.com_EV_Root_Certification_Authority_RSA_R2.pem Adding debian:Entrust.net_Premium_2048_Secure_Server_CA.pem Adding debian:E-Tugra_Global_Root_CA_ECC_v3.pem Adding debian:Hongkong_Post_Root_CA_1.pem Adding debian:SZAFIR_ROOT_CA2.pem Adding debian:TUBITAK_Kamu_SM_SSL_Kok_Sertifikasi_-_Surum_1.pem Adding debian:Atos_TrustedRoot_2011.pem Adding debian:DigiCert_High_Assurance_EV_Root_CA.pem Adding debian:emSign_Root_CA_-_C1.pem Adding debian:Go_Daddy_Root_Certificate_Authority_-_G2.pem Adding debian:GDCA_TrustAUTH_R5_ROOT.pem Adding debian:GlobalSign_Root_CA_-_R3.pem Adding debian:DigiCert_Assured_ID_Root_G3.pem Adding debian:Autoridad_de_Certificacion_Firmaprofesional_CIF_A62634068_2.pem Adding debian:Certainly_Root_R1.pem Adding debian:vTrus_Root_CA.pem Adding debian:Certainly_Root_E1.pem Adding debian:Autoridad_de_Certificacion_Firmaprofesional_CIF_A62634068.pem Adding debian:TWCA_Root_Certification_Authority.pem Adding debian:Starfield_Root_Certificate_Authority_-_G2.pem Adding debian:Amazon_Root_CA_3.pem Adding debian:GTS_Root_R1.pem Adding debian:SwissSign_Gold_CA_-_G2.pem Adding debian:Certum_Trusted_Root_CA.pem Adding debian:Hellenic_Academic_and_Research_Institutions_RootCA_2015.pem Adding debian:AffirmTrust_Networking.pem Adding debian:emSign_ECC_Root_CA_-_G3.pem Adding debian:HARICA_TLS_ECC_Root_CA_2021.pem Adding debian:certSIGN_ROOT_CA.pem Adding debian:Actalis_Authentication_Root_CA.pem Adding debian:SSL.com_Root_Certification_Authority_RSA.pem Adding debian:Certigna_Root_CA.pem Adding debian:XRamp_Global_CA_Root.pem Adding debian:Baltimore_CyberTrust_Root.pem Adding debian:Trustwave_Global_ECC_P256_Certification_Authority.pem Adding debian:QuoVadis_Root_CA_2_G3.pem Adding debian:GTS_Root_R3.pem Adding debian:COMODO_RSA_Certification_Authority.pem Adding debian:ISRG_Root_X2.pem Adding debian:SwissSign_Silver_CA_-_G2.pem Adding debian:IdenTrust_Public_Sector_Root_CA_1.pem Adding debian:Microsoft_ECC_Root_Certificate_Authority_2017.pem Adding debian:UCA_Global_G2_Root.pem Adding debian:DigiCert_Assured_ID_Root_CA.pem Adding debian:Entrust_Root_Certification_Authority_-_G2.pem Adding debian:QuoVadis_Root_CA_2.pem Adding debian:Trustwave_Global_Certification_Authority.pem Adding debian:OISTE_WISeKey_Global_Root_GB_CA.pem Adding debian:HiPKI_Root_CA_-_G1.pem Adding debian:E-Tugra_Certification_Authority.pem Adding debian:GTS_Root_R4.pem Adding debian:Amazon_Root_CA_2.pem Adding debian:Amazon_Root_CA_1.pem Adding debian:SecureTrust_CA.pem Adding debian:GlobalSign_Root_R46.pem Adding debian:IdenTrust_Commercial_Root_CA_1.pem Adding debian:DigiCert_Global_Root_G2.pem Adding debian:Comodo_AAA_Services_root.pem Adding debian:SSL.com_Root_Certification_Authority_ECC.pem Adding debian:T-TeleSec_GlobalRoot_Class_2.pem Adding debian:Starfield_Class_2_CA.pem Adding debian:DigiCert_Global_Root_CA.pem Adding debian:SecureSign_RootCA11.pem Adding debian:certSIGN_Root_CA_G2.pem Adding debian:DigiCert_TLS_ECC_P384_Root_G5.pem Adding debian:Entrust_Root_Certification_Authority_-_G4.pem Adding debian:OISTE_WISeKey_Global_Root_GC_CA.pem Adding debian:DigiCert_Global_Root_G3.pem Adding debian:Secure_Global_CA.pem Adding debian:Microsoft_RSA_Root_Certificate_Authority_2017.pem Adding debian:DigiCert_Assured_ID_Root_G2.pem Adding debian:Telia_Root_CA_v2.pem Adding debian:emSign_ECC_Root_CA_-_C3.pem Adding debian:COMODO_Certification_Authority.pem Adding debian:AffirmTrust_Premium_ECC.pem Adding debian:GLOBALTRUST_2020.pem Adding debian:E-Tugra_Global_Root_CA_RSA_v3.pem Adding debian:Amazon_Root_CA_4.pem Adding debian:COMODO_ECC_Certification_Authority.pem Adding debian:AffirmTrust_Commercial.pem Adding debian:SSL.com_EV_Root_Certification_Authority_ECC.pem Adding debian:AC_RAIZ_FNMT-RCM.pem Adding debian:Go_Daddy_Class_2_CA.pem Adding debian:QuoVadis_Root_CA_3_G3.pem Adding debian:D-TRUST_EV_Root_CA_1_2020.pem Adding debian:GlobalSign_Root_CA.pem Adding debian:GlobalSign_ECC_Root_CA_-_R5.pem Adding debian:USERTrust_RSA_Certification_Authority.pem Adding debian:D-TRUST_Root_Class_3_CA_2_EV_2009.pem Adding debian:Izenpe.com.pem Adding debian:ANF_Secure_Server_Root_CA.pem Adding debian:Security_Communication_RootCA2.pem done. Processing triggers for mailcap (3.70+nmu1ubuntu1) ... Processing triggers for fontconfig (2.13.1-4.2ubuntu5) ... Processing triggers for desktop-file-utils (0.26-1ubuntu3) ... Processing triggers for hicolor-icon-theme (0.17-2) ... Processing triggers for gnome-menus (3.36.0-1ubuntu3) ... Processing triggers for man-db (2.10.2-1) ... Processing triggers for ca-certificates (20230311ubuntu0.22.04.1) ... Updating certificates in /etc/ssl/certs... 0 added, 0 removed; done. Running hooks in /etc/ca-certificates/update.d... done. done.
- Install the Java Runtime Environment:
sudo apt install -y default-idk
The log file for this is:
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: default-jdk-headless libice-dev libpthread-stubs0-dev libsm-dev libx11-dev libxau-dev libxcb1-dev libxdmcp-dev libxt-dev openjdk-11-jdk openjdk-11-jdk-headless x11proto-dev xorg-sgml-doctools xtrans-dev Suggested packages: libice-doc libsm-doc libx11-doc libxcb-doc libxt-doc openjdk-11-demo openjdk-11-source visualvm The following NEW packages will be installed: default-jdk default-jdk-headless libice-dev libpthread-stubs0-dev libsm-dev libx11-dev libxau-dev libxcb1-dev libxdmcp-dev libxt-dev openjdk-11-jdk openjdk-11-jdk-headless x11proto-dev xorg-sgml-doctools xtrans-dev 0 upgraded, 15 newly installed, 0 to remove and 4 not upgraded. Need to get 76.9 MB of archives. After this operation, 90.6 MB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 openjdk-11-jdk-headless amd64 11.0.21+9-0ubuntu1~22.04 [73.5 MB] Get:2 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 default-jdk-headless amd64 2:1.11-72build2 [942 B] Get:3 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 openjdk-11-jdk amd64 11.0.21+9-0ubuntu1~22.04 [1,327 kB] Get:4 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 default-jdk amd64 2:1.11-72build2 [908 B] Get:5 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 xorg-sgml-doctools all 1:1.11-1.1 [10.9 kB] Get:6 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 x11proto-dev all 2021.5-1 [604 kB] Get:7 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libice-dev amd64 2:1.0.10-1build2 [51.4 kB] Get:8 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libpthread-stubs0-dev amd64 0.4-1build2 [5,516 B] Get:9 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libsm-dev amd64 2:1.2.3-1build2 [18.1 kB] Get:10 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libxau-dev amd64 1:1.0.9-1build5 [9,724 B] Get:11 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libxdmcp-dev amd64 1:1.1.3-0ubuntu5 [26.5 kB] Get:12 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 xtrans-dev all 1.4.0-1 [68.9 kB] Get:13 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libxcb1-dev amd64 1.14-3ubuntu3 [86.5 kB] Get:14 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libx11-dev amd64 2:1.7.5-1ubuntu0.3 [744 kB] Get:15 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libxt-dev amd64 1:1.2.1-1 [396 kB] Fetched 76.9 MB in 6s (12.7 MB/s) Selecting previously unselected package openjdk-11-jdk-headless:amd64. (Reading database ... 203527 files and directories currently installed.) Preparing to unpack .../00-openjdk-11-jdk-headless_11.0.21+9-0ubuntu1~22.04_amd6 4.deb ... Unpacking openjdk-11-jdk-headless:amd64 (11.0.21+9-0ubuntu1~22.04) ... Selecting previously unselected package default-jdk-headless. Preparing to unpack .../01-default-jdk-headless_2%3a1.11-72build2_amd64.deb ... Unpacking default-jdk-headless (2:1.11-72build2) ... Selecting previously unselected package openjdk-11-jdk:amd64. Preparing to unpack .../02-openjdk-11-jdk_11.0.21+9-0ubuntu1~22.04_amd64.deb ... Unpacking openjdk-11-jdk:amd64 (11.0.21+9-0ubuntu1~22.04) ... Selecting previously unselected package default-jdk. Preparing to unpack .../03-default-jdk_2%3a1.11-72build2_amd64.deb ... Unpacking default-jdk (2:1.11-72build2) ... Selecting previously unselected package xorg-sgml-doctools. Preparing to unpack .../04-xorg-sgml-doctools_1%3a1.11-1.1_all.deb ... Unpacking xorg-sgml-doctools (1:1.11-1.1) ... Selecting previously unselected package x11proto-dev. Preparing to unpack .../05-x11proto-dev_2021.5-1_all.deb ... Unpacking x11proto-dev (2021.5-1) ... Selecting previously unselected package libice-dev:amd64. Preparing to unpack .../06-libice-dev_2%3a1.0.10-1build2_amd64.deb ... Unpacking libice-dev:amd64 (2:1.0.10-1build2) ... Selecting previously unselected package libpthread-stubs0-dev:amd64. Preparing to unpack .../07-libpthread-stubs0-dev_0.4-1build2_amd64.deb ... Unpacking libpthread-stubs0-dev:amd64 (0.4-1build2) ... Selecting previously unselected package libsm-dev:amd64. Preparing to unpack .../08-libsm-dev_2%3a1.2.3-1build2_amd64.deb ... Unpacking libsm-dev:amd64 (2:1.2.3-1build2) ... Selecting previously unselected package libxau-dev:amd64. Preparing to unpack .../09-libxau-dev_1%3a1.0.9-1build5_amd64.deb ... Unpacking libxau-dev:amd64 (1:1.0.9-1build5) ... Selecting previously unselected package libxdmcp-dev:amd64. Preparing to unpack .../10-libxdmcp-dev_1%3a1.1.3-0ubuntu5_amd64.deb ... Unpacking libxdmcp-dev:amd64 (1:1.1.3-0ubuntu5) ... Selecting previously unselected package xtrans-dev. Preparing to unpack .../11-xtrans-dev_1.4.0-1_all.deb ... Unpacking xtrans-dev (1.4.0-1) ... Selecting previously unselected package libxcb1-dev:amd64. Preparing to unpack .../12-libxcb1-dev_1.14-3ubuntu3_amd64.deb ... Unpacking libxcb1-dev:amd64 (1.14-3ubuntu3) ... Selecting previously unselected package libx11-dev:amd64. Preparing to unpack .../13-libx11-dev_2%3a1.7.5-1ubuntu0.3_amd64.deb ... Unpacking libx11-dev:amd64 (2:1.7.5-1ubuntu0.3) ... Selecting previously unselected package libxt-dev:amd64. Preparing to unpack .../14-libxt-dev_1%3a1.2.1-1_amd64.deb ... Unpacking libxt-dev:amd64 (1:1.2.1-1) ... Setting up openjdk-11-jdk-headless:amd64 (11.0.21+9-0ubuntu1~22.04) ... update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jar to provide /usr/bin/jar (jar) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jarsigner to p rovide /usr/bin/jarsigner (jarsigner) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/javac to provi de /usr/bin/javac (javac) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/javadoc to pro vide /usr/bin/javadoc (javadoc) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/javap to provi de /usr/bin/javap (javap) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jcmd to provid e /usr/bin/jcmd (jcmd) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jdb to provide /usr/bin/jdb (jdb) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jdeprscan to p rovide /usr/bin/jdeprscan (jdeprscan) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jdeps to provi de /usr/bin/jdeps (jdeps) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jfr to provide /usr/bin/jfr (jfr) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jimage to prov ide /usr/bin/jimage (jimage) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jinfo to provi de /usr/bin/jinfo (jinfo) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jlink to provi de /usr/bin/jlink (jlink) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jmap to provid e /usr/bin/jmap (jmap) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jmod to provid e /usr/bin/jmod (jmod) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jps to provide /usr/bin/jps (jps) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jrunscript to provide /usr/bin/jrunscript (jrunscript) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jshell to prov ide /usr/bin/jshell (jshell) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jstack to prov ide /usr/bin/jstack (jstack) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jstat to provi de /usr/bin/jstat (jstat) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jstatd to prov ide /usr/bin/jstatd (jstatd) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/rmic to provid e /usr/bin/rmic (rmic) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/serialver to p rovide /usr/bin/serialver (serialver) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jaotc to provi de /usr/bin/jaotc (jaotc) in auto mode update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jhsdb to provi de /usr/bin/jhsdb (jhsdb) in auto mode Setting up libpthread-stubs0-dev:amd64 (0.4-1build2) ... Setting up xtrans-dev (1.4.0-1) ... Setting up default-jdk-headless (2:1.11-72build2) ... Setting up openjdk-11-jdk:amd64 (11.0.21+9-0ubuntu1~22.04) ... update-alternatives: using /usr/lib/jvm/java-11-openjdk-amd64/bin/jconsole to pr ovide /usr/bin/jconsole (jconsole) in auto mode Setting up xorg-sgml-doctools (1:1.11-1.1) ... Setting up default-jdk (2:1.11-72build2) ... Processing triggers for sgml-base (1.30) ... Setting up x11proto-dev (2021.5-1) ... Setting up libxau-dev:amd64 (1:1.0.9-1build5) ... Setting up libice-dev:amd64 (2:1.0.10-1build2) ... Setting up libsm-dev:amd64 (2:1.2.3-1build2) ... Processing triggers for man-db (2.10.2-1) ... Setting up libxdmcp-dev:amd64 (1:1.1.3-0ubuntu5) ... Setting up libxcb1-dev:amd64 (1.14-3ubuntu3) ... Setting up libx11-dev:amd64 (2:1.7.5-1ubuntu0.3) ... Setting up libxt-dev:amd64 (1:1.2.1-1) ...
- Download SQL Developer from here; and then install SQL Developer to the /opt directory on your Ubuntu local instance:
Use the following command to unzip the SQL Developer files to the /opt directory:
sudo unzip ~/Downloads/sqldeveloper-23.1.0.097.1607-no-jre.zip
- Create the following /usr/local/bin/sqldeveloper symbolic link:
sudo ln -s /opt/sqldeveloper/sqldeveloper.sh /usr/local/bin/sqldeveloper
- Edit the /opt/sqldeveloper/sqldeveloper.sh file by replacing the following line:
cd "`dirname $0`"/sqldeveloper/bin && bash sqldeveloper $*
with this version:
/opt/sqldeveloper/sqldeveloper/bin/sqldeveloper $*
- Now, you can launch SQL Developer from any location on your local Ubuntu operating system, like:
sqldeveloper
- You can now connect as the system user through SQL Developer to the Oracle Database 23ai Free Docker instance with the following connection information:
(Excuse recycling the version from 21c but I didn’t see any utility in making a new screen shot.)
- You can also create a Desktop shortcut by creating the sqldeveloper.desktop file in the /usr/share/applications directory. The SQL Developer icon is provided in the sqldeveloper base directory.
You should create the following sqldeveloper.desktop file to use a Desktop shortcut:
[Desktop Entry] Name=Oracle SQL Developer Comment=SQL Developer from Oracle GenericName=SQL Tool Exec=/usr/local/bin/sqldeveloper Icon=/opt/sqldeveloper/icon.png Type=Application StartupNotify=true Categories=Utility;Oracle;Development;SQL;
As always, I hope this helps those trying to accomplish this task.
Parametric Queries
In 2021, I wrote a MySQL example for my class on the usefulness of Common Table Expressions (CTEs). When discussing the original post, I would comment on how you could extend the last example to build a parametric reporting table.
Somebody finally asked for a concrete example. So, this explains how to build a sample MySQL parametric query by leveraging a filter cross join and tests the parameter use with a Python script.
You can build this in any database you prefer but I used a studentdb database with the sakila sample database installed. I’ve granted privileges to both databases to the student user. The following SQL is required for the example:
-- Conditionally drop the levels table. DROP TABLE IF EXISTS levels; -- Create the levels list. CREATE TABLE levels ( level_id int unsigned primary key auto_increment , parameter_set enum('Three','Five') , description varchar(20) , min_roles int , max_roles int ); -- Insert values into the list table. INSERT INTO levels ( parameter_set , description , min_roles , max_roles ) VALUES ('Three','Hollywood Star', 30, 99999) ,('Three','Prolific Actor', 20, 29) ,('Three','Newcommer',1,19) ,('Five','Newcommer',1,9) ,('Five','Junior Actor',10,19) ,('Five','Professional Actor',20,29) ,('Five','Major Actor',30,39) ,('Five','Hollywood Star',40,99999); |
The sample lets you use the three or five value labels while filtering on any partial full_name value as the result of the query below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- Query the data. WITH actors AS (SELECT a.actor_id , a.first_name , a.last_name , COUNT(*) AS num_roles FROM sakila.actor a INNER JOIN sakila.film_actor fa ON a.actor_id = fa.actor_id GROUP BY actor_id) SELECT CONCAT(a.last_name,', ',a.first_name) full_name , l.description , a.num_roles FROM actors a CROSS JOIN levels l WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles AND l.parameter_set = 'Five' AND a.last_name LIKE CONCAT('H','%') ORDER BY a.last_name , a.first_name; |
They extends a concept exercise found in Chapter 9 on subqueries in Alan Beaulieu’s Learning SQL book.
This is the parametric Python program, which embeds the function locally (to make it easier for those who don’t write a lot of Python). You could set the PYTHONPATH to a relative src directory and import your function if you prefer.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | #!/usr/bin/python # Import the libraries. import sys import mysql.connector from mysql.connector import errorcode # ============================================================ # Define function to check and replace arguments. def check_replace(argv): # Set defaults for incorrect parameter values. defaults = ("Three","_") # Declare empty list variables. inputs = [] args = () # Check whether or not parameters exist after file name. if isinstance(argv,list) and len(argv) != 0: # Check whether there are at least two parameters. if len(argv) >= 2: # Loop through available command-line arguments. for element in argv: # Check first of two parameter values and substitute # default value if input value is an invalid option. if len(inputs) == 0 and (element in ('Three','Five')) or \ len(inputs) == 1 and (isinstance(element,str)): inputs.append(element) elif len(inputs) == 0: inputs.append(defaults[0]) elif len(inputs) == 1: inputs.append(defaults[1]) # Assign arguments to parameters. args = (inputs) # Check whether only one parameter value exists. elif len(argv) == 1 and (argv[0] in ('Three','Five')): args = (argv[0],"_") # Assume only one parameter is valid and substitute an # empty string as the second parameter. else: args = (defaults[0],"_") # Substitute defaults when missing parameters. else: args = defaults # Return parameters as a tuple. return args # ============================================================ # Assign command-line argument list to variable by removing # the program file name. # ============================================================ params = check_replace(sys.argv[1:]) # ============================================================ # Attempt the query. # ============================================================ # Use a try-catch block to manage the connection. # ============================================================ try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("WITH actors AS " "(SELECT a.first_name " " , a.last_name " " , COUNT(*) AS num_roles " " FROM sakila.actor a INNER JOIN sakila.film_actor fa " " ON a.actor_id = fa.actor_id " " GROUP BY a.first_name " " , a.last_name ) " " SELECT CONCAT(a.last_name,', ',a.first_name) AS full_name " " , l.description " " , a.num_roles " " FROM actors a CROSS JOIN levels l " " WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles " " AND l.parameter_set = %s " " AND a.last_name LIKE CONCAT(%s,'%') " " ORDER BY a.last_name " " , a.first_name") # Execute cursor. cursor.execute(query, params) # Display the rows returned by the query. for (full_name, description, num_roles) in cursor: print('{0} is a {1} with {2} films.'.format( full_name.title() , description.title() , num_roles)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. 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 the connection when the try block completes. else: cnx.close() |
As always, I hope this helps those trying to understand how CTEs can solve problems that would otherwise be coded in external imperative languages like Python.
Oracle 23c Free Ext Files
This is an example of how you would upload data from a flat file, or Comma Separated Value (CSV) file inside Docker Oracle Database 23c Free. It’s important to note that in the file upload you are transferring information that doesn’t have surrogate key values by leveraing joins inside a MERGE statement.
Step #1 : Create a virtual directory
You can create a virtual directory without a physical directory but it won’t work when you try to access it. Therefore, you should create the physical directory first. Assuming you’ve created the Docker Oracle Database 23c Free instance, you should put the code in subdirectories of the /opt/oracle file directory.
- Connect as the root user with the following Docker command:
docker exec -it --user root oracle23c bash
Issue the following commands as the oracle user inside the Docker container to create the necessary physical directories. You may need to refer to my earlier blog post if you haven’t setup the oracle user inside the Docker instance. While this blog post will only use the /opt/oracle/upload/text and /opt/oracle/upload/log directories, a subsequent post will demonstrate the preprocessing module for the external tables.
mkdir /opt/oracle/upload mkdir /opt/oracle/upload/text mkdir /opt/oracle/upload/log mkdir /opt/oracle/upload/preproc
- Connect to the Oracle Database 23c Free inside the container as the system user to create a c##studentrole, and do the following three things:
- Grant privileges to the c##studentrole, and grant the c##studentrole to the c##student user.
-- Create the role. CREATE ROLE c##studentrole; -- Grant privileges to the role. GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW TO c##studentrole; -- Grant privileges to the user. GRANT c##studentrole TO c##student;
- As the system user, create the necessary virtual directories that map to the physical directories inside the Docker container:
CREATE DIRECTORY upload AS '/opt/oracle/upload/text'; CREATE DIRECTORY preproc AS '/opt/oracle/upload/preproc'; CREATE DIRECTORY LOG AS '/opt/oracle/upload/log';
- As the system user, grant the necessary privileges on the virtual directories to the c##studentrole role:
GRANT read ON DIRECTORY upload TO c##studentrole; GRANT read, WRITE ON DIRECTORY LOG TO c##studentrole; GRANT read, EXECUTE ON DIRECTORY preproc TO c##studentrole;
- Grant privileges to the c##studentrole, and grant the c##studentrole to the c##student user.
Step #2 : Position your CSV file in the physical directory
After creating the virtual directory, copy the following contents into a file named kingdom_import.csv in the /opt/oracle/upload/texgt directory or folder. If you attempt to do this in Windows, you need to disable Windows UAC before performing this step.
Place the following in the kingdom_import.csv file. The trailing commas aren’t too meaningful in Oracle but they’re very helpful if you use the file in MySQL. A key element in creating this files requires that you avoid trailing line returns at the bottom of the file because they’re inserted as null values. There should be no lines after the last row of data.
'Narnia',77600,'Peter the Magnificent','20-MAR-1272','19-JUN-1292','The Lion, The Witch and The Wardrobe', 'Narnia',77600,'Edmund the Just','20-MAR-1272','19-JUN-1292','The Lion, The Witch and The Wardrobe', 'Narnia',77600,'Susan the Gentle','20-MAR-1272','19-JUN-1292','The Lion, The Witch and The Wardrobe', 'Narnia',77600,'Lucy the Valiant','20-MAR-1272','19-JUN-1292','The Lion, The Witch and The Wardrobe', 'Narnia',42100,'Peter the Magnificent','12-APR-1531','31-MAY-1328','Prince Caspian', 'Narnia',42100,'Edmund the Just','12-APR-1531','31-MAY-1328','Prince Caspian', 'Narnia',42100,'Susan the Gentle','12-APR-1531','31-MAY-1328','Prince Caspian', 'Narnia',42100,'Lucy the Valiant','12-APR-1531','31-MAY-1328','Prince Caspian', 'Camelot',15200,'King Arthur','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Lionel','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Bors','10-MAR-0631','12-DEC-0635','The Once and Future King', 'Camelot',15200,'Sir Bors','10-MAR-0640','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Galahad','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Gawain','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Tristram','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Percival','10-MAR-0631','12-DEC-0686','The Once and Future King', 'Camelot',15200,'Sir Lancelot','30-SEP-0670','12-DEC-0686','The Once and Future King', |
Step #3 : Reconnect as the student user
Disconnect and connect as the c##student user, or reconnect as the c##student user. The reconnect syntax that protects your password is:
CONNECT c##student@free |
Step #4 : Run the script that creates tables and sequences
Copy the following into a create_kingdom_upload.sql file within a directory of your choice. I use varchar as the data type because it’s an alias for varchar2 and highlights appropriately with the GeSHi formatting. Then, run it as the student account.
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 | -- Conditionally drop tables. DROP TABLE IF EXISTS kingdom; DROP TABLE IF EXISTS knight; DROP TABLE IF EXISTS kingdom_knight_import; -- Conditionally drop sequences. DROP SEQUENCE IF EXISTS kingdom_s1; DROP SEQUENCE IF EXISTS knight_s1; -- Create normalized kingdom table. CREATE TABLE kingdom ( kingdom_id NUMBER , kingdom_name VARCHAR(20) , population NUMBER , book VARCHAR(40)); -- Create a sequence for the kingdom table. CREATE SEQUENCE kingdom_s1; -- Create normalized knight table. CREATE TABLE knight ( knight_id NUMBER , knight_name VARCHAR(22) , kingdom_allegiance_id NUMBER , allegiance_start_date DATE , allegiance_end_date DATE , book VARCHAR(40)); -- Create a sequence for the knight table. CREATE SEQUENCE knight_s1; -- Create external import table. CREATE TABLE kingdom_knight_import ( kingdom_name VARCHAR(20) , population NUMBER , knight_name VARCHAR(22) , allegiance_start_date DATE , allegiance_end_date DATE , book VARCHAR(40)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY upload ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'LOG':'kingdom_import.bad' DISCARDFILE 'LOG':'kingdom_import.dis' LOGFILE 'LOG':'kingdom_import.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('kingdom_import.csv')) REJECT LIMIT UNLIMITED; |
Step #5 : Test your access to the external table
There a number of things that could go wrong with setting up an external table, such as file permissions. Before moving on to the balance of the steps, you should test what you’ve done. Run the following query from the student account to check whether or not you can access the kingdom_import.csv file.
1 2 3 4 5 6 7 8 9 10 11 12 | SET PAGESIZE 999 COL kingdom_name FORMAT A7 HEADING "Kingdom|Name" COL folks FORMAT 99999 HEADING "Folks" COL knight_name FORMAT A21 HEADING "Knight Name" COL dates FORMAT A11 HEADING "Start Date" COL source_book FORMAT A38 HEADING "Book" SELECT kingdom_name , knight_name , TO_CHAR(allegiance_start_date,'DD-MON-YYYY') || TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS dates , book FROM kingdom_knight_import; |
Step #6 : Create the upload procedure
Copy the following into a create_upload_procedure.sql file within a virtual directory of your choice. As noted above in the external table definition writes only occur in the log virtual directory. This is important because there are articles out there on the Internet that could misdirect you when you get the following error message on the upload virtual directory.
ORA-06564: Object UPLOAD does not exist or is not accessible to the user. |
By the way, you’ll only see that error if you fail to:
- Designate the procedure as AUTH_ID CURRENT, and
- Enabled SERVEROUTPUT inside the SQL*Plus command-line interface (CLI) session or inside the glogin.sql file for the Oracle Database 23c Free Docker instance.
Then, run it as the student account.
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 | -- Create a procedure to wrap the transaction. CREATE OR REPLACE PROCEDURE upload_kingdom AUTHID CURRENT_USER IS BEGIN -- Set save point for an all or nothing transaction. SAVEPOINT starting_point; -- Insert or update the table, which makes this rerunnable when the file hasn't been updated. MERGE INTO kingdom target USING (SELECT DISTINCT k.kingdom_id , kki.kingdom_name , kki.population , kki.book FROM kingdom_knight_import kki LEFT JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population AND kki.book = k.book) SOURCE ON (target.kingdom_id = SOURCE.kingdom_id) WHEN MATCHED THEN UPDATE SET kingdom_name = SOURCE.kingdom_name WHEN NOT MATCHED THEN INSERT VALUES ( kingdom_s1.nextval , SOURCE.kingdom_name , SOURCE.population , SOURCE.book); -- Insert or update the table, which makes this rerunnable when the file hasn't been updated. MERGE INTO knight target USING (SELECT kn.knight_id , kki.knight_name , k.kingdom_id , kki.allegiance_start_date AS start_date , kki.allegiance_end_date AS end_date , kki.book FROM kingdom_knight_import kki INNER JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population LEFT JOIN knight kn ON k.kingdom_id = kn.kingdom_allegiance_id AND kki.knight_name = kn.knight_name AND kki.allegiance_start_date = kn.allegiance_start_date AND kki.allegiance_end_date = kn.allegiance_end_date AND kki.book = kn.book) SOURCE ON (target.kingdom_allegiance_id = SOURCE.kingdom_id) WHEN MATCHED THEN UPDATE SET allegiance_start_date = SOURCE.start_date , allegiance_end_date = SOURCE.end_date , book = SOURCE.book WHEN NOT MATCHED THEN INSERT VALUES ( knight_s1.nextval , SOURCE.knight_name , SOURCE.kingdom_id , SOURCE.start_date , SOURCE.end_date , SOURCE.book); -- Save the changes. COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); ROLLBACK TO starting_point; RETURN; END; / |
Step #7 : Run the upload procedure
You can run the file by calling the script above. The procedure ensures that records are inserted or updated into their respective tables.
EXECUTE upload_kingdom; |
Step #8 : Test the results of the upload procedure
You can test whether or not it worked by running the following queries.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- Format Oracle output. COLUMN kingdom_id FORMAT 999 HEADING "Kingdom|ID #" COLUMN kingdom_name FORMAT A14 HEADING "Kingdom|Name" COLUMN population FORMAT 999,999 HEADING "Population" COLUMN book FORMAT A40 HEADING "Source Book" -- Check the kingdom table. SELECT * FROM kingdom; -- Format Oracle output. SET PAGESIZE 999 COLUMN knight_id FORMAT 999 HEADING "Knight|ID #" COLUMN knight_name FORMAT A23 HEADING "Knight|Name" COLUMN kingdom_allegiance_id FORMAT 999 HEADING "Kingdom|ID #" COLUMN allegiance_start_date FORMAT A11 HEADING "Allegiance|Start Date" COLUMN allegiance_end_date FORMAT A11 HEADING "Allegiance|End Date" -- Check the knight table. SELECT knight_id , knight_name , kingdom_allegiance_id , TO_CHAR(allegiance_start_date,'DD-MON-YYYY') AS allegiance_start_date , TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS allegiance_end_date FROM knight; |
It should display the following information:
Kingdom Kingdom
ID # Name Population Source Book
------- -------------- ---------- ----------------------------------------
1 Narnia 42,100 Prince Caspian
2 Narnia 77,600 The Lion, The Witch and The Wardrobe
3 Camelot 15,200 The Once and Future King
Knight Knight Kingdom Allegiance Allegiance
ID # Name ID # Start Date End Date
------ ----------------------- ------- ----------- -----------
1 Peter the Magnificent 2 20-MAR-1272 19-JUN-1292
2 Edmund the Just 2 20-MAR-1272 19-JUN-1292
3 Susan the Gentle 2 20-MAR-1272 19-JUN-1292
4 Lucy the Valiant 2 20-MAR-1272 19-JUN-1292
5 Peter the Magnificent 1 12-APR-1531 31-MAY-1328
6 Edmund the Just 1 12-APR-1531 31-MAY-1328
7 Susan the Gentle 1 12-APR-1531 31-MAY-1328
8 Lucy the Valiant 1 12-APR-1531 31-MAY-1328
9 King Arthur 3 10-MAR-0631 12-DEC-0686
10 Sir Lionel 3 10-MAR-0631 12-DEC-0686
11 Sir Bors 3 10-MAR-0631 12-DEC-0635
12 Sir Bors 3 10-MAR-0640 12-DEC-0686
13 Sir Galahad 3 10-MAR-0631 12-DEC-0686
14 Sir Gawain 3 10-MAR-0631 12-DEC-0686
15 Sir Tristram 3 10-MAR-0631 12-DEC-0686
16 Sir Percival 3 10-MAR-0631 12-DEC-0686
17 Sir Lancelot 3 30-SEP-0670 12-DEC-0686 |
You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.
Oracle 23c Free SQL*Plus
It’s always frustrated me when using the sqlplus command-line interface (CLI) that you can’t just “up arrow” to through the history. At least, that’s the default case unless you wrap the sqlplus executable.
I like to do my development work as close to the database as possible. The delay from SQL Developer to the database or VSCode to the database is just too long. Therefore, I like the native sqlplus to be as efficient as possible. This post shows you how to install the rlwarp utility to wrap sqlplus and create a sandboxed student user for a local development account inside the Oracle 23c Free container. You should note that the Docker or Podman Container is using Oracle Unbreakable Linux 8 as it’s native OS.
You can connect to your Docker version of Oracle Database 23c Free with the following command:
docker exec -it -u root oracle23c bash |
You can’t just use dnf to install rlwrap and get it to magically install all the dependencies. That would be too easy, eh?
Attempting to do so will lock your base OS and eventually force you to kill with prejudice the hung dnf process (at least it forced me to do so). You need to determine the rlwrap dependencies and then install them first. In that process, I noticed that the which utility program wasn’t installed in the container.
Naturally, I installed the which utility first with this command:
dnf install -y which |
Display detailed console log →
Last metadata expiration check: 0:26:00 ago on Thu Dec 21 05:18:09 2023. Dependencies resolved. ================================================================================ Package Architecture Version Repository Size ================================================================================ Installing: which x86_64 2.21-20.el8 ol8_baseos_latest 50 k Transaction Summary ================================================================================ Install 1 Package Total download size: 50 k Installed size: 81 k Downloading Packages: which-2.21-20.el8.x86_64.rpm 80 kB/s | 50 kB 00:00 -------------------------------------------------------------------------------- Total 80 kB/s | 50 kB 00:00 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : which-2.21-20.el8.x86_64 1/1 Running scriptlet: which-2.21-20.el8.x86_64 1/1 Verifying : which-2.21-20.el8.x86_64 1/1 Installed: which-2.21-20.el8.x86_64 Complete! |
The rlwrap dependencies are: glibc, ncurses, perl, readline, python, and git. Only the perl, python, and git are missing from the list of formal dependencies but there’s another dependency the epel-release package.
If you want to verify whether a package is installed, you can use the rpm command like this:
rpm -qa | grep package_name |
I installed the perl programming environment (a big install) with this command:
dnf install -y perl |
Display detailed console log →
Last metadata expiration check: 0:28:29 ago on Thu Dec 21 05:18:09 2023.
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
perl x86_64 4:5.26.3-422.el8 ol8_appstream 73 k
Installing dependencies:
dwz x86_64 0.12-10.el8 ol8_appstream 109 k
efi-srpm-macros noarch 3-3.0.1.el8 ol8_appstream 22 k
file x86_64 5.33-24.el8 ol8_baseos_latest 77 k
ghc-srpm-macros noarch 1.4.2-7.el8 ol8_appstream 9.3 k
glibc-gconv-extra x86_64 2.28-225.0.3.el8 ol8_baseos_latest 1.5 M
go-srpm-macros noarch 2-17.el8 ol8_appstream 13 k
groff-base x86_64 1.22.3-18.el8 ol8_baseos_latest 1.0 M
ocaml-srpm-macros noarch 5-4.el8 ol8_appstream 9.3 k
openblas-srpm-macros noarch 2-2.el8 ol8_appstream 7.9 k
perl-Algorithm-Diff noarch 1.1903-9.el8 ol8_baseos_latest 52 k
perl-Archive-Tar noarch 2.30-1.el8 ol8_baseos_latest 79 k
perl-Archive-Zip noarch 1.60-3.el8 ol8_appstream 108 k
perl-Attribute-Handlers noarch 0.99-422.el8 ol8_appstream 89 k
perl-B-Debug noarch 1.26-2.el8 ol8_appstream 26 k
perl-CPAN noarch 2.18-397.el8 ol8_appstream 554 k
perl-CPAN-Meta noarch 2.150010-396.el8 ol8_appstream 191 k
perl-CPAN-Meta-Requirements noarch 2.140-396.el8 ol8_appstream 37 k
perl-CPAN-Meta-YAML noarch 0.018-397.el8 ol8_appstream 34 k
perl-Carp noarch 1.42-396.el8 ol8_baseos_latest 30 k
perl-Compress-Bzip2 x86_64 2.26-6.el8 ol8_appstream 72 k
perl-Compress-Raw-Bzip2 x86_64 2.081-1.el8 ol8_baseos_latest 40 k
perl-Compress-Raw-Zlib x86_64 2.081-1.el8 ol8_baseos_latest 68 k
perl-Config-Perl-V noarch 0.30-1.el8 ol8_appstream 22 k
perl-DB_File x86_64 1.842-1.el8 ol8_appstream 83 k
perl-Data-Dumper x86_64 2.167-399.el8 ol8_baseos_latest 58 k
perl-Data-OptList noarch 0.110-6.el8 ol8_appstream 31 k
perl-Data-Section noarch 0.200007-3.el8 ol8_appstream 30 k
perl-Devel-PPPort x86_64 3.36-5.el8 ol8_appstream 118 k
perl-Devel-Peek x86_64 1.26-422.el8 ol8_appstream 94 k
perl-Devel-SelfStubber noarch 1.06-422.el8 ol8_appstream 76 k
perl-Devel-Size x86_64 0.81-2.el8 ol8_appstream 34 k
perl-Digest noarch 1.17-395.el8 ol8_baseos_latest 27 k
perl-Digest-MD5 x86_64 2.55-396.el8 ol8_baseos_latest 37 k
perl-Digest-SHA x86_64 1:6.02-1.el8 ol8_appstream 66 k
perl-Encode x86_64 4:2.97-3.el8 ol8_baseos_latest 1.5 M
perl-Encode-devel x86_64 4:2.97-3.el8 ol8_appstream 39 k
perl-Env noarch 1.04-395.el8 ol8_appstream 21 k
perl-Errno x86_64 1.28-422.el8 ol8_baseos_latest 76 k
perl-Exporter noarch 5.72-396.el8 ol8_baseos_latest 34 k
perl-ExtUtils-CBuilder noarch 1:0.280230-2.el8 ol8_appstream 48 k
perl-ExtUtils-Command noarch 1:7.34-1.el8 ol8_appstream 19 k
perl-ExtUtils-Embed noarch 1.34-422.el8 ol8_appstream 79 k
perl-ExtUtils-Install noarch 2.14-4.el8 ol8_appstream 46 k
perl-ExtUtils-MM-Utils noarch 1:7.34-1.el8 ol8_appstream 16 k
perl-ExtUtils-MakeMaker noarch 1:7.34-1.el8 ol8_appstream 300 k
perl-ExtUtils-Manifest noarch 1.70-395.el8 ol8_appstream 36 k
perl-ExtUtils-Miniperl noarch 1.06-422.el8 ol8_appstream 77 k
perl-ExtUtils-ParseXS noarch 1:3.35-2.el8 ol8_appstream 83 k
perl-File-Fetch noarch 0.56-2.el8 ol8_appstream 33 k
perl-File-HomeDir noarch 1.002-4.el8 ol8_appstream 61 k
perl-File-Path noarch 2.15-2.el8 ol8_baseos_latest 38 k
perl-File-Temp noarch 0.230.600-1.el8 ol8_baseos_latest 63 k
perl-File-Which noarch 1.22-2.el8 ol8_appstream 23 k
perl-Filter x86_64 2:1.58-2.el8 ol8_appstream 82 k
perl-Filter-Simple noarch 0.94-2.el8 ol8_appstream 29 k
perl-Getopt-Long noarch 1:2.50-4.el8 ol8_baseos_latest 63 k
perl-HTTP-Tiny noarch 0.074-2.el8 ol8_baseos_latest 57 k
perl-IO x86_64 1.38-422.el8 ol8_baseos_latest 142 k
perl-IO-Compress noarch 2.081-1.el8 ol8_baseos_latest 258 k
perl-IO-Socket-IP noarch 0.39-5.el8 ol8_baseos_latest 47 k
perl-IO-Socket-SSL noarch 2.066-4.module+el8.6.0+20623+f0897f98
ol8_appstream 298 k
perl-IO-Zlib noarch 1:1.10-422.el8 ol8_baseos_latest 81 k
perl-IPC-Cmd noarch 2:1.02-1.el8 ol8_appstream 43 k
perl-IPC-SysV x86_64 2.07-397.el8 ol8_appstream 43 k
perl-IPC-System-Simple noarch 1.25-17.el8 ol8_appstream 43 k
perl-JSON-PP noarch 1:2.97.001-3.el8 ol8_appstream 68 k
perl-Locale-Codes noarch 3.57-1.el8 ol8_appstream 310 k
perl-Locale-Maketext noarch 1.28-396.el8 ol8_appstream 99 k
perl-Locale-Maketext-Simple noarch 1:0.21-422.el8 ol8_appstream 79 k
perl-MIME-Base64 x86_64 3.15-396.el8 ol8_baseos_latest 31 k
perl-MRO-Compat noarch 0.13-4.el8 ol8_appstream 24 k
perl-Math-BigInt noarch 1:1.9998.11-7.el8 ol8_baseos_latest 196 k
perl-Math-BigInt-FastCalc x86_64 0.500.600-6.el8 ol8_appstream 27 k
perl-Math-BigRat noarch 0.2614-1.el8 ol8_appstream 40 k
perl-Math-Complex noarch 1.59-422.el8 ol8_baseos_latest 109 k
perl-Memoize noarch 1.03-422.el8 ol8_appstream 119 k
perl-Module-Build noarch 2:0.42.24-5.el8 ol8_appstream 273 k
perl-Module-CoreList noarch 1:5.20181130-1.el8 ol8_appstream 87 k
perl-Module-CoreList-tools noarch 1:5.20181130-1.el8 ol8_appstream 22 k
perl-Module-Load noarch 1:0.32-395.el8 ol8_appstream 19 k
perl-Module-Load-Conditional noarch 0.68-395.el8 ol8_appstream 24 k
perl-Module-Loaded noarch 1:0.08-422.el8 ol8_appstream 75 k
perl-Module-Metadata noarch 1.000033-395.el8 ol8_appstream 44 k
perl-Mozilla-CA noarch 20160104-7.0.1.module+el8.3.0+21136+b437fca9
ol8_appstream 15 k
perl-Net-Ping noarch 2.55-422.el8 ol8_appstream 102 k
perl-Net-SSLeay x86_64 1.88-2.module+el8.6.0+20623+f0897f98
ol8_appstream 379 k
perl-Package-Generator noarch 1.106-11.el8 ol8_appstream 27 k
perl-Params-Check noarch 1:0.38-395.el8 ol8_appstream 24 k
perl-Params-Util x86_64 1.07-22.el8 ol8_appstream 44 k
perl-PathTools x86_64 3.74-1.el8 ol8_baseos_latest 90 k
perl-Perl-OSType noarch 1.010-396.el8 ol8_appstream 29 k
perl-PerlIO-via-QuotedPrint noarch 0.08-395.el8 ol8_appstream 13 k
perl-Pod-Checker noarch 4:1.73-395.el8 ol8_appstream 33 k
perl-Pod-Escapes noarch 1:1.07-395.el8 ol8_baseos_latest 20 k
perl-Pod-Html noarch 1.22.02-422.el8 ol8_appstream 88 k
perl-Pod-Parser noarch 1.63-396.el8 ol8_appstream 108 k
perl-Pod-Perldoc noarch 3.28-396.el8 ol8_baseos_latest 88 k
perl-Pod-Simple noarch 1:3.35-395.el8 ol8_baseos_latest 213 k
perl-Pod-Usage noarch 4:1.69-395.el8 ol8_baseos_latest 34 k
perl-Scalar-List-Utils x86_64 3:1.49-2.el8 ol8_baseos_latest 68 k
perl-SelfLoader noarch 1.23-422.el8 ol8_appstream 83 k
perl-Socket x86_64 4:2.027-3.el8 ol8_baseos_latest 59 k
perl-Software-License noarch 0.103013-2.el8 ol8_appstream 137 k
perl-Storable x86_64 1:3.11-3.el8 ol8_baseos_latest 98 k
perl-Sub-Exporter noarch 0.987-15.el8 ol8_appstream 73 k
perl-Sub-Install noarch 0.928-14.el8 ol8_appstream 27 k
perl-Sys-Syslog x86_64 0.35-397.el8 ol8_appstream 50 k
perl-Term-ANSIColor noarch 4.06-396.el8 ol8_baseos_latest 46 k
perl-Term-Cap noarch 1.17-395.el8 ol8_baseos_latest 23 k
perl-Test noarch 1.30-422.el8 ol8_appstream 90 k
perl-Test-Harness noarch 1:3.42-1.el8 ol8_appstream 279 k
perl-Test-Simple noarch 1:1.302135-1.el8 ol8_appstream 516 k
perl-Text-Balanced noarch 2.03-395.el8 ol8_appstream 58 k
perl-Text-Diff noarch 1.45-2.el8 ol8_baseos_latest 45 k
perl-Text-Glob noarch 0.11-4.el8 ol8_appstream 17 k
perl-Text-ParseWords noarch 3.30-395.el8 ol8_baseos_latest 18 k
perl-Text-Tabs+Wrap noarch 2013.0523-395.el8 ol8_baseos_latest 24 k
perl-Text-Template noarch 1.51-1.el8 ol8_appstream 64 k
perl-Thread-Queue noarch 3.13-1.el8 ol8_appstream 24 k
perl-Time-HiRes x86_64 4:1.9758-2.el8 ol8_appstream 61 k
perl-Time-Local noarch 1:1.280-1.el8 ol8_baseos_latest 33 k
perl-Time-Piece x86_64 1.31-422.el8 ol8_appstream 98 k
perl-URI noarch 1.73-3.el8 ol8_baseos_latest 116 k
perl-Unicode-Collate x86_64 1.25-2.el8 ol8_appstream 686 k
perl-Unicode-Normalize x86_64 1.25-396.el8 ol8_baseos_latest 82 k
perl-autodie noarch 2.29-396.el8 ol8_appstream 98 k
perl-bignum noarch 0.49-2.el8 ol8_appstream 43 k
perl-constant noarch 1.33-396.el8 ol8_baseos_latest 25 k
perl-devel x86_64 4:5.26.3-422.el8 ol8_appstream 600 k
perl-encoding x86_64 4:2.22-3.el8 ol8_appstream 68 k
perl-experimental noarch 0.019-2.el8 ol8_appstream 24 k
perl-inc-latest noarch 2:0.500-9.el8 ol8_appstream 25 k
perl-interpreter x86_64 4:5.26.3-422.el8 ol8_baseos_latest 6.3 M
perl-libnet noarch 3.11-3.el8 ol8_baseos_latest 121 k
perl-libnetcfg noarch 4:5.26.3-422.el8 ol8_appstream 78 k
perl-libs x86_64 4:5.26.3-422.el8 ol8_baseos_latest 1.6 M
perl-local-lib noarch 2.000024-2.el8 ol8_appstream 74 k
perl-macros x86_64 4:5.26.3-422.el8 ol8_baseos_latest 72 k
perl-open noarch 1.11-422.el8 ol8_appstream 78 k
perl-parent noarch 1:0.237-1.el8 ol8_baseos_latest 20 k
perl-perlfaq noarch 5.20180605-1.el8 ol8_appstream 386 k
perl-podlators noarch 4.11-1.el8 ol8_baseos_latest 118 k
perl-srpm-macros noarch 1-25.el8 ol8_appstream 11 k
perl-threads x86_64 1:2.21-2.el8 ol8_baseos_latest 61 k
perl-threads-shared x86_64 1.58-2.el8 ol8_baseos_latest 48 k
perl-utils noarch 5.26.3-422.el8 ol8_appstream 129 k
perl-version x86_64 6:0.99.24-1.el8 ol8_appstream 67 k
python-rpm-macros noarch 3-45.el8 ol8_appstream 16 k
python-srpm-macros noarch 3-45.el8 ol8_appstream 16 k
python3-pyparsing noarch 2.1.10-7.el8 ol8_baseos_latest 142 k
python3-rpm-macros noarch 3-45.el8 ol8_appstream 15 k
qt5-srpm-macros noarch 5.15.3-1.el8 ol8_appstream 11 k
redhat-rpm-config noarch 131-1.0.1.el8 ol8_appstream 91 k
rust-srpm-macros noarch 5-2.el8 ol8_appstream 9.2 k
systemtap-sdt-devel x86_64 4.9-3.0.1.el8 ol8_appstream 88 k
zip x86_64 3.0-23.el8 ol8_baseos_latest 270 k
Installing weak dependencies:
perl-Encode-Locale noarch 1.05-10.module+el8.3.0+7692+542c56f9
ol8_appstream 22 k
perl-TermReadKey x86_64 2.37-7.el8 ol8_appstream 40 k
Enabling module streams:
perl 5.26
perl-IO-Socket-SSL 2.066
perl-libwww-perl 6.34
Transaction Summary
================================================================================
Install 159 Packages
Total download size: 25 M
Installed size: 73 M
Downloading Packages:
(1/159): file-5.33-24.el8.x86_64.rpm 163 kB/s | 77 kB 00:00
(2/159): perl-Algorithm-Diff-1.1903-9.el8.noarc 531 kB/s | 52 kB 00:00
(3/159): groff-base-1.22.3-18.el8.x86_64.rpm 1.5 MB/s | 1.0 MB 00:00
(4/159): perl-Archive-Tar-2.30-1.el8.noarch.rpm 642 kB/s | 79 kB 00:00
(5/159): perl-Carp-1.42-396.el8.noarch.rpm 449 kB/s | 30 kB 00:00
(6/159): perl-Compress-Raw-Bzip2-2.081-1.el8.x8 452 kB/s | 40 kB 00:00
(7/159): perl-Compress-Raw-Zlib-2.081-1.el8.x86 968 kB/s | 68 kB 00:00
(8/159): perl-Data-Dumper-2.167-399.el8.x86_64. 734 kB/s | 58 kB 00:00
(9/159): perl-Digest-1.17-395.el8.noarch.rpm 391 kB/s | 27 kB 00:00
(10/159): perl-Digest-MD5-2.55-396.el8.x86_64.r 481 kB/s | 37 kB 00:00
(11/159): perl-Errno-1.28-422.el8.x86_64.rpm 811 kB/s | 76 kB 00:00
(12/159): perl-Encode-2.97-3.el8.x86_64.rpm 9.4 MB/s | 1.5 MB 00:00
(13/159): perl-File-Path-2.15-2.el8.noarch.rpm 627 kB/s | 38 kB 00:00
(14/159): perl-Exporter-5.72-396.el8.noarch.rpm 466 kB/s | 34 kB 00:00
(15/159): perl-Getopt-Long-2.50-4.el8.noarch.rp 867 kB/s | 63 kB 00:00
(16/159): perl-File-Temp-0.230.600-1.el8.noarch 648 kB/s | 63 kB 00:00
(17/159): perl-HTTP-Tiny-0.074-2.el8.noarch.rpm 847 kB/s | 57 kB 00:00
(18/159): perl-IO-Compress-2.081-1.el8.noarch.r 3.5 MB/s | 258 kB 00:00
(19/159): perl-IO-1.38-422.el8.x86_64.rpm 1.2 MB/s | 142 kB 00:00
(20/159): perl-IO-Socket-IP-0.39-5.el8.noarch.r 614 kB/s | 47 kB 00:00
(21/159): perl-IO-Zlib-1.10-422.el8.noarch.rpm 881 kB/s | 81 kB 00:00
(22/159): perl-MIME-Base64-3.15-396.el8.x86_64. 425 kB/s | 31 kB 00:00
(23/159): perl-Math-BigInt-1.9998.11-7.el8.noar 1.5 MB/s | 196 kB 00:00
(24/159): perl-Math-Complex-1.59-422.el8.noarch 1.5 MB/s | 109 kB 00:00
(25/159): perl-Pod-Escapes-1.07-395.el8.noarch. 300 kB/s | 20 kB 00:00
(26/159): perl-PathTools-3.74-1.el8.x86_64.rpm 1.2 MB/s | 90 kB 00:00
(27/159): perl-Pod-Perldoc-3.28-396.el8.noarch. 1.2 MB/s | 88 kB 00:00
(28/159): perl-Pod-Simple-3.35-395.el8.noarch.r 2.2 MB/s | 213 kB 00:00
(29/159): perl-Pod-Usage-1.69-395.el8.noarch.rp 499 kB/s | 34 kB 00:00
(30/159): perl-Scalar-List-Utils-1.49-2.el8.x86 947 kB/s | 68 kB 00:00
(31/159): perl-Socket-2.027-3.el8.x86_64.rpm 864 kB/s | 59 kB 00:00
(32/159): perl-Storable-3.11-3.el8.x86_64.rpm 1.2 MB/s | 98 kB 00:00
(33/159): perl-Term-ANSIColor-4.06-396.el8.noar 677 kB/s | 46 kB 00:00
(34/159): perl-Term-Cap-1.17-395.el8.noarch.rpm 321 kB/s | 23 kB 00:00
(35/159): perl-Text-Diff-1.45-2.el8.noarch.rpm 596 kB/s | 45 kB 00:00
(36/159): perl-Text-ParseWords-3.30-395.el8.noa 257 kB/s | 18 kB 00:00
(37/159): perl-Text-Tabs+Wrap-2013.0523-395.el8 351 kB/s | 24 kB 00:00
(38/159): perl-Time-Local-1.280-1.el8.noarch.rp 440 kB/s | 33 kB 00:00
(39/159): perl-URI-1.73-3.el8.noarch.rpm 1.6 MB/s | 116 kB 00:00
(40/159): perl-Unicode-Normalize-1.25-396.el8.x 1.1 MB/s | 82 kB 00:00
(41/159): perl-constant-1.33-396.el8.noarch.rpm 395 kB/s | 25 kB 00:00
(42/159): perl-libnet-3.11-3.el8.noarch.rpm 1.8 MB/s | 121 kB 00:00
(43/159): perl-libs-5.26.3-422.el8.x86_64.rpm 13 MB/s | 1.6 MB 00:00
(44/159): perl-macros-5.26.3-422.el8.x86_64.rpm 1.1 MB/s | 72 kB 00:00
(45/159): perl-parent-0.237-1.el8.noarch.rpm 279 kB/s | 20 kB 00:00
(46/159): perl-podlators-4.11-1.el8.noarch.rpm 1.3 MB/s | 118 kB 00:00
(47/159): perl-interpreter-5.26.3-422.el8.x86_6 14 MB/s | 6.3 MB 00:00
(48/159): glibc-gconv-extra-2.28-225.0.3.el8.x8 601 kB/s | 1.5 MB 00:02
(49/159): perl-threads-2.21-2.el8.x86_64.rpm 876 kB/s | 61 kB 00:00
(50/159): perl-threads-shared-1.58-2.el8.x86_64 657 kB/s | 48 kB 00:00
(51/159): python3-pyparsing-2.1.10-7.el8.noarch 2.0 MB/s | 142 kB 00:00
(52/159): zip-3.0-23.el8.x86_64.rpm 3.7 MB/s | 270 kB 00:00
(53/159): dwz-0.12-10.el8.x86_64.rpm 1.6 MB/s | 109 kB 00:00
(54/159): efi-srpm-macros-3-3.0.1.el8.noarch.rp 350 kB/s | 22 kB 00:00
(55/159): ghc-srpm-macros-1.4.2-7.el8.noarch.rp 125 kB/s | 9.3 kB 00:00
(56/159): go-srpm-macros-2-17.el8.noarch.rpm 198 kB/s | 13 kB 00:00
(57/159): ocaml-srpm-macros-5-4.el8.noarch.rpm 154 kB/s | 9.3 kB 00:00
(58/159): openblas-srpm-macros-2-2.el8.noarch.r 116 kB/s | 7.9 kB 00:00
(59/159): perl-5.26.3-422.el8.x86_64.rpm 921 kB/s | 73 kB 00:00
(60/159): perl-Archive-Zip-1.60-3.el8.noarch.rp 1.4 MB/s | 108 kB 00:00
(61/159): perl-Attribute-Handlers-0.99-422.el8. 1.2 MB/s | 89 kB 00:00
(62/159): perl-B-Debug-1.26-2.el8.noarch.rpm 356 kB/s | 26 kB 00:00
(63/159): perl-CPAN-2.18-397.el8.noarch.rpm 5.3 MB/s | 554 kB 00:00
(64/159): perl-CPAN-Meta-2.150010-396.el8.noarc 2.3 MB/s | 191 kB 00:00
(65/159): perl-CPAN-Meta-Requirements-2.140-396 512 kB/s | 37 kB 00:00
(66/159): perl-CPAN-Meta-YAML-0.018-397.el8.noa 508 kB/s | 34 kB 00:00
(67/159): perl-Compress-Bzip2-2.26-6.el8.x86_64 990 kB/s | 72 kB 00:00
(68/159): perl-Config-Perl-V-0.30-1.el8.noarch. 337 kB/s | 22 kB 00:00
(69/159): perl-DB_File-1.842-1.el8.x86_64.rpm 1.2 MB/s | 83 kB 00:00
(70/159): perl-Data-OptList-0.110-6.el8.noarch. 457 kB/s | 31 kB 00:00
(71/159): perl-Data-Section-0.200007-3.el8.noar 423 kB/s | 30 kB 00:00
(72/159): perl-Devel-PPPort-3.36-5.el8.x86_64.r 1.6 MB/s | 118 kB 00:00
(73/159): perl-Devel-Peek-1.26-422.el8.x86_64.r 960 kB/s | 94 kB 00:00
(74/159): perl-Devel-SelfStubber-1.06-422.el8.n 831 kB/s | 76 kB 00:00
(75/159): perl-Devel-Size-0.81-2.el8.x86_64.rpm 510 kB/s | 34 kB 00:00
(76/159): perl-Digest-SHA-6.02-1.el8.x86_64.rpm 859 kB/s | 66 kB 00:00
(77/159): perl-Encode-Locale-1.05-10.module+el8 285 kB/s | 22 kB 00:00
(78/159): perl-Encode-devel-2.97-3.el8.x86_64.r 510 kB/s | 39 kB 00:00
(79/159): perl-Env-1.04-395.el8.noarch.rpm 321 kB/s | 21 kB 00:00
(80/159): perl-ExtUtils-CBuilder-0.280230-2.el8 730 kB/s | 48 kB 00:00
(81/159): perl-ExtUtils-Command-7.34-1.el8.noar 248 kB/s | 19 kB 00:00
(82/159): perl-ExtUtils-Embed-1.34-422.el8.noar 1.1 MB/s | 79 kB 00:00
(83/159): perl-ExtUtils-Install-2.14-4.el8.noar 661 kB/s | 46 kB 00:00
(84/159): perl-ExtUtils-MM-Utils-7.34-1.el8.noa 243 kB/s | 16 kB 00:00
(85/159): perl-ExtUtils-MakeMaker-7.34-1.el8.no 4.0 MB/s | 300 kB 00:00
(86/159): perl-ExtUtils-Manifest-1.70-395.el8.n 500 kB/s | 36 kB 00:00
(87/159): perl-ExtUtils-Miniperl-1.06-422.el8.n 1.1 MB/s | 77 kB 00:00
(88/159): perl-File-HomeDir-1.002-4.el8.noarch. 980 kB/s | 61 kB 00:00
(89/159): perl-File-Fetch-0.56-2.el8.noarch.rpm 483 kB/s | 33 kB 00:00
(90/159): perl-ExtUtils-ParseXS-3.35-2.el8.noar 1.1 MB/s | 83 kB 00:00
(91/159): perl-Filter-Simple-0.94-2.el8.noarch. 417 kB/s | 29 kB 00:00
(92/159): perl-File-Which-1.22-2.el8.noarch.rpm 312 kB/s | 23 kB 00:00
(93/159): perl-Filter-1.58-2.el8.x86_64.rpm 1.1 MB/s | 82 kB 00:00
(94/159): perl-IO-Socket-SSL-2.066-4.module+el8 3.6 MB/s | 298 kB 00:00
(95/159): perl-IPC-Cmd-1.02-1.el8.noarch.rpm 545 kB/s | 43 kB 00:00
(96/159): perl-IPC-SysV-2.07-397.el8.x86_64.rpm 544 kB/s | 43 kB 00:00
(97/159): perl-IPC-System-Simple-1.25-17.el8.no 535 kB/s | 43 kB 00:00
(98/159): perl-JSON-PP-2.97.001-3.el8.noarch.rp 853 kB/s | 68 kB 00:00
(99/159): perl-Locale-Codes-3.57-1.el8.noarch.r 3.7 MB/s | 310 kB 00:00
(100/159): perl-MRO-Compat-0.13-4.el8.noarch.rp 399 kB/s | 24 kB 00:00
(101/159): perl-Locale-Maketext-1.28-396.el8.no 1.4 MB/s | 99 kB 00:00
(102/159): perl-Locale-Maketext-Simple-0.21-422 1.1 MB/s | 79 kB 00:00
(103/159): perl-Math-BigInt-FastCalc-0.500.600- 371 kB/s | 27 kB 00:00
(104/159): perl-Math-BigRat-0.2614-1.el8.noarch 560 kB/s | 40 kB 00:00
(105/159): perl-Memoize-1.03-422.el8.noarch.rpm 1.6 MB/s | 119 kB 00:00
(106/159): perl-Module-Build-0.42.24-5.el8.noar 3.4 MB/s | 273 kB 00:00
(107/159): perl-Module-CoreList-tools-5.2018113 297 kB/s | 22 kB 00:00
(108/159): perl-Module-CoreList-5.20181130-1.el 1.1 MB/s | 87 kB 00:00
(109/159): perl-Module-Load-0.32-395.el8.noarch 242 kB/s | 19 kB 00:00
(110/159): perl-Module-Load-Conditional-0.68-39 316 kB/s | 24 kB 00:00
(111/159): perl-Module-Loaded-0.08-422.el8.noar 972 kB/s | 75 kB 00:00
(112/159): perl-Module-Metadata-1.000033-395.el 664 kB/s | 44 kB 00:00
(113/159): perl-Mozilla-CA-20160104-7.0.1.modul 229 kB/s | 15 kB 00:00
(114/159): perl-Net-Ping-2.55-422.el8.noarch.rp 1.5 MB/s | 102 kB 00:00
(115/159): perl-Package-Generator-1.106-11.el8. 386 kB/s | 27 kB 00:00
(116/159): perl-Params-Check-0.38-395.el8.noarc 333 kB/s | 24 kB 00:00
(117/159): perl-Net-SSLeay-1.88-2.module+el8.6. 4.4 MB/s | 379 kB 00:00
(118/159): perl-Perl-OSType-1.010-396.el8.noarc 459 kB/s | 29 kB 00:00
(119/159): perl-Params-Util-1.07-22.el8.x86_64. 656 kB/s | 44 kB 00:00
(120/159): perl-PerlIO-via-QuotedPrint-0.08-395 206 kB/s | 13 kB 00:00
(121/159): perl-Pod-Checker-1.73-395.el8.noarch 449 kB/s | 33 kB 00:00
(122/159): perl-Pod-Parser-1.63-396.el8.noarch. 1.6 MB/s | 108 kB 00:00
(123/159): perl-Pod-Html-1.22.02-422.el8.noarch 1.1 MB/s | 88 kB 00:00
(124/159): perl-SelfLoader-1.23-422.el8.noarch. 1.1 MB/s | 83 kB 00:00
(125/159): perl-Software-License-0.103013-2.el8 1.8 MB/s | 137 kB 00:00
(126/159): perl-Sub-Exporter-0.987-15.el8.noarc 1.0 MB/s | 73 kB 00:00
(127/159): perl-Sub-Install-0.928-14.el8.noarch 383 kB/s | 27 kB 00:00
(128/159): perl-Sys-Syslog-0.35-397.el8.x86_64. 734 kB/s | 50 kB 00:00
(129/159): perl-TermReadKey-2.37-7.el8.x86_64.r 536 kB/s | 40 kB 00:00
(130/159): perl-Test-1.30-422.el8.noarch.rpm 1.2 MB/s | 90 kB 00:00
(131/159): perl-Test-Harness-3.42-1.el8.noarch. 3.4 MB/s | 279 kB 00:00
(132/159): perl-Test-Simple-1.302135-1.el8.noar 5.2 MB/s | 516 kB 00:00
(133/159): perl-Text-Glob-0.11-4.el8.noarch.rpm 272 kB/s | 17 kB 00:00
(134/159): perl-Text-Balanced-2.03-395.el8.noar 807 kB/s | 58 kB 00:00
(135/159): perl-Text-Template-1.51-1.el8.noarch 841 kB/s | 64 kB 00:00
(136/159): perl-Time-HiRes-1.9758-2.el8.x86_64. 855 kB/s | 61 kB 00:00
(137/159): perl-Thread-Queue-3.13-1.el8.noarch. 319 kB/s | 24 kB 00:00
(138/159): perl-Time-Piece-1.31-422.el8.x86_64. 1.3 MB/s | 98 kB 00:00
(139/159): perl-autodie-2.29-396.el8.noarch.rpm 1.3 MB/s | 98 kB 00:00
(140/159): perl-Unicode-Collate-1.25-2.el8.x86_ 7.2 MB/s | 686 kB 00:00
(141/159): perl-bignum-0.49-2.el8.noarch.rpm 620 kB/s | 43 kB 00:00
(142/159): perl-encoding-2.22-3.el8.x86_64.rpm 934 kB/s | 68 kB 00:00
(143/159): perl-devel-5.26.3-422.el8.x86_64.rpm 6.5 MB/s | 600 kB 00:00
(144/159): perl-experimental-0.019-2.el8.noarch 327 kB/s | 24 kB 00:00
(145/159): perl-inc-latest-0.500-9.el8.noarch.r 331 kB/s | 25 kB 00:00
(146/159): perl-libnetcfg-5.26.3-422.el8.noarch 1.0 MB/s | 78 kB 00:00
(147/159): perl-local-lib-2.000024-2.el8.noarch 1.1 MB/s | 74 kB 00:00
(148/159): perl-srpm-macros-1-25.el8.noarch.rpm 157 kB/s | 11 kB 00:00
(149/159): perl-open-1.11-422.el8.noarch.rpm 1.0 MB/s | 78 kB 00:00
(150/159): perl-perlfaq-5.20180605-1.el8.noarch 4.7 MB/s | 386 kB 00:00
(151/159): perl-version-0.99.24-1.el8.x86_64.rp 1.0 MB/s | 67 kB 00:00
(152/159): perl-utils-5.26.3-422.el8.noarch.rpm 1.7 MB/s | 129 kB 00:00
(153/159): python-rpm-macros-3-45.el8.noarch.rp 219 kB/s | 16 kB 00:00
(154/159): python3-rpm-macros-3-45.el8.noarch.r 243 kB/s | 15 kB 00:00
(155/159): python-srpm-macros-3-45.el8.noarch.r 239 kB/s | 16 kB 00:00
(156/159): qt5-srpm-macros-5.15.3-1.el8.noarch. 132 kB/s | 11 kB 00:00
(157/159): rust-srpm-macros-5-2.el8.noarch.rpm 128 kB/s | 9.2 kB 00:00
(158/159): redhat-rpm-config-131-1.0.1.el8.noar 1.2 MB/s | 91 kB 00:00
(159/159): systemtap-sdt-devel-4.9-3.0.1.el8.x8 1.2 MB/s | 88 kB 00:00
--------------------------------------------------------------------------------
Total 4.6 MB/s | 25 MB 00:05
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : python-srpm-macros-3-45.el8.noarch 1/159
Installing : python-rpm-macros-3-45.el8.noarch 2/159
Installing : python3-rpm-macros-3-45.el8.noarch 3/159
Installing : rust-srpm-macros-5-2.el8.noarch 4/159
Installing : qt5-srpm-macros-5.15.3-1.el8.noarch 5/159
Installing : perl-srpm-macros-1-25.el8.noarch 6/159
Installing : openblas-srpm-macros-2-2.el8.noarch 7/159
Installing : ocaml-srpm-macros-5-4.el8.noarch 8/159
Installing : go-srpm-macros-2-17.el8.noarch 9/159
Installing : ghc-srpm-macros-1.4.2-7.el8.noarch 10/159
Installing : efi-srpm-macros-3-3.0.1.el8.noarch 11/159
Installing : dwz-0.12-10.el8.x86_64 12/159
Installing : zip-3.0-23.el8.x86_64 13/159
Installing : python3-pyparsing-2.1.10-7.el8.noarch 14/159
Installing : systemtap-sdt-devel-4.9-3.0.1.el8.x86_64 15/159
Installing : groff-base-1.22.3-18.el8.x86_64 16/159
Installing : perl-Digest-1.17-395.el8.noarch 17/159
Installing : perl-Digest-MD5-2.55-396.el8.x86_64 18/159
Installing : perl-Data-Dumper-2.167-399.el8.x86_64 19/159
Installing : perl-libnet-3.11-3.el8.noarch 20/159
Installing : perl-URI-1.73-3.el8.noarch 21/159
Installing : perl-Pod-Escapes-1:1.07-395.el8.noarch 22/159
Installing : perl-IO-Socket-IP-0.39-5.el8.noarch 23/159
Installing : perl-Time-Local-1:1.280-1.el8.noarch 24/159
Installing : perl-Mozilla-CA-20160104-7.0.1.module+el8.3.0+21 25/159
Installing : perl-IO-Socket-SSL-2.066-4.module+el8.6.0+20623+ 26/159
Installing : perl-Net-SSLeay-1.88-2.module+el8.6.0+20623+f089 27/159
Installing : perl-Term-ANSIColor-4.06-396.el8.noarch 28/159
Installing : perl-Term-Cap-1.17-395.el8.noarch 29/159
Installing : perl-File-Temp-0.230.600-1.el8.noarch 30/159
Installing : perl-HTTP-Tiny-0.074-2.el8.noarch 31/159
Installing : perl-Pod-Simple-1:3.35-395.el8.noarch 32/159
Installing : perl-podlators-4.11-1.el8.noarch 33/159
Installing : perl-Pod-Perldoc-3.28-396.el8.noarch 34/159
Installing : perl-Text-ParseWords-3.30-395.el8.noarch 35/159
Installing : perl-Pod-Usage-4:1.69-395.el8.noarch 36/159
Installing : perl-MIME-Base64-3.15-396.el8.x86_64 37/159
Installing : perl-Storable-1:3.11-3.el8.x86_64 38/159
Installing : perl-Getopt-Long-1:2.50-4.el8.noarch 39/159
Installing : perl-Errno-1.28-422.el8.x86_64 40/159
Installing : perl-Socket-4:2.027-3.el8.x86_64 41/159
Installing : perl-Encode-4:2.97-3.el8.x86_64 42/159
Installing : perl-Carp-1.42-396.el8.noarch 43/159
Installing : perl-Exporter-5.72-396.el8.noarch 44/159
Installing : perl-libs-4:5.26.3-422.el8.x86_64 45/159
Installing : perl-Scalar-List-Utils-3:1.49-2.el8.x86_64 46/159
Installing : perl-parent-1:0.237-1.el8.noarch 47/159
Installing : perl-macros-4:5.26.3-422.el8.x86_64 48/159
Installing : perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch 49/159
Installing : perl-Unicode-Normalize-1.25-396.el8.x86_64 50/159
Installing : perl-File-Path-2.15-2.el8.noarch 51/159
Installing : perl-IO-1.38-422.el8.x86_64 52/159
Installing : perl-PathTools-3.74-1.el8.x86_64 53/159
Installing : perl-constant-1.33-396.el8.noarch 54/159
Installing : perl-threads-1:2.21-2.el8.x86_64 55/159
Installing : perl-threads-shared-1.58-2.el8.x86_64 56/159
Installing : perl-interpreter-4:5.26.3-422.el8.x86_64 57/159
Installing : perl-version-6:0.99.24-1.el8.x86_64 58/159
Installing : perl-Time-HiRes-4:1.9758-2.el8.x86_64 59/159
Installing : perl-CPAN-Meta-Requirements-2.140-396.el8.noarch 60/159
Installing : perl-ExtUtils-Manifest-1.70-395.el8.noarch 61/159
Installing : perl-ExtUtils-ParseXS-1:3.35-2.el8.noarch 62/159
Installing : perl-Test-Harness-1:3.42-1.el8.noarch 63/159
Installing : perl-Module-CoreList-1:5.20181130-1.el8.noarch 64/159
Installing : perl-Module-Metadata-1.000033-395.el8.noarch 65/159
Installing : perl-Compress-Raw-Zlib-2.081-1.el8.x86_64 66/159
Installing : perl-Filter-2:1.58-2.el8.x86_64 67/159
Installing : perl-SelfLoader-1.23-422.el8.noarch 68/159
Installing : perl-Module-Load-1:0.32-395.el8.noarch 69/159
Installing : perl-Perl-OSType-1.010-396.el8.noarch 70/159
Installing : perl-Text-Balanced-2.03-395.el8.noarch 71/159
Installing : perl-encoding-4:2.22-3.el8.x86_64 72/159
Installing : perl-Net-Ping-2.55-422.el8.noarch 73/159
Installing : perl-Compress-Raw-Bzip2-2.081-1.el8.x86_64 74/159
Installing : perl-IO-Compress-2.081-1.el8.noarch 75/159
Installing : perl-IO-Zlib-1:1.10-422.el8.noarch 76/159
Installing : perl-Math-Complex-1.59-422.el8.noarch 77/159
Installing : perl-Math-BigInt-1:1.9998.11-7.el8.noarch 78/159
Installing : perl-JSON-PP-1:2.97.001-3.el8.noarch 79/159
Installing : perl-Math-BigRat-0.2614-1.el8.noarch 80/159
Installing : perl-CPAN-Meta-YAML-0.018-397.el8.noarch 81/159
Installing : perl-CPAN-Meta-2.150010-396.el8.noarch 82/159
Installing : perl-Digest-SHA-1:6.02-1.el8.x86_64 83/159
Installing : perl-ExtUtils-Command-1:7.34-1.el8.noarch 84/159
Installing : perl-Locale-Maketext-1.28-396.el8.noarch 85/159
Installing : perl-Locale-Maketext-Simple-1:0.21-422.el8.noarc 86/159
Installing : perl-Params-Check-1:0.38-395.el8.noarch 87/159
Installing : perl-Module-Load-Conditional-0.68-395.el8.noarch 88/159
Installing : perl-Params-Util-1.07-22.el8.x86_64 89/159
Installing : perl-Pod-Html-1.22.02-422.el8.noarch 90/159
Installing : perl-Sub-Install-0.928-14.el8.noarch 91/159
Installing : perl-Data-OptList-0.110-6.el8.noarch 92/159
Installing : perl-bignum-0.49-2.el8.noarch 93/159
Installing : perl-Math-BigInt-FastCalc-0.500.600-6.el8.x86_64 94/159
Installing : perl-open-1.11-422.el8.noarch 95/159
Installing : perl-Filter-Simple-0.94-2.el8.noarch 96/159
Installing : perl-Devel-SelfStubber-1.06-422.el8.noarch 97/159
Installing : perl-Archive-Zip-1.60-3.el8.noarch 98/159
Installing : perl-Module-CoreList-tools-1:5.20181130-1.el8.no 99/159
Installing : perl-experimental-0.019-2.el8.noarch 100/159
Installing : perl-Algorithm-Diff-1.1903-9.el8.noarch 101/159
Installing : perl-Text-Diff-1.45-2.el8.noarch 102/159
Installing : perl-Archive-Tar-2.30-1.el8.noarch 103/159
Installing : perl-Attribute-Handlers-0.99-422.el8.noarch 104/159
Installing : perl-B-Debug-1.26-2.el8.noarch 105/159
Installing : perl-Compress-Bzip2-2.26-6.el8.x86_64 106/159
Installing : perl-Config-Perl-V-0.30-1.el8.noarch 107/159
Installing : perl-DB_File-1.842-1.el8.x86_64 108/159
Installing : perl-Devel-PPPort-3.36-5.el8.x86_64 109/159
Installing : perl-Devel-Size-0.81-2.el8.x86_64 110/159
Installing : perl-Encode-Locale-1.05-10.module+el8.3.0+7692+5 111/159
Installing : perl-Env-1.04-395.el8.noarch 112/159
Installing : perl-ExtUtils-MM-Utils-1:7.34-1.el8.noarch 113/159
Installing : perl-IPC-Cmd-2:1.02-1.el8.noarch 114/159
Installing : perl-File-Fetch-0.56-2.el8.noarch 115/159
Installing : perl-IPC-SysV-2.07-397.el8.x86_64 116/159
Installing : perl-IPC-System-Simple-1.25-17.el8.noarch 117/159
Installing : perl-autodie-2.29-396.el8.noarch 118/159
Installing : perl-Locale-Codes-3.57-1.el8.noarch 119/159
Installing : perl-Memoize-1.03-422.el8.noarch 120/159
Installing : perl-Module-Loaded-1:0.08-422.el8.noarch 121/159
Installing : perl-Package-Generator-1.106-11.el8.noarch 122/159
Installing : perl-Sub-Exporter-0.987-15.el8.noarch 123/159
Installing : perl-Pod-Checker-4:1.73-395.el8.noarch 124/159
Installing : perl-Pod-Parser-1.63-396.el8.noarch 125/159
Installing : perl-Sys-Syslog-0.35-397.el8.x86_64 126/159
Installing : perl-TermReadKey-2.37-7.el8.x86_64 127/159
Installing : perl-Test-1.30-422.el8.noarch 128/159
Installing : perl-Test-Simple-1:1.302135-1.el8.noarch 129/159
Installing : perl-Text-Glob-0.11-4.el8.noarch 130/159
Installing : perl-Text-Template-1.51-1.el8.noarch 131/159
Installing : perl-Time-Piece-1.31-422.el8.x86_64 132/159
Installing : perl-Unicode-Collate-1.25-2.el8.x86_64 133/159
Installing : perl-local-lib-2.000024-2.el8.noarch 134/159
Installing : perl-utils-5.26.3-422.el8.noarch 135/159
Installing : perl-Thread-Queue-3.13-1.el8.noarch 136/159
Installing : perl-File-Which-1.22-2.el8.noarch 137/159
Installing : perl-File-HomeDir-1.002-4.el8.noarch 138/159
Installing : perl-Devel-Peek-1.26-422.el8.x86_64 139/159
Installing : perl-MRO-Compat-0.13-4.el8.noarch 140/159
Installing : perl-Data-Section-0.200007-3.el8.noarch 141/159
Installing : perl-Software-License-0.103013-2.el8.noarch 142/159
Installing : perl-PerlIO-via-QuotedPrint-0.08-395.el8.noarch 143/159
Installing : perl-perlfaq-5.20180605-1.el8.noarch 144/159
Installing : glibc-gconv-extra-2.28-225.0.3.el8.x86_64 145/159
Running scriptlet: glibc-gconv-extra-2.28-225.0.3.el8.x86_64 145/159
Installing : file-5.33-24.el8.x86_64 146/159
Installing : redhat-rpm-config-131-1.0.1.el8.noarch 147/159
Installing : perl-ExtUtils-Install-2.14-4.el8.noarch 148/159
Installing : perl-devel-4:5.26.3-422.el8.x86_64 149/159
Installing : perl-ExtUtils-MakeMaker-1:7.34-1.el8.noarch 150/159
Installing : perl-ExtUtils-CBuilder-1:0.280230-2.el8.noarch 151/159
Installing : perl-ExtUtils-Embed-1.34-422.el8.noarch 152/159
Installing : perl-ExtUtils-Miniperl-1.06-422.el8.noarch 153/159
Installing : perl-libnetcfg-4:5.26.3-422.el8.noarch 154/159
Installing : perl-Encode-devel-4:2.97-3.el8.x86_64 155/159
Installing : perl-inc-latest-2:0.500-9.el8.noarch 156/159
Installing : perl-Module-Build-2:0.42.24-5.el8.noarch 157/159
Installing : perl-CPAN-2.18-397.el8.noarch 158/159
Installing : perl-4:5.26.3-422.el8.x86_64 159/159
Running scriptlet: perl-4:5.26.3-422.el8.x86_64 159/159
Verifying : file-5.33-24.el8.x86_64 1/159
Verifying : glibc-gconv-extra-2.28-225.0.3.el8.x86_64 2/159
Verifying : groff-base-1.22.3-18.el8.x86_64 3/159
Verifying : perl-Algorithm-Diff-1.1903-9.el8.noarch 4/159
Verifying : perl-Archive-Tar-2.30-1.el8.noarch 5/159
Verifying : perl-Carp-1.42-396.el8.noarch 6/159
Verifying : perl-Compress-Raw-Bzip2-2.081-1.el8.x86_64 7/159
Verifying : perl-Compress-Raw-Zlib-2.081-1.el8.x86_64 8/159
Verifying : perl-Data-Dumper-2.167-399.el8.x86_64 9/159
Verifying : perl-Digest-1.17-395.el8.noarch 10/159
Verifying : perl-Digest-MD5-2.55-396.el8.x86_64 11/159
Verifying : perl-Encode-4:2.97-3.el8.x86_64 12/159
Verifying : perl-Errno-1.28-422.el8.x86_64 13/159
Verifying : perl-Exporter-5.72-396.el8.noarch 14/159
Verifying : perl-File-Path-2.15-2.el8.noarch 15/159
Verifying : perl-File-Temp-0.230.600-1.el8.noarch 16/159
Verifying : perl-Getopt-Long-1:2.50-4.el8.noarch 17/159
Verifying : perl-HTTP-Tiny-0.074-2.el8.noarch 18/159
Verifying : perl-IO-1.38-422.el8.x86_64 19/159
Verifying : perl-IO-Compress-2.081-1.el8.noarch 20/159
Verifying : perl-IO-Socket-IP-0.39-5.el8.noarch 21/159
Verifying : perl-IO-Zlib-1:1.10-422.el8.noarch 22/159
Verifying : perl-MIME-Base64-3.15-396.el8.x86_64 23/159
Verifying : perl-Math-BigInt-1:1.9998.11-7.el8.noarch 24/159
Verifying : perl-Math-Complex-1.59-422.el8.noarch 25/159
Verifying : perl-PathTools-3.74-1.el8.x86_64 26/159
Verifying : perl-Pod-Escapes-1:1.07-395.el8.noarch 27/159
Verifying : perl-Pod-Perldoc-3.28-396.el8.noarch 28/159
Verifying : perl-Pod-Simple-1:3.35-395.el8.noarch 29/159
Verifying : perl-Pod-Usage-4:1.69-395.el8.noarch 30/159
Verifying : perl-Scalar-List-Utils-3:1.49-2.el8.x86_64 31/159
Verifying : perl-Socket-4:2.027-3.el8.x86_64 32/159
Verifying : perl-Storable-1:3.11-3.el8.x86_64 33/159
Verifying : perl-Term-ANSIColor-4.06-396.el8.noarch 34/159
Verifying : perl-Term-Cap-1.17-395.el8.noarch 35/159
Verifying : perl-Text-Diff-1.45-2.el8.noarch 36/159
Verifying : perl-Text-ParseWords-3.30-395.el8.noarch 37/159
Verifying : perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch 38/159
Verifying : perl-Time-Local-1:1.280-1.el8.noarch 39/159
Verifying : perl-URI-1.73-3.el8.noarch 40/159
Verifying : perl-Unicode-Normalize-1.25-396.el8.x86_64 41/159
Verifying : perl-constant-1.33-396.el8.noarch 42/159
Verifying : perl-interpreter-4:5.26.3-422.el8.x86_64 43/159
Verifying : perl-libnet-3.11-3.el8.noarch 44/159
Verifying : perl-libs-4:5.26.3-422.el8.x86_64 45/159
Verifying : perl-macros-4:5.26.3-422.el8.x86_64 46/159
Verifying : perl-parent-1:0.237-1.el8.noarch 47/159
Verifying : perl-podlators-4.11-1.el8.noarch 48/159
Verifying : perl-threads-1:2.21-2.el8.x86_64 49/159
Verifying : perl-threads-shared-1.58-2.el8.x86_64 50/159
Verifying : python3-pyparsing-2.1.10-7.el8.noarch 51/159
Verifying : zip-3.0-23.el8.x86_64 52/159
Verifying : dwz-0.12-10.el8.x86_64 53/159
Verifying : efi-srpm-macros-3-3.0.1.el8.noarch 54/159
Verifying : ghc-srpm-macros-1.4.2-7.el8.noarch 55/159
Verifying : go-srpm-macros-2-17.el8.noarch 56/159
Verifying : ocaml-srpm-macros-5-4.el8.noarch 57/159
Verifying : openblas-srpm-macros-2-2.el8.noarch 58/159
Verifying : perl-4:5.26.3-422.el8.x86_64 59/159
Verifying : perl-Archive-Zip-1.60-3.el8.noarch 60/159
Verifying : perl-Attribute-Handlers-0.99-422.el8.noarch 61/159
Verifying : perl-B-Debug-1.26-2.el8.noarch 62/159
Verifying : perl-CPAN-2.18-397.el8.noarch 63/159
Verifying : perl-CPAN-Meta-2.150010-396.el8.noarch 64/159
Verifying : perl-CPAN-Meta-Requirements-2.140-396.el8.noarch 65/159
Verifying : perl-CPAN-Meta-YAML-0.018-397.el8.noarch 66/159
Verifying : perl-Compress-Bzip2-2.26-6.el8.x86_64 67/159
Verifying : perl-Config-Perl-V-0.30-1.el8.noarch 68/159
Verifying : perl-DB_File-1.842-1.el8.x86_64 69/159
Verifying : perl-Data-OptList-0.110-6.el8.noarch 70/159
Verifying : perl-Data-Section-0.200007-3.el8.noarch 71/159
Verifying : perl-Devel-PPPort-3.36-5.el8.x86_64 72/159
Verifying : perl-Devel-Peek-1.26-422.el8.x86_64 73/159
Verifying : perl-Devel-SelfStubber-1.06-422.el8.noarch 74/159
Verifying : perl-Devel-Size-0.81-2.el8.x86_64 75/159
Verifying : perl-Digest-SHA-1:6.02-1.el8.x86_64 76/159
Verifying : perl-Encode-Locale-1.05-10.module+el8.3.0+7692+5 77/159
Verifying : perl-Encode-devel-4:2.97-3.el8.x86_64 78/159
Verifying : perl-Env-1.04-395.el8.noarch 79/159
Verifying : perl-ExtUtils-CBuilder-1:0.280230-2.el8.noarch 80/159
Verifying : perl-ExtUtils-Command-1:7.34-1.el8.noarch 81/159
Verifying : perl-ExtUtils-Embed-1.34-422.el8.noarch 82/159
Verifying : perl-ExtUtils-Install-2.14-4.el8.noarch 83/159
Verifying : perl-ExtUtils-MM-Utils-1:7.34-1.el8.noarch 84/159
Verifying : perl-ExtUtils-MakeMaker-1:7.34-1.el8.noarch 85/159
Verifying : perl-ExtUtils-Manifest-1.70-395.el8.noarch 86/159
Verifying : perl-ExtUtils-Miniperl-1.06-422.el8.noarch 87/159
Verifying : perl-ExtUtils-ParseXS-1:3.35-2.el8.noarch 88/159
Verifying : perl-File-Fetch-0.56-2.el8.noarch 89/159
Verifying : perl-File-HomeDir-1.002-4.el8.noarch 90/159
Verifying : perl-File-Which-1.22-2.el8.noarch 91/159
Verifying : perl-Filter-2:1.58-2.el8.x86_64 92/159
Verifying : perl-Filter-Simple-0.94-2.el8.noarch 93/159
Verifying : perl-IO-Socket-SSL-2.066-4.module+el8.6.0+20623+ 94/159
Verifying : perl-IPC-Cmd-2:1.02-1.el8.noarch 95/159
Verifying : perl-IPC-SysV-2.07-397.el8.x86_64 96/159
Verifying : perl-IPC-System-Simple-1.25-17.el8.noarch 97/159
Verifying : perl-JSON-PP-1:2.97.001-3.el8.noarch 98/159
Verifying : perl-Locale-Codes-3.57-1.el8.noarch 99/159
Verifying : perl-Locale-Maketext-1.28-396.el8.noarch 100/159
Verifying : perl-Locale-Maketext-Simple-1:0.21-422.el8.noarc 101/159
Verifying : perl-MRO-Compat-0.13-4.el8.noarch 102/159
Verifying : perl-Math-BigInt-FastCalc-0.500.600-6.el8.x86_64 103/159
Verifying : perl-Math-BigRat-0.2614-1.el8.noarch 104/159
Verifying : perl-Memoize-1.03-422.el8.noarch 105/159
Verifying : perl-Module-Build-2:0.42.24-5.el8.noarch 106/159
Verifying : perl-Module-CoreList-1:5.20181130-1.el8.noarch 107/159
Verifying : perl-Module-CoreList-tools-1:5.20181130-1.el8.no 108/159
Verifying : perl-Module-Load-1:0.32-395.el8.noarch 109/159
Verifying : perl-Module-Load-Conditional-0.68-395.el8.noarch 110/159
Verifying : perl-Module-Loaded-1:0.08-422.el8.noarch 111/159
Verifying : perl-Module-Metadata-1.000033-395.el8.noarch 112/159
Verifying : perl-Mozilla-CA-20160104-7.0.1.module+el8.3.0+21 113/159
Verifying : perl-Net-Ping-2.55-422.el8.noarch 114/159
Verifying : perl-Net-SSLeay-1.88-2.module+el8.6.0+20623+f089 115/159
Verifying : perl-Package-Generator-1.106-11.el8.noarch 116/159
Verifying : perl-Params-Check-1:0.38-395.el8.noarch 117/159
Verifying : perl-Params-Util-1.07-22.el8.x86_64 118/159
Verifying : perl-Perl-OSType-1.010-396.el8.noarch 119/159
Verifying : perl-PerlIO-via-QuotedPrint-0.08-395.el8.noarch 120/159
Verifying : perl-Pod-Checker-4:1.73-395.el8.noarch 121/159
Verifying : perl-Pod-Html-1.22.02-422.el8.noarch 122/159
Verifying : perl-Pod-Parser-1.63-396.el8.noarch 123/159
Verifying : perl-SelfLoader-1.23-422.el8.noarch 124/159
Verifying : perl-Software-License-0.103013-2.el8.noarch 125/159
Verifying : perl-Sub-Exporter-0.987-15.el8.noarch 126/159
Verifying : perl-Sub-Install-0.928-14.el8.noarch 127/159
Verifying : perl-Sys-Syslog-0.35-397.el8.x86_64 128/159
Verifying : perl-TermReadKey-2.37-7.el8.x86_64 129/159
Verifying : perl-Test-1.30-422.el8.noarch 130/159
Verifying : perl-Test-Harness-1:3.42-1.el8.noarch 131/159
Verifying : perl-Test-Simple-1:1.302135-1.el8.noarch 132/159
Verifying : perl-Text-Balanced-2.03-395.el8.noarch 133/159
Verifying : perl-Text-Glob-0.11-4.el8.noarch 134/159
Verifying : perl-Text-Template-1.51-1.el8.noarch 135/159
Verifying : perl-Thread-Queue-3.13-1.el8.noarch 136/159
Verifying : perl-Time-HiRes-4:1.9758-2.el8.x86_64 137/159
Verifying : perl-Time-Piece-1.31-422.el8.x86_64 138/159
Verifying : perl-Unicode-Collate-1.25-2.el8.x86_64 139/159
Verifying : perl-autodie-2.29-396.el8.noarch 140/159
Verifying : perl-bignum-0.49-2.el8.noarch 141/159
Verifying : perl-devel-4:5.26.3-422.el8.x86_64 142/159
Verifying : perl-encoding-4:2.22-3.el8.x86_64 143/159
Verifying : perl-experimental-0.019-2.el8.noarch 144/159
Verifying : perl-inc-latest-2:0.500-9.el8.noarch 145/159
Verifying : perl-libnetcfg-4:5.26.3-422.el8.noarch 146/159
Verifying : perl-local-lib-2.000024-2.el8.noarch 147/159
Verifying : perl-open-1.11-422.el8.noarch 148/159
Verifying : perl-perlfaq-5.20180605-1.el8.noarch 149/159
Verifying : perl-srpm-macros-1-25.el8.noarch 150/159
Verifying : perl-utils-5.26.3-422.el8.noarch 151/159
Verifying : perl-version-6:0.99.24-1.el8.x86_64 152/159
Verifying : python-rpm-macros-3-45.el8.noarch 153/159
Verifying : python-srpm-macros-3-45.el8.noarch 154/159
Verifying : python3-rpm-macros-3-45.el8.noarch 155/159
Verifying : qt5-srpm-macros-5.15.3-1.el8.noarch 156/159
Verifying : redhat-rpm-config-131-1.0.1.el8.noarch 157/159
Verifying : rust-srpm-macros-5-2.el8.noarch 158/159
Verifying : systemtap-sdt-devel-4.9-3.0.1.el8.x86_64 159/159
Installed:
dwz-0.12-10.el8.x86_64
efi-srpm-macros-3-3.0.1.el8.noarch
file-5.33-24.el8.x86_64
ghc-srpm-macros-1.4.2-7.el8.noarch
glibc-gconv-extra-2.28-225.0.3.el8.x86_64
go-srpm-macros-2-17.el8.noarch
groff-base-1.22.3-18.el8.x86_64
ocaml-srpm-macros-5-4.el8.noarch
openblas-srpm-macros-2-2.el8.noarch
perl-4:5.26.3-422.el8.x86_64
perl-Algorithm-Diff-1.1903-9.el8.noarch
perl-Archive-Tar-2.30-1.el8.noarch
perl-Archive-Zip-1.60-3.el8.noarch
perl-Attribute-Handlers-0.99-422.el8.noarch
perl-B-Debug-1.26-2.el8.noarch
perl-CPAN-2.18-397.el8.noarch
perl-CPAN-Meta-2.150010-396.el8.noarch
perl-CPAN-Meta-Requirements-2.140-396.el8.noarch
perl-CPAN-Meta-YAML-0.018-397.el8.noarch
perl-Carp-1.42-396.el8.noarch
perl-Compress-Bzip2-2.26-6.el8.x86_64
perl-Compress-Raw-Bzip2-2.081-1.el8.x86_64
perl-Compress-Raw-Zlib-2.081-1.el8.x86_64
perl-Config-Perl-V-0.30-1.el8.noarch
perl-DB_File-1.842-1.el8.x86_64
perl-Data-Dumper-2.167-399.el8.x86_64
perl-Data-OptList-0.110-6.el8.noarch
perl-Data-Section-0.200007-3.el8.noarch
perl-Devel-PPPort-3.36-5.el8.x86_64
perl-Devel-Peek-1.26-422.el8.x86_64
perl-Devel-SelfStubber-1.06-422.el8.noarch
perl-Devel-Size-0.81-2.el8.x86_64
perl-Digest-1.17-395.el8.noarch
perl-Digest-MD5-2.55-396.el8.x86_64
perl-Digest-SHA-1:6.02-1.el8.x86_64
perl-Encode-4:2.97-3.el8.x86_64
perl-Encode-Locale-1.05-10.module+el8.3.0+7692+542c56f9.noarch
perl-Encode-devel-4:2.97-3.el8.x86_64
perl-Env-1.04-395.el8.noarch
perl-Errno-1.28-422.el8.x86_64
perl-Exporter-5.72-396.el8.noarch
perl-ExtUtils-CBuilder-1:0.280230-2.el8.noarch
perl-ExtUtils-Command-1:7.34-1.el8.noarch
perl-ExtUtils-Embed-1.34-422.el8.noarch
perl-ExtUtils-Install-2.14-4.el8.noarch
perl-ExtUtils-MM-Utils-1:7.34-1.el8.noarch
perl-ExtUtils-MakeMaker-1:7.34-1.el8.noarch
perl-ExtUtils-Manifest-1.70-395.el8.noarch
perl-ExtUtils-Miniperl-1.06-422.el8.noarch
perl-ExtUtils-ParseXS-1:3.35-2.el8.noarch
perl-File-Fetch-0.56-2.el8.noarch
perl-File-HomeDir-1.002-4.el8.noarch
perl-File-Path-2.15-2.el8.noarch
perl-File-Temp-0.230.600-1.el8.noarch
perl-File-Which-1.22-2.el8.noarch
perl-Filter-2:1.58-2.el8.x86_64
perl-Filter-Simple-0.94-2.el8.noarch
perl-Getopt-Long-1:2.50-4.el8.noarch
perl-HTTP-Tiny-0.074-2.el8.noarch
perl-IO-1.38-422.el8.x86_64
perl-IO-Compress-2.081-1.el8.noarch
perl-IO-Socket-IP-0.39-5.el8.noarch
perl-IO-Socket-SSL-2.066-4.module+el8.6.0+20623+f0897f98.noarch
perl-IO-Zlib-1:1.10-422.el8.noarch
perl-IPC-Cmd-2:1.02-1.el8.noarch
perl-IPC-SysV-2.07-397.el8.x86_64
perl-IPC-System-Simple-1.25-17.el8.noarch
perl-JSON-PP-1:2.97.001-3.el8.noarch
perl-Locale-Codes-3.57-1.el8.noarch
perl-Locale-Maketext-1.28-396.el8.noarch
perl-Locale-Maketext-Simple-1:0.21-422.el8.noarch
perl-MIME-Base64-3.15-396.el8.x86_64
perl-MRO-Compat-0.13-4.el8.noarch
perl-Math-BigInt-1:1.9998.11-7.el8.noarch
perl-Math-BigInt-FastCalc-0.500.600-6.el8.x86_64
perl-Math-BigRat-0.2614-1.el8.noarch
perl-Math-Complex-1.59-422.el8.noarch
perl-Memoize-1.03-422.el8.noarch
perl-Module-Build-2:0.42.24-5.el8.noarch
perl-Module-CoreList-1:5.20181130-1.el8.noarch
perl-Module-CoreList-tools-1:5.20181130-1.el8.noarch
perl-Module-Load-1:0.32-395.el8.noarch
perl-Module-Load-Conditional-0.68-395.el8.noarch
perl-Module-Loaded-1:0.08-422.el8.noarch
perl-Module-Metadata-1.000033-395.el8.noarch
perl-Mozilla-CA-20160104-7.0.1.module+el8.3.0+21136+b437fca9.noarch
perl-Net-Ping-2.55-422.el8.noarch
perl-Net-SSLeay-1.88-2.module+el8.6.0+20623+f0897f98.x86_64
perl-Package-Generator-1.106-11.el8.noarch
perl-Params-Check-1:0.38-395.el8.noarch
perl-Params-Util-1.07-22.el8.x86_64
perl-PathTools-3.74-1.el8.x86_64
perl-Perl-OSType-1.010-396.el8.noarch
perl-PerlIO-via-QuotedPrint-0.08-395.el8.noarch
perl-Pod-Checker-4:1.73-395.el8.noarch
perl-Pod-Escapes-1:1.07-395.el8.noarch
perl-Pod-Html-1.22.02-422.el8.noarch
perl-Pod-Parser-1.63-396.el8.noarch
perl-Pod-Perldoc-3.28-396.el8.noarch
perl-Pod-Simple-1:3.35-395.el8.noarch
perl-Pod-Usage-4:1.69-395.el8.noarch
perl-Scalar-List-Utils-3:1.49-2.el8.x86_64
perl-SelfLoader-1.23-422.el8.noarch
perl-Socket-4:2.027-3.el8.x86_64
perl-Software-License-0.103013-2.el8.noarch
perl-Storable-1:3.11-3.el8.x86_64
perl-Sub-Exporter-0.987-15.el8.noarch
perl-Sub-Install-0.928-14.el8.noarch
perl-Sys-Syslog-0.35-397.el8.x86_64
perl-Term-ANSIColor-4.06-396.el8.noarch
perl-Term-Cap-1.17-395.el8.noarch
perl-TermReadKey-2.37-7.el8.x86_64
perl-Test-1.30-422.el8.noarch
perl-Test-Harness-1:3.42-1.el8.noarch
perl-Test-Simple-1:1.302135-1.el8.noarch
perl-Text-Balanced-2.03-395.el8.noarch
perl-Text-Diff-1.45-2.el8.noarch
perl-Text-Glob-0.11-4.el8.noarch
perl-Text-ParseWords-3.30-395.el8.noarch
perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch
perl-Text-Template-1.51-1.el8.noarch
perl-Thread-Queue-3.13-1.el8.noarch
perl-Time-HiRes-4:1.9758-2.el8.x86_64
perl-Time-Local-1:1.280-1.el8.noarch
perl-Time-Piece-1.31-422.el8.x86_64
perl-URI-1.73-3.el8.noarch
perl-Unicode-Collate-1.25-2.el8.x86_64
perl-Unicode-Normalize-1.25-396.el8.x86_64
perl-autodie-2.29-396.el8.noarch
perl-bignum-0.49-2.el8.noarch
perl-constant-1.33-396.el8.noarch
perl-devel-4:5.26.3-422.el8.x86_64
perl-encoding-4:2.22-3.el8.x86_64
perl-experimental-0.019-2.el8.noarch
perl-inc-latest-2:0.500-9.el8.noarch
perl-interpreter-4:5.26.3-422.el8.x86_64
perl-libnet-3.11-3.el8.noarch
perl-libnetcfg-4:5.26.3-422.el8.noarch
perl-libs-4:5.26.3-422.el8.x86_64
perl-local-lib-2.000024-2.el8.noarch
perl-macros-4:5.26.3-422.el8.x86_64
perl-open-1.11-422.el8.noarch
perl-parent-1:0.237-1.el8.noarch
perl-perlfaq-5.20180605-1.el8.noarch
perl-podlators-4.11-1.el8.noarch
perl-srpm-macros-1-25.el8.noarch
perl-threads-1:2.21-2.el8.x86_64
perl-threads-shared-1.58-2.el8.x86_64
perl-utils-5.26.3-422.el8.noarch
perl-version-6:0.99.24-1.el8.x86_64
python-rpm-macros-3-45.el8.noarch
python-srpm-macros-3-45.el8.noarch
python3-pyparsing-2.1.10-7.el8.noarch
python3-rpm-macros-3-45.el8.noarch
qt5-srpm-macros-5.15.3-1.el8.noarch
redhat-rpm-config-131-1.0.1.el8.noarch
rust-srpm-macros-5-2.el8.noarch
systemtap-sdt-devel-4.9-3.0.1.el8.x86_64
zip-3.0-23.el8.x86_64
Complete! |
I installed the python3 with this command:
dnf install -y python3 |
Display detailed console log →
Last metadata expiration check: 0:31:49 ago on Thu Dec 21 05:18:09 2023.
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
python36 x86_64 3.6.8-38.module+el8.9.0+90104+968a3e84
ol8_appstream 18 k
Installing dependencies:
platform-python-pip noarch 9.0.3-23.el8 ol8_baseos_latest 1.6 M
python3-pip noarch 9.0.3-23.el8 ol8_appstream 20 k
python3-setuptools noarch 39.2.0-7.el8 ol8_baseos_latest 163 k
Enabling module streams:
python36 3.6
Transaction Summary
================================================================================
Install 4 Packages
Total download size: 1.8 M
Installed size: 7.0 M
Downloading Packages:
(1/4): python3-pip-9.0.3-23.el8.noarch.rpm 61 kB/s | 20 kB 00:00
(2/4): python36-3.6.8-38.module+el8.9.0+90104+9 229 kB/s | 18 kB 00:00
(3/4): python3-setuptools-39.2.0-7.el8.noarch.r 335 kB/s | 163 kB 00:00
(4/4): platform-python-pip-9.0.3-23.el8.noarch. 1.9 MB/s | 1.6 MB 00:00
--------------------------------------------------------------------------------
Total 2.2 MB/s | 1.8 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : python3-setuptools-39.2.0-7.el8.noarch 1/4
Installing : platform-python-pip-9.0.3-23.el8.noarch 2/4
Installing : python36-3.6.8-38.module+el8.9.0+90104+968a3e84.x86_ 3/4
Running scriptlet: python36-3.6.8-38.module+el8.9.0+90104+968a3e84.x86_ 3/4
Installing : python3-pip-9.0.3-23.el8.noarch 4/4
Running scriptlet: python3-pip-9.0.3-23.el8.noarch 4/4
Verifying : platform-python-pip-9.0.3-23.el8.noarch 1/4
Verifying : python3-setuptools-39.2.0-7.el8.noarch 2/4
Verifying : python3-pip-9.0.3-23.el8.noarch 3/4
Verifying : python36-3.6.8-38.module+el8.9.0+90104+968a3e84.x86_ 4/4
Installed:
platform-python-pip-9.0.3-23.el8.noarch
python3-pip-9.0.3-23.el8.noarch
python3-setuptools-39.2.0-7.el8.noarch
python36-3.6.8-38.module+el8.9.0+90104+968a3e84.x86_64
Complete! |
I installed the git module with this command:
dnf install -y git |
Display detailed console log →
Last metadata expiration check: 0:33:00 ago on Thu Dec 21 05:18:09 2023. Dependencies resolved. ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: git x86_64 2.39.3-1.el8_8 ol8_appstream 104 k Installing dependencies: emacs-filesystem noarch 1:26.1-11.el8 ol8_baseos_latest 70 k git-core x86_64 2.39.3-1.el8_8 ol8_appstream 11 M git-core-doc noarch 2.39.3-1.el8_8 ol8_appstream 3.0 M less x86_64 530-1.el8 ol8_baseos_latest 164 k perl-Error noarch 1:0.17025-2.el8 ol8_appstream 46 k perl-Git noarch 2.39.3-1.el8_8 ol8_appstream 79 k Transaction Summary ================================================================================ Install 7 Packages Total download size: 14 M Installed size: 45 M Downloading Packages: (1/7): git-2.39.3-1.el8_8.x86_64.rpm 233 kB/s | 104 kB 00:00 (2/7): emacs-filesystem-26.1-11.el8.noarch.rpm 155 kB/s | 70 kB 00:00 (3/7): less-530-1.el8.x86_64.rpm 309 kB/s | 164 kB 00:00 (4/7): perl-Error-0.17025-2.el8.noarch.rpm 519 kB/s | 46 kB 00:00 (5/7): perl-Git-2.39.3-1.el8_8.noarch.rpm 722 kB/s | 79 kB 00:00 (6/7): git-core-doc-2.39.3-1.el8_8.noarch.rpm 5.1 MB/s | 3.0 MB 00:00 (7/7): git-core-2.39.3-1.el8_8.x86_64.rpm 12 MB/s | 11 MB 00:00 -------------------------------------------------------------------------------- Total 11 MB/s | 14 MB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : perl-Error-1:0.17025-2.el8.noarch 1/7 Installing : less-530-1.el8.x86_64 2/7 Installing : git-core-2.39.3-1.el8_8.x86_64 3/7 Installing : git-core-doc-2.39.3-1.el8_8.noarch 4/7 Installing : emacs-filesystem-1:26.1-11.el8.noarch 5/7 Installing : perl-Git-2.39.3-1.el8_8.noarch 6/7 Installing : git-2.39.3-1.el8_8.x86_64 7/7 Running scriptlet: git-2.39.3-1.el8_8.x86_64 7/7 Verifying : emacs-filesystem-1:26.1-11.el8.noarch 1/7 Verifying : less-530-1.el8.x86_64 2/7 Verifying : git-2.39.3-1.el8_8.x86_64 3/7 Verifying : git-core-2.39.3-1.el8_8.x86_64 4/7 Verifying : git-core-doc-2.39.3-1.el8_8.noarch 5/7 Verifying : perl-Error-1:0.17025-2.el8.noarch 6/7 Verifying : perl-Git-2.39.3-1.el8_8.noarch 7/7 Installed: emacs-filesystem-1:26.1-11.el8.noarch git-2.39.3-1.el8_8.x86_64 git-core-2.39.3-1.el8_8.x86_64 git-core-doc-2.39.3-1.el8_8.noarch less-530-1.el8.x86_64 perl-Error-1:0.17025-2.el8.noarch perl-Git-2.39.3-1.el8_8.noarch Complete! |
I installed the epel-release container with this command:
dnf install -y epel-release |
Display detailed console log →
Last metadata expiration check: 0:40:34 ago on Thu Dec 21 05:18:09 2023. Dependencies resolved. ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: oracle-epel-release-el8 x86_64 1.0-5.el8 ol8_baseos_latest 15 k Transaction Summary ================================================================================ Install 1 Package Total download size: 15 k Installed size: 18 k Downloading Packages: oracle-epel-release-el8-1.0-5.el8.x86_64.rpm 49 kB/s | 15 kB 00:00 -------------------------------------------------------------------------------- Total 49 kB/s | 15 kB 00:00 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : oracle-epel-release-el8-1.0-5.el8.x86_64 1/1 Verifying : oracle-epel-release-el8-1.0-5.el8.x86_64 1/1 Installed: oracle-epel-release-el8-1.0-5.el8.x86_64 Complete! |
After installing all of these, you’re now ready to install the core rlwrap utility program. Like the other installations, you use:
dnf install -y rlwrap |
Display detailed console log →
Oracle Linux 8 EPEL Packages for Development (x 15 MB/s | 58 MB 00:03 Oracle Linux 8 EPEL Modular Packages for Develo 404 kB/s | 322 kB 00:00 Dependencies resolved. ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: rlwrap x86_64 0.46.1-1.el8 ol8_developer_EPEL 140 k Installing dependencies: perl-File-Slurp noarch 9999.19-19.el8 ol8_appstream 47 k Transaction Summary ================================================================================ Install 2 Packages Total download size: 186 k Installed size: 426 k Downloading Packages: (1/2): perl-File-Slurp-9999.19-19.el8.noarch.rp 94 kB/s | 47 kB 00:00 (2/2): rlwrap-0.46.1-1.el8.x86_64.rpm 242 kB/s | 140 kB 00:00 -------------------------------------------------------------------------------- Total 321 kB/s | 186 kB 00:00 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : perl-File-Slurp-9999.19-19.el8.noarch 1/2 Installing : rlwrap-0.46.1-1.el8.x86_64 2/2 Running scriptlet: rlwrap-0.46.1-1.el8.x86_64 2/2 Verifying : rlwrap-0.46.1-1.el8.x86_64 1/2 Verifying : perl-File-Slurp-9999.19-19.el8.noarch 2/2 Installed: perl-File-Slurp-9999.19-19.el8.noarch rlwrap-0.46.1-1.el8.x86_64 Complete! |
At this point, you need to create a sandboxed user account for the Docker instance because as a developer using the root user for simple tasks is a bad idea. While you could do this with a Docker command, the Oracle 23c Free edition raised a lock on the /etc/group file when I tried it. Naturally, that’s not a problem because you can connect as the root user with this syntax:
docker exec -it -u root oracle23c bash |
As the root user, create a student account as a developer account in the Oracle 23c Free container:
useradd -u 501 -g dba -G users -d /home/student -s /bin/bash/ -c "Student" -n student |
You’ll be unable to leverage the tnsnames.ora file unless you alter the prior command to replace dba with oinstall or add the following command:
usermod -a -G oinstall student |
Exit the Oracle 23c Free container as the root user and reconnect as the student user with this syntax:
docker exec -it --user student oracle23c bash |
While you’re connected as the root user, you should create an upload directory as a subdirectory of the $ORACLE_BASE directory. The $ORACLE_BASE directory in the Oracle Database 23c Free Docker image is the /opt/oracle directory.
You should use the following syntax to create the upload directory and change its permission to that of the Oracle Database 23c Free installation (for a future blog post on developing external table deployment on the Docker image):
mkdir /opt/oracle chown -R oracle:install /opt/oracle/upload |
You also can add the following student function to the Ubuntu student user’s .bashrc file. It means all you need to type to connect to the Oracle Database 23c Free Docker instance is “student“. I like shortcuts like this one, which let you leverage one-line Python commands.
student () { # Discover the fully qualified program name. path=`which docker 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} = "docker" ]]; then python -c "import subprocess; subprocess.run(['docker exec -it --user student oracle23c bash'], shell=True)" else echo "Docker is unavailable: Install the docker package." fi } |
Open a Ubuntu Terminal shell and type a student function name to connect to the Docker Oracle Database 23c Free instance where you can now test things like external tables with the SQL*Plus command line without installing it on the Ubuntu local operating system.
student@student-virtual-machine:~$ student [student@d28375f0c43f ~]$ sqlplus c##student/student@free SQL*Plus: Release 23.0.0.0.0 - Production on Wed Jan 3 02:14:22 2024 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Last Successful login time: Wed Jan 03 2024 01:56:44 +00: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> |
Then, I added this sqlplus function to the /home/student/.bashrc file, which is owned by the student user. However, I also added the instruction to change to the student user’s home directory because the Oracle 23c Free container will connect you to the /home/oracle directory by default. I also added the default long list (ll) alias to the .bashrc file.
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 sqlplus "${@}" else echo "Command-line history unavailable: Install the rlwrap package." $ORACLE_HOME/bin/sqlplus "${@}" fi } # Change to the user's home directory. cd ${HOME} # Create a long list alias: alias ll='ls -l --color=auto' |
After you’ve configured your student user, you can configure the oracle user account to work like a regular server. Exit the Docker Oracle Database 23c Free as the student user, then connect as the root user with this command:
docker exec -it -u root oracle23c bash |
As the root user you can become the oracle user with the following command:
su - oracle |
Now, add the following .bashrc shell in the /home/oracle directory:
# The oracle user's .bashrc # Source global definitions if [ -f /etc/bashrc ]; then . /etc/bashrc fi # User specific environment if ! [[ "$PATH" =~ "$HOME/.local/bin:$HOME/bin:" ]] then PATH="$HOME/.local/bin:$HOME/bin:$PATH" fi export PATH # Uncomment the following line if you don't like systemctl's auto-paging feature: # export SYSTEMD_PAGER= # User specific aliases and functions export ORACLE_SID=FREE export ORACLE_BASE=/opt/oracle export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree export PATH=$PATH:/$ORACLE_HOME/bin # Change to the user's home directory. cd ${HOME} # Create a long list alias: alias ll='ls -l --color=auto' 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 sqlplus "${@}" else echo "Command-line history unavailable: Install the rlwrap package." $ORACLE_HOME/bin/sqlplus "${@}" fi } |
You need to manually source the .bashrc for the oracle user because it’s not an externally available user. Use this syntax to connect as the internal user:
sqlplus / as sysdba |
It’ll display:
SQL*Plus: RELEASE 23.0.0.0.0 - Production ON Wed Jan 3 07:08:11 2024 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. ALL rights reserved. 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> |
After all this, I can now click the “up arrow” to edit any of the sqlplus command history. If you like to work inside sqlplus natively, this should help you.
SQL 1 v.s. SQL 3
In Alan Beaulieu’s wonderful book Learning SQL: Generate, Manipulate, and Retrieve Data, he uses a SQL 1 to demonstrates a CROSS JOIN in MySQL counting from 1 to 100. My students always find it difficult to read because the subqueries take so much space it makes it difficult to see the process, for example he gives this SQL 1 solution:
SELECT ones.x + tens.x + 1 AS counter FROM (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 ) ones CROSS JOIN (SELECT 0 AS x UNION ALL SELECT 10 AS x UNION ALL SELECT 20 AS x UNION ALL SELECT 30 AS x UNION ALL SELECT 40 AS x UNION ALL SELECT 50 AS x UNION ALL SELECT 60 AS x UNION ALL SELECT 70 AS x UNION ALL SELECT 80 AS x UNION ALL SELECT 90 AS x ) tens ORDER BY counter; |
While anybody with a command of SQL should be able to see how it works, for those new to SQL it’s difficult. It’s more effective to use a Common Table Expression with the WITH clause because the derived tables become variables in the scope of the WITH clause and the final query works more like a CROSS JOIN between two tables:
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 0 AS x UNION ALL SELECT 10 AS x UNION ALL SELECT 20 AS x UNION ALL SELECT 30 AS x UNION ALL SELECT 40 AS x UNION ALL SELECT 50 AS x UNION ALL SELECT 60 AS x UNION ALL SELECT 70 AS x UNION ALL SELECT 80 AS x UNION ALL SELECT 90 AS x ) SELECT ones.x + tens.x + 1 AS counter FROM ones CROSS JOIN tens ORDER BY counter; |
As always, I hope this helps somebody trying to sort out the syntax and workflow.
AWS EC2 TNS Listener
Having configured an AlmaLinux 8.6 with Oracle Database 11g XE, MySQL 8.0.30, and PostgreSQL 15, we migrated it to AWS EC2 and provisioned it. We used the older and de-supported Oracle Database 11g XE because it didn’t require any kernel modifications and had a much smaller footprint.
I had to address why attempting to connect with the sqlplus utility raised the following error after provisioning a copy with a new static IP address:
ERROR: ORA-12514: TNS:listener does NOT currently know OF service requested IN CONNECT descriptor |
A connection from SQL Developer raises a more addressable error, like:
ORA-17069 |
I immediately tried to check the connection with the tnsping utility and found that tnsping worked fine. However, when I tried to connect with the sqlplus utility it raised an ORA-12514 connection error.
There were no diagnostic steps beyond checking the tnsping utility. So, I had to experiment with what might block communication.
I changed the host name from ip-172-58-65-82.us-west-2.compute.internal to a localhost string in both the listener.ora and tnsnames.ora. The listener.ora file:
# listener.ora Network Configuration FILE: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe) (PROGRAM = extproc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) ) DEFAULT_SERVICE_LISTENER = (XE) |
The tnsnames.ora file:
# tnsnames.ora Network Configuration FILE: XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) |
I suspected that it might be related to the localhost value. So, I checked the /etc/hostname and /etc/hosts files.
Then, I modified /etc/hostname file by removing the AWS EC2 damain address. I did it on a memory that Oracle’s TNS raises errors for dots or periods in some addresses.
The /etc/hostname file:
ip-172-58-65-82 |
The /etc/hosts file:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ip-172-58-65-82 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 ip-172-58-65-82 |
Now, we can connect to the Oracle Database 11g XE instance with the sqlplus utility. I believe this type of solution will work for other AWS EC2 provisioned Oracle databases.
Subscribe via RSS