Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 2.1 Instructions

without comments

Lab #2.1: Oracle Assignment

Objectives

The lab is designed to teach you how to work with basic Linux commands in the environment and teach you how to access and setup the Video Store model shipped with the textbook. It specifically achieves these objectives:

  • Learn how to navigate directories in Linux.
  • Learn how to write a returnable script file that creates or recreates a generic Video Store model.

Business Scenario

Application development involves writing and testing software. As a rule, many software developers begin their careers in quality and assurance. Quality and assurance typically test new software against use cases.

The lab is designed to teach you how to use the Fedora VM environment to test PL/SQL programs. You won’t write any software but you should try to understand how the software and test case works.

Help Section

The following is an set of example Linux commands. Click the Instructional Material link to see the preparation lesson material.

Instructional Material →

You change directory with the cd command. If there’s no argument, the cd command returns you to the home directory. The cd command changes directory to another directory when you append an argument. You can provide a local directory name, a relative directory name, or a fully qualified directory name.

The first example shows you how to use the cd command to change directory to a fully qualified directory:

cd /home/student/Data

The second example shows you how to use the cd command to change directory to a local directory, which is a subdirectory in your present working directory:

cd Data

The third example shows you how to use the cd command to change directory to a relative sibling directory. The . (dot) represents the present working directory and the .. (double dot) represents the parent directory. The command gives the .. (double dot), a forward slash, and a sibling directory name.

cd ../lab2

Students may want or need supplemental information on the Linux Command-Line You can download the .

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

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

Lab Instructions →

The lab is premised on you installing and configuring the Fedora Virtual Machine (VM). After installing the VM, you should connect as the student user with the password provided by the instructor.

Then, you need to do following:

  • You should navigate to the following directory: 

    cd /home/student/Data/cit325/lib/

    You should check for the directory contents with the ls command, and you should see the following:

    12cPLSQLCode.zip  cleanup_oracle.sql  Oracle12cPLSQLCode

    If you don’t see the 12cPLSQLCode.zip file, you may also be missing the Oracle12cPLSQLCode directory. You can use the unzip utility to uncompress the 12cPLSQLCode.zip file. It should automatically create the Oracle12cPLSQLCode directory, it’s subdirectories, and it’s files.

    Inside the Oracle12cPLSQLCode directory, you should check for the contents of the directory with the ls command:

    You should see the following files:

    Chapter1   Chapter11  Chapter13  Chapter3  Chapter5  Chapter7  Chapter9
Chapter10  Chapter12  Chapter2   Chapter4  Chapter6  Chapter8  Introduction

    Change to the Introduction directory and list, ls, the contents of the directory. You should see the following:

    contact_insert_10g.SQL  insert_rental_items.SQL  transaction_procedure.SQL
contact_insert.SQL      insert_rental.SQL        update_price_function.SQL
create_video_store.SQL  insert_transactions.SQL
  • Write the following apply_plsql_lab1.sql file in the /home/student/Data/cit325/lab1 directory: 

    @/home/student/Data/cit325/lib/cleanup_oracle.sql
@/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/create_video_store.sql

You can test the installation of the correct video store database by using the test case that follows.

Test Case

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

Test Case Instructions →

Rather than have students write or run multiple confirmation queries, you are provided with a Native Dynamic SQL (NDS) [Chapter 13] solution. As most advanced test cases do, this test case requires three steps:

  1. Create a SQL object type, like a record data structure, and a SQL collection of the SQL object type. Like most SQL solutions, you need to drop the types from the most dependent to the least dependent before you can create a re-runnable test case.

    You can do these steps with the following syntax:

    /* Drop the types from most to least dependent. */
DROP TYPE table_list;
DROP TYPE table_struct;
 
/* Create the record type structure. */
CREATE OR REPLACE
  TYPE table_struct IS OBJECT
  ( table_name    VARCHAR2(30)
  , column_cnt    NUMBER
  , row_cnt       NUMBER );
/
 
/* Create the collection of a record type structure. */
CREATE OR REPLACE
  TYPE table_list IS TABLE OF table_struct;
/

  2. Create a function that uses three cursors, combines the cursor results into a collection, and returns the collection. The first cursor is static, the second cursor is dynamic, and the third is a string that the program runs dynamically (the last is what’s called Native Dynamic SQL – NDS).

    While these concepts are all advanced topics, you’ll learn how to do them gradually in the course. You only need to copy the code into the file and test it.

    You write the listing function with the following code:

    /* Drop the function before attempting to create it. */
DROP FUNCTION listing;
 
/* Create the listing function. */
CREATE OR REPLACE
FUNCTION listing RETURN table_list IS
 
  /* Variable list. */
  lv_column_cnt  NUMBER;
  lv_row_cnt     NUMBER;
 
  /* Declare a statement variable. */
  stmt  VARCHAR2(200);
 
  /* Declare a system reference cursor variable. */
  lv_refcursor  SYS_REFCURSOR;
  lv_table_cnt  NUMBER;
 
  /* Declare an output variable.  */
  lv_list  TABLE_LIST := table_list();
 
  /* Declare a table list cursor that excludes APEX tables. */
  CURSOR c IS
    SELECT table_name
    FROM   user_tables
    WHERE  table_name NOT IN
            ('DEPT','EMP','APEX$_ACL','APEX$_WS_WEBPG_SECTIONS','APEX$_WS_ROWS'
            ,'APEX$_WS_HISTORY','APEX$_WS_NOTES','APEX$_WS_LINKS'
            ,'APEX$_WS_TAGS','APEX$_WS_FILES','APEX$_WS_WEBPG_SECTION_HISTORY'
            ,'DEMO_USERS','DEMO_CUSTOMERS','DEMO_ORDERS','DEMO_PRODUCT_INFO'
            ,'DEMO_ORDER_ITEMS','DEMO_STATES');
 
  /* Declare a column count. */
  CURSOR cnt
  ( cv_table_name  VARCHAR2 ) IS
    SELECT   table_name
    ,        COUNT(column_id) AS cnt_columns
    FROM     user_tab_columns
    WHERE    table_name = cv_table_name
    GROUP BY table_name;
 
BEGIN
  /* Read through the data set of non-environment variables. */
  FOR i IN c LOOP
 
    /* Count the columns of a table. */
    FOR j IN cnt(i.table_name) LOOP
      lv_column_cnt := j.cnt_columns;
    END LOOP;
 
    /* Declare a statement. */
    stmt := 'SELECT COUNT(*) AS column_cnt FROM '||i.table_name;
 
    /* Open the cursor and write set to collection. */
    OPEN lv_refcursor FOR stmt;
    LOOP
      FETCH lv_refcursor INTO lv_table_cnt;
      EXIT WHEN lv_refcursor%NOTFOUND; 
      lv_list.EXTEND;
      lv_list(lv_list.COUNT) := table_struct(
                                    table_name => i.table_name
                                  , column_cnt => lv_column_cnt
                                  , row_cnt    => lv_table_cnt );
    END LOOP;
  END LOOP;
 
  RETURN lv_list;
END;
/

  3. The final step requires that you run the listing function and show the results.

    You query the listing function with query:

    COL table_name   FORMAT A20     HEADING "Table Name"
COL column_cnt   FORMAT 9,999  HEADING "Column #"
COL row_cnt      FORMAT 9,999  HEADING "Row #"
SELECT table_name
,      column_cnt
,      row_cnt
FROM   TABLE(listing);

    It should return the following output:

    Table Name	     Column #  Row #
-------------------- -------- ------
SYSTEM_USER		   11	   5
COMMON_LOOKUP		   10	  49
MEMBER			    9	  10
CONTACT 		   10	  18
ADDRESS 		   10	  18
STREET_ADDRESS		    8	  28
TELEPHONE		   11	  18
RENTAL			    8  4,694
ITEM			   14	  93
RENTAL_ITEM		    9  4,703
PRICE			   11	 558
TRANSACTION		   12  4,694
CALENDAR		    9	 300
AIRPORT 		    9	   6
ACCOUNT_LIST		    8	 200
 
15 rows selected.

Written by michaelmclaughlin

January 5th, 2017 at 12:27 pm

Posted in