Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 2.2 Instructions

without comments

Lab #2.2: Oracle Assignment

Objectives

The lab is designed to teach you how to write anonymous block PL/SQL programs that evaluate and correct where possible input parameters. It specifically achieves these objectives:

  • Learn how to create basic anonymous block PL/SQL programs.
  • Learn how to assign substitution variables to an anonymous block PL/SQL program.
  • Learn how to write an if-then-elsif-else block.
  • Learn how to print output from a PL/SQL program to the console or external log file.
  • Learn how to run anonymous block PL/SQL programs interactively in SQL*Plus environment.
  • Learn how to run anonymous block PL/SQL program files from the operating system.Learn how to use Oracle’s SQL built-in functions, like SUBSTR and LENGTH.
  • Learn how to manage evaluate and correct variable length inputs that violate the size parameters of the program.
  • Learn how to build effective test cases to evaluate PL/SQL programming.

Business Scenario

Application development involves writing and testing software. As a rule, many software developers begin their careers in quality and assurance. After running unit tests, many developers learn how to automate their program testing.

The lab is designed to teach you how to write a small PL/SQL program, a SQL test case, and then automate the SQL test case with Bash shell scripting. As a rule, organizing file permissions is the largest problem with this type of automation test.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.

Instructional Material →

The following code represents a sample returnable1.sql file. It let’s you print the message “Hello World!” on the console. It sets the appropriate SQL*Plus environment commands to support effectively formatted output in the interactive console and external log 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

-- This is a sample file, and you should display a multiple-line comment
-- identifying the file, author, and date. Here's the format:
/*
   Name:   rerunnable1.sql
   Author: Student first and last name
   Date:   DD-MON-YYYY
*/
 
-- Put code that you call from other scripts here because they may create
-- their own log files. For example, you call other program scripts by
-- putting an "@" symbol before the name of a relative file name or a 
-- fully qualified file name. 
 
 
-- Open your log file and make sure the extension is ".txt".
SPOOL rerunnable.txt
 
-- Add an environment command to allow PL/SQL to print to console.
SET SERVEROUTPUT ON SIZE UNLIMITED
 
 
-- Put your code here, like this "Hello World!" program.
BEGIN
  dbms_output.put_line('Hello World!');
END;
/
 
-- Close your log file.
SPOOL OFF
 
-- Instruct the program to exit SQL*Plus, which you need when you call a
-- a program from the command line. Please make sure you comment the 
-- following command when you want to remain inside the interactive
-- SQL*Plus connection.
QUIT;

Assuming you name the file that contains the preceding code as rerunnable1.sql and you put it in the relative directory where you login to SQL*Plus, you can call it like so:

@rerunnable1.SQL

It prints:

Hello World!

You can modify the program to use a substation variable, like so:

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

-- This is a sample file, and you should display a multiple-line comment
-- identifying the file, author, and date. Here's the format:
/*
   Name:   rerunnable1.sql
   Author: Student first and last name
   Date:   DD-MON-YYYY
*/
 
-- Put code that you call from other scripts here because they may create
-- their own log files. For example, you call other program scripts by
-- putting an "@" symbol before the name of a relative file name or a 
-- fully qualified file name. 
 
 
-- Open your log file and make sure the extension is ".txt".
-- ------------------------------------------------------------
-- Remove any spool filename and spool off command when you call
-- the script from a shell script.
-- ------------------------------------------------------------
SPOOL rerunnable.txt
 
-- Add an environment command to allow PL/SQL to print to console.
SET SERVEROUTPUT ON SIZE UNLIMITED
SET VERIFY OFF
 
-- Put your code here, like this "Hello Whom!" program.
BEGIN
  dbms_output.put_line('Hello '||'&1'||'!');
END;
/
 
-- Close your log file.
-- ------------------------------------------------------------
-- Remove any spool filename and spool off command when you call
-- the script from a shell script.
-- ------------------------------------------------------------
SPOOL OFF
 
-- Instruct the program to exit SQL*Plus, which you need when you call a
-- a program from the command line. Please make sure you comment the 
-- following command when you want to remain inside the interactive
-- SQL*Plus connection.
QUIT;

You should note that line 20 sets a SQL*Plus environment variable that suppressed displaying a set of old and new value prompts.

Assuming you name the file that contains the preceding code as rerunnable1.sql and you put it in the relative directory where you login to SQL*Plus, you can call it like so:

@rerunnable1.SQL Harry

After prompting you for the value of $1, it prints:

Hello Harry!

The Bash shell script won’t work properly when you have either of these SQL*Plus statements in your SQL script.

-- SPOOL file_name.txt

-- SPOOL OFF

You can test this program from the command-line with the following call, which uses the SQL*Plus “silent” [-s] option flag:

sqlplus -s student/student @rerunnable1.sql Harry

You provided the value at runtime, so the log file will only contain the following:

Hello Harry!

Students may want or need supplemental articles that let them review tips and techniques. The following is brief list of web articles that may help complete this lab.

The lab has two parts the description of the problem to solve and the test case that helps the student evaluate whether they’ve solved the problem.

Lab Description

[15 points] Click the Lab Instructions link to open the instructions inside the current webpage.

Lab Instructions →

You need to write an anonymous block PL/SQL program that accepts a single parameter. If there is no parameter, you should print “Hello World!”; if the parameter is 10 characters or less, you should print the parameter instead of “World”; if the parameter is more than 10 characters in length, you should print the first 10 characters of the parameter instead of “World”.

You should use two variables inside your program. The lv_raw_input should contain the input value regardless of length. The lv_input should contain the first ten characters of the input value.

You should remember that all dynamic assignments should be made inside the execution block. There’s no way for you to manage assignment errors raised inside the declaration block. You should consider the execution block like the try{} element of a Java program and the exception block as the catch{} element of a Java program.

An IF-ELSIF-ELSE block should manage all three possibilities, and print the appropriate message to the console or log file. You should name your lab solution file as apply_plsql_lab2-2.sql, which is what you see in the test case section.

Test Case

Click the Test Case Instructions link to open the test case instructions inside the current webpage.

Test Case Instructions →

You test the apply_plsql_lab2-2.sql script three times and inspect it’s log file three times.

  • Test 1: The null parameter. 

    sqlplus -s student/student @apply_plsql_lab2-2.sql ''

  • Test 2: The a string 10 characters or less.

    You should see the following in the log file:

    Hello World!

    sqlplus -s student/student @apply_plsql_lab2-2.sql Harry

    You should see the following in the log file:

    Hello Harry!

  • Test 3: The a string longer than 10 characters. 

    sqlplus -s student/student @apply_plsql_lab2-2.sql Rumpelstiltskin

    You should see the following in the log file:

    Hello Rumpelstil!

Click the Test Case Automation link to open the instructions on how to create an automated series of test cases that write to a single log file.

Test Case Automation →

The log writing capability of SQL*Plus is cumbersome compared to the power of the bash shell in Linux or Unix, and Microsoft’s Powershell in Windows. You would script a Linux bash shell solution to show all the test case results.

  • The apply_plsql_lab2-2.sh code automates writing a log file for all three test cases.

    • 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

    #!/bin/bash
 
# Remove file from system.
find . -name apply_plsql_lab2-2.txt -exec rm -f {} \;
 
# Create log file with header information.
echo 'Log: apply_plsql_lab2-2.txt' >> apply_plsql_lab2-2.txt
echo '==========================================' >> apply_plsql_lab2-2.txt
 
# Call script file with a string less than 10 characters.
echo 'Test Case: String less than 11 characters'  >> apply_plsql_lab2-2.txt
echo '------------------------------------------' >> apply_plsql_lab2-2.txt
sqlplus -s student/student @apply_plsql_lab2-2.sql Harry 2>/dev/null |
while IFS='\n' read msg; do
  echo $msg >> apply_plsql_lab2-2.txt
done
 
# Call script file with a string great than 10 characters.
echo '==========================================' >> apply_plsql_lab2-2.txt
echo 'Test Case: String more than 10 characters'  >> apply_plsql_lab2-2.txt
echo '------------------------------------------' >> apply_plsql_lab2-2.txt
sqlplus -s student/student @apply_plsql_lab2-2.sql Rumpelstilskin 2>/dev/null |
while IFS='\n' read msg; do
  echo $msg >> apply_plsql_lab2-2.txt
done
 
# Call script file with a null value.
echo '==========================================' >> apply_plsql_lab2-2.txt
echo 'Test Case: Empty string'                    >> apply_plsql_lab2-2.txt
echo '------------------------------------------' >> apply_plsql_lab2-2.txt
sqlplus -s student/student @apply_plsql_lab2-2.sql '' 2>/dev/null |
while IFS='\n' read msg; do
  echo $msg >> apply_plsql_lab2-2.txt
done
echo '==========================================' >> apply_plsql_lab2-2.txt
  • After you create the apply_plsql_lab2-2.sh script, you need to change its file privileges. They should become read, write, and execute for the owner, and read and execute for the group and other users. You can make that change with the Linux or Unix chmod command, like: 

    chmod 755 apply_plsql_lab2-2.sh

    The Bash shell script won’t work properly when you have either of these SQL*Plus statements in your SQL script.

    -- SPOOL file_name.txt

    -- SPOOL OFF

  • After changing the file privileges, you can run the apply_plsql_lab2-2.sh script as follows from the lab2 directory: 

    ./apply_plsql_lab2-2.sh

  • You can read the apply_plsql_lab2-2.txt file with the following command: 

    cat apply_plsql_lab2-2.txt

    You should see the following log file:

    Log: apply_plsql_lab2-2.txt
==========================================
Test Case: String less than 11 characters
------------------------------------------
Hello Harry!
 
PL/SQL procedure successfully completed.
 
==========================================
Test Case: String more than 10 characters
------------------------------------------
Hello Rumpelstil!
 
PL/SQL procedure successfully completed.
 
==========================================
Test Case: Empty string
------------------------------------------
Hello World!
 
PL/SQL procedure successfully completed.
 
==========================================

Written by michaelmclaughlin

December 15th, 2016 at 11:28 am

Posted in