|
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 oracle/learning
-- 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 oracle user.
--
pause
CONNECT oracle/learning
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 oracle user.
--
pause
GRANT CREATE ANY DIRECTORY TO oracle
/
GRANT DROP ANY DIRECTORY TO oracle
/
-- The CREATE ANY DIRECTORY and DROP ANY DIRECTORY
privileges
-- have been granted to the ORACLE user.
--
pause
CLEAR SCR
-- Connect to SQLPLUS as the oracle user.
--
pause
CONNECT oracle/learning
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 oracle user.
--
pause
REVOKE CREATE ANY DIRECTORY FROM oracle
/
REVOKE DROP ANY DIRECTORY FROM oracle
/
-- The CREATE ANY DIRECTORY and DROP ANY DIRECTORY
privileges
-- have been revoked from the ORACLE 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 |
