How to write bash shell scripts against Oracle

Bash Arrays & Oracle

Last week, I wrote about how to use bash arrays and the MySQL database to create unit and integration test scripts. While the MySQL example was nice for some users, there were some others who wanted me to show how to write bash shell scripts for Oracle unit and integration testing. That’s what this blog post does.

If you don’t know much about bash shell, you should start with the prior post to learn about bash arrays, if-statements, and for-loops. In this blog post I only cover how to implement a bash shell script that runs SQL scripts in silent mode and then queries the database in silent mode and writes the output to an external file.

I’ve copied the basic ERD for the example because of a request from a reader. In their opinion it makes cross referencing the two posts unnecessary.

To run the bash shell script, you’ll need the following SQL files, which you can see by clicking not he title below. There are several differences. For example, Oracle doesn’t support a DROP IF EXISTS syntax and requires you to write anonymous blocks in their PL/SQL language; and you must explicitly issue a QUIT; statement even when running in silent mode unlike MySQL, which implicitly issues an exit.

Setup SQL Files ↓

The actor.sql file:

-- Drop actor table and actor_s sequence.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('ACTOR','ACTOR_S')) LOOP
    IF    i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS';
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name;
    END IF;
  END LOOP;
END;
/
 
-- Create an actor table.
CREATE TABLE actor
( actor_id    NUMBER CONSTRAINT actor_pk PRIMARY KEY
, actor_name  VARCHAR(30)  NOT NULL );
 
-- Create an actor_s sequence.
CREATE SEQUENCE actor_s;
 
-- Insert two rows.
INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Hemsworth');
INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Pine');
INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Pratt');
 
-- Quit session.
QUIT;

The film.sql file:

-- Drop film table and film_s sequence.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('FILM','FILM_S')) LOOP
    IF    i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS';
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name;
    END IF;
  END LOOP;
END;
/
 
-- Create a film table.
CREATE TABLE film
( film_id    NUMBER CONSTRAINT film_pk PRIMARY KEY
, film_name  VARCHAR(30)  NOT NULL );
 
-- Create an actor_s sequence.
CREATE SEQUENCE film_s;
 
-- Insert four rows.
INSERT INTO film VALUES (film_s.NEXTVAL,'Thor');
INSERT INTO film VALUES (film_s.NEXTVAL,'Thor: The Dark World');
INSERT INTO film VALUES (film_s.NEXTVAL,'Star Trek');
INSERT INTO film VALUES (film_s.NEXTVAL,'Star Trek into Darkness');
INSERT INTO film VALUES (film_s.NEXTVAL,'Guardians of the Galaxy');
 
-- Quit session.
QUIT;

The movie.sql file:

-- Drop movie table and movie_s sequence.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('MOVIE','MOVIE_S')) LOOP
    IF    i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS';
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name;
    END IF;
  END LOOP;
END;
/
 
-- Create an movie table.
CREATE TABLE movie
( movie_id   NUMBER  CONSTRAINT movie_pk   PRIMARY KEY
, actor_id   NUMBER  CONSTRAINT movie_nn1  NOT NULL
, film_id    NUMBER  CONSTRAINT movie_nn2  NOT NULL
, CONSTRAINT actor_fk FOREIGN KEY (actor_id)
  REFERENCES actor (actor_id)
, CONSTRAINT film_fk  FOREIGN KEY (film_id)
  REFERENCES film(film_id));
 
-- Create table constraint.
CREATE SEQUENCE movie_s;
 
-- Insert translation rows.
INSERT INTO movie
VALUES
( movie_s.NEXTVAL
,(SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Hemsworth')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Thor'));
 
INSERT INTO movie
VALUES
( movie_s.NEXTVAL
,(SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Hemsworth')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Thor: The Dark World'));
 
INSERT INTO movie
VALUES
( movie_s.NEXTVAL
,(SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pine')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Star Trek'));
 
INSERT INTO movie
VALUES
( movie_s.NEXTVAL
,(SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pine')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Star Trek into Darkness'));
 
INSERT INTO movie
VALUES
( movie_s.NEXTVAL
,(SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pratt')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Guardians of the Galaxy'));
 
-- Quit session.
QUIT;

The tables.sql file, lets you verify the creation of the actor, film, and movie tables:

-- Set Oracle column width.
COL table_name FORMAT A30 HEADING "Table Name"
 
-- Query the tables.
SELECT   table_name
FROM     user_tables;
 
-- Exit SQL*Plus.
QUIT;

The results.sql file, lets you see join results from actor, film, and movie tables:

-- Format query.
COL film_actors FORMAT A40 HEADING "Actors in Films"
 
-- Diagnostic query.
SELECT   a.actor_name || ', ' || f.film_name AS film_actors
FROM     actor a INNER JOIN movie m
ON       a.actor_id = m.actor_id INNER JOIN film f
ON       m.film_id = f.film_id;
 
-- Quit the session.
QUIT;

If you don’t have a sample test schema to use to test this script, you can create a sample schema with the following create_user.sql file. The file depends on the existence of a users and temp tablespace.

Click the link below to see the source code for a script that let’s you create a sample user account as the system user:

Create sample User SQL File ↓

You can use the dbms_metadata.get_ddl function to discover the existence of the tablespaces. The following SQL syntax returns the SQL DDL statement that created a users or temp tablespace:

1 2	SET LONG 200000 SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') FROM dual;

You create the sample database with the following SQL statements:

-- Drop the sample user table.
BEGIN
  FOR i IN (SELECT   username
            FROM     dba_users
            WHERE    username = 'SAMPLE') LOOP
    EXECUTE IMMEDIATE 'DROP USER ' || i.username || ' CASCADE';
  END LOOP;
END;
/
 
-- Create the sample user table.
CREATE USER sample IDENTIFIED BY sample
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 50M ON users;
 
-- Grant privileges to sample user.
GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION
,     CREATE TABLE, CREATE TRIGGER, CREATE TYPE
,     CREATE VIEW TO sample;

The following list_oracle.sh shell script expects to receive the username, password, and fully qualified path in that specific order. The script names are entered manually in the array because this should be a unit test script.

This is an insecure version of the list_oracle.sh script because you provide the password on the command line. It’s better to provide the password as you run the script.

#!/usr/bin/bash
 
# Assign user and password
username="${1}"
password="${2}"
directory="${3}"
 
echo "User name:" ${username}
echo "Password: " ${password}
echo "Directory:" ${directory}
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="actor.sql"
cmd[1]="film.sql"
cmd[2]="movie.sql"
 
# Call the array elements.
for i in ${cmd[*]}; do
  sqlplus -s ${username}/${password} @${directory}/${i} > /dev/null
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
sqlplus -s ${username}/${password} @${directory}/tables.sql 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read actor_name; do
  echo $actor_name
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
sqlplus -s ${username}/${password} @${directory}/result.sql 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read actor_name; do
  echo $actor_name
done

The IFS (Internal Field Separator) works with whitespace by default. The IFS on lines 29 and 37 sets the IFS to a line return ('\n'). That’s the trick to display the data, and you can read more about the IFS in this question and answer post.

You can run the shell script with the following syntax:

./list_oracle.sh sample sample /home/student/Code/bash/oracle > output.txt

You can then display the results from the output.txt file with the following command:

cat output.txt command:

It will display the following output:

User name: sample
Password:  sample
Directory: /home/student/Code/bash/oracle
 
Table Name
------------------------------
MOVIE
FILM
ACTOR
 
Actors in Films
----------------------------------------
Chris Hemsworth, Thor
Chris Hemsworth, Thor: The Dark World
Chris Pine, Star Trek
Chris Pine, Star Trek into Darkness
Chris Pratt, Guardians of the Galaxy

As always, I hope this helps those looking for a solution.

Written by maclochlainn

May 21st, 2015 at 1:16 am

Posted in bash,Fedora,Linux,Oracle,Oracle 11g,Oracle 12c,Oracle XE,sql

Tagged with Oracle DBA, Oracle Developer

MacLochlainns Weblog

Bash Arrays & Oracle

Recent Posts

Things Written About

Pages

Blogroll

Archives