Database Tutorial

Course Tutorial Site

Site Admin

Instructions

without comments

Lab #9: External Tables

Learn how to create an externally managed table and how to run anonymous block PL/SQL programs to fix data states in the some of the tables. You create the airport and account_list tables and their respective airport_s1 and account_list_s1 sequences before running the anonymous block PLSQL programs. You also insert six rows into the common_lookup table.

Lab Instructions

This lab has four unit testing steps placed into one test script.

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

Lab Instructions →

You begin these steps by running the following script:

  • The lab8/apply_oracle_lab8.sql script.

You put the script into your apply_oracle_lab9.sql script, which should be in the following directory:

/home/student/Data/cit225/lab9

You should use the following general prototype as your starting point for the apply_oracle_lab9.sql script:

Sample script →

This expands to show you how to structure your apply_oracle_lab9.sql script.

-- ------------------------------------------------------------------
-- Call the prior lab.
-- ------------------------------------------------------------------
 
@@/home/student/Data/cit225/oracle/lab8/apply_oracle_lab8.sql
 
-- Open log file.  
SPOOL apply_oracle_lab9.txt
 
-- --------------------------------------------------------
--  Step #1
--  -------
--  Create the TRANSACTION table per the web page spec.
-- --------------------------------------------------------
 
-- Insert step #1 statements here.
 
-- --------------------------------------------------------
--  Step #2
--  -------
--  Insert two rows for the TRANSACTION_TYPE column and
--  four rows for the PAYMENT_METHOD_TYPE column of the
--  TRANSACTION table into the COMMON_LOOKUP table.
-- --------------------------------------------------------
 
-- Insert step #2 statements here.
 
-- --------------------------------------------------------
--  Step #3
--  -------
--  Create the AIRPORT and ACCOUNT_LIST tables; and 
--  insert rows into both tables.
-- --------------------------------------------------------
 
-- Insert step #3 statements here.
 
-- --------------------------------------------------------
--  Step #4
--  -------
--  Create an external table TRANSACTION_UPLOAD that uses
--  a pre-seeded source file.
-- --------------------------------------------------------
 
-- Insert step #4 statements here.
 
-- Close log file.
SPOOL OFF
 
-- Make all changes permanent.
COMMIT;

You should complete the following four steps:

  1. [6 points] Create the following transaction table as per the specification, but do so understanding the business logic of the model. After creating the transaction table, create a unique index on the columns that make up the natural key and call it the natural_key index.

Instruction Details →

You create the transaction table and a transaction_s1 sequence.

  1. You create the TRANSACTION table and a TRANSACTION_S1 sequence as qualified below.

Implementation Note: The TRANSACTION table has a range relationship to a CALENDAR table that will be defined later.

Business Rule: You may enter an account number into the TRANSACTION_ACCOUNT column of 111-111-111-111 for a debit, and 222-222-222-222 for a credit. A debit recognizes payment for a rental, and is a value that adds to the company’s assets. A credit recognizes a refund for a rental, and is a value that decreases the company’s assets. Debits should have a TRANSACTION_TYPE that maps to the corresponding debit row in the COMMON_LOOKUP table, and credits should have a TRANSACTION_TYPE that maps to the corresponding credit row in the COMMON_LOOKUP table.

System Logic: In a future refinement of our pilot design, the TRANSACTION_ACCOUNT column will be replaced by a foreign key that points to the CHART_OF_ACCOUNT table. A CHART_OF_ACCOUNT table holds rows that describe every account, like it’s account number (e.g., 111-111-111-111), description, start and end date.

Table Name: TRANSACTION
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
TRANSACTION_ID PRIMARY KEY Integer Maximum
TRANSACTION_ACCOUNT NOT NULL String 15
TRANSACTION_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
NOT NULL
TRANSACTION_DATE NOT NULL Date Date
TRANSACTION_AMOUNT NOT NULL Number Maximum
RENTAL_ID FOREIGN KEY RENTAL RENTAL_ID Integer Maximum
NOT NULL
PAYMENT_METHOD_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
NOT NULL
PAYMENT_ACCOUNT_NUMBER NOT NULL String 19
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
CREATION_DATE NOT NULL Date Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
LAST_UPDATE_DATE NOT NULL Date Date

* The TRANSACTION_AMOUNT column uses a NUMBER data type but the column may contain an integer or decimal value.

Don’t forget that on the Oracle Database, that you’ll need to provide an explicit TRANSACTION_S1 sequence. The sequence should start with the default value of a 1.

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

COLUMN table_name   FORMAT A14  HEADING "Table Name"
COLUMN column_id    FORMAT 9999 HEADING "Column ID"
COLUMN column_name  FORMAT A22  HEADING "Column Name"
COLUMN nullable     FORMAT A8   HEADING "Nullable"
COLUMN data_type    FORMAT A12  HEADING "Data Type"
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'TRANSACTION'
ORDER BY 2;

It should display the following results:

Table Name     Column ID Column Name            Nullable Data Type
-------------- --------- ---------------------- -------- ------------
TRANSACTION            1 TRANSACTION_ID         NOT NULL NUMBER(22)
TRANSACTION            2 TRANSACTION_ACCOUNT    NOT NULL VARCHAR2(15)
TRANSACTION            3 TRANSACTION_TYPE       NOT NULL NUMBER(22)
TRANSACTION            4 TRANSACTION_DATE       NOT NULL DATE
TRANSACTION            5 TRANSACTION_AMOUNT     NOT NULL NUMBER(22)
TRANSACTION            6 RENTAL_ID              NOT NULL NUMBER(22)
TRANSACTION            7 PAYMENT_METHOD_TYPE    NOT NULL NUMBER(22)
TRANSACTION            8 PAYMENT_ACCOUNT_NUMBER NOT NULL VARCHAR2(19)
TRANSACTION            9 CREATED_BY             NOT NULL NUMBER(22)
TRANSACTION           10 CREATION_DATE          NOT NULL DATE
TRANSACTION           11 LAST_UPDATED_BY        NOT NULL NUMBER(22)
TRANSACTION           12 LAST_UPDATE_DATE       NOT NULL DATE
 
12 rows selected.
  1. You create the NATURAL_KEY unique index as qualified below.

After you create the table, you need to add a UNIQUE INDEX on the following columns to the TRANSACTION table. This is necessary to improve the run-time performance of the merge operations later in this lab.

  • RENTAL_ID
  • TRANSACTION_TYPE
  • TRANSACTION_DATE
  • PAYMENT_METHOD_TYPE
  • PAYMENT_ACCOUNT_NUMBER
  • TRANSACTION_ACCOUNT

You should use the following formatting and query to verify completion of this step:

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

COLUMN table_name       FORMAT A12  HEADING "Table Name"
COLUMN index_name       FORMAT A16  HEADING "Index Name"
COLUMN uniqueness       FORMAT A8   HEADING "Unique"
COLUMN column_position  FORMAT 9999 HEADING "Column Position"
COLUMN column_name      FORMAT A24  HEADING "Column Name"
SELECT   i.table_name
,        i.index_name
,        i.uniqueness
,        ic.column_position
,        ic.column_name
FROM     user_indexes i INNER JOIN user_ind_columns ic
ON       i.index_name = ic.index_name
WHERE    i.table_name = 'TRANSACTION'
AND      i.uniqueness = 'UNIQUE'
AND      i.index_name = 'NATURAL_KEY';

It should display the following results:

Table Name   Index Name       Unique   Column Position Column Name
------------ ---------------- -------- --------------- ------------------------
TRANSACTION  NATURAL_KEY      UNIQUE                 1 RENTAL_ID
TRANSACTION  NATURAL_KEY      UNIQUE                 2 TRANSACTION_TYPE
TRANSACTION  NATURAL_KEY      UNIQUE                 3 TRANSACTION_DATE
TRANSACTION  NATURAL_KEY      UNIQUE                 4 PAYMENT_METHOD_TYPE
TRANSACTION  NATURAL_KEY      UNIQUE                 5 PAYMENT_ACCOUNT_NUMBER
TRANSACTION  NATURAL_KEY      UNIQUE                 6 TRANSACTION_ACCOUNT
 
6 rows selected.

  1. [6 points] Insert the following two TRANSACTION_TYPE rows and four PAYMENT_METHOD_TYPE rows into the COMMON_LOOKUP table. They should have valid who-audit column data.

Instruction Details →

You insert the six rows by using the following instructions.

Table Name: COMMON_LOOKUP
Lookup Table Lookup Column Lookup Type Lookup Meaning Lookup Code
TRANSACTION TRANSACTION_TYPE CREDIT Credit CR
TRANSACTION TRANSACTION_TYPE DEBIT Debit DR
TRANSACTION PAYMENT_METHOD_TYPE DISCOVER_CARD Discover Card
TRANSACTION PAYMENT_METHOD_TYPE VISA_CARD Visa Card
TRANSACTION PAYMENT_METHOD_TYPE MASTER_CARD Master Card
TRANSACTION PAYMENT_METHOD_TYPE CASH Cash

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10

COLUMN common_lookup_table  FORMAT A20 HEADING "COMMON_LOOKUP_TABLE"
COLUMN common_lookup_column FORMAT A20 HEADING "COMMON_LOOKUP_COLUMN"
COLUMN common_lookup_type   FORMAT A20 HEADING "COMMON_LOOKUP_TYPE"
SELECT   common_lookup_table
,        common_lookup_column
,        common_lookup_type
FROM     common_lookup
WHERE    common_lookup_table = 'TRANSACTION'
AND      common_lookup_column IN ('TRANSACTION_TYPE','PAYMENT_METHOD_TYPE')
ORDER BY 1, 2, 3 DESC;

It should display the following results:

COMMON_LOOKUP_TABLE  COMMON_LOOKUP_COLUMN COMMON_LOOKUP_TYPE
-------------------- -------------------- --------------------
TRANSACTION          PAYMENT_METHOD_TYPE  VISA_CARD
TRANSACTION          PAYMENT_METHOD_TYPE  MASTER_CARD
TRANSACTION          PAYMENT_METHOD_TYPE  DISCOVER_CARD
TRANSACTION          PAYMENT_METHOD_TYPE  CASH
TRANSACTION          TRANSACTION_TYPE     DEBIT
TRANSACTION          TRANSACTION_TYPE     CREDIT
 
6 rows selected.

  1. [26 points] Create the following airport and account_list tables as per the specification, but do so understanding the business logic of the model.

Instruction Details →

You create and populate the airport and account_list tables.

Implementation Note: The AIRPORT table has a filtered substring relationship to ACCOUNT numbers because the business model is evolving toward airport kiosks rather than stores. The ACCOUNT_LIST contains a set of pre-seeded values that may be consumed as new customers are entered into the Video Store application.

Business Rule: You must seed the AIRPORT with any additional information that may be added to the model to support new customers. The city where the customer lives needs to be mapped to the nearest regional airport. All ACCOUNT_NUMBER column values must contain a valid account number found in the ACCOUNT_LIST table. When an account number is assigned to a customer, you must indicate the SYSTEM_USER_ID linked to assigning the account number in the CONSUMED_BY column of the ACCOUNT_LIST table, and provide the date of that activity in the CONSUMED_BY column.

System Logic: Seeding procedures are provided in the lab to update all existing data in the model, provided you add any required new cities in the AIRPORT table. The import of external records is now dependent on accurate ACCOUNT_NUMBER values in the MEMBER table. You must update all pre-existing records.

  1. You need to create the AIRPORT table and the AIRPORT_S1 sequences.

Table Name: AIRPORT
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
AIRPORT_ID PRIMARY KEY Integer Maximum
AIRPORT_CODE NOT NULL String 3
AIRPORT_CITY NOT NULL String 30
CITY NOT NULL String 30
STATE_PROVINCE NOT NULL String 30
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
CREATION_DATE NOT NULL Date Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
LAST_UPDATE_DATE NOT NULL Date Date

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

COLUMN table_name   FORMAT A14  HEADING "Table Name"
COLUMN column_id    FORMAT 9999 HEADING "Column ID"
COLUMN column_name  FORMAT A22  HEADING "Column Name"
COLUMN nullable     FORMAT A8   HEADING "Nullable"
COLUMN data_type    FORMAT A12  HEADING "Data Type"
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'AIRPORT'
ORDER BY 2;

It should display the following results:

Table Name     Column ID Column Name            Nullable Data Type
-------------- --------- ---------------------- -------- ------------
AIRPORT                1 AIRPORT_ID             NOT NULL NUMBER(22)
AIRPORT                2 AIRPORT_CODE           NOT NULL VARCHAR2(3)
AIRPORT                3 AIRPORT_CITY           NOT NULL VARCHAR2(30)
AIRPORT                4 CITY                   NOT NULL VARCHAR2(30)
AIRPORT                5 STATE_PROVINCE         NOT NULL VARCHAR2(30)
AIRPORT                6 CREATED_BY             NOT NULL NUMBER(22)
AIRPORT                7 CREATION_DATE          NOT NULL DATE
AIRPORT                8 LAST_UPDATED_BY        NOT NULL NUMBER(22)
AIRPORT                9 LAST_UPDATE_DATE       NOT NULL DATE
 
9 rows selected.

  1. You need to create a unique natural key (named NK_AIRPORT) index for the AIRPORT table. You should create it with the following four columns.

  • AIRPORT_CODE
  • AIRPORT_CITY
  • CITY
  • STATE_PROVINCE

You should use the following formatting and query to verify completion of this step:

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

COLUMN table_name       FORMAT A12  HEADING "Table Name"
COLUMN index_name       FORMAT A16  HEADING "Index Name"
COLUMN uniqueness       FORMAT A8   HEADING "Unique"
COLUMN column_position  FORMAT 9999 HEADING "Column Position"
COLUMN column_name      FORMAT A24  HEADING "Column Name"
SELECT   i.table_name
,        i.index_name
,        i.uniqueness
,        ic.column_position
,        ic.column_name
FROM     user_indexes i INNER JOIN user_ind_columns ic
ON       i.index_name = ic.index_name
WHERE    i.table_name = 'AIRPORT'
AND      i.uniqueness = 'UNIQUE'
AND      i.index_name = 'NK_AIRPORT';

It should display the following results:

Table Name   Index Name       Unique   Column Position Column Name
------------ ---------------- -------- --------------- ------------------------
AIRPORT      NK_AIRPORT       UNIQUE                 1 AIRPORT_CODE
AIRPORT      NK_AIRPORT       UNIQUE                 2 AIRPORT_CITY
AIRPORT      NK_AIRPORT       UNIQUE                 3 CITY
AIRPORT      NK_AIRPORT       UNIQUE                 4 STATE_PROVINCE
 
4 rows selected.

  1. You need to seed the AIRPORT table with at least these cities, and any others that you’ve used for inserted values in the CONTACT table.

Table Name: AIRPORT
Airport Code Airport City City State Province
LAX Los Angeles Los Angeles California
SLC Salt Lake City Provo Utah
SLC Salt Lake City Spanish Fork Utah
SFO San Francisco San Francsico California
SJC San Jose San Jose California
SJC San Jose San Carlos California

You should use the following query to verify completion of this step:

1
2
3
4
5
6
7
8
9

COLUMN code           FORMAT A4  HEADING "Code"
COLUMN airport_city   FORMAT A14 HEADING "Airport City"
COLUMN city           FORMAT A14 HEADING "City"
COLUMN state_province FORMAT A10 HEADING "State or|Province"
SELECT   airport_code AS code
,        airport_city
,        city
,        state_province
FROM     airport;

It should display the following results:

                                         State or
Code   Airport City     City             Province
------ ---------------- ---------------- ------------
LAX    Los Angeles      Los Angeles      California
SFO    San Francisco    San Francisco    California
SJC    San Jose         San Carlos       California
SJC    San Jose         San Jose         California
SLC    Salt Lake City   Provo            Utah
SLC    Salt Lake City   Spanish Fork     Utah
 
6 rows selected.

  1. You need to create the ACCOUNT_LIST table and ACCOUNT_LIST_S1 sequence.

Table Name: ACCOUNT_LIST
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
ACCOUNT_LIST_ID PRIMARY KEY Integer Maximum
ACCOUNT_NUMBER NOT NULL String 10
CONSUMED_DATE Date Date
CONSUMED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
CREATION_DATE NOT NULL Date Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
LAST_UPDATE_DATE NOT NULL Date Date

You should use the following formatting and query to verify completion of this step:

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

COLUMN table_name   FORMAT A14
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'ACCOUNT_LIST'
ORDER BY 2;

It should display the following results (please note that the CONSUMED_DATE and CONSUMED_BY columns are nullable until you use the account number):

TABLE NAME     COLUMN_ID COLUMN NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
ACCOUNT_LIST           1 ACCOUNT_LIST_ID        NOT NULL NUMBER(22)
ACCOUNT_LIST           2 ACCOUNT_NUMBER         NOT NULL VARCHAR2(10)
ACCOUNT_LIST           3 CONSUMED_DATE                   DATE
ACCOUNT_LIST           4 CONSUMED_BY                     NUMBER(22)
ACCOUNT_LIST           5 CREATED_BY             NOT NULL NUMBER(22)
ACCOUNT_LIST           6 CREATION_DATE          NOT NULL DATE
ACCOUNT_LIST           7 LAST_UPDATED_BY        NOT NULL NUMBER(22)
ACCOUNT_LIST           8 LAST_UPDATE_DATE       NOT NULL DATE
 
8 rows selected.

Don’t forget that you need to create a ACCOUNT_LIST_S1 sequence to enable the procedure to work.

  1. You need to seed the ACCOUNT_LIST table. This would take more time than leveraging a stored procedure but writing stored procedures from class is a beyond scope. Therefore, you’ve been provided with stored procedures for the databases. Click on the Setup Script to see how the procedure works, how to run the procedure, and how to check how many rows are inserted into the ACCOUNT_LIST table.

Setup Script →

Click on this to see a necessary PL/SQL procedure for this lab.

This contains the necessary seeding code that lets you apply specific rules to populate the ACCOUNT_LIST table. It is dependent upon you’re seeding of the AIRPORT table. It won’t work correctly if you have incorrect values in that table.

You should just copy and past this code into a text editor of your choice. Then, you can run it as the student user against the student database. After you have it in a file, you should run it to place the procedure in your database. 

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

-- Create or replace seeding procedure.
CREATE OR REPLACE PROCEDURE seed_account_list IS
  /* Declare variable to capture table, and column. */
  lv_table_name   VARCHAR2(90);
  lv_column_name  VARCHAR2(30);
 
  /* Declare an exception variable and PRAGMA map. */
  not_null_column  EXCEPTION;
  PRAGMA EXCEPTION_INIT(not_null_column,-1400);
 
BEGIN
  /* Set savepoint. */
  SAVEPOINT all_or_none;
 
  FOR i IN (SELECT DISTINCT airport_code FROM airport) LOOP
    FOR j IN 1..50 LOOP
 
      INSERT INTO account_list
      VALUES
      ( account_list_s1.NEXTVAL
      , i.airport_code||'-'||LPAD(j,6,'0')
      , NULL
      , NULL
      , 1002
      , SYSDATE
      , 1002
      , SYSDATE);
    END LOOP;
  END LOOP;
 
  /* Commit the writes as a group. */
  COMMIT;
 
EXCEPTION
  WHEN not_null_column THEN
    /* Capture the table and column name that triggered the error. */
    lv_table_name := (TRIM(BOTH '"' FROM RTRIM(REGEXP_SUBSTR(SQLERRM,'".*\."',REGEXP_INSTR(SQLERRM,'\.',1,1)),'."')));
    lv_column_name := (TRIM(BOTH '"' FROM REGEXP_SUBSTR(SQLERRM,'".*"',REGEXP_INSTR(SQLERRM,'\.',1,2))));
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
    RAISE_APPLICATION_ERROR(
       -20001
      ,'Remove the NOT NULL contraint from the '||lv_column_name||' column in'||CHR(10)||' the '||lv_table_name||' table.');
  WHEN OTHERS THEN
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
END;
/

After you create the procedure in your database, you call it like so:

EXECUTE seed_account_list();

You should use the following query to verify completion of this step:

1
2
3
4
5
6

COLUMN object_name FORMAT A18
COLUMN object_type FORMAT A12
SELECT   object_name
,        object_type
FROM     user_objects
WHERE    object_name = 'SEED_ACCOUNT_LIST';

It should display the following results:

OBJECT_NAME        OBJECT_TYPE
------------------ ------------
SEED_ACCOUNT_LIST  PROCEDURE
 
1 row selected.

The procedure has an embedded COMMIT instruction. You can query the ACCOUNT_LIST table to make sure it inserted 50 rows for each airport.

After you’ve created the ACCOUNT_LIST table and the SEED_ACCOUNT_LIST procedure, you call the SEED_ACCOUNT_LIST procedure to populate the table. The following query verifies that you’ve successfully run the SEED_ACCOUNT_LIST procedure:

COLUMN airport FORMAT A7
SELECT   SUBSTR(account_number,1,3) AS "Airport"
,        COUNT(*) AS "# Accounts"
FROM     account_list
WHERE    consumed_date IS NULL
GROUP BY SUBSTR(account_number,1,3)
ORDER BY 1;

It should display the following:

Airport # Accounts
------- ----------
LAX             50
SFO             50
SJC             50
SLC             50
 
4 rows selected.
  1. In a prior lab and in both the create and seed scripts, the STATE_PROVINCE column values uses a mixture of US Postal Services state abbreviations and full state names. You need to update all STATE_PROVINCE values with their full state names because a subsequent seeding and the import program rely on full STATE_PROVINCE names. You need to update any pre-seeded US Postal Service state abbreviations with the full state names.

The following UPDATE statement changes US Postal Services abbreviations to the full state name:

1
2
3

UPDATE address
SET    state_province = 'California'
WHERE  state_province = 'CA';
  1. You need to run the script that creates the UPDATE_MEMBER_ACCOUNT procedure, and then you need to call it to update values in the MEMBER and ACCOUNT_LIST tables. It relies on you having run the SEED_ACCOUNT_LIST procedure successfully.

Setup Script →

Click on this to see a necessary PL/SQL procedure for this lab.

This contains the necessary code to consume values from the ACCOUNT_LIST table and update the ACCOUNT_NUMBER column of the MEMBER table with correct values from the ACCOUNT_LIST table.

You should just copy and past this code into your apply_lab9_oracle.sql script. Then, you can run it as the student user.

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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104

CREATE OR REPLACE PROCEDURE update_member_account IS
 
  /* Declare a local variable. */
  lv_account_number  VARCHAR2(10);
  lv_update_records  BOOLEAN := FALSE;
  lv_update_message  VARCHAR2(80);
 
  /* Declare a SQL cursor fabricated from local variables. */  
  CURSOR member_cursor IS
    SELECT   DISTINCT
             m.member_id
    ,        a.city
    ,        a.state_province
    FROM     member m INNER JOIN contact c
    ON       m.member_id = c.member_id INNER JOIN address a
    ON       c.contact_id = a.contact_id 
    WHERE    m.account_number NOT IN
              ( SELECT al.account_number
                FROM   account_list al 
                WHERE  al.consumed_by IS NOT NULL
                AND    al.consumed_date IS NOT NULL )
    ORDER BY m.member_id;
 
BEGIN
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  $IF $$DEBUG = 1 $THEN
    dbms_output.put_line(CHR(10));
    dbms_output.put_line('Debugging Consumed By ID Numbers.');
    dbms_output.put_line('------------------------------------------');
  $END
 
  /* Open a local cursor. */  
  FOR i IN member_cursor LOOP
 
      /* Found records to update. */
      lv_update_records := TRUE;
 
      /* Secure a unique account number as they are consumed from the list. */
      WITH ordered AS
      ( SELECT al.account_number
        ,      al.consumed_by
        ,      al.consumed_date
        FROM   account_list al
        ORDER BY al.account_number )
      SELECT al.account_number
      INTO   lv_account_number
      FROM   ordered al INNER JOIN airport ap
      ON     SUBSTR(al.account_number,1,3) = ap.airport_code
      WHERE  ap.city = i.city
      AND    ap.state_province = i.state_province
      AND    al.consumed_by IS NULL
      AND    al.consumed_date IS NULL
      AND    ROWNUM < 2
      ORDER BY al.account_number;
 
      $IF $$DEBUG = 1 $THEN
        dbms_output.put_line(
            '[id]['||i.member_id||']: '
          ||'lv_account_number ['||lv_account_number||']');
      $END
 
      /* Update with a unique account number linked to their nearest airport. */
      UPDATE member
      SET    account_number = lv_account_number
      WHERE  member_id = i.member_id;
 
      /* Mark consumed the last used account number. */      
      UPDATE account_list
      SET    consumed_by = 1002
      ,      consumed_date = SYSDATE
      WHERE  account_number = lv_account_number;
 
  END LOOP;
 
  /* Set closing message. */
  IF NOT lv_update_records THEN
    lv_update_message := 'No records require update.'||CHR(10)
                      || '------------------------------------------';
  ELSE
    lv_update_message := '------------------------------------------';
  END IF;
 
  /* Write closing line. */
  $IF $$DEBUG = 1 $THEN
    dbms_output.put_line(lv_update_message);
  $END
 
  /* Commit the writes as a group. */
  COMMIT;
 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('You have an error in your AIRPORT table inserts.');
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
  WHEN OTHERS THEN
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
END;
/

You should use the following query to verify completion of this step:

1
2
3
4
5
6

COLUMN object_name FORMAT A22
COLUMN object_type FORMAT A12
SELECT   object_name
,        object_type
FROM     user_objects
WHERE    object_name = 'UPDATE_MEMBER_ACCOUNT';

It should display the following results:

OBJECT_NAME            OBJECT_TYPE
---------------------- ------------
UPDATE_MEMBER_ACCOUNT  PROCEDURE
 
1 row selected.

After you create the procedure in your database, you call it like so:

EXECUTE update_member_account();

The procedure has an embedded COMMIT instruction. You can query the updated ACCOUNT_NUMBER values from the MEMBER table. It inserts one row for each row in the MEMBER table.

After you create the ACCOUNT_LIST table, you update the MEMBER table by calling the UPDATE_MEMBER_ACCOUNT procedure to populate the table. The following query verifies that change:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- Format the SQL statement display.
COLUMN member_id      FORMAT 999999 HEADING "Member|ID #"
COLUMN last_name      FORMAT A7     HEADING "Last|Name"
COLUMN account_number FORMAT A10    HEADING "Account|Number"
COLUMN acity          FORMAT A12    HEADING "Address City"
COLUMN apstate        FORMAT A10    HEADING "Airport|State or|Province"
COLUMN alcode         FORMAT A5     HEADING "Airport|Account|Code"
 
-- Query distinct members and addresses.
SELECT   DISTINCT
         m.member_id
,        c.last_name
,        m.account_number
,        a.city AS acity
,        ap.state_province AS apstate
,        SUBSTR(al.account_number,1,3) AS alcode
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN address a
ON       c.contact_id = a.contact_id INNER JOIN airport ap
ON       a.city = ap.city
AND      a.state_province = ap.state_province INNER JOIN account_list al
ON       ap.airport_code = SUBSTR(al.account_number,1,3)
ORDER BY 1;

You should see the following rows in the MEMBER table.

					Address    Airport  
 Member Last	Account 		State or   Account
   ID # Name	Number	   Address City Province   Code
------- ------- ---------- ------------ ---------- --------
   1001 Winn	SJC-000001 San Jose	California SJC
   1002 Vizquel SJC-000002 San Jose	California SJC
   1003 Sweeney SJC-000003 San Jose	California SJC
   1004 Clinton SLC-000001 Provo	Utah	   SLC
   1005 Moss	SLC-000002 Provo	Utah	   SLC
   1006 Gretelz SLC-000003 Provo	Utah	   SLC
   1007 Royal	SLC-000004 Provo	Utah	   SLC
   1008 Smith	SLC-000005 Spanish Fork Utah	   SLC
   1009 Potter	SLC-000006 Provo	Utah	   SLC

Troubleshooting Script ↓

You should open the nested element to find and run a diagnostic script if the previous query failed to return the correct results.

If you didn’t get the result above, it’s likely that a step was missed in this or an earlier lab in the sequence of labs. The missing step most likely caused the update_member_account procedure to fail because it couldn’t resolve a join between tables. The following diagnostic query should be used when the previous query failed.

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

-- Reset the pagesize.
SET PAGESIZE 99
SET LINESIZE 92
 
-- Verify the account changes in the MEMBER table.
COLUMN member_id      FORMAT 999999 HEADING "Member|ID #"
COLUMN last_name      FORMAT A9     HEADING "Last|Name"
COLUMN account_number FORMAT A10    HEADING "Account|Number"
COLUMN acity          FORMAT A14    HEADING "Address City"
COLUMN apcity         FORMAT A14    HEADING "Airport|List City"
COLUMN astate         FORMAT A10    HEADING "Address|State or|Province"
COLUMN apstate        FORMAT A10    HEADING "Airport|State or|Province"
COLUMN apcode         FORMAT A5     HEADING "Airport|Code"
COLUMN alcode         FORMAT A5     HEADING "Account|Airport|Code"
 
-- Run the query.
SELECT   DISTINCT
         m.member_id
,        c.last_name
,        m.account_number
,        a.city AS acity
,        ap.city AS apcity
,        a.state_province AS astate
,        ap.state_province AS apstate
,        ap.airport_code AS apcode
,        SUBSTR(al.account_number,1,3) AS alcode
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN address a
ON       c.contact_id = a.contact_id RIGHT JOIN airport ap
ON       a.city = ap.city
AND      a.state_province = ap.state_province RIGHT JOIN account_list al
ON       ap.airport_code = SUBSTR(al.account_number,1,3)
ORDER BY 1;
 
-- Reset the pagesize.
SET PAGESIZE 80

This should yield the following results:

							 Address    Airport             Account
 Member Last	Account 				 State or   State or   Airport  Airport
   ID # Name	Number	   Address City   Airport City	 Province   Province   Code     Code
------- ------- ---------- -------------- -------------- ---------- ---------- -------- --------
   1001 Winn	SJC-000001 San Jose	  San Jose	 California California SJC	SJC
   1002 Vizquel SJC-000002 San Jose	  San Jose	 California California SJC	SJC
   1003 Sweeney SJC-000003 San Jose	  San Jose	 California California SJC	SJC
   1004 Clinton SLC-000001 Provo	  Provo 	 Utah	    Utah       SLC	SLC
   1005 Moss	SLC-000002 Provo	  Provo 	 Utah	    Utah       SLC	SLC
   1006 Gretelz SLC-000003 Provo	  Provo 	 Utah	    Utah       SLC	SLC
   1007 Royal	SLC-000004 Provo	  Provo 	 Utah	    Utah       SLC	SLC
   1008 Smith	SLC-000005 Spanish Fork   Spanish Fork	 Utah	    Utah       SLC	SLC
   1009 Potter	SLC-000006 Provo	  Provo 	 Utah	    Utah       SLC	SLC
					  Los Angeles		    California LAX	LAX
					  San Carlos		    California SJC	SJC
					  San Francisco 	    California SFO	SFO

After updating the MEMBER table, you are ready to move forward to the next step.

  1. [6 points] Create the following transaction_upload table as per the specification, but do so understanding the business logic of the model. As a Big Picture, our video store just got a consolidation of rentals from a store that’s closed for our customers.

    You need to import the values and ensure that our source data agrees with the other store. For example, do all customer names, addresses, account numbers match our data. If they do, the merge should go well. If they don’t, the figures will ultimately be incorrect.

Instruction Details →

You create a temporary table and query the data from the external comma-separated values (CSV) file.

Implementation Note: The TRANSACTION_UPLOAD table is in unnormalized form, which means it has repeating rows and unique rows.

Business Rule: The data has secured a valid CUSTOMER_ID and ITEM_ID before generating the CSV source file. You need to create an import table to support the upload of remote store data.

System Logic: This will be implemented in an Oracle database as an external table (or, this short essay on external tables), and in MySQL as a memory resident table. Memory resident tables disappear when the database is rebooted. You use the ENGINE=MEMORY as opposed to the ENGINE=INNODB syntax. These rules support the business rules provided you have no FOREIGN KEY references in the TRANSACTION_UPLOAD table.

Table Name: TRANSACTION_UPLOAD
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
ACCOUNT_NUMBER String 10
FIRST_NAME String 20
MIDDLE_NAME String 20
LAST_NAME String 20
CHECK_OUT_DATE Date Date
RETURN_DATE Date Date
RENTAL_ITEM_TYPE String 12
TRANSACTION_TYPE String 14
TRANSACTION_AMOUNT Number Decimal
TRANSACTION_DATE Date Date
ITEM_ID Integer Maximum
PAYMENT_METHOD_TYPE String 14
PAYMENT_ACCOUNT_NUMBER String 19

Linux and Oracle Virtual Directory Setup Steps →

Open these additional instructions only if you are not using the instructor provided Linux image. The instructions will enable you to position and set privileges on files that will support the Oracle virtual directory that you create in the lab.

Before you create the TRANSACTION_UPLOAD table, you need to create a physical and virtual directory. You should use the following physical path for the upload directory (the Fedora image has these directories already):

/u01/app/oracle/upload

Assuming you’re working in an Oracle Database 11g XE instance (other than the course’s Fedora image), you can create a valid upload directory as the root user. You assume the root user privilege with the following command:

su - root

You should navigate to this directory:

/u01/app/oracle

Then, issue this command as the root user to create a new upload directory:

mkdir upload

Now you have the following directory:

/u01/app/oracle/upload

Assuming you’ve created the upload directory as the root user, copy all *.csv files to this directory. Then, as the root user you should issue the following two commands from the /u01/app/oracle directory:

chown -R oracle:dba upload
chmod -R 755 upload

Having made that change you should now be able to query the external file source, like a *.csv (comma-separated values) file. Hope this helps those trying to use external tables.

Unzip the transaction_upload.csv file if you’re not using the course’s Fedora image, and put the file in the /u01/app/oracle/upload directory.

Assuming that you have created the necessary transaction_upload.csv file in the /u01/app/oracle/upload directory, which was done for those students using the standard image, you need to do the following:

Connect as the system user. You have two options to connect as the privileged system user.

  • Connect to sqlplus with the following command: 

    sqlplus system/cangetin

    or, connect as the student user first and then change your user account to the system user in the same session with the following command:

    SQL> CONNECT SYSTEM/cangetin

  • As the system user, issue the following two commands: 

    CREATE DIRECTORY upload AS '/u01/app/oracle/upload';
GRANT READ, WRITE ON DIRECTORY upload TO student;
  • Don’t forget to connect back as the student user before testing your lba.

Please check the Oracle External Tables web page if you need more information about how to setup external tables in an Oracle database.

Unlike ordinary tables, a query against the data dictionary doesn’t yield a full understanding of an external table. To see the storage clause of an external table, you must use the get_ddl function found in the dbms_metadata package.

You can use the following query to see the full structure of an external table:

SET LONG 200000  -- Enables the display of the full statement.
SELECT   dbms_metadata.get_ddl('TABLE','TRANSACTION_UPLOAD') AS "Table Description"
FROM     dual;

This should show you the following:

Table Description
------------------------------------------------------------
  CREATE TABLE "STUDENT"."TRANSACTION_UPLOAD"
   (    "ACCOUNT_NUMBER" VARCHAR2(10),
        "FIRST_NAME" VARCHAR2(20),
        "MIDDLE_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(20),
        "CHECK_OUT_DATE" DATE,
        "RETURN_DATE" DATE,
        "RENTAL_ITEM_TYPE" VARCHAR2(12),
        "TRANSACTION_TYPE" VARCHAR2(14),
        "TRANSACTION_AMOUNT" NUMBER,
        "TRANSACTION_DATE" DATE,
        "ITEM_ID" NUMBER,
        "PAYMENT_METHOD_TYPE" VARCHAR2(14),
        "PAYMENT_ACCOUNT_NUMBER" VARCHAR2(19)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "UPLOAD"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'UPLOAD':'transaction_upload.bad'
      DISCARDFILE 'UPLOAD':'transaction_upload.dis'
      LOGFILE     'UPLOAD':'transaction_upload.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL     )
      LOCATION
       ( 'transaction_upload.csv'
       )
    )
   REJECT LIMIT UNLIMITED
 
1 row selected.

You can verify that you have access to the external roles:

SELECT   COUNT(*) AS "External Rows"
FROM     transaction_upload;

External Rows
-------------
        11520

Test Case

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

Test Case Instructions →

You should embed the verification queries from these instructions inside your apply_lab9_oracle.sql script file. You should call the apply_lab9_oracle.sql script from the sqlplus command-line utility

@apply_oracle_lab9.sql

You should submit your apply_oracle_lab9.sql script file and apply_oracle_lab9.txt log file for a grade.

Written by michaelmclaughlin

August 13th, 2018 at 11:39 am

Posted in