|
Inserting employees� picture into the EMP
table using BLOB
Hands-On
introduction
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.
Your
assignments are:
1- To Modify
the EMP table,
2- To create a
physical and logical directory,
3- To create a
procedure to load the picture (BLOB),
4- To populate
the EMP table.
Connect to
SQLPLUS as the oracle user.
SQL> CONNECT oracle/learning
First, add the
photo column to the EMP table.
SQL> ALTER
TABLE emp ADD (photo blob)
SQL> /
EMPTY_BLOB( )
Empty the
photo column in the EMP table. The
EMPTY_BLOB function returns an empty locator
of type BLOB (binary large object). Use
EMPTY_BLOB to initialize a BLOB to "empty."
Before you can work with a BLOB, either to
reference it in SQL DML statements such as
INSERTs or to assign it a value in PL/SQL,
it must contain a locator. It cannot be
NULL. The locator might point to an empty
BLOB value, but it will be a valid
BLOB locator.
SQL> UPDATE
emp
SET photo = empty_blob( )
SQL> /
SQL> commit;
In order to
proceed, you must have previously created a
directory named ephoto (Employee Photo) in
the root directory. Assuming that you have
already created that folder and have copied
two pictures into the ephoto folder.
CREATE
DIRECTORY �
Create a
directory object called photo_folder.
SQL> CREATE OR
REPLACE DIRECTORY photo_folder AS 'c:'
SQL> /
Notice that you don't have the
sufficient privilege to create a directory
object.
Now, connect
to SQLPLUS as the system/manager user.
SQL> CONNECT
system/manager
GRANT CREATE
ANY DIRECTORY
Grant the
CREATE ANY DIRECTORY and DROP ANY DIRECTORY
privileges to the oracle user.
SQL> GRANT CREATE
ANY DIRECTORY TO oracle
SQL> /
GRANT DROP ANY DIRECTORY
SQL> GRANT
DROP ANY DIRECTORY TO oracle
SQL> /
The CREATE ANY
DIRECTORY and DROP ANY DIRECTORY privileges
have been granted to the ORACLE user.
Connect to
SQLPLUS as the oracle user.
SQL> CONNECT
oracle/learning
Create a
directory object called photo_folder.
SQL> CREATE OR
REPLACE DIRECTORY photo_folder AS 'c:'
SQL> /
Notice that the directory object
was created.
Create a
procedure to store picture
Write a stored
procedure to read the employee number and
its photo file name and then store the
employees' picture into the EMP table.
SQL> 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;
SQL> /
Store pictures
to Oracle database
Execute the
procedure to insert the first employee
picture into the EMP table.
SQL> EXECUTE
insert_photo(7369, 'pic001.GIF')
SQL> /
The photo of the first employee
in the EMP table was inserted. Save the
transaction.
SQL> commit;
Execute the
procedure again to insert the second
employee picture into the EMP table.
SQL> EXECUTE
insert_photo(7499, 'pic002.GIF')
SQL> /
The photo of the second employee
in the EMP table was inserted. Save the
transaction.
SQL> commit;
dbms_lob.getlength() procedure
The DBMS_LOB
package contains procedures and functions
that manipulate Oracle large objects. The
GETLENGHT() procedure is one of the stored
procedures in the DBMS_LOB package. It
returns the size of a large object in the
Oracle database.
Test to see if
the photos of the employees were inserted.
SQL> SELECT empno,
ename,
dbms_lob.getlength(photo) "Photo Size"
FROM emp
SQL> /
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.
DROP DIRECTORY
�
Now, drop the
PHOTO_FOLDER directory and the INSERT_PHOTO
procedure.
SQL> DROP
DIRECTORY photo_folder
SQL> /
DROP PROCEDURE
�
SQL> DROP
PROCEDURE insert_photo
SQL> /
Connect as the
system/manager user
SQL> CONNECT system/manager
Revoke the
CREATE ANY DIRECTORY privilege�
Revoke the
CREATE ANY DIRECTORY and DROP ANY DIRECTORY
privileges from the oracle user.
SQL> REVOKE CREATE
ANY DIRECTORY FROM oracle
SQL> /
Revoke the DROP ANY DIRECTORY privilege
SQL> REVOKE
DROP ANY DIRECTORY FROM oracle
SQL> /
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
exercise over.
Questions:
Q: How do you
add a column to a table?
Q: What does
the EMPTY_BLOB() function?
Q: How do you
create a directory in the Oracle database?
Q: Does
everyone can create a directory in the
Oracle database?
Q: Write a
stored procedure to read the employee number
and its photo file name and then store the
employee�s picture into the EMP table.
Q: How do you
test that there is a picture in a column?
Q: What does
the DBMS_LOB package?
Q: What does
the GETLENGTH() function in the DBMS_LOB
package?
Q: How do you
drop a directory from your Oracle database?
Q: How and
when do you grant the CREATE ANY DIRECTORY
privilege to a user?
Q: How do you
revoke the CREATE ANY DIRECTORY privilege
from a user? |
