Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 9 Instructions

without comments

Lab #9: Oracle Assignment

Objectives

The lab is designed to teach you how to work with large text and binary files, and how you can load and access them in an Oracle database. The SQL and PL/SQL programming language don’t manage images, which means we limit the discussion to large text files.

You will write an anonymous PL/SQL block that dynamically reads the content of an external file directory to match and upload large text source files:

  • Learn how to position large text files for upload to a database application.
  • Learn how to read and upload text files into an Oracle database.
  • Learn how to work external tables with preprocessing instructions.
  • Learn how to leverage application library procedures to solve a programming problem.

Business Scenario

Application programming solutions often require uploading large text files or images to their application. Uploading them one at a time doesn’t meet operational needs. Batch uploading of these types of text and image files is a functional requirement and classic use case.

The Oracle Database 10g forward enable you to bulk upload text and image files. There are two components necessary to accomplish this type of upload. Oracle’s external files with preprocessing is one component, which you will learn how to use in this lab. Oracle’s DBMS_LOB builtin package is the other component, which you also will learn how to use in this lab.

Instructions show you how to use these tools to accomplish the task. Your only required coding is to leverage the provided components by writing an anonymous block program that leverages the technology stack.

Help Section

The following small articles cover points in a narrower scope than the external articles provided in this section. The lab presumes that you read the externally referenced articles before examining these smaller tutorial and lab targeted points. You can think of the external articles as setting your context and knowledge base, and these small help sections as guides to like related tasks necessary to complete the lab.

  • Unlike other labs, the Help Instructional Material can not be skipped. You must do it before attempting the lab.

Instructional Material →

The following examples highlight tasks that may have already been done for you, or tasks that you must do to accomplish the lab.

  • How to edit a file as the root user in Linux or Unix.

    You have two essential options to edit files as the root user. One uses the vi (or, vim) editor; and the other uses the GUI emacs editor.

    You launch the vi (or on Linix the vim open source version) by issuing the following command. The trailing ampersand (&) is necessary when you want to keep the Terminal session open and available while using the editor. The ampersand tells Linux or Unix to start the editor in a background process, which leaves your Terminal session to run commands independent of your use of the editor of choice.

    vi

    emacs

    You can find tutorials on both editors at the following links. Please note that most employers will expect you to be able to use one or both of these editors unless they are in a Microsoft-only IT shop.

  • How to create a physical repository for text files and how to map an Oracle virtual directory to the physical repository.

    If not done in your Linux installation, you need to create a textfile physical directory as a subdirectory of the /u01/app/oracle/upload directory. You need to perform that task as the root user.

    You can use either of the following commands to become the root user. They can be run as the student user because the student user is in the sudoer’s list on the Linux instance:

    su - root

    sudo sh

    After becoming the root user, you navigate (or change directory with the cd command) to the /u01/app/oracle/upload directory. There, you create the textfile directory.

    You can use the mkdir command to create the textfile directory.

    mkdir textfile

    After creating the textfile subdirectory, you need to change ownership of the textfile directory from the root and root to oracle and dba. The oracle user becomes the owner of the directory and dba becomes the primary group of the directory, which mirrors the ownership of the Oracle database installation. This is a requirement for Oracle to access the textfile directory.

    • As the root user, you change the ownership of the textfile directory with the following command:

    chown oracle:dba textfile

    You can use the ll or ls -a command from the /u01/app/oracle/upload directory to verify the change in directory ownership.

    If you needed to create the textfile directory, you need to copy the Harry Potter text files from the Chapter10/textfiles directory of the Oracle Database 12c PL/SQL Programming textbook’s code repository into the /u01/app/oracle/upload/textfile directory.

    You should copy the files as the root user. If you created your own instance you will need to download the files from github. The following command should work when the files exist on your local Linux instance.

    cp /home/student/Data/cit325/lib/Oracle12cPLSQLCode/Chapter10/textfiles/Harry*.txt .

    After copying the Harry Potter files, you need to change their ownership from the root user to the oracle user. The Oracle database can only read files owned by the oracle user and from a directory owned by the oracle user.

    You use the following syntax as the root user from the /u01/app/oracle/upload/textfile directory:

    chown oracle:dba textfile

    You verify the change with the ll or ls -a command, like:

    ll H*.txt

    You should see the following in the /u01/app/oracle/upload/textfile directory:

    -rw-r--r--. 1 oracle dba 6737 Nov  6 15:50 HarryPotter1.txt
-rw-r--r--. 1 oracle dba 7999 Nov  6 15:50 HarryPotter2.txt
-rw-r--r--. 1 oracle dba 5936 Nov  6 15:50 HarryPotter3.txt
-rw-r--r--. 1 oracle dba 7721 Nov  6 15:50 HarryPotter4.txt
-rw-r--r--. 1 oracle dba 8061 Nov  6 15:50 HarryPotter5.txt

    When you have the foregoing files in the /u01/app/oracle/upload/textfile directory, you’re ready to work on a shell script that creates a list of files.

  • How to create a uploadtext virtual directory in an Oracle database.

    If not done in your Linux installation, you need to create a uploadtext virtual directory as the system user. Then, you need to grant read, write, and execute privileges to the student user on the uploadtext directory.

    Connect as the system user and run the following query before you try to create the virtual directory.

    COL directory_name  FORMAT A16
COL directory_path  FORMAT A60
SELECT directory_name
,      directory_path
FROM   dba_directories;

    It should display the following when setup correctly:

    DIRECTORY_NAME     DIRECTORY_PATH
---------------- ------------------------------------------------------------
IMPORTER         /u01/app/oracle/import
UPLOAD           /u01/app/oracle/upload
DATA_PUMP_DIR    /u01/app/oracle/admin/XE/dpdump/
XMLDIR           /u01/app/oracle/product/11.2.0/xe/rdbms/xml

  • How to grant privileges on a uploadtext virtual directory in an Oracle database.

    If not done in your Linux installation, you need to grant read, write, and execute privileges on the uploadtext virtual directory. You do that as the system user.

    COL grantor    FORMAT A10
COL table_name FORMAT A30
COL privilege  FORMAT A10
SELECT grantor
,      table_name
,      privilege
FROM   dba_tab_privs
WHERE  table_name = 'UPLOADTEXT';

    It should display the following when setup correctly:

    GRANTOR    TABLE_NAME			  PRIVILEGE
---------- ------------------------------ ----------
SYS	   UPLOADTEXT			  EXECUTE
SYS	   UPLOADTEXT			  READ
SYS	   UPLOADTEXT			  WRITE
SYSTEM	   UPLOADTEXT			  EXECUTE
SYSTEM	   UPLOADTEXT			  READ
SYSTEM	   UPLOADTEXT			  WRITE

    If the virtual directory or granted privileges don’t exist, you need to create them. As the system user you should grant read, write, and execute privileges to the student user on the uploadtext directory.

    You use the syntax covered in Chapter 10 (pages 413 & 766) as the system user to create an uploadtext virtual directory. You should grant read, write, and execute privileges to the student user.

    The following syntax creates an uploadtext virtual directory:

    CREATE DIRECTORY uploadtext AS '/u01/app/oracle/upload/textfile';

    You grant privileges to the student user as the system user, like:

    GRANT READ, WRITE, EXECUTE ON DIRECTORY uploadtext TO student;

  • How to write a dir_list.sh BASH shell script that reads a directory list and returns it as a list, like a table.

    If not done in your Linux installation, you need to write the dir_list.sh file and ensure that it has the right ownership and file privileges. The dir_list.sh file should contain the following:

    /usr/bin/ls /u01/app/oracle/upload/textfile | /usr/bin/grep -v dir | /usr/bin/grep txt

    You can test the shell script as either the root or oracle user with the following syntax:

    ./dir_list.sh

    It should return the following:

    HarryPotter1.txt
HarryPotter2.txt
HarryPotter3.txt
HarryPotter4.txt
HarryPotter5.txt

    You can check the ownership and privileges of the file with the ll command, like:

    ll dir_list.sh

    It should return the following:

    -rwxr-xr-x. 1 oracle dba 87 Mar  2 21:17 dir_list.sh

    You can set the ownership and privileges of the file with a combination of the chown and chmod commands if they haven’t been set with the following two commands:

    chown oracle:dba dir_list.sh
chmod 755 dir_list.sh

The lab builds on work done in the Lab 7 and Lab 8. It does not use any of the code other than the setup code provided to you from the course Oracle Database 12c PL/SQL Programming textbook. The lab has you load large text files from a directory in the operating system.

You will copy files into a local directory on the operating system, configure a virtual directory inside the Oracle database, and develop an external table that leverages preprocessor. The supporting documentation to understand the process of creating external tables and external tables with preprocessing are:

An external table with preprocessing lets you read a list of tables from the file system into an external table. You can then read list of files via a SQL cursor and loop through the result set while calling the load_clob_from_file procedure to write the large file from a file iinto a CLOB column. This is the technique that is most often used when writing large text files into an Oracle database. Similar approaches exist in other commercial databases.

The trick to making these types of uploads work, often requires adding or maintaining what are sometimes called targeting columns. A targeting column in an upload holds the file name or a fully qualified file name. A file name is simply the file name a dot (.) and file extension. A fully qualified file name is a path, a file name, a dot (.), and file extension. Oracle’s use of virtual directories lends itself to simple file names because the path is resolved when you create a virtual directory in the Oracle database.

Oracle’s solution is complex from an architectural perspective but simple from a programmer’s perspective. The Database Administrator (DBA) is typically responsible for creating virtual directories and the System Administrator (SA) is typically responsible for creating the physical directory and making sure it and the files have the correct system privileges.

Your Lab 7 script should run the following two scripts, or your Lab 8 script should run the following two scripts. However, if both labs fail to run the necessary pre-reqs for Lab 9, you can call these two scripts:

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

Assuming your Lab 8 includes calls to both scripts, you should call your apply_plsql_lab8.sql script to set the pre-reqs for Lab 9:

@/home/student/Data/cit325/lab8/apply_plsql_lab8.sql

The lab has one part.

Lab Description

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

Lab Instructions →

You should create the apply_plsql_lab9.sql script with the following code components. The script file should contain all component, including verification scripts.

  • Verify the avenger external table resolves to the physical avenger.csv file.

    The first part of the lab requires that you verify that the student user can access the physical file directory. You should have an avenger.csv file in the /u01/app/oracle/upload/textfile directory.

    You can verify the integrity of the avenger.csv file by displaying the contents of the file, like this:

    cat /u01/app/oracle/upload/textfile/avenger.csv

    1,'Anthony','Stark','Iron Man'
2,'Thor','Odinson','God of Thunder'
3,'Steven','Rogers','Captain America'
4,'Bruce','Banner','Hulk'
5,'Clinton','Barton','Hawkeye'
6,'Natasha','Romanoff','Black Widow'

    After verifying the physical file, you can create the avenger external table. The following code creates the avenger external table (but you shouldn’t forget to drop the avenger table in your script file):

    CREATE TABLE avenger
( avenger_id      NUMBER
, first_name      VARCHAR2(20)
, last_name       VARCHAR2(20)
, character_name  VARCHAR2(20))
 ORGANIZATION EXTERNAL
 ( TYPE oracle_loader
   DEFAULT DIRECTORY uploadtext
   ACCESS PARAMETERS
   ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
     BADFILE     'UPLOAD':'avenger.bad'
     DISCARDFILE 'UPLOAD':'avenger.dis'
     LOGFILE     'UPLOAD':'avenger.log'
     FIELDS TERMINATED BY ','
     OPTIONALLY ENCLOSED BY "'"
     MISSING FIELD VALUES ARE NULL )
   LOCATION ('avenger.csv'))
REJECT LIMIT 0;

    You can verify that the table works with the following query:

    SELECT * FROM avenger;

    It should print the following:

    AVENGER_ID FIRST_NAME LAST_NAME  CHARACTER_NAME
---------- ---------- ---------- ---------------
	 1 Anthony    Stark	 Iron Man
	 2 Thor       Odinson	 God of Thunder
	 3 Steven     Rogers	 Captain America
	 4 Bruce      Banner	 Hulk
	 5 Clinton    Barton	 Hawkeye
	 6 Natasha    Romanoff	 Black Widow
  • Create the file_list external table using a preprocessing clause; and verify the file_list external table resolves to the results of the physical dir_list.sh script file.

    You can create the file_list external table with the following SQL statement:

    CREATE TABLE file_list
( file_name VARCHAR2(60))
ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY uploadtext
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      PREPROCESSOR uploadtext:'dir_list.sh'
      BADFILE     'UPLOADTEXT':'dir_list.bad'
      DISCARDFILE 'UPLOADTEXT':'dir_list.dis'
      LOGFILE     'UPLOADTEXT':'dir_list.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL)
    LOCATION ('dir_list.sh'))
  REJECT LIMIT UNLIMITED;

    It should look like this with a desc[ribe] command:

     Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME					    VARCHAR2(60)

    You should be able use the following query to see the data if you have done the following 11 things correctly during setup (some of which may have been done for you in the VM instance):

    1. Create the physical /u01/app/oracle/upload/textfile directory.
    2. Ensure the ownership of the /u01/app/oracle/upload/textfile physical directory is oracle:dba, which means an oracle user as the owner and a primary dba group.
    3. Copy the HarryPotter*.txt files from the specified source directory into the /u01/app/oracle/upload/textfile directory.
    4. Change the file privileges to -rw-r--r-- on the HarryPotter*.txt files.
    5. Change the ownership of the HarryPotter*.txt files to oracle:dba, which means an oracle user as the owner and a primary dba group.
    6. Create the dir_list.sh script, as qualified above, in the /u01/app/oracle/upload/textfile directory.
    7. Change the file privileges to -rwxr-xr-x on the dir_list.sh file.
    8. Change the ownership of the dir_list.sh file to oracle:dba, which means an oracle user as the owner and a primary dba group.
    9. Create the uploadtext virtual directory as the system user, and map it to the /u01/app/oracle/upload/textfile physical directory.
    10. Grant read, write, and execute privileges on the uploadtext virtual directory to the student user account.
    11. Create the file_list external table with a PREPROCESSOR parameter.

    SELECT * FROM file_list;

    You should create a microtest case with the external table before pulling pieces together because it is easier to debug as a small isolated step instead of one step in a larger script. You could test it by creating a script file that only drops and creates the file_list external table, and then test it as shown in the following screen shot (double click image to see full size view).

  • You need to create the load_clob_from_file procedure in the student user schema.

    • You can copy the load_clob_from_file.sql script from Chapter 10 of the Oracle Database 12c PL/SQL Programming textbook’s code repository. Then, you copy the portion of the script that creates the load_clob_from_file procedure into your apply_plsql_lab9.sql script.

    For your convenience, below you will find the portion of the script that creates the load_clob_from_file procedure. It appears exactly as presented below in the Oracle Database 12c PL/SQL Programming textbook.

    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

    CREATE OR REPLACE PROCEDURE load_clob_from_file
( src_file_name     IN VARCHAR2
, table_name        IN VARCHAR2
, column_name       IN VARCHAR2
, primary_key_name  IN VARCHAR2
, primary_key_value IN VARCHAR2 ) IS
 
  -- Define local variables for DBMS_LOB.LOADCLOBFROMFILE procedure.
  des_clob   CLOB;
  src_clob   BFILE := BFILENAME('GENERIC',src_file_name);
  des_offset NUMBER := 1;
  src_offset NUMBER := 1;
  ctx_lang   NUMBER := dbms_lob.default_lang_ctx;
  warning    NUMBER;
 
  -- Define a pre-reading size.
  src_clob_size NUMBER;
 
  -- Define local variable for Native Dynamic SQL.
  stmt VARCHAR2(2000);
 
BEGIN
 
  -- Opening source file is a mandatory operation.
  IF dbms_lob.fileexists(src_clob) = 1 AND NOT dbms_lob.isopen(src_clob) = 1 THEN
    src_clob_size := dbms_lob.getlength(src_clob);
    dbms_lob.OPEN(src_clob,DBMS_LOB.LOB_READONLY);
  END IF;
 
  -- Assign dynamic string to statement.
  stmt := 'UPDATE '||table_name||' '
       || 'SET    '||column_name||' = empty_clob() '
       || 'WHERE  '||primary_key_name||' = '||''''||primary_key_value||''' '
       || 'RETURNING '||column_name||' INTO :locator';
 
  -- Run dynamic statement.       
  EXECUTE IMMEDIATE stmt USING OUT des_clob;
 
  -- Read and write file to CLOB, close source file and commit.
  dbms_lob.loadclobfromfile( dest_lob     => des_clob
                           , src_bfile    => src_clob
                           , amount       => dbms_lob.getlength(src_clob)
                           , dest_offset  => des_offset
                           , src_offset   => src_offset
                           , bfile_csid   => dbms_lob.default_csid
                           , lang_context => ctx_lang
                           , warning      => warning );
 
  -- Close open source file.
  dbms_lob.CLOSE(src_clob);
 
  -- Commit write and conditionally acknowledge it.
  IF src_clob_size = dbms_lob.getlength(des_clob) THEN
    $IF $$DEBUG = 1 $THEN
      dbms_output.put_line('Success!');
    $END
    COMMIT;
  ELSE
    $IF $$DEBUG = 1 $THEN
      dbms_output.put_line('Failure.');
    $END
    RAISE dbms_lob.operation_failed;
  END IF;
 
END load_clob_from_file;
/

    After copying it into the apply_plsql_lab9.sql script, you should change the following incorrect GENERIC virtual directory name on line 10:

    10

      src_clob   BFILE := BFILENAME('GENERIC',src_file_name);

    10

      src_clob   BFILE := BFILENAME('UPLOADTEXT',src_file_name);

    After you create the load_clob_from_file procedure, you can use the SQL*Plus DESC(rise) command to see the load_clob_from_file procedure’s signature. It should look like this:

    PROCEDURE load_clob_from_file
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SRC_FILE_NAME			VARCHAR2		IN
 TABLE_NAME			VARCHAR2		IN
 COLUMN_NAME			VARCHAR2		IN
 PRIMARY_KEY_NAME		VARCHAR2		IN
 PRIMARY_KEY_VALUE		VARCHAR2		IN

  • You can verify that the load_clob_from_file procedure works correctly in the following anonymous PL/SQL block, which you deploy in the student user schema. (NOTE: The anonymous block below is like what you will use inside the final update_item_description procedure, only the cursor SELECT-list, tables, join, and WHERE clause will change. Also, you should note that the call to the load_clob_from_file procedure will no longer use a string literal for the src_file_name value.) 

    DECLARE
 /* Declare a cursor to find a single row from the item table. */
 CURSOR c IS
   SELECT i.item_id
   ,      i.item_title
   FROM   item i INNER JOIN common_lookup cl
   ON     i.item_type = cl.common_lookup_id
   WHERE  UPPER(i.item_title) LIKE '%GOBLET%'
   AND    UPPER(cl.common_lookup_meaning) = 'DVD: WIDE SCREEN';
BEGIN
  /* Read the cursor and load one large text file to the row. */
  FOR i IN c LOOP
    load_clob_from_file( src_file_name     => 'HarryPotter4.txt'
                       , table_name        => 'ITEM'
                       , column_name       => 'ITEM_DESC'
                       , primary_key_name  => 'ITEM_ID'
                       , primary_key_value => TO_CHAR(i.item_id) );
  END LOOP;
END;
/

    You can use the following query to verify the success of the anonymous PL/SQL block:

    SELECT i.item_id
,      i.item_title
,      LENGTH(i.item_desc)
FROM   item i INNER JOIN common_lookup cl
ON     i.item_type = cl.common_lookup_id
WHERE  UPPER(i.item_title) LIKE '%GOBLET%'
AND    UPPER(cl.common_lookup_meaning) = 'DVD: WIDE SCREEN';

    It prints the following result:

       ITEM_ID ITEM_TITLE                            LENGTH(I.ITEM_DESC)
---------- ------------------------------------- -------------------
      1028 Harry Potter and the Goblet of Fire                  7721
  • You need to add a text_file_name column to the item table. The text_file_name column lets you match external file names to specific rows in the item table.

    You need to fix the existing data set before you change the item table. The item_title column holds a “Harry Potter and the Sorcer’s Stone” string, which is incorrect. You need to fix the item_title column for those rows with the following UPDATE statement:

    UPDATE item i
SET    i.item_title = 'Harry Potter and the Sorcerer''s Stone'
WHERE  i.item_title = 'Harry Potter and the Sorcer''s Stone';

    After fixing the data error, add a text_file_name column as a VARCHAR2(30) to the item table. (See the Column Maintenance section in the textbook for the ALTER statement syntax, which you can find on pages 780 through 782.)

    You can verify the list of distinct item_table values that are DVD or VHS tables with the following query:

    COL text_file_name  FORMAT A16
COL item_title      FORMAT A42
SELECT   DISTINCT
         text_file_name
,        item_title
FROM     item i INNER JOIN common_lookup cl
ON       i.item_type = cl.common_lookup_id
WHERE    REGEXP_LIKE(i.item_title,'^.*'||'Harry'||'.*$')
AND      cl.common_lookup_table = 'ITEM'
AND      cl.common_lookup_column = 'ITEM_TYPE'
AND      REGEXP_LIKE(cl.common_lookup_type,'^(dvd|vhs).*$','i')
ORDER BY i.item_title;

    You should see the following results:

    TEXT_FILE_NAME	   ITEM_TITLE
------------------ ------------------------------------------
                   Harry Potter and the Chamber of Secrets
                   Harry Potter and the Goblet of Fire
                   Harry Potter and the Order of the Phoenix
                   Harry Potter and the Prisoner of Azkaban
                   Harry Potter and the Sorcerer's Stone

    After you add the text_file_name column, you can write five UPDATE statements. One UPDATE statement for each of the unique titles. The UPDATE statements add a string into the text_file_name column that matches physical file names found in the uploadtext virtual directory. More specifically, they represent the file_name values returned by the preprocessing dir_list.sh script.

    Use the following table as your guide to writing the UPDATE statements:

    Table Name: ITEM
    TEXT_FILE_NAME ITEM_TITLE
    HarryPotter1.txt Harry Potter and the Sorcerer’s Stone
    HarryPotter2.txt Harry Potter and the Chamber of Secrets
    HarryPotter3.txt Harry Potter and the Prisoner of Azkaban
    HarryPotter4.txt Harry Potter and the Goblet of Fire
    HarrpPotter5.txt Harry Potter and the Order of the Phoenix

    You can verify the list of distinct item_table values that are DVD or VHS tables:

    The following lets you UPDATE the text_file_name column values by adding the file name value would match values in the text_file_name column following:

    UPDATE item i
SET    i.text_file_name = 'HarryPotter1.txt'
WHERE  i.item_title = 'Harry Potter and the Sorcerer''s Stone';

    You can verify the list of distinct text_file_name and item_table column values that are DVD or VHS tables with the following query:

    COL text_file_name  FORMAT A16
COL item_title      FORMAT A42
SELECT   DISTINCT
         text_file_name
,        item_title
FROM     item i INNER JOIN common_lookup cl
ON       i.item_type = cl.common_lookup_id
WHERE    REGEXP_LIKE(i.item_title,'^.*'||'Harry'||'.*$')
AND      cl.common_lookup_table = 'ITEM'
AND      cl.common_lookup_column = 'ITEM_TYPE'
AND      REGEXP_LIKE(cl.common_lookup_type,'^(dvd|vhs).*$','i')
ORDER BY i.text_file_name;

    You should see the following results:

    TEXT_FILE_NAME	   ITEM_TITLE
------------------ ------------------------------------------
HarryPotter2.txt   Harry Potter and the Chamber of Secrets
HarryPotter4.txt   Harry Potter and the Goblet of Fire
HarryPotter5.txt   Harry Potter and the Order of the Phoenix
HarryPotter3.txt   Harry Potter and the Prisoner of Azkaban
HarryPotter1.txt   Harry Potter and the Sorcerer's Stone
  • Write an update_item_description procedure to upload file names that match a partial item_title value. The update_item_description procedure should take one pv_item_title parameter and update all item_desc column values with the contents of files with matching file names.

    The update_item_description procedure should contain the following (please check code sample calling load_clob_from_file procedure above or review the example in the textbook):

    • Accept a single pv_item_title parameter, which should use a VARCHAR2(30) data type.
    • Use a dynamic cursor that accepts a partial string value for the item_title column. (The simplest approach would write a query against a join of the item and file_list tables. That join would use a match between the text_file_name column in the item table and file_name column in the file_list table.)
    • Call the load_clob_from_file procedure for every row returned from the item table that has a text_file_name that matches the file_name values from the file_list external table.

    After writing and successfully compiling the update_item_description procedure, run the test case provided below.

Test Case

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

Test Case Instructions →

  • You test that the components are read from an external directory and stored in the item_desc column of the item table.

    • You can test the update_item_description procedure with the following command:

    EXECUTE upload_item_description('Harry Potter');

    You can use the following to test the success of the upload:

    COL item_id     FORMAT 9999
COL item_title  FORMAT A44
COL text_size   FORMAT 999,999
SET PAGESIZE 99
SELECT   item_id
,        item_title
,        LENGTH(item_desc) AS text_size
FROM     item
WHERE    REGEXP_LIKE(item_title,'^Harry Potter.*$')
AND      item_type IN
          (SELECT common_lookup_id 
           FROM   common_lookup 
           WHERE  common_lookup_table = 'ITEM' 
           AND    common_lookup_column = 'ITEM_TYPE'
           AND    REGEXP_LIKE(common_lookup_type,'^(dvd|vhs).*$','i'))
ORDER BY item_id;

    It should print the following:

    ITEM_ID ITEM_TITLE				     TEXT_SIZE
------- -------------------------------------------- ---------
   1021 Harry Potter and the Sorcerer's Stone		 6,737
   1022 Harry Potter and the Sorcerer's Stone		 6,737
   1023 Harry Potter and the Chamber of Secrets 	 7,999
   1024 Harry Potter and the Chamber of Secrets 	 7,999
   1025 Harry Potter and the Prisoner of Azkaban	 5,936
   1026 Harry Potter and the Prisoner of Azkaban	 5,936
   1027 Harry Potter and the Chamber of Secrets 	 7,999
   1028 Harry Potter and the Goblet of Fire		 7,721
   1029 Harry Potter and the Goblet of Fire		 7,721
   1030 Harry Potter and the Goblet of Fire		 7,721
   1031 Harry Potter and the Order of the Phoenix	 8,061

    Written by michaelmclaughlin

    November 6th, 2016 at 2:35 pm

    Posted in