Archive for the ‘SQL*Plus’ Category
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.
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.
Wrapping sqlplus
After sorting out the failures of Oracle Database 11g (11.2.0) on AlmaLinux, I grabbed the Enterprise Linux 9 rlwrap library. The rlwrap is a ‘readline wrapper’ that uses the GNU readline library to
allow the editing of keyboard input for any other command. Input history is remembered across invocations, separately for each command; history completion and search work as in bash and completion word
lists can be specified on the command line.
Installed it with the dnf utility:
dnf install -y rlwrap |
It gave me this log file:
Last metadata expiration check: 0:53:30 ago on Fri 02 Dec 2022 01:07:54 AM EST. Dependencies resolved. ================================================================================================================================ Package Architecture Version Repository Size ================================================================================================================================ Installing: rlwrap x86_64 0.45.2-3.el9 epel 132 k Transaction Summary ================================================================================================================================ Install 1 Package Total download size: 132 k Installed size: 323 k Downloading Packages: rlwrap-0.45.2-3.el9.x86_64.rpm 162 kB/s | 132 kB 00:00 -------------------------------------------------------------------------------------------------------------------------------- Total 117 kB/s | 132 kB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Preparing : 1/1 Installing : rlwrap-0.45.2-3.el9.x86_64 1/1 Running scriptlet: rlwrap-0.45.2-3.el9.x86_64 1/1 Verifying : rlwrap-0.45.2-3.el9.x86_64 1/1 Installed: rlwrap-0.45.2-3.el9.x86_64 Complete! |
Then, I added this sqlplus function to the student account’s .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 } |
Then, I connected to the old, but tiny, footprint of Oracle Database 11g XE for testing, which worked:
Yes, I couldn’t resist. After all Version 11 was the last non-pluggable release and it’s been 11 years since its release. A double lucky 11.
Naturally, you can always use vi (or vim) to edit the command history provided you include the following command in your .bashrc file:
set -o vi |
Next, I’ll build a new VM instance with the current version of Oracle Database XE for student testing.
As always, I hope this helps those working with Oracle’s database products.
Oracle’s Sparse Lists
Oracle’s PL/SQL Programming Language is really quite nice. I’ve written 8 books on it and still have fun coding in it. One nasty little detail about Oracle’s lists, introduced in Oracle 8 as PL/SQL Tables according their documentation, is they rely on sequential numeric indexes. Unfortunately, Oracle lists support a DELETE method, which can create gaps in the sequential indexes.
Oracle calls a sequence without gaps densely populated and a sequence with gaps sparsely populated. This can cause problems when PL/SQL code inadvertently removes elements at the beginning, end, or somewhere in the middle of the list. That’s because a program can then pass the sparsely populated list as a parameter to another stored function or procedure where the developer may traverse the list in a for-loop. That traversal may raise an exception in a for-loop, like this when it has gaps in the index sequence:
DECLARE * ERROR AT line 1: ORA-01403: no data found ORA-06512: AT line 20 |
Oracle’s myriad built-in libraries don’t offer a function to compact a sparsely populated list into a densely populated list. This post provides a compact stored procedure that converts a sparsely populated list to a densely populated list.
The first step to using the compact stored procedure requires that you create an object type in SQL, like this list of 20-character strings:
DROP TYPE list; CREATE OR REPLACE TYPE list IS TABLE OF VARCHAR2(20); / |
Now, you can implement the compact stored procedure by passing the User-Defined Type as it’s sole parameter.
CREATE OR REPLACE PROCEDURE compact ( sparse IN OUT LIST ) IS /* Declare local variables. */ iterator NUMBER; -- Leave iterator as null. /* Declare new list. */ dense LIST := list(); BEGIN /* Initialize the iterator with the starting value, which is necessary because the first element of the original list could have been deleted in earlier operations. Setting the initial iterator value to the first numeric index value ensures you start at the lowest available index value. */ iterator := sparse.FIRST; /* Convert sparsely populated list to densely populated. */ WHILE (iterator <= sparse.LAST) LOOP dense.EXTEND; dense(dense.COUNT) := sparse(iterator); iterator := sparse.NEXT(iterator); END LOOP; /* Replace the input parameter with the compacted list. */ sparse := dense; END; / |
Before we test the compact stored procedure, let’s create deleteElement stored procedure for our testing:
CREATE OR REPLACE PROCEDURE deleteElement ( sparse IN OUT LIST , element IN NUMBER ) IS BEGIN /* Delete a value. */ sparse.DELETE(element); END; / |
Now, let’s use an anonymous block to test compacting a sparsely populated list into a densely populated list. The test program will remove the first, last, and one element in the middle before printing the sparsely populated list’s index and string values. This test will show you gaps in the remaining non-sequential index values.
After you see the gaps, the test program compacts the remaining list values into a new densely populated list. It then prints the new index values with the data values.
DECLARE /* Declare a four item list. */ lv_strings LIST := list('one','two','three','four','five','six','seven'); BEGIN /* Check size of list. */ dbms_output.put_line('Print initial list size: ['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); /* Delete a value. */ deleteElement(lv_strings,lv_strings.FIRST); deleteElement(lv_strings,3); deleteElement(lv_strings,lv_strings.LAST); /* Check size of list. */ dbms_output.put_line('Print modified list size: ['||lv_strings.COUNT||']'); dbms_output.put_line('Print max index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); FOR i IN 1..lv_strings.LAST LOOP IF lv_strings.EXISTS(i) THEN dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']'); END IF; END LOOP; /* Call a procedure by passing current sparse collection and the procedure returns dense collection. */ dbms_output.put_line('==================================='); dbms_output.put_line('Compacting list.'); compact(lv_strings); dbms_output.put_line('==================================='); /* Print the new maximum index value and list size. */ dbms_output.put_line('Print new index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); FOR i IN 1..lv_strings.COUNT LOOP dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']'); END LOOP; dbms_output.put_line('==================================='); END; / |
It produces output, like:
Print initial list size: [7] =================================== Print modified list size: [4] Print max index and size: [6][4] =================================== List list index and item: [2][two] List list index and item: [4][four] List list index and item: [5][five] List list index and item: [6][six] =================================== Compacting list. =================================== Print new index and size: [4][4] =================================== List list index and item: [1][two] List list index and item: [2][four] List list index and item: [3][five] List list index and item: [4][six] =================================== |
You can extend this concept by creating User-Defined Types with multiple attributes, which are essentially lists of tuples (to draw on Pythonic lingo).
Wrap Oracle SQL*Plus
One of the key problems with Oracle’s deployment is that you can not use the up-arrow key to navigate the sqlplus command-line history. Here’s little Bash shell function that you can put in your .bashrc file. It requires you to have your system administrator install the rlwrap package, which wraps the sqlplus command-line history.
You should also set the $ORACLE_HOME environment variable before you put this function in your .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 } |
If you port this shell script to an environment where rlwrap is not installed, it simply prints the error message and advises you to install the rlwrap package.
As always, I hope this helps those looking for a solution.
DBeaver for PostgreSQL
I’m migrating my database classes from the Oracle database to the PostgreSQL database. Using the Oracle Express Edition has always required a virtualized image because students use Windows and Mac OS. Also, the university doesn’t like my use of a virtualized image. Virtualization imposes incremental cost on students to have high end laptops.
The available Docker images don’t typically support the Oracle Express Edition. That means there are licensing implications tied to Oracle.
As a committee, we decided to use PostgreSQL as our new database platform. We opted to use PostgreSQL over MySQL because it supports arrays and stored procedures. PostgreSQL PL/pgSQL are a solid implementation of stored procedures. While MySQL supports a stored procedure language, it effectively discourages using it.
We use sqlplus, mysql, or psql Command-Line Interface (CLI) as the primary interface. That’s because CLI supports production-oriented deployment and DevOps practices. The Open Source DBeaver project provides a clean native GUI management console for PostgreSQL. DBeaver also supports other databases, which pgAdmin doesn’t.
You click the Download button from the DBeaver home page. It support Windows, Mac OS, and Linux distributions. I downloaded the Linux RPM package to my Downloads directory. You can run the RPM with the following syntax:
rpm -ivh dbeaver-ce-6.2.3-stable.x86_64.rpm |
It doesn’t check for dependencies. The installation is quick. You can verify that it’s installed with the following command:
which dbeaver |
It installs here:
/usr/bin/dbeaver |
If you failed to install the PostgreSQL JAR file, you’ll see the following error message:
It will look like the following on Linux:
You should have a user in the pg_hba.conf file, like the following:
host all all 127.0.0.1/32 trust |
As always, I hope this helps those solving problems.
External Tables
Oracle Database 9i introduced external tables. You can create external tables to load plain text files by using Oracle SQL*Loader. Alternatively, you can create external tables that load and unload files by using Oracle Data Pump. This article demonstrates both techniques.
You choose external tables that use Oracle SQL*Loader when you want to import plain text files. There are three types of plain text files. They are comma-separated value (CSV), tab-separated value (TSV), and position specific text files.
External tables that use Oracle Data Pump don’t work with plain text files. They work with an Oracle proprietary format. That means you load source files previously created by an Oracle Data Pump export. You typically create external tables with Oracle Data Pump when you’re moving large data sets between database instances.
External tables use Oracle’s virtual directories. An Oracle virtual directory is an internal reference in the data dictionary. A virtual directory maps a unique directory name to a physical directory on the local operating system. Virtual directories were simple before Oracle Database 12c gave us the multitenant architecture. In a multitenant database there are two types of virtual directories. One services the schemas of the Container Database (CDB) and it’s in the CDB’s SYS schema. The other services the schemas of a Pluggable Database (PDB) and it’s in the ADMIN schema for the PDB.
You can create a CDB virtual directory as SYSTEM user with the following syntax in Windows:
SQL> CREATE DIRECTORY upload AS 'C:\Data\Upload'; |
or, like this in Linux or Unix:
SQL> CREATE DIRECTORY upload AS '/u01/app/oracle'; |
There are some subtle differences between these two statements. Windows directories or folders start with a logical drive letter, like C:\, D:\, and so forth. Linux and Unix directories start with a mount point like /u01.
One of the subtle differences is directory and file ownership. You can change ownership for a directory in Windows as the Administrator account. The change makes the directory publically accessible, and that’s probably fine for a test database. After such a change, the Oracle user can find the external file even when parent directories aren’t navigable. Although, a production database on Windows would requires more skill at setting and restricting file permissions.
Linux and Unix directories require that the oracle user can navigate the tree from the mount point to the target physical directory. Also, you must designate the ownership of external files as the same as the Oracle Database user. Assuming a standard install of the Oracle Database 11g XE instance, you would issue the following shell command as the root user to change file ownership and access privileges:
# chown –R oracle:dba /u01/app/oracle/upload # chmod –R 755 /u01/app/oracle/upload |
After you create the virtual directory, you must grant privileges or a role to the user that defines the external table. While data and log files should be separated, this example assumes they co-exist in the same directory.
The following statement grants read privilege for the data file and write privileges for the log files to a CDB user. You should run this statement as the system user.
SQL> GRANT read, WRITE ON DIRECTORY upload TO c##importer; |
or, like this in non-multitenant database or PDB user:
SQL> GRANT read, WRITE ON DIRECTORY upload TO importer; |
The last preparation steps require a plain text file in the physical directory. Let’s create a CSV file of key Avenger characters, and name it the avenger.csv file.
The avenger.csv file holds the following values:
1,'Anthony','Stark','Iron Man' 2,'Thor','Odinson','God of Thunder' 3,'Steven','Rogers','Captain America' 4,'Bruce','Banner','Hulk' 5,'Clinton','Barton','Hawkeye' 6,'Natasha','Romanoff','Black Widow' |
You create the external table after creating the virtual directory, granting read and write privileges on the virtual directory, and creating an external physical file. The syntax for the CREATE TABLE statement of an external table is very similar to the syntax of an ordinary table. The difference between the two types of tables is a clause. An internal table has a STORAGE clause, while an external table has an ORGANIZATION EXTERNAL clause.
The following creates the avenger table as an external table:
SQL> CREATE TABLE avenger 2 ( avenger_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , character_name VARCHAR2(20)) 6 ORGANIZATION EXTERNAL 7 ( TYPE oracle_loader 8 DEFAULT DIRECTORY upload 9 ACCESS PARAMETERS 10 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII 11 BADFILE 'UPLOAD':'avenger.bad' 12 DISCARDFILE 'UPLOAD':'avenger.dis' 13 LOGFILE 'UPLOAD':'avenger.log' 14 FIELDS TERMINATED BY ',' 15 OPTIONALLY ENCLOSED BY "'" 16 MISSING FIELD VALUES ARE NULL) 17 LOCATION ('avenger.csv')) 18 REJECT LIMIT UNLIMITED; |
Lines 1 through 5 create the columns of the avenger table. Lines 6 through 17 contain the ORGANIZATION EXTERNAL clause. Line 7 designates the external table as managed by the Oracle SQL*Loader utility. Line 8 sets the default virtual directory. Lines 11 through 12 set the bad, discard, and log file location. The bad and discard files keep all that can’t be read. The log file keeps all rows read by a query against the avenger table.
You also have the option of making all reads automatic parallel. You simply add a PARALLEL clause, like this:
19 PARALLEL; |
A simple query with SQL*Plus formatting lets us test whether the avenger table works. The query to display all columns of all rows is:
SQL> COLUMN first_name FORMAT A10 SQL> COLUMN last_name FORMAT A10 SQL> COLUMN character_name FORMAT A15 SQL> SELECT * FROM avenger; |
Yields the following formatted output:
AVENGER_ID FIRST_NAME LAST_NAME CHARACTER_NAME
---------- ---------- ---------- ---------------
1 Anthony Stark Iron Man
2 Thor Odinson God of Thunder
3 Steven Rogers Captain America
4 Bruce Banner Hulk
5 Clinton Barton Hawkeye
6 Natasha Romanoff Black Widow
6 rows selected. |
It’s possible to redefine the avenger table to use either relative or fixed positional columns. You change the ACCESS PARAMETERS clause on lines 9 through 16 to make this change.
The following ACCESS PARAMETERS clause runs across lines 9 through 19 and creates relative position definition:
9 ACCESS PARAMETERS 10 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII 11 BADFILE 'UPLOAD':'avenger.bad' 12 DISCARDFILE 'UPLOAD':'avenger.dis' 13 LOGFILE 'UPLOAD':'avenger.log' 14 FIELDS 15 MISSING FIELD VALUES ARE NULL 16 ( avenger_id CHAR(4) 17 , first_name CHAR(20) 18 , last_name CHAR(20) 19 , character_name CHAR(4))) |
You can change from the relative position, to a fixed position by changing lines 16 through 19. The change for fixed length strings is:
16 ( avenger_id POSITION 1:4 17 , first_name POSITION 5:24 18 , last_name POSITION 25:44 19 , character_name POSITION 45:64)) |
Having worked with the Oracle SQL*Loader version of external tables, lets create one that uses Oracle Data Pump. Assuming we keep the same data structure, drop the avenger table, and create a catalog managed avenger_internal table.
This statement creates the avenger_internal table:
SQL> CREATE TABLE avenger_internal 2 ( avenger_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , character_name VARCHAR2(20)); |
To avoid writing six INSERT statements, you can write one INSERT statement with a query against the SQL*Loader avenger table. The syntax for that INSERT statement is:
SQL> INSERT INTO avenger_internal 2 SELECT * FROM avenger; |
With an internally managed table, you create an avenger_export table that uses Oracle Data Pump like this:
SQL> CREATE TABLE avenger_export 2 ORGANIZATION EXTERNAL 3 ( TYPE oracle_datapump 4 DEFAULT DIRECTORY upload 5 LOCATION ('avenger_export.dmp')) AS 6 SELECT avenger_id 7 , first_name 8 , last_name 9 , character_name 10 FROM avenger_internal; |
The CREATE TABLE statement exports data to the avenger_export.dmp file immediately. You must drop and recreate the avenger_export table to get a fresh extract of the avenger_internal table’s data. You must also remove the previous avenger_export.dmp file before you try to recreate the avenger_export table.
You raise the following error when you fail to remove the previous export file:
CREATE TABLE avenger_export * ERROR AT line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-11012: FILE avenger_export.dmp IN /u01/... already EXISTS |
This is a simple example with only four columns. You might think you can use the SELECT * as the SELECT-list of the query on lines 6 through 10. If you’re running Oracle Database 12c, you can use the shorter syntax, but if you’re running Oracle Database 11g you can’t. If you attempt it in an Oracle Database 11g instance, the CREATE TABLE statement returns the following error:
ERROR at line 6:
ORA-30656: COLUMN TYPE NOT supported ON external organized TABLE |
You create an avenger_import table with another twist on this now familiar Oracle SQL syntax. The CREATE TABLE statement is:
SQL> CREATE TABLE avenger_import 2 ( avenger_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , character_name VARCHAR2(20)) 6 ORGANIZATION EXTERNAL 7 ( TYPE oracle_datapump 8 DEFAULT DIRECTORY up2load 9 LOCATION ('avenger_export.dmp')); |
Like the export process, the import process happens immediately when the CREATE TABLE statement runs. A query against the avenger_import table would show you the original six rows we started with in the plain text files.
This article has introduced Oracle external tables. It has shown you how to import plain text files with SQL*Loader. It has also shown you how to export files from tables.
Substitutable Columns
Oracle’s substitutable columns are interesting and substantially different than Oracle’s nested tables. The benefit of substitutable columns is that you can create one for an object type or any subtypes of that object type. Unfortunately, you can’t create the same behavior with nested tables because Oracle’s implementation of collection types are always final data types and you can’t extend their behaviors.
The Oracle Database has three types of collections. Two are SQL scoped collection types and the remaining one is a PL/SQL-only collection. You can only use the two SQL scoped collection types as column data types. One of the SQL-scoped collection types is an Attribute Data Type (ADT), which uses a base data type of DATA, NUMBER, or VARCHAR2.
The base data types of a UDT are scalar data types and scalar data types are data types that hold one thing. The other SQL-scoped collection type is a collection of User-Defined Types (UDTs), which are object types that you create like record structures by assembling sets of basic scalar data types. The elements of a UDT are known as members, whereas the instances of a collection are known as elements because they indexed in a set.
You can join a row with any nested table by using a cross join because they match the row with the nested table by using an ID-dependent join. An ID-dependent join is inexpensive because it relies on a structural dependency, the existence of the nested table in a column of a row. Typical joins on the other hand are joins between two tables or two copies of the same table. These non ID-dependent joins use at least matching values in one column of each table or one column of two copies of a table.
Joins between substitutable columns that hold UDTs are unlike joins between nested tables. The following sets up an example to demonstrate how you can join the non-substitutable columns of a row with the substitutable columns.
- You need a base UDT object type that you can extend, where extend means you can create a subtype of the base object type. While this is straight forward when you create an Oracle object type with methods, it isn’t necessarily straight forward when you want to simply create a base data structure as a generalized type with subtypes.
The important clause is overriding the
FINALdefault by making the base typeNOT FINAL. The example useBASE_Tas the generalized type or data structure of a substitutable column:CREATE OR REPLACE TYPE base_t AS OBJECT ( base_id NUMBER ) NOT FINAL; /
- After you create your base data structure, you create a specialized subtype. The following example creates a
PERSON_Ttype and accepts the default ofFINAL, which means you can’t create another subtype level.CREATE OR REPLACE TYPE person_t UNDER base_t ( first_name VARCHAR2(20) , middle_name VARCHAR2(20) , last_name VARCHAR2(20)); /
- With a generalized
BASE_Ttype and a specializedPERSON_Tsubtype, you create aCUSTOMERtable with a substitutableCUSTOMER_NAMEcolumn. TheCUSTOMER_NAMEcolumn uses the generalizedBASE_Tdata type. You should also create aCUSTOMER_Ssequence that you can use as a surrogate key column for the table.CREATE TABLE customer ( customer_id NUMBER , customer_name BASE_T ); CREATE SEQUENCE customer_s;
- You can now populate the table with instances of the
BASE_Ttype or thePERSON_Tsubtype. The following inserts three rows into theCUSTOMERtable. One for Hank Pym the original Ant-Man, one for Scott Lang the succeeding Ant-Man, and another for Darren Cross the original Yellowjacket.INSERT INTO customer VALUES ( customer_s.NEXTVAL , person_t( customer_s.CURRVAL , first_name => 'Hank' , middle_name => NULL , last_name => 'Pym')); INSERT INTO customer VALUES ( customer_s.NEXTVAL , person_t( customer_s.CURRVAL , first_name => 'Scott' , middle_name => NULL , last_name => 'Lang')); INSERT INTO customer VALUES ( customer_s.NEXTVAL , person_t( customer_s.CURRVAL , first_name => 'Darren' , middle_name => NULL , last_name => 'Cross'));
- The significance or problem associated with substitutable columns is that the actual columns of the object data type are hidden, which means you can’t query them like they’re nested elements of the substitutable column. The following query demonstrates what happens when you try to access those hidden member columns:
SELECT customer_id , customer_name.base_id , customer_name.first_name , customer_name.middle_name , customer_name.last_name FROM customer;
It returns the following error message:
, customer_name.last_name * ERROR at line 5: ORA-00904: "CUSTOMER_NAME"."LAST_NAME": invalid identifier - This error message may lead you to call the
CUSTOMER_NAMEcolumn in a subquery and use theTABLEfunction to convert it to a result set. However, it also fails because a UDT object type by itself is an ordinary object type not a collection of object types. TheTABLEfunction can’t promote the single instance to collection.SELECT * FROM TABLE(SELECT TREAT(customer_name AS person_t) FROM customer);
It returns the following error message:
FROM TABLE(SELECT TREAT(customer_name AS person_t) FROM customer) * ERROR at line 2: ORA-22905: cannot access rows from a non-nested table item - The non-nested table error message should lead you to wrap the call to the
TREATfunction in a call to theCOLLECTfunction, like this:COL base_id FORMAT 9999 HEADING "Base|ID #" COL customer_name FORMAT A38 HEADING "Customer Name" COL first_name FORMAT A6 HEADING "First|Name" COL middle_name FORMAT A6 HEADING "Middle|Name" COL last_name FORMAT A6 HEADING "Last|Name" SELECT * FROM TABLE( SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte FROM customer);
It returns the substitutable column’s hidden column labels and their values:
Base First Middle Last ID # Name Name Name ----- ------ ------ ------ 1 Hank Pym 2 Scott Lang 3 Darren Cross - After learning how to unwrap the hidden columns of the substitutable column, you can now join the ordinary columns to the hidden columns like this:
COL customer_id FORMAT 9999 HEADING "Customer|ID #" COL base_id FORMAT 9999 HEADING "Base|ID #" COL customer_name FORMAT A38 HEADING "Customer Name" COL first_name FORMAT A6 HEADING "First|Name" COL middle_name FORMAT A6 HEADING "Middle|Name" COL last_name FORMAT A6 HEADING "Last|Name" SELECT c.customer_id , o.* FROM customer c INNER JOIN TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte FROM customer) o ON c.customer_id = o.base_id ORDER BY c.customer_id;
It returns the ordinary column and substitutable column’s hidden column labels and their values:
Customer Base First Middle Last ID # ID # Name Name Name -------- ----- ------ ------ ------ 1 1 Hank Pym 2 2 Scott Lang 3 3 Darren Cross - The preceding query only returns values when the substitutable column holds a value. It fails to return a value when the substitutable column holds a null value. You need to use a
LEFT JOINto ensure you see all ordinary columns whether or not the substitutable column holds a value.COL customer_id FORMAT 9999 HEADING "Customer|ID #" COL base_id FORMAT 9999 HEADING "Base|ID #" COL customer_name FORMAT A38 HEADING "Customer Name" COL first_name FORMAT A6 HEADING "First|Name" COL middle_name FORMAT A6 HEADING "Middle|Name" COL last_name FORMAT A6 HEADING "Last|Name" SELECT c.customer_id , o.* FROM customer c LEFT JOIN TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte FROM customer) o ON c.customer_id = o.base_id ORDER BY c.customer_id;
It returns the ordinary column and substitutable column’s hidden column labels and their values when the substitutable column holds an instance value. However, it only returns the ordinary column when the substitutable column holds a null value, as shown below:
Customer Base First Middle Last ID # ID # Name Name Name -------- ----- ------ ------ ------ 1 1 Hank Pym 2 2 Scott Lang 3 3 Darren Cross 4 - It should be noted that queries like this have a cost, and that cost is high. So, you should only implement substitutable columns when the maintenance coding costs (or sustaining engineering) outweighs the processing cost.
You can determine the cost like this:
EXPLAIN PLAN SET STATEMENT_ID = 'Strange' FOR SELECT c.customer_id , o.* FROM customer c LEFT JOIN TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte FROM customer) o ON c.customer_id = o.base_id ORDER BY c.customer_id;
You can query the cost like this:
SET LINESIZE 130 SELECT * FROM TABLE(dbms_xplan.display(NULL,'Strange'));
It should return something like this for the sample table and solution:
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------- Plan hash value: 2373055701 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8168 | 550K| | 167 (2)| 00:00:03 | | 1 | SORT ORDER BY | | 8168 | 550K| 624K| 167 (2)| 00:00:03 | |* 2 | HASH JOIN OUTER | | 8168 | 550K| | 32 (4)| 00:00:01 | | 3 | TABLE ACCESS FULL | CUSTOMER | 5 | 15 | | 2 (0)| 00:00:01 | | 4 | VIEW | | 8168 | 526K| | 29 (0)| 00:00:01 | | 5 | COLLECTION ITERATOR PICKLER FETCH| | 8168 | | | 29 (0)| 00:00:01 | | 6 | SORT AGGREGATE | | 1 | 14 | | | | | 7 | TABLE ACCESS FULL | CUSTOMER | 5 | 70 | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C"."CUSTOMER_ID"="O"."SYS_NC_ROWINFO$"."BASE_ID"(+))
It only raises the last column in the SELECT-list because that’s the first place where it fails to recognize an identifier, which is a valid column name in scope of the query.
As always, I hope this explains how to insert and query the hidden columns of a substitutable column, and how you join ordinary columns and hidden columns of a substitutable column from a table.
Finding Direct Indexes
If you’re not using Toad DBA Suite, it’s sometimes hard to find solutions. Somebody wanted to know how to find indexes that aren’t indirect. Indirect indexes are those created for a primary key because a primary key column or set of columns are both not null and uniquely constrained. Likewise, you create a unique index when you can create a unique constraint. You can’t drop a unique index for a primary key without dropping the primary key or unique constraint that indirectly created it.
The following query returns indexes with one or more columns that are created by a CREATE INDEX statement on a target table. It excludes unique indexes created by a primary key constraint, and it returns the relative position of columns in an index:
COLUMN sequence_name FORMAT A22 HEADING "Sequence Name" COLUMN column_position FORMAT 999 HEADING "Column|Position" COLUMN column_name FORMAT A22 HEADING "Column|Name" SELECT uin.index_name , uic.column_position , uic.column_name FROM user_indexes uin INNER JOIN user_ind_columns uic ON uin.index_name = uic.index_name AND uin.table_name = uic.table_name WHERE uin.table_name = UPPER('&&table_name') AND NOT uin.index_name IN (SELECT constraint_name FROM user_constraints WHERE table_name = UPPER('&&table_name')) ORDER BY uin.index_name , uic.column_position; |
It can be rewritten into a function, which can then drop indexes based on a table name:
CREATE OR REPLACE FUNCTION drop_indexes_on ( pv_table_name VARCHAR2 ) RETURN NUMBER IS /* A return value. */ lv_return NUMBER := 0; /* A query to return only directly created indexes. */ CURSOR find_indexes_on ( cv_table_name VARCHAR2 ) IS SELECT DISTINCT ui.index_name FROM user_indexes ui INNER JOIN user_ind_columns uic ON ui.index_name = uic.index_name AND ui.table_name = uic.table_name WHERE ui.table_name = UPPER(cv_table_name) AND NOT ui.index_name IN (SELECT constraint_name FROM user_constraints WHERE table_name = UPPER(cv_table_name)); /* Declare function autonomous. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* Drop the indexes on a table. */ FOR i IN find_indexes_on(pv_table_name) LOOP EXECUTE IMMEDIATE 'DROP INDEX '||i.index_name; lv_return := 1; END LOOP; RETURN lv_return; END drop_indexes_on; / |
You can call the drop_on_indexes_on function like this:
SELECT drop_indexes_on(UPPER('address_lab')) FROM dual; |
Hope this helps those who need to work with dropping indexes.
Subscribe via RSS