Your organization wants to add their employees’ pictures into the EMP table using a stored procedure. You have now been assigned to complete this task. They want you to add one more column to the EMP table with a datatype of BLOB (Binary Large Object). You should write a procedure so they can enter the employee number and their picture’s file name, and run the procedure to add their picture.

-- Hands-On 10 (Inserting employees' picture into the EMP table using BLOB) 

SET ECHO ON
connect iself/schooling
-- to clear the mess.
-- don't show this in the movie.
alter table emp drop (photo)
/
delete from emp
where empno = 9000
/


CLEAR SCR
-- Connect to SQLPLUS as the iself user.
--
pause


CONNECT iself/schooling
pause

CLEAR SCR
-- First, add the photo column to the EMP table.
--
pause

ALTER TABLE emp ADD (photo blob)
/
pause


CLEAR SCR
-- Empty the photo column in the EMP table.
--
pause

UPDATE emp
SET photo = empty_blob()
/
commit;
pause


CLEAR SCR
-- In order to proceed you must have previously created 
-- a directory named ephoto in the root directory.

-- We have already created that folder and have copied two pictures 
-- into the ephoto folder.

pause
pause

CLEAR SCr
-- Create a directory object called photo_folder.
--
pause

CREATE OR REPLACE DIRECTORY photo_folder AS 'c:\ephoto'
/
-- Notice that you don't have the sufficient privilege
-- to create a directory object.
--
pause 

CLEAR SCR
-- Now, connect to SQLPLUS as the system/manager user.
-- 
pause

CONNECT system/manager
pause


CLEAR SCR
-- Grant the CREATE ANY DIRECTORY and DROP ANY DIRECTORY 
-- privileges to the iself user. 
-- 
pause

GRANT CREATE ANY DIRECTORY TO iself
/

GRANT DROP ANY DIRECTORY TO iself
/
-- The CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges
-- have been granted to the ISELF user.
--
pause 

CLEAR SCR
-- Connect to SQLPLUS as the iself user.
-- 
pause

CONNECT iself/schooling
pause

CLEAR SCR
-- Create a directory object called photo_folder.
-- 
pause

CREATE OR REPLACE DIRECTORY photo_folder AS 'c:\ephoto'
/
-- Notice that the directory object was created.
--
pause 

CLEAR SCR
-- Write a stored procedure to read the employee number and
-- its photo file name and then store the employees' picture
-- into the EMP table. 
--
pause

-- The next page is a complete stored procedure that stores pictures
-- into the EMP table.

-- You are encouraged to pause the movie and to take
-- notes about the stored procedure. When you have finished, you may 
-- continue playing the movie.

pause

CLEAR SCR
CREATE OR REPLACE PROCEDURE insert_photo 
(p_empno NUMBER, p_photo VARCHAR2) 
AS 
f_photo BFILE; 
b_photo BLOB; 
Begin

-- Update the employee photo
UPDATE emp
SET photo = empty_blob()
WHERE empno = p_empno
RETURN photo into b_photo;

-- find where the photo's pointer is located.
f_photo := bfilename('PHOTO_FOLDER', p_photo);
-- open the photo as read-only option. 
dbms_lob.fileopen(f_photo, dbms_lob.file_readonly);
-- load the photo into column photo. 
dbms_lob.loadfromfile(b_photo,f_photo, dbms_lob.getlength(f_photo));
-- close the photo's pointer. 
dbms_lob.fileclose(f_photo);
-- Save the loaded photo record. 
COMMIT;
EXCEPTION
-- Check for your error messages
WHEN others THEN
dbms_output.put_line('*** ERROR *** Check you procedure.');
END; 
/
pause 

CLEAR SCR
-- Execute the procedure to insert the first employee picture
-- into the EMP table.
--
pause


EXECUTE insert_photo(7369, 'pic001.GIF')
/
-- The photo of the first employee in the EMP table was inserted.
commit;
pause

CLEAR SCR
-- Execute the procedure again to insert the second employee picture
-- into the EMP table.
--
pause


EXECUTE insert_photo(7499, 'pic002.GIF')
/
-- The photo of the second employee in the EMP table was inserted.
commit;
pause


CLEAR SCR
-- Test to see if the photos of the employees were inserted.
pause

SELECT empno, ename,
dbms_lob.getlength(photo) "Photo Size"
FROM emp
/
-- Notice that the PHOTO SIZE column has a positive number.
-- This indicates that these two employees have pictures 
-- in the PHOTO column in the EMP table.
pause

CLEAR SCR
-- Now, drop the PHOTO_FOLDER directory and 
-- the INSERT_PHOTO procedure.
--
pause

DROP DIRECTORY photo_folder
/
DROP PROCEDURE insert_photo
/
pause

CLEAR SCR
-- Connect as the system/manager user
-- 
pause

CONNECT system/manager
pause


CLEAR SCR
-- Revoke the CREATE ANY DIRECTORY and DROP ANY DIRECTORY 
-- privileges from the iself user. 
-- 
pause

REVOKE CREATE ANY DIRECTORY FROM iself
/

REVOKE DROP ANY DIRECTORY FROM iself
/
-- The CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges
-- have been revoked from the ISELF user.

-- You have dropped the objects and revoked the privileges
-- so that you can perform this excercise over and over again.

pause 

CLEAR SCR
-- Now, practice this Hands-On over and over
-- until you become a master at it.

-- Do not forget to watch the next Hands-On movie to view 
-- your photos that were inserted into the EMP table.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck!
pause