"A good friend can
tell you what is the matter with you in a minute. He may not
seem such a good friend after telling." Arthur Brisbane,
"The Book of Today" |
Read
first then play the video:
FRM-VIDEO -Inserting
employee's picture to a table
FRM-VIDEO -Viewing
a picture using BLOB datatype in a FORM
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
iself user.
SQL> CONNECT iself/schooling
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 iself user.
SQL> GRANT CREATE ANY DIRECTORY TO iself
SQL> /
GRANT DROP ANY DIRECTORY
SQL> GRANT DROP ANY
DIRECTORY TO iself
SQL> /
The CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges
have been granted to the ISELF user.
Connect to SQLPLUS as the
iself user.
SQL> CONNECT iself/schooling
Create a directory object
called photo_folder.
SQL> CREATE OR REPLACE DIRECTORY
photo_folder AS 'c:\xxx'
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 iself user.
SQL> REVOKE CREATE ANY DIRECTORY FROM
iself
SQL> /
Revoke the DROP ANY DIRECTORY privilege
SQL> REVOKE DROP ANY
DIRECTORY FROM iself
SQL> /
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 exercise over.
"As a scientist, I
am not sure anymore that life can be reduced to a class
struggle, to dialectical materialism, or any set of formulas.
Life is spontaneous and it is unpredictable, it is magical. I
think that we have struggled so hard with the tangible that we
have forgotten the intangible." Diane Frolov and Andrew
Schneider, Northern Exposure, Zarya, 1994 |
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?
|