Bash script execution with an output: linuxconfig.org $ cat bash.txt Bash Scripting Tutorial Guide linuxconfig.org $./bash-script.sh bash.txt Number of elements: 4 Bash Scripting Tutorial Guide linuxconfig.org $ Bash if / else / fi statements Simple Bash if/else statement. Please note the spacing inside the [ and ] brackets!
Last week, I wrote about 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. 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 - Drop actor table and actors sequence.
BEGIN FOR i IN ( SELECT objectname, objecttype FROM userobjects WHERE objectname IN ( 'ACTOR', 'ACTORS' ) ) LOOP IF i.objecttype = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE ' i.objectname ' CASCADE CONSTRAINTS'; ELSIF i.objecttype = 'SEQUENCE' THEN EXECUTE IMMEDIATE 'DROP SEQUENCE ' i.objectname; END IF; END LOOP; END; / - Create an actor table. CREATE TABLE actor ( actorid NUMBER CONSTRAINT actorpk PRIMARY KEY, actorname VARCHAR ( 30 ) NOT NULL ); - Create an actors sequence. CREATE SEQUENCE actors; - Insert two rows. INSERT INTO actor VALUES (actors. NEXTVAL, 'Chris Hemsworth' ); INSERT INTO actor VALUES (actors. NEXTVAL, 'Chris Pine' ); INSERT INTO actor VALUES (actors.
NEXTVAL, 'Chris Pratt' ); - Quit session. QUIT; - Drop actor table and actors sequence. BEGIN FOR i IN (SELECT objectname, objecttype FROM userobjects WHERE objectname IN ('ACTOR','ACTORS')) LOOP IF i.objecttype = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE ' i.objectname ' CASCADE CONSTRAINTS'; ELSIF i.objecttype = 'SEQUENCE' THEN EXECUTE IMMEDIATE 'DROP SEQUENCE ' i.objectname; END IF; END LOOP; END; / - Create an actor table. CREATE TABLE actor ( actorid NUMBER CONSTRAINT actorpk PRIMARY KEY, actorname VARCHAR(30) NOT NULL ); - Create an actors sequence. CREATE SEQUENCE actors; - Insert two rows. INSERT INTO actor VALUES (actors.NEXTVAL,'Chris Hemsworth'); INSERT INTO actor VALUES (actors.NEXTVAL,'Chris Pine'); INSERT INTO actor VALUES (actors.NEXTVAL,'Chris Pratt'); - Quit session.
QUIT; The film.sql file. 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 - Drop film table and films sequence. BEGIN FOR i IN ( SELECT objectname, objecttype FROM userobjects WHERE objectname IN ( 'FILM', 'FILMS' ) ) LOOP IF i.objecttype = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE ' i.objectname ' CASCADE CONSTRAINTS'; ELSIF i.objecttype = 'SEQUENCE' THEN EXECUTE IMMEDIATE 'DROP SEQUENCE ' i.objectname; END IF; END LOOP; END; / - Create a film table.
CREATE TABLE film ( filmid NUMBER CONSTRAINT filmpk PRIMARY KEY, filmname VARCHAR ( 30 ) NOT NULL ); - Create an actors sequence. CREATE SEQUENCE films; - Insert four rows. INSERT INTO film VALUES (films. NEXTVAL, 'Thor' ); INSERT INTO film VALUES (films. NEXTVAL, 'Thor: The Dark World' ); INSERT INTO film VALUES (films.
NEXTVAL, 'Star Trek' ); INSERT INTO film VALUES (films. NEXTVAL, 'Star Trek into Darkness' ); INSERT INTO film VALUES (films. NEXTVAL, 'Guardians of the Galaxy' ); - Quit session. QUIT; - Drop film table and films sequence. BEGIN FOR i IN (SELECT objectname, objecttype FROM userobjects WHERE objectname IN ('FILM','FILMS')) LOOP IF i.objecttype = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE ' i.objectname ' CASCADE CONSTRAINTS'; ELSIF i.objecttype = 'SEQUENCE' THEN EXECUTE IMMEDIATE 'DROP SEQUENCE ' i.objectname; END IF; END LOOP; END; / - Create a film table. CREATE TABLE film ( filmid NUMBER CONSTRAINT filmpk PRIMARY KEY, filmname VARCHAR(30) NOT NULL ); - Create an actors sequence. CREATE SEQUENCE films; - Insert four rows.
INSERT INTO film VALUES (films.NEXTVAL,'Thor'); INSERT INTO film VALUES (films.NEXTVAL,'Thor: The Dark World'); INSERT INTO film VALUES (films.NEXTVAL,'Star Trek'); INSERT INTO film VALUES (films.NEXTVAL,'Star Trek into Darkness'); INSERT INTO film VALUES (films.NEXTVAL,'Guardians of the Galaxy'); - Quit session. QUIT; The movie.sql file. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 - Drop movie table and movies sequence. BEGIN FOR i IN ( SELECT objectname, objecttype FROM userobjects WHERE objectname IN ( 'MOVIE', 'MOVIES' ) ) LOOP IF i.objecttype = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE ' i.objectname ' CASCADE CONSTRAINTS'; ELSIF i.objecttype = 'SEQUENCE' THEN EXECUTE IMMEDIATE 'DROP SEQUENCE ' i.objectname; END IF; END LOOP; END; / - Create an movie table.
CREATE TABLE movie ( movieid NUMBER CONSTRAINT moviepk PRIMARY KEY, actorid NUMBER CONSTRAINT movienn1 NOT NULL, filmid NUMBER CONSTRAINT movienn2 NOT NULL, CONSTRAINT actorfk FOREIGN KEY (actorid ) REFERENCES actor (actorid ), CONSTRAINT filmfk FOREIGN KEY (filmid ) REFERENCES film (filmid ) ); - Create table constraint. CREATE SEQUENCE movies; - Insert translation rows.
INSERT INTO movie VALUES ( movies. NEXTVAL, ( SELECT actorid FROM actor WHERE actorname = 'Chris Hemsworth' ), ( SELECT filmid FROM film WHERE filmname = 'Thor' ) ); INSERT INTO movie VALUES ( movies. NEXTVAL, ( SELECT actorid FROM actor WHERE actorname = 'Chris Hemsworth' ), ( SELECT filmid FROM film WHERE filmname = 'Thor: The Dark World' ) ); INSERT INTO movie VALUES ( movies. NEXTVAL, ( SELECT actorid FROM actor WHERE actorname = 'Chris Pine' ), ( SELECT filmid FROM film WHERE filmname = 'Star Trek' ) ); INSERT INTO movie VALUES ( movies.
NEXTVAL, ( SELECT actorid FROM actor WHERE actorname = 'Chris Pine' ), ( SELECT filmid FROM film WHERE filmname = 'Star Trek into Darkness' ) ); INSERT INTO movie VALUES ( movies. NEXTVAL, ( SELECT actorid FROM actor WHERE actorname = 'Chris Pratt' ), ( SELECT filmid FROM film WHERE filmname = 'Guardians of the Galaxy' ) ); - Quit session. QUIT; - Drop movie table and movies sequence.
![]()
BEGIN FOR i IN (SELECT objectname, objecttype FROM userobjects WHERE objectname IN ('MOVIE','MOVIES')) LOOP IF i.objecttype = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE ' i.objectname ' CASCADE CONSTRAINTS'; ELSIF i.objecttype = 'SEQUENCE' THEN EXECUTE IMMEDIATE 'DROP SEQUENCE ' i.objectname; END IF; END LOOP; END; / - Create an movie table. CREATE TABLE movie ( movieid NUMBER CONSTRAINT moviepk PRIMARY KEY, actorid NUMBER CONSTRAINT movienn1 NOT NULL, filmid NUMBER CONSTRAINT movienn2 NOT NULL, CONSTRAINT actorfk FOREIGN KEY (actorid) REFERENCES actor (actorid), CONSTRAINT filmfk FOREIGN KEY (filmid) REFERENCES film(filmid)); - Create table constraint. CREATE SEQUENCE movies; - Insert translation rows.
1 2 3 4 5 6 7 8 9 10 11 - Format query. COL filmactors FORMAT A40 HEADING 'Actors in Films' - Diagnostic query. SELECT a.actorname ', ' f.filmname AS filmactors FROM actor a INNER JOIN movie m ON a.actorid = m.actorid INNER JOIN film f ON m.filmid = f.filmid; - Quit the session. QUIT; - Format query. COL filmactors FORMAT A40 HEADING 'Actors in Films' - Diagnostic query. SELECT a.actorname ', ' f.filmname AS filmactors FROM actor a INNER JOIN movie m ON a.actorid = m.actorid INNER JOIN film f ON m.filmid = f.filmid; - 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 createuser.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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 - Drop the sample user table. BEGIN FOR i IN ( SELECT username FROM dbausers 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; - Drop the sample user table. BEGIN FOR i IN (SELECT username FROM dbausers 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 listoracle.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 listoracle.sh script because you provide the password on the command line. It’s better to provide the password as you run the script. Recent Posts. Things Written About Things Written About Pages.
Blogroll. Archives.
I will add an item on a context menu, called ' Open with ABC'. This context menu appear when I right-clicked a folder. When the ' Open with ABC' is selected, it will launch a Java program, ' abc.class'. That Java program need ' directory path' of that chosen folder, as argument. How my Bash script will look like? Informations you might need:.
java: usr/bin/java. abc Java program: home/john/myapp/abc.class. The script will be put inside home/john/.config/caja/scripts. The john is my username for Linux Mint MATE 17.2, with caja as file manager Below questions might close to my problem:. My Bash script looks like this: #!/bin/bash java -cp '$HOME/myapp' abc $CAJASCRIPTSELECTEDFILEPATHS Explanations: Since my program was placed inside here: /home/john/myapp/abc.class. I must always use -cp java options to locate my 'abc' program first. The $HOME represents my home directory, /home/john,.
The $CAJASCRIPTSELECTEDFILEPATHS environment variable represents the path of the folder I selected, which eventually being passed as argument to 'abc' program. Note that these solution is made in caja specific environment - no guarantee to work in elsewhere. Credits to @pranav, thanks.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |