iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 20

“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?