MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Troubleshoot Oracle Errors

without comments

It’s always a bit difficult to trap errors in SQL*Developer when you’re running scripts that do multiple things. As old as it is, using the SQL*Plus utility and spooling to log files is generally the fastest way to localize errors across multiple elements of scripts. Unfortunately, you must break up you components into local components, like a when you create a type, procedure, function, or package.

This is part of my solution to leverage in-depth testing of the Oracle Database 23ai Free container from an Ubuntu native platform. You can find this prior post shows you how to setup Oracle*Client for Ubuntu and connect to the Oracle Database 23ai Free container.

After you’ve done that, put the following oracle_errors Bash shell function into your testing context, or into your .bashrc file:

# Troubleshooting errors utility function.
oracle_errors ()
{
  #  Oracle Error prefixes qualify groups of error types, like
  #  this subset of error prefixes used in the Bash function.
  # ============================================================
  #  JMS - Java Messaging Errors
  #  JZN - JSON Errors
  #  KUP - External Table Access Errors
  #  LGI - File I/O Errors
  #  OCI - Oracle Call Interface Errors
  #  ORA - Oracle Database Errors
  #  PCC - Oracle Precompiler Errors
  #  PLS - Oracle PL/SQL Errors
  #  PLW - Oracle PL/SQL Warnings
  #  SP2 - Oracle SQL*Plus Errors
  #  SQL - SQL Library Errors
  #  TNS - SQL*Net (networking) Errors
  # ============================================================
 
  # Define a array of Oracle error prefixes.
  prefixes=("jms" "jzn" "kup" "lgi" "oci" "ora" "pcc" "pls" "plw" "sp2" "sql" "tns")
 
  # Prepend the -e for the grep utility to use regular expression pattern matching; and
  # use the ^before the Oracle error prefixes to avoid returning lines that may
  # contain the prefix in a comment, like the word lookup contains the prefix kup.
  for str in ${prefixes[@]}; do
    patterns+=" -e ^${str}"
  done
 
  # Display output from a SQL*Plus show errors command written to a log file when
  # a procedure, function, object type, or package body fails to compile. This
  # prints the warning message followed by the line number displayed.
  patterns+=" -e ^warning"
  patterns+=" -e ^[0-9]/[0-9]"
 
  # Assign any file filter to the ext variable.
  ext=${1}
 
  # Assign the extension or simply use a wildcard for all files.
  if [ ! -z ${ext} ]; then
    ext="*.${ext}"
  else
    ext="*"
  fi
 
  # Assign the number of qualifying files to a variable.
  fileNum=$(ls -l ${ext} 2>/dev/null | grep -v ^l | wc -l)
 
  # Evaluate the number of qualifying files and process.
  if [ ${fileNum} -eq "0" ]; then
    echo "[0] files exist."
  elif [ ${fileNum} -eq "1" ]; then
    fileName=$(ls ${ext})
    find `pwd` -type f | grep -in ${ext} ${patterns}  |
    while IFS='\n' read list; do
      echo "${fileName}:${list}"
    done
  else
    find `pwd` -type f | grep -in ${ext} ${patterns}  |
    while IFS='\n' read list; do
      echo "${list}"
    done
  fi
 
  # Clear ${patterns} variable.
  patterns=""
}

Now, let’s create a debug.txt test file to demonstrate how to use the oracle_errors, like:

ORA-12704: character SET mismatch
PLS-00124: name OF EXCEPTION expected FOR FIRST arg IN exception_init PRAGMA
SP2-00200: Environment error
JMS-00402: Class NOT found
JZN-00001: END OF input

You can navigate to your logging directory and call the oracle_errors function, like:

oracle_errors txt

It’ll return the following, which is file number, line number, and error code:

debug.txt:1:ORA-12704: character set mismatch
debug.txt:2:PLS-00124: name of exception expected for first arg in exception_init pragma
debug.txt:3:SP2-00200: Environment error
debug.txt:4:JMS-00402: Class not found
debug.txt:5:JZN-00001: End of input

There are other Oracle error prefixes but the ones I’ve selected are the more common errors for Java, JavaScript, PL/SQL, Python, and SQL testing. You can add others if your use cases require them to the prefixes array. Just a note for those new to Bash shell scripting the “${variable_name}” is required for arrays.

For a more complete example, I created the following files for a trivial example of procedure overloading in PL/SQL:

  1. tables.sql – that creates two tables.
  2. spec.sql – that creates a package specification.
  3. body.sql – that implements a package specification.
  4. test.sql – that implements a test case using the package.
  5. integration.sql – that calls the the scripts in proper order.

The tables.sql, spec.sql, body.sql, and test.sql use the SQL*Plus spool command to write log files, like:

SPOOL spec.txt
 
-- Insert code here ...
 
SPOOL OFF

The body.sql file includes SQL*Plus list and show errors commands, like:

SPOOL spec.txt
 
-- Insert code here ...
 
LIST
SHOW ERRORS
 
SPOOL OFF

The integration.sql script calls the tables.sql, spec.sql, body.sql, and test.sql in order. Corrupting the spec.sql file by adding a stray “x” to one of the parameter names causes a cascade of errors. After running the integration.sql file with the introduced error, the Bash oracle_errors function returns:

body.txt:2:Warning: Package Body created with compilation errors.
body.txt:148:4/13     PLS-00323: subprogram or cursor 'WARNER_BROTHER' is declared in a      
test.txt:4:ORA-06550: line 2, column 3: 
test.txt:5:PLS-00306: wrong number or types of arguments in call to 'WARNER_BROTHER' 
test.txt:6:ORA-06550: line 2, column 3:

I hope that helps those learning how to program and perform integration testing in an Oracle Database.

Written by maclochlainn

July 9th, 2024 at 4:37 pm

Posted in bash,Database,DBA,Linux,Oracle,Oracle 23ai,pl/sql,sql,SQL Developer,Unix

Tagged with

«