Lab Assignment #11
CIS 208A PL/SQL

1. ______Binary Large Objects, such as sound (MP3), photos (JPEG, BMP), proprietary formats (PDF, DOC, XLS), and executables (EXE, DDL).

2. ______Character Large Objects, such as resumes, text articles, source code files.

3. ______Binary Files, just like BLOBs but stored outside the database, often on separate media (CD, DVD, HD-DVD).

1. State the datatypes of the three kinds of LOB. Which are internal and which are external? What kinds of data can be stored in each one?

2. You will use a partial copy of the employees table. Create this copy by executing:

CREATE TABLE lob_emps

AS SELECT employee_id, last_name

FROM employees

WHERE employee_id IN (103,104);

  1. You need to add a column to the table to store employees’ annual performance evaluations, which are stored in text format. Some evaluations may be very large. Why would you not use a VARCHAR2 datatype for this?
  1. Add a LONG column named annual_eval to the copy table.
  1. Populate the LONG column for the two employees using the following values: employee_id 103, value “Programs Java and HTML well.” Employee_id 104, value “Useless at both HTML and Java.”
  1. Why would it be better if this column were CLOB, not LONG? Convert the column to CLOB using an ALTER TABLE statement.
  1. Describe the table to check that the column is now CLOB. Then SELECT the CLOB data for the two employees.
  1. How is BFILE data stored differently from other types of LOB data (CLOB and BLOB)?
  1. List three restrictions of using BFILEs.
  1. BFILES:
  1. What is a DIRECTORY database object? State two reasons why a DIRECTORY is needed when using BFILEs.
  1. What two SQL statements would you use to create a directory called MYDIR pointing to an operating system directory called ‘/u01/mybfiles’, and to allow Oracle user TOM to read BFILEs stored in that directory?
  1. You do not have the privilege to create your own directories. Query the dictionary to see what directory has already been created for you.
  1. Copy and execute the following anonymous block. Then modify it to declare and use a single record instead of a scalar variable for each column. Make sure that your code will still work if an extra column is added to the departments table later. Execute your modified block and save your code.

DECLARE

v_dept_id departments.department_id%TYPE;

v_dept_name departments.department_name%TYPE;

v_mgr_id departments.manager_id%TYPE;

v_loc_id departments.location_id%TYPE;

BEGIN

SELECT department_id, department_name,

manager_id, location_id

INTO v_dept_id, v_dept_name,

v_mgr_id, v_loc_id

FROM departments

WHERE department_id = 80;

DBMS_OUTPUT.PUT_LINE(v_dept_id || ' ' || v_dept_name

|| ' ' || v_mgr_id

|| ' ' || v_loc_id);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('This department does not exist');

END;