Oracle 11g | MacLochlainns Weblog

This post is designed to illustrate PL/SQL code with techniques employed before Oracle Database 12c with manual sequence references rather than auto generated IDs. The trick is small but should be made with older code. It impacts creation statement, conditional drop statements, and modification to stored procedures that manage transactions across two or more tables.

The key to the difference is in the table creation. Prior to Oracle Database 12c, there were no automatic sequences that you could assign to table definitions. Tables were created without sequences, and sequences were created independently. Likewise, there was no “IF EXISTS” clause for a DROP statement, which meant conditional drops were required as anonymous blocks.

Old tables and sequences from the Oracle Database 11g would use the following definitions:

/* Conditionally drop grandma table and grandma_s sequence. */
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ','GRANDMA','GRANDMA_SEQ')
            ORDER BY 1 DESC, 2) LOOP
    IF i.object_type = 'TABLE' THEN
      /* Use the cascade constraints to drop the dependent constraint. */
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
/* Create the table. */
CREATE TABLE GRANDMA
( grandma_id     NUMBER       CONSTRAINT grandma_nn1 NOT NULL
, grandma_house  VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL
, CONSTRAINT grandma_pk       PRIMARY KEY (grandma_id)
);
 
/* Create the sequence. */
CREATE SEQUENCE grandma_seq;
 
/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        CONSTRAINT tweetie_bird_nn1 NOT NULL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn2 NOT NULL
, grandma_id          NUMBER        CONSTRAINT tweetie_bird_nn3 NOT NULL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);
 
/* Create sequence. */
CREATE SEQUENCE tweetie_bird_seq;

The key to the differences in Oracle Database 12c are in the table creation, and then in the INSERT statement because you can exclude the surrogate key column. A surrogate key column is also known as an ID column or sequence identified column.

New tables and sequences from the Oracle Database 12c forward use the following definitions to manage sequences:

/* Conditionally drop grandma table and grandma_s sequence. */
DROP TABLE IF EXISTS tweetie_bird;
DROP TABLE IF EXISTS grandma;
 
/* Create the table. */
CREATE TABLE GRANDMA
( grandma_id     NUMBER        DEFAULT grandma_seq.NEXTVAL
, grandma_house  VARCHAR2(30)  CONSTRAINT grandma_nn2 NOT NULL
, CONSTRAINT grandma_pk        PRIMARY KEY (grandma_id)
);
 
/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        DEFAULT tweetie_bird_seq.NEXTVAL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn1 NOT NULL
, grandma_id          NUMBER        DEFAULT grandma_seq.CURRVAL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);

This is the pattern that I setup to explain the new default surrogate primary and foreign keys when I wrote the Oracle Database 12c PL/SQL Programming book (pp. 20-22). Unfortunately, it was just a simple example and fits this use case:

You write one create_something procedure or function, also known interchangeably as a method, to insert initial records; and
You write one add_something method add a subsequent dependent record; and

This is a common practice by many but it creates unnecessary code that you need to maintain, which translates to accumulated technical debt throughout a design. The technical debt grows by a factor of two when you want to overload behaviors for security features or batch processing.

A better solution is to write one method that accommodates both an creates a new record and adds dependent records. The warner_brother procedure will do that later in this post. To prepare for the warner_brother procedure the dependent tweetie_bird table needs to change.

The default of the .currval of a “parent” table’s sequence value is no longer guaranteed in an independent action. The create and add methods now share the same method, and inspection of an existing independent record is necessary for both the create and add methods.

The new tweetie_bird table looks like:

/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        DEFAULT tweetie_bird_seq.NEXTVAL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn1 NOT NULL
, grandma_id          NUMBER        CONSTRAINT tweetie_bird_nn2 NOT NULL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);

The next part of the post discusses how to write the create and add procedure in pre-12c and 12c forward implementations. Clearly, the automatic generated sequence values are the best approach when you use user-defined names for them as shown earlier.

The warner_brother procedure models the idea that any grandma may have one or more Tweetie birds, which means you must check if there’s a row in the grandma table. If there’s a row in the grandma table, you shouldn’t insert a new row in the grandma table. The internal get_grandma_id function returns:

A zero if there is no existing row in the grandma table. After which, the nested if-block will insert a new row into the grandma table; and then assign the grandma_seq.CURRVAL value to a local variable.
An existing grandma_id value, which can be used as a foreign key value in the subsequent insert statement to the tweety_bird table.

The warner_brother procedure for Oracle Database 11g is shown below:

/* Create or replace procedure warner_brother. */
CREATE OR REPLACE PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2 ) IS
 
  /* Declare a local variable for an existing grandma_id. */
  lv_grandma_id   NUMBER;
 
  FUNCTION get_grandma_id
  ( pv_grandma_house  VARCHAR2 ) RETURN NUMBER IS
 
    /* Initialized local return variable. */
    lv_retval  NUMBER := 0;  -- Default value is 0.
 
    /* A cursor that lookups up a grandma's ID by their name. */
    CURSOR find_grandma_id
    ( cv_grandma_house  VARCHAR2 ) IS
      SELECT grandma_id
      FROM   grandma
      WHERE  grandma_house = cv_grandma_house;
 
  BEGIN   
    /* Assign a grandma_id as the return value when a row exists. */
    FOR i IN find_grandma_id(pv_grandma_house) LOOP
      lv_retval := i.grandma_id;
    END LOOP;
 
    /* Return 0 when no row found and the grandma_id when a row is found. */
    RETURN lv_retval;
  END get_grandma_id;
 
BEGIN
 
  /* Set the savepoint. */
  SAVEPOINT starting;
 
  /*
   *  Identify whether a member account exists and assign it's value
   *  to a local variable.
   */
  lv_grandma_id := get_grandma_id(pv_grandma_house);
 
  /*
   *  Conditionally insert a new member account into the member table
   *  only when a member account does not exist.
   */
  IF lv_grandma_id = 0 THEN
 
    /* Insert grandma. */
    INSERT INTO grandma
    ( grandma_id
    , grandma_house )
    VALUES
    ( grandma_seq.NEXTVAL
    , pv_grandma_house );
    /* Assign grandma_seq.currval to local variable. */
    lv_grandma_id := grandma_seq.CURRVAL;
 
  END IF;
 
  /* Insert tweetie bird. */
  INSERT INTO tweetie_bird
  ( tweetie_bird_id
  , tweetie_bird_house 
  , grandma_id )
  VALUES
  ( tweetie_bird_seq.NEXTVAL
  , pv_tweetie_bird_house
  , lv_grandma_id );
 
  /* If the program gets here, both insert statements work. Commit it. */
  COMMIT;
 
EXCEPTION
  /* When anything is broken do this. */
  WHEN OTHERS THEN
    /* Until any partial results. */
    ROLLBACK TO starting;
END;
/

/* Create or replace procedure warner_brother. */ CREATE OR REPLACE PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 ) IS /* Declare a local variable for an existing grandma_id. */ lv_grandma_id NUMBER; FUNCTION get_grandma_id ( pv_grandma_house VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_grandma_id ( cv_grandma_house VARCHAR2 ) IS SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_grandma_id; BEGIN /* Set the savepoint. */ SAVEPOINT starting; /* * Identify whether a member account exists and assign it's value * to a local variable. */ lv_grandma_id := get_grandma_id(pv_grandma_house); /* * Conditionally insert a new member account into the member table * only when a member account does not exist. */ IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_id , grandma_house ) VALUES ( grandma_seq.NEXTVAL , pv_grandma_house ); /* Assign grandma_seq.currval to local variable. */ lv_grandma_id := grandma_seq.CURRVAL; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_id , tweetie_bird_house , grandma_id ) VALUES ( tweetie_bird_seq.NEXTVAL , pv_tweetie_bird_house , lv_grandma_id ); /* If the program gets here, both insert statements work. Commit it. */ COMMIT; EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; /

The warner_brother procedure for Oracle Database 12c forward simplifies somethings and complicates others. The DEFAULT for the grandma_id foreign key column in the tweetie_bird table must be overwritten with a copy of the correct primary key value. More or less, it requires the correct grandma_id column value by finding the existing row in the grandma table. The internal get_grandma_id function grabs that value while returning a value that precludes writing to the grandma table.

The warner_brother procedure for Oracle Database 12c forward shown below shows that the grandma_id column default value is overridden by a local lv_grandma_id value returned by the internal get_grandma_id function on lines 60 thru 65 below.

/* Create or replace procedure warner_brother. */
CREATE OR REPLACE PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2 ) IS
 
  /* Declare a local variable for an existing grandma_id. */
  lv_grandma_id   NUMBER;
 
  FUNCTION get_grandma_id
  ( pv_grandma_house  VARCHAR2 ) RETURN NUMBER IS
 
    /* Initialized local return variable. */
    lv_retval  NUMBER := 0;  -- Default value is 0.
 
    /* A cursor that lookups up a grandma's ID by their name. */
    CURSOR find_grandma_id
    ( cv_grandma_house  VARCHAR2 ) IS
      SELECT grandma_id
      FROM   grandma
      WHERE  grandma_house = cv_grandma_house;
 
  BEGIN   
    /* Assign a grandma_id as the return value when a row exists. */
    FOR i IN find_grandma_id(pv_grandma_house) LOOP
      lv_retval := i.grandma_id;
    END LOOP;
 
    /* Return 0 when no row found and the grandma_id when a row is found. */
    RETURN lv_retval;
  END get_grandma_id;
 
BEGIN
 
  /* Set the savepoint. */
  SAVEPOINT starting;
 
  /*
   *  Identify whether a member account exists and assign it's value
   *  to a local variable.
   */
  lv_grandma_id := get_grandma_id(pv_grandma_house);
 
  /*
   *  Conditionally insert a new member account into the member table
   *  only when a member account does not exist.
   */
  IF lv_grandma_id = 0 THEN
 
    /* Insert grandma. */
    INSERT INTO grandma
    ( grandma_house )
    VALUES
    ( pv_grandma_house );
    /* Assign grandma_seq.currval to local variable. */
    lv_grandma_id := grandma_seq.CURRVAL;
 
  END IF;
 
  /* Insert tweetie bird. */
  INSERT INTO tweetie_bird
  ( tweetie_bird_house 
  , grandma_id )
  VALUES
  ( pv_tweetie_bird_house
  , lv_grandma_id );
 
  /* If the program gets here, both insert statements work. Commit it. */
  COMMIT;
 
EXCEPTION
  /* When anything is broken do this. */
  WHEN OTHERS THEN
    /* Until any partial results. */
    ROLLBACK TO starting;
END;
/

You can test either version with the following anonymous PL/SQL block:

/* Test the warner_brother procedure. */
BEGIN
  warner_brother( pv_grandma_house      => 'Yellow House'
                , pv_tweetie_bird_house => 'Cage');
  warner_brother( pv_grandma_house      => 'Yellow House'
                , pv_tweetie_bird_house => 'Tree House');
END;
/

Ultimately, we’ll use a natural join to test the integrity of primary and foreign key relationship:

/* Query results from warner_brother procedure. */
COL grandma_id          FORMAT 9999999  HEADING "Grandma|ID #"
COL grandma_house       FORMAT A20      HEADING "Grandma House"
COL tweetie_bird_id     FORMAT 9999999  HEADING "Tweetie|Bird ID"
COL tweetie_bird_house  FORMAT A20      HEADING "Tweetie Bird House"
SELECT *
FROM   grandma NATURAL JOIN tweetie_bird;

As always, I hope this helps those trying to leverage existing PL/SQL code into more modern code.

Reviewing old files, I thought posting my tkprof.ksh would be helpful. So, here’s the script that assumes you’re using Oracle e-Business Suite (Demo database, hence the APPS/APPS connection); and if I get a chance this summer I’ll convert it to Bash shell.

#!/bin/ksh
# -------------------------------------------------------------------------
# Author:   Michael McLaughlin
# Name:     tkprof.ksh
 
# Purpose:  The program takes the following arguments:
#           1. A directory
#           2. A search string
#           3. A target directory
#           It assumes raw trace files have an extension of ".trc".
#           The output file name follows this pattern (because it is
#           possible for multiple tracefiles to be written during the
#           same minute).
# -------------------------------------------------------------------------
 
# Function to find minimum field delimiter.
function min
{
  # Find the whitespace that preceeds the file date.
  until [[ $(ls -al $i | cut -c$minv-$minv) == " " ]]; do
    let minv=minv+1
  done
}
 
# Function to find maximum field delimiter.
function max
{
  # Find the whitespace that succeeds the file date.
  until [[ $(ls -al $i | cut -c$maxv-$maxv) == " " ]]; do
    let maxv=maxv+1
  done
}
 
# Debugging enabled by unremarking the "set -x"
# set -x
 
# Print header information
print =================================================================
print Running [tkprof.ksh] script ...
 
# Evaluate whether an argument is provide and if no argument
# is provided, then substitute the present working directory.
if   [[ $# == 0 ]]; then
  dir=${PWD}  
  str="*"
  des=${PWD}  
elif [[ $# == 1 ]]; then
  dir=${1}
  str="*"
  des=${1}
elif [[ $# == 2 ]]; then
  dir=${1}
  str=${2}
  des=${1}
elif [[ $# == 3 ]]; then
  dir=${1}
  str=${2}
  des=${3}
fi
 
# Evaluate whether the argument is a directory file.
if [[ -d ${dir} ]] && [[ -d ${des} ]]; then
 
  # Print what directory and search string are targets.
  print =================================================================
  print Run in tkprof from [${dir}] directory ...
  print The files contain a string of [${str}] ...
  print =================================================================
 
  # Evaluate whether the argument is the present working
  # directory and if not change directory to that target
  # directory so file type evaluation will work.
  if [[ ${dir} != ${PWD} ]]; then
    cd ${dir} 
  fi
 
  # Set file counter.
  let fcnt=0
 
  # Submit compression to the background as a job.
  for i in $(grep -li "${str}" *.trc); do
 
    # Evaluate whether file is an ordinary file.
    if [[ -f ${i} ]]; then
 
      # Set default values each iteration.
      let minv=40
      let maxv=53
 
      # Increment counter.
      let fcnt=fcnt+1
 
      # Call functions to reset min and max values where necessary.
      min ${i}
      max ${i}
 
      # Parse date stamp from trace file without multiple IO calls.
      # Assumption that the file is from the current year.
      date=$(ls -al ${i} | cut -c${minv}-${maxv}) 
      mon=$(echo ${date} | cut -c1-3)
      yr=$(date          | cut -c25-28)
 
      # Validate month is 10 or greater to pad for reduced whitespace.
      if (( $(echo ${date} | cut -c5-6) < 10 )); then
        day=0$(echo ${date}| cut -c5-5)
        hr=$(echo ${date}  | cut -c7-8)
        min=$(echo ${date} | cut -c10-11)
      else
        day=$(echo ${date} | cut -c5-6)
        hr=$(echo ${date}  | cut -c8-9)
        min=$(echo ${date} | cut -c11-12)
      fi
 
      fn=file${fcnt}_${day}-${mon}-${yr}_${hr}:${min}:${day}
 
      print Old [$i] and new [$des/$fn]
      tkprof ${i} ${des}/${fn}.prf explain=APPS/APPS sort='(prsela,exeela,fchela)'
 
      # Print what directory and search string are targets.
      print =================================================================
    fi
  done
 
else
  # Print message that a directory argument was not provided.
  print You failed to provie a single valid directory argument.
fi

#!/bin/ksh # ------------------------------------------------------------------------- # Author: Michael McLaughlin # Name: tkprof.ksh # Purpose: The program takes the following arguments: # 1. A directory # 2. A search string # 3. A target directory # It assumes raw trace files have an extension of ".trc". # The output file name follows this pattern (because it is # possible for multiple tracefiles to be written during the # same minute). # ------------------------------------------------------------------------- # Function to find minimum field delimiter. function min { # Find the whitespace that preceeds the file date. until [[ $(ls -al $i | cut -c$minv-$minv) == " " ]]; do let minv=minv+1 done } # Function to find maximum field delimiter. function max { # Find the whitespace that succeeds the file date. until [[ $(ls -al $i | cut -c$maxv-$maxv) == " " ]]; do let maxv=maxv+1 done } # Debugging enabled by unremarking the "set -x" # set -x # Print header information print ================================================================= print Running [tkprof.ksh] script ... # Evaluate whether an argument is provide and if no argument # is provided, then substitute the present working directory. if [[ $# == 0 ]]; then dir=${PWD} str="*" des=${PWD} elif [[ $# == 1 ]]; then dir=${1} str="*" des=${1} elif [[ $# == 2 ]]; then dir=${1} str=${2} des=${1} elif [[ $# == 3 ]]; then dir=${1} str=${2} des=${3} fi # Evaluate whether the argument is a directory file. if [[ -d ${dir} ]] && [[ -d ${des} ]]; then # Print what directory and search string are targets. print ================================================================= print Run in tkprof from [${dir}] directory ... print The files contain a string of [${str}] ... print ================================================================= # Evaluate whether the argument is the present working # directory and if not change directory to that target # directory so file type evaluation will work. if [[ ${dir} != ${PWD} ]]; then cd ${dir} fi # Set file counter. let fcnt=0 # Submit compression to the background as a job. for i in $(grep -li "${str}" *.trc); do # Evaluate whether file is an ordinary file. if [[ -f ${i} ]]; then # Set default values each iteration. let minv=40 let maxv=53 # Increment counter. let fcnt=fcnt+1 # Call functions to reset min and max values where necessary. min ${i} max ${i} # Parse date stamp from trace file without multiple IO calls. # Assumption that the file is from the current year. date=$(ls -al ${i} | cut -c${minv}-${maxv}) mon=$(echo ${date} | cut -c1-3) yr=$(date | cut -c25-28) # Validate month is 10 or greater to pad for reduced whitespace. if (( $(echo ${date} | cut -c5-6) < 10 )); then day=0$(echo ${date}| cut -c5-5) hr=$(echo ${date} | cut -c7-8) min=$(echo ${date} | cut -c10-11) else day=$(echo ${date} | cut -c5-6) hr=$(echo ${date} | cut -c8-9) min=$(echo ${date} | cut -c11-12) fi fn=file${fcnt}_${day}-${mon}-${yr}_${hr}:${min}:${day} print Old [$i] and new [$des/$fn] tkprof ${i} ${des}/${fn}.prf explain=APPS/APPS sort='(prsela,exeela,fchela)' # Print what directory and search string are targets. print ================================================================= fi done else # Print message that a directory argument was not provided. print You failed to provie a single valid directory argument. fi

I hope this helps those looking for a solution.

Unless dbca lets us build the listener.ora file, we often leave off some component. For example, running listener control program the following status indicates an incorrectly configured listener.ora file.

lsnrctl status

It returns the following, which displays an endpoint for the XDB Server (I’m using Oracle Database 11g XE because it’s pre-containerized and has a small testing footprint):

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-MAR-2023 00:59:06
 
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                21-MAR-2023 21:17:37
Uptime                    2 days 3 hr. 41 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

The listener is missing the second SID_LIST_LISTENER value of CLRExtProc value. A complete listener.ora file should be as follows for the Oracle Database XE:

# 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)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (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.localdomain)(PORT = 1521))
    )
  )
 
DEFAULT_SERVICE_LISTENER = (XE)

With this listener.ora file, the Oracle listener control utility will return the following correct status, which hides the XDB Server’s endpoint:

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-MAR-2023 02:38:57
 
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                24-MAR-2023 02:38:15
Uptime                    0 days 0 hr. 0 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

It seems a number of examples on the web left the SID_LIST_LISTENER value of CLRExtProc value out of the listener.ora file. As always, I hope this helps those looking for a complete solution rather than generic instructions without a concrete example.

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.

This is a collection of blog posts for installing and configuring AlmaLinux with the Oracle, PostgreSQL, MySQL databases and several programming languages. Sample programs show how to connect PHP and Python to the MySQL database.

I used Oracle Database 11g XE in this instance to keep the footprint as small as possible. It required a few tricks and discovering the missing library that caused folks grief eleven years ago. I build another with a current Oracle Database XE after the new year.

If you see something that I missed or you’d like me to add, let me know. As time allows, I’ll try to do that. Naturally, the post will get updates as things are added later.

This post makes the assumption that you’ve installed the current version of Java and the Java SDK. If you haven’t, you can find instructions on my blog. You will also need to have an installation of the Oracle database running on your server or a remote service, or cloud micro service.

The remainder of this post deals with downloading, installing, and configuring Oracle’s SQL Developer for AlmaLinux 9, which is the GNU version of Red Hat Enterprise 9.

Go to Oracle’s download site and download the sqldeveloper RPM file. You will need to provide your Oracle credentials to download SQL Developer. It will download to your sudoer account’s Download directory. In this example the sudoer user is the student user.

You should see the following web page and click on the Download link, provided you’re installing on Linux it’ll look like the next image.

Then, you need to accept the license and click the Download button. Oracle will prompt you for your credentials if you’re not logged in on the web page already.

Next, you need to navigate to the Downloads directory and install the sqldeveloper RPM. Assuming your sudoer user is student, you can get to the Downloads directory with the following command.

cd ~student/Downloads

Assuming, you downloaded the SQL Developer package, you can use the following command to install any downloaded version of sqldeveloper package.

sudo rpm -Uvh `ls sqldeveloper*.rpm`

The log file for this is:

Display detailed console log →

warning: RPM v3 packages are deprecated: sqldeveloper-22.2.1-234.1810.noarch
Verifying...                          ################################# [100%]
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...                          ################################# [100%]
warning: RPM v3 packages are deprecated: sqldeveloper-22.2.1-234.1810.noarch
Updating / installing...
   1:sqldeveloper-22.2.1-234.1810     ################################# [100%]

Click on Activities link in the upper left corner and then the clustered nine dots to view applications. Choose the SQL Developer icon and double click and you should see the following dialog if you’re a first time user. Unless you’re upgrading, click the No button to proceed.
The first official screen after checking whether you need to transfer existing settings wants to know whether or allow or disallow user tracking. Click the OK button if you don’t mind Oracle tracking your use, or click the Allow automated usage reporting to Oracle checkbox to disallow Oracle tracking your use before you click the OK button.
The next screen lets you set up a TNS names file or use an existing file. I clicked on the XE existing database to continue.

This is a password prompt for the TNS name resolution of XE, which should point to the Oracle Database 11g Express Edition. (I’d use a more current version but I couldn’t resist using the smaller footprint of the pre-containerized Oracle databases.)

Replace XE with the name of a sandboxed user, like student, and the password for the student user before you click the OK button. (If you don’t know what a sandboxed user is, you should. It’s a user with limited access to a database of the same name in the context of an Oracle database. A non-sandboxed user has global privileges like the system user.)
The next screen lets you enter SQL statements agains the student database. You can click the X button in the top right corner to close the application.

You’ve now installed SQL Developer. However, sometimes I want to start SQL Developer from the command-line interface (CLI) but you’ll get a bunch of warnings and unnecessary Java non-critical errors. So, I create an alias to avoid the extraneous noise. I create the sqldeveloper alias in the .bashrc file for it. You can create a sqldeveloper alias by adding the following line to your .bashrc file:

The unnecessary noise when you don’t create a sqldeveloper alias.

Display detailed console log →

Oracle SQL Developer
Copyright (c) 2005, 2021, Oracle and/or its affiliates. All rights reserved.
 
OpenJDK 64-Bit Server VM warning: Options -Xverify:none and -noverify were deprecated in JDK 13 and will likely be removed in a future release.
WARNING: A terminally deprecated method in java.lang.System has been called
WARNING: System::setSecurityManager has been called by org.netbeans.TopSecurityManager (file:/opt/sqldeveloper/netbeans/platform/lib/boot.jar)
WARNING: Please consider reporting this to the maintainers of org.netbeans.TopSecurityManager
WARNING: System::setSecurityManager will be removed in a future release
WARNING: A terminally deprecated method in java.lang.System has been called
WARNING: System::setSecurityManager has been called by oracle.ide.IdeCore (file:/opt/sqldeveloper/ide/extensions/oracle.ide.jar)
WARNING: Please consider reporting this to the maintainers of oracle.ide.IdeCore
WARNING: System::setSecurityManager will be removed in a future release
java.lang.IllegalAccessException: class oracle.ideimpl.config.EnvironOptionsPanel cannot access class com.sun.java.swing.plaf.gtk.GTKLookAndFeel (in module java.desktop) because module java.desktop does not export com.sun.java.swing.plaf.gtk to unnamed module @49c746f
	at java.base/jdk.internal.reflect.Reflection.newIllegalAccessException(Reflection.java:392)
	at java.base/java.lang.reflect.AccessibleObject.checkAccess(AccessibleObject.java:674)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:489)
	at java.base/java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128)
	at java.base/jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347)
	at java.base/java.lang.Class.newInstance(Class.java:645)
	at oracle.ideimpl.config.EnvironOptionsPanel._initLafCombo(EnvironOptionsPanel.java:540)
	at oracle.ideimpl.config.EnvironOptionsPanel.initComponents(EnvironOptionsPanel.java:238)
	at oracle.ideimpl.config.EnvironOptionsPanel.<init>(EnvironOptionsPanel.java:99)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
	at javax.ide.util.MetaClass.newInstance(MetaClass.java:145)
	at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:122)
	at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:65)
	at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)
java.lang.IllegalAccessException: class oracle.ideimpl.config.EnvironOptionsPanel cannot access class com.sun.java.swing.plaf.gtk.GTKLookAndFeel (in module java.desktop) because module java.desktop does not export com.sun.java.swing.plaf.gtk to unnamed module @49c746f
	at java.base/jdk.internal.reflect.Reflection.newIllegalAccessException(Reflection.java:392)
	at java.base/java.lang.reflect.AccessibleObject.checkAccess(AccessibleObject.java:674)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:489)
	at java.base/java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128)
	at java.base/jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347)
	at java.base/java.lang.Class.newInstance(Class.java:645)
	at oracle.ideimpl.config.EnvironOptionsPanel._initLafCombo(EnvironOptionsPanel.java:540)
	at oracle.ideimpl.config.EnvironOptionsPanel.initComponents(EnvironOptionsPanel.java:238)
	at oracle.ideimpl.config.EnvironOptionsPanel.<init>(EnvironOptionsPanel.java:99)
	at oracle.dbtools.raptor.config.EnvironOptionsPanelWrapper.<init>(EnvironOptionsPanelWrapper.java:30)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
	at javax.ide.util.MetaClass.newInstance(MetaClass.java:145)
	at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:122)
	at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:65)
	at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)

Oracle SQL Developer Copyright (c) 2005, 2021, Oracle and/or its affiliates. All rights reserved. OpenJDK 64-Bit Server VM warning: Options -Xverify:none and -noverify were deprecated in JDK 13 and will likely be removed in a future release. WARNING: A terminally deprecated method in java.lang.System has been called WARNING: System::setSecurityManager has been called by org.netbeans.TopSecurityManager (file:/opt/sqldeveloper/netbeans/platform/lib/boot.jar) WARNING: Please consider reporting this to the maintainers of org.netbeans.TopSecurityManager WARNING: System::setSecurityManager will be removed in a future release WARNING: A terminally deprecated method in java.lang.System has been called WARNING: System::setSecurityManager has been called by oracle.ide.IdeCore (file:/opt/sqldeveloper/ide/extensions/oracle.ide.jar) WARNING: Please consider reporting this to the maintainers of oracle.ide.IdeCore WARNING: System::setSecurityManager will be removed in a future release java.lang.IllegalAccessException: class oracle.ideimpl.config.EnvironOptionsPanel cannot access class com.sun.java.swing.plaf.gtk.GTKLookAndFeel (in module java.desktop) because module java.desktop does not export com.sun.java.swing.plaf.gtk to unnamed module @49c746f at java.base/jdk.internal.reflect.Reflection.newIllegalAccessException(Reflection.java:392) at java.base/java.lang.reflect.AccessibleObject.checkAccess(AccessibleObject.java:674) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:489) at java.base/java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128) at java.base/jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347) at java.base/java.lang.Class.newInstance(Class.java:645) at oracle.ideimpl.config.EnvironOptionsPanel._initLafCombo(EnvironOptionsPanel.java:540) at oracle.ideimpl.config.EnvironOptionsPanel.initComponents(EnvironOptionsPanel.java:238) at oracle.ideimpl.config.EnvironOptionsPanel.<init>(EnvironOptionsPanel.java:99) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) at javax.ide.util.MetaClass.newInstance(MetaClass.java:145) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:122) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:65) at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) java.lang.IllegalAccessException: class oracle.ideimpl.config.EnvironOptionsPanel cannot access class com.sun.java.swing.plaf.gtk.GTKLookAndFeel (in module java.desktop) because module java.desktop does not export com.sun.java.swing.plaf.gtk to unnamed module @49c746f at java.base/jdk.internal.reflect.Reflection.newIllegalAccessException(Reflection.java:392) at java.base/java.lang.reflect.AccessibleObject.checkAccess(AccessibleObject.java:674) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:489) at java.base/java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128) at java.base/jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347) at java.base/java.lang.Class.newInstance(Class.java:645) at oracle.ideimpl.config.EnvironOptionsPanel._initLafCombo(EnvironOptionsPanel.java:540) at oracle.ideimpl.config.EnvironOptionsPanel.initComponents(EnvironOptionsPanel.java:238) at oracle.ideimpl.config.EnvironOptionsPanel.<init>(EnvironOptionsPanel.java:99) at oracle.dbtools.raptor.config.EnvironOptionsPanelWrapper.<init>(EnvironOptionsPanelWrapper.java:30) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) at javax.ide.util.MetaClass.newInstance(MetaClass.java:145) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:122) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:65) at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833)

You create the alias like this:

alias  sqldeveloper="sqldeveloper 2>/dev/null &"

That completes the instructions. Good luck with SQL Developer. It’s a great tool.

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!

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.

It was always aware of a problem with Oracle 11g XE on various Linux platforms from 10 years ago. I knew it was misleading but never found the time to explain the error that occurred during the cloning of the instance.

While it would occur when you were on an unsupported version of Linux, it was easy to fix. For example, after downloading the old compressed oracle-xe-11.2.0-1.0.x86_64.rpm.zip file, you uncompress it. Then, you run the file with the following command:

rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

This command will install the packages in verbose syntax and display the following messages:

[sudo] password for mclaughlinm:
Preparing packages for installation...
oracle-xe-11.2.0-1.0
Executing post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.

Connect as the root user to another instance of the terminal and run the following command:

/etc/init.d/oracle-xe configure

You will see the following control output:

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express 
Edition.  The following questions will determine whether the database should 
be starting upon system boot, the ports it will use, and the passwords that 
will be used for database accounts.  Press <Enter> to accept the defaults. 
Ctrl-C will abort.
 
Specify the HTTP port that will be used for Oracle Application Express [8080]:
 
Specify a port that will be used for the database listener [1521]:
 
Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of 
different passwords for each database account.  This can be done after 
initial configuration:
Confirm the password:
 
Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y
 
Starting Oracle Net Listener...Done
Configuring database...grep: /u01/app/oracle/product/11.2.0/xe/config/log/*.log: No such file or directory
grep: /u01/app/oracle/product/11.2.0/xe/config/log/*.log: No such file or directory
Done
/bin/chmod: cannot access '/u01/app/oracle/diag': No such file or directory
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

This looks like an unsolvable problem, and for many it was too hard to solve. Most never knew the next step to take to discover the missing library. The failure actually occurs when the configuration tries to launch SQL*Plus. You can test that by creating the following oracle_env.sh parameter script:

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
 
ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe; export ORACLE_HOME
ORACLE_SID=XE; export ORACLE_SID
NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`; export NLS_LANG
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
 
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
 
if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

Then, source the oracle_env.sh file like this:

. ./oracle_env.sh

As the oracle user, try to connect to the sqlplus executable with this command:

sqlplus / as sysdba

It’ll raise the following error:

sqlplus: error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory

You won’t find the /usr/lib64/libnsl.so.1 because it’s a symbolic link to the /usr/lib64/libnsl-2.29.so shared library file, which you can find on older Fedora installations. AlmaLinux has libnsl2, which you can download from the pgks.org.

After finding the library and installing it in the /usr/lib64 directory, the balance of the fix is to run the cloning manually. This type of error can occur for newer version of the database but it’s easiest to highlight with the Oracle 11g XE installation.

You also can find it in the libnsl2-devel development libraries on the pkgs.org web site:

You may need to build the libnsl.so.1 symbolic link as the root user with the following command:

ln -s libnsl-2.29.so libnsl.so.1

Ensure the file permissions for these files are:

-rwxr-xr-x. 1 root root 218488 Dec  2 01:33 libnsl-2.29.so
lrwxrwxrwx. 1 root root     14 Dec  2 01:39 libnsl.so.1 -> libnsl-2.29.so

After you create the database, you can provision a student user and database, like so:

Oracle Database 11g (Pre-containerization)

After you create and provision the Oracle Database 11g XE, you create an instance with the following two step process.

Create a student Oracle user account with the following command:
CREATE USER student IDENTIFIED BY student DEFAULT TABLESPACE users QUOTA 200M ON users TEMPORARY TABLESPACE temp;
CREATE USER student IDENTIFIED BY student DEFAULT TABLESPACE users QUOTA 200M ON users TEMPORARY TABLESPACE temp;
Grant necessary privileges to the newly created student user:
GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO student;
GRANT CREATE cluster, CREATE indextype, CREATE operator , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO student;

Oracle Database 21c (Post-containerization)

After you create and provision the Oracle Database 21c Express Edition (XE), you can create a c##student container user with the following two step process.

Create a c##student Oracle user account with the following command:
CREATE USER c##student IDENTIFIED BY student DEFAULT TABLESPACE users QUOTA 200M ON users TEMPORARY TABLESPACE temp;
CREATE USER c##student IDENTIFIED BY student DEFAULT TABLESPACE users QUOTA 200M ON users TEMPORARY TABLESPACE temp;
Grant necessary privileges to the newly created c##student user:
GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO c##student;
GRANT CREATE cluster, CREATE indextype, CREATE operator , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO c##student;

As always, it should help you solve new problems.

Students wanted to see how to write PL/SQL functions that accept, process, and return lists of values. I thought it would be cool to also demonstrate coupling of loop behaviors and wrote the example using the 12-Days of Christmas lyrics.

The twelve_days function accepts two different collections. One is an Attribute Data Type (ADT) and the other a User-Defined Type (UDT). An ADT is based on a scalar data type, and a UDT is based on an object type. Object types are basically data structures, and they support both positional and named notation for variable assignments.

The twelve_days function returns a list of string, which is an ADT of the VARCHAR2 data type. Creating the ADT types is easy and a single step, like:

/* Create a days object type. */
CREATE OR REPLACE
  TYPE days IS TABLE OF VARCHAR2(8);
/
 
/* Create a string object type. */
CREATE OR REPLACE
  TYPE song IS TABLE OF VARCHAR2(36);
/

Creating the UDT is more complex and requires two steps. You need to create the UDT object type, or structure, and then the list based on the UDT object type, like:

/* Create a lyric object type. */
CREATE OR REPLACE
  TYPE lyric IS OBJECT
  ( DAY   VARCHAR2(8)
  , gift  VARCHAR2(24));
/
 
/* Create a lyrics object type. */
CREATE OR REPLACE
  TYPE lyrics IS TABLE OF LYRIC;
/

Now, you can create the twelve_days function that uses these ADT and UDT types, like:

CREATE OR REPLACE
  FUNCTION twelve_days
  ( pv_days   DAYS
  , pv_gifts  LYRICS ) RETURN song IS
 
  /* Initialize the collection of lyrics. */
  lv_retval  SONG := song();
 
  /* Local procedure to add to the song. */
  PROCEDURE ADD
  ( pv_input  VARCHAR2 ) IS
  BEGIN
    lv_retval.EXTEND;
    lv_retval(lv_retval.COUNT) := pv_input;
  END ADD;
 
BEGIN
  /* Read forward through the days. */
  FOR i IN 1..pv_days.COUNT LOOP
    ADD('On the ' || pv_days(i) || ' day of Christmas');
    ADD('my true love sent to me:');
 
    /* Read backward through the lyrics based on the ascending value of the day. */
    FOR j IN REVERSE 1..i LOOP
      IF i = 1 THEN
        ADD('-'||'A'||' '||pv_gifts(j).gift);
      ELSE
        ADD('-'||pv_gifts(j).DAY||' '||pv_gifts(j).gift);
      END IF;
    END LOOP;
 
    /* A line break by verse. */
    ADD(CHR(13));
  END LOOP;
 
  /* Return the song's lyrics. */
  RETURN lv_retval;
END;
/

You may notice the local add procedure on lines 10 thru 15. It lets you perform the two tasks required for populating an element in a SQL object type list in one line in the main body of the twelve_days function.

The add procedure first uses the EXTEND function to allocate space before assigning the input value to the newly allocated element in the list. Next, you can call the function inside the following SQL query:

SELECT column_value AS "12-Days of Christmas"
FROM   TABLE(twelve_days(days('first','second','third','fourth'
                             ,'fifth','sixth','seventh','eighth'
                             ,'nineth','tenth','eleventh','twelfth')
                        ,lyrics(lyric(DAY => 'and a', gift => 'Partridge in a pear tree')
                               ,lyric(DAY => 'Two',   gift => 'Turtle doves')
                               ,lyric(DAY => 'Three', gift => 'French hens')
                               ,lyric(DAY => 'Four',  gift => 'Calling birds')
                               ,lyric(DAY => 'Five',  gift => 'Golden rings' )
                               ,lyric(DAY => 'Six',   gift => 'Geese a laying')
                               ,lyric(DAY => 'Seven', gift => 'Swans a swimming')
                               ,lyric(DAY => 'Eight', gift => 'Maids a milking')
                               ,lyric(DAY => 'Nine',  gift => 'Ladies dancing')
                               ,lyric(DAY => 'Ten',   gift => 'Lords a leaping')
                               ,lyric(DAY => 'Eleven',gift => 'Pipers piping')
                               ,lyric(DAY => 'Twelve',gift => 'Drummers drumming'))));

It will print:

12-Days of Christmas
------------------------------------
On the first day of Christmas
my true love sent to me:
-A Partridge in a pear tree
 
On the second day of Christmas
my true love sent to me:
-Two Turtle doves
-and a Partridge in a pear tree
 
On the third day of Christmas
my true love sent to me:
-Three French hens
-Two Turtle doves
-and a Partridge in a pear tree
 
... redacted for space ...
 
On the twelfth day of Christmas
my true love sent to me:
-Twelve Drummers drumming
-Eleven Pipers piping
-Ten Lords a leaping
-Nine Ladies dancing
-Eight Maids a milking
-Seven Swans a swimming
-Six Geese a laying
-Five Golden rings
-Four Calling birds
-Three French hens
-Two Turtle doves
-and a Partridge in a pear tree

As always, I hope the example helps those looking for a solution to this type of problem.

The first time you launch SQL Developer, you may see a very small or tiny display on the screen. With some high resolution screens the text is unreadable. Unless you manually configure the sqldeveloper shortcut, you generally can’t use it.

On my virtualization on a 27″ screen it looks like:

As an Administrator user, you right click the SQLDeveloper icon and click the Compatibility tab, which should look like the following dialog. You need to check the Compatibility Mode, which by default is unchecked with Windows 8 displayed in the select list.

Check the Compatibility Mode box and the select list will no longer be gray scaled. Click on the select list box and choose Windows 7. After the change you should see the following:

After that change, you need to click on the Change high DPI settings gray scaled button, which will display the following dialog box.

Click the Override high DPI scaling behavior check box. It will change the gray highlighted Scaling Performed by select box to white. Then, you click the Scaling Performed by select box and choose the System option.

Click the OK button on the nested SQLDeveloper Properties dialog box. Then, click the Apply button on the SQLDeveloper Properties button and the OK button. You will see a workable SQL Developer interface when you launch the program through your modified shortcut.

MacLochlainns Weblog

Archive for the ‘Oracle 11g’ Category

Comparative Techniques

A tkprof Korn Shell

Listener for APEX

AWS EC2 TNS Listener

AlmaLinux Install & Configuration

AlmaLinux+SQLDeveloper

Wrapping sqlplus

Oracle Library Missing

PL/SQL List Function

Tiny SQL Developer

Recent Posts

Things Written About

Pages

Blogroll

Archives