Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 3 Instructions

without comments

Lab #3: Oracle Assignment

Objectives

The lab is designed to teach you how to work with composite data types in the scope of an anonymous PL/SQL programs. You evaluate and create an anonymous PL/SQL block that accepts three input parameters-a variable length string, a number, and a date. The anonymous block PL/SQL program should achieve these objectives:

  • Learn how to declare a PL/SQL record type.
  • Learn how to evaluate random string inputs and determine if they’re a number, string, or date.
  • Learn how to use Oracle’s regular expression functions (pp. 1,000 thru 1,017 in the textbook).
  • Learn how to cast strings as number, string, or date.
  • Learn how to check the physical size of strings before assigning them to internally declared variables.
  • Learn how to assign individual variables to a record structure.
  • Learn how to print the contents of a record structure.

Business Scenario

Application development involves writing and testing software. As software developers become more capable, they begin writing more complex programs.

The lab is designed to teach you how to write a PL/SQL program that inspect input parameters to verify whether they are a string, number, or string that may be cast as a date.

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 how to check for various types of strings and numbers. There are different approaches but these work and they may help you solve the lab.

  • How to find a number in a string?

    • 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

    DECLARE
  /* Declare a collection of strings. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Declare a variable of the string collection. */
  lv_strings  LIST := list('10','Ten','Twelve12');
BEGIN
  /* Loop through list of values to find only the numbers. */
  FOR i IN 1..lv_strings.COUNT LOOP
    IF REGEXP_LIKE(lv_strings(i),'^[[:digit:]]*$') THEN
      dbms_output.put_line('Print number ['||lv_strings(i)||']');
    END IF;
  END LOOP;
END;
/

    It prints:

    Print NUMBER [10]

    The following anonymous block returns numbers, strings, and strings that meet the general date formats of Oracle. Unfortunately, the general date format doesn’t guarantee the day of the month fits within the range of valid dates for a given month, and the 3-character string format doesn’t guarantee a valid 3-character month.

    You can modify this logic below to identify a number and a string, which may include numbers. You can also use the verify_date function from the How to filter date strings article to confirm a valid date values. Just make sure that you change the verify_date function so that it returns a null value from the function when the date is invalid. 

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

    DECLARE
  /* Declare a collection of strings. */
  TYPE list IS TABLE OF VARCHAR2(12);
  /* Declare a variable of the string collection. */
  lv_strings  LIST := list('10','Ten','Twelve12','28-FEB-2016');
BEGIN
  /* Loop through list of values to find only the numbers. */
  FOR i IN 1..lv_strings.COUNT LOOP
    IF REGEXP_LIKE(lv_strings(i),'^[[:digit:]]*$') OR
       REGEXP_LIKE(lv_strings(i),'^[0-9]{2,2}-[[:alpha:]]{3,3}-([0-9]{2,2}|[0-9]{4,4})$') OR
       REGEXP_LIKE(lv_strings(i),'^[[:alnum:]]*$') THEN
       dbms_output.put_line('Print string ['||lv_strings(i)||']');
    END IF;
  END LOOP;
END;
/

    It prints:

    Print string [10]
Print string [Ten]
Print string [Twelve12]
Print string [28-FEB-2016]

Students may want or need supplemental articles that let them review tips and techniques. The following is a function that checks for valid dates inside strings. You should run this type of check after you rule out a number and alphanumeric string.

The lab has one part. The test cases should submit the arguments in different order sequences, which means you should test the following:

  • A date, number, and string
  • A number, date, and string
  • A string, number, and date

This means that you need to loop through the argument list. The most effective way to do that is to put the arguments into a list of strings, and loop through the list of strings.

Lab Description

[25 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 three string parameters and loads them into a collection of 100-character variable length strings. You assign substitution variables with numbers as names. The example below shows an anonymous block with two substitution variables that can be provided as arguments to a PL/SQL program.

DECLARE
  lv_input1  VARCHAR2(100);
  lv_input2  VARCHAR2(100);
  ...
BEGIN
  lv_input1 := '&1';
  lv_input2 := '&2';
  ...
END;
/

After declaring the individual string variables, you need to read through the collection of strings and find a number, date, and string value to assign to a local variable of the user-defined three_type RECORD type. Your program should manage when one or more the three parameters fail to validate as a distinct number, date, or alphanumeric string. The program should only assign the last number, date, or string to the record type, and you should consider a branching IF-statement without a default ELSE block.

The local three_type RECORD data type should have the following description:

Record Type: STRUCT
Member Name Member Type
xnum NUMBER
xdate DATE
xstring VARCHAR2(30)

While it is possible to write the logic to validate a date inside your anonymous block program, it is much simpler to modify the verify_date program from the blog entry (from the How to filter date strings link in the Instructional Material section). You would modify the verify_date function to return a null value for any invalid date. Inside the branching IF-statement, you would check whether the value return is not null before assigning it to the date member of the local three_type RECORD variable.

After assigning the available values to the local variable of the three_type RECORD type, you should then print the values from the variable of the record type in the following order: number, string, and date. You should write all data output to a single log file.

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_lab3.sql script three times and inspect it’s log file three times.

  • Test 1: Only two valid data types as parameters in this order: number and string. 

    sqlplus -s student/student @apply_plsql_lab3.sql 38 catch22

    You should see the following in the log file:

    Record  [38] [catch22] []

  • Test 2: All three valid data types as parameters in this order: number, string, and date. 

    sqlplus -s student/student @apply_plsql_lab3.sql 41 drone 13-OCT-1307

    You should see the following in the log file:

    Record  [41] [drone] [13-OCT-1307]

  • Test 3: All three valid data types as parameters in this order: date, string, and number. 

    sqlplus -s student/student @apply_plsql_lab3.sql 31-APR-2017 dagger 81

    You should see the following in the log file, which should be printed in the same order:

    Record  [81] [dagger] []

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_lab3.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_lab3.txt -exec rm -f {} \;
 
# Create log file with header information.
echo 'Log: apply_plsql_lab3.txt' >> apply_plsql_lab3.txt
echo '==================================================' >> apply_plsql_lab3.txt
 
# Call script file with a valid number, string, and null value for date.
echo 'Test Case: Valid number, string, null             '  >> apply_plsql_lab3.txt
echo '--------------------------------------------------' >> apply_plsql_lab3.txt
sqlplus -s student/student @apply_plsql_lab3.sql 38 catch22 '' 2>/dev/null |
while IFS='\n' read msg; do
  echo $msg >> apply_plsql_lab3.txt
done
 
# Call script file with a valid number, string, and date.
echo '==================================================' >> apply_plsql_lab3.txt
echo 'Test Case: Valid number, string, and date         ' >> apply_plsql_lab3.txt
echo '--------------------------------------------------' >> apply_plsql_lab3.txt
sqlplus -s student/student @apply_plsql_lab3.sql 41 aerodromes '13-OCT-1307' 2>/dev/null |
while IFS='\n' read msg; do
  echo $msg >> apply_plsql_lab3.txt
done
 
# Call script file with a valid number, string, and invalid date.
echo '==================================================' >> apply_plsql_lab3.txt
echo 'Test Case: Valid number, string, and invalid date ' >> apply_plsql_lab3.txt
echo '--------------------------------------------------' >> apply_plsql_lab3.txt
sqlplus -s student/student @apply_plsql_lab3.sql '31-APR-2017' dagger 81 2>/dev/null |
while IFS='\n' read msg; do
  echo $msg >> apply_plsql_lab3.txt
done
echo '==========================================' >> apply_plsql_lab3.txt
  • After you create the apply_plsql_lab3.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_lab3.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_lab3.sh script as follows from the lab3 directory: 

    ./apply_plsql_lab3.sh

  • You can read the apply_plsql_lab3.txt file with the following command: 

    cat apply_plsql_lab3.txt

    You should see the following log file:

    Log: apply_plsql_lab3.txt
==================================================
Test Case: Valid number, string, null
--------------------------------------------------
Record [38] [catch22] []
 
PL/SQL procedure successfully completed.
 
==================================================
Test Case: Valid number, string, and date
--------------------------------------------------
Record [41] [aerialdromes] [13-OCT-07]
 
PL/SQL procedure successfully completed.
 
==================================================
Test Case: Valid number, string, and invalid date
--------------------------------------------------
Record [81] [dagger] []
 
PL/SQL procedure successfully completed.
 
==========================================

Written by michaelmclaughlin

December 15th, 2016 at 11:29 am

Posted in