--STEP 1
    --First add the emp_photo column to the emp table.
    ALTER TABLE emp ADD (emp_photo BLOB);

--STEP 2
    --Empty the emp_photo column in the Emp table.
    UPDATE  emp
    SET     emp_photo = EMPTY_BLOB();
    COMMIT;

--STEP 3
    --In order to proceed you must have previously created a directory
    -- named emp_photo in the root directory.
    
    --We have already created that folder and have copied two pictures in
    --emp_photo folder.
    --Create a directory object called emp_photo

    CREATE OR REPLACE DIRECTORY Emp_Photo AS 'C:\EMP\';
 
    --If error occured while creating directory like
    --Error at line 1:
    --Ora-01031: insufficient privileges

    --That means you don't have sufficient privileges to create directory
    --For that connect to SQLPLUS as SYSTEM user
    
    CONNECT SYSTEM/SA*****@MI****

    --Grant CREATE ANY DIRETORY and DROP ANY DIRECTORY privileges to scott user

    GRANT CREATE ANY DIRECTORY TO scott;
    GRANT DROP ANY DIRECTORY TO scott;

    --The CREATE ANY DIRETORY and DROP ANY DIRECTORY privileges have been 
    --granted to the user

    --Now connect to SQLPLUS as scott user

    CONNECT SCOTT/SA*****@MI****;

    --Now Create a directory object called emp_photo

    CREATE OR REPLACE DIRECTORY Emp_Photo AS 'C:\EMP\';

    --This time, the directory will get created without error
    --You can check whether it is created or not using following

--    SELECT  *
  --  FROM    all_directories;

--STEP 4
    --Write a stoed procedure to read the employee number and its
    --photo file name and then stores employees's picture into the emp
    --table

    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     emp_photo   = EMPTY_BLOB()
        WHERE   empno       = p_empno
        RETURN  emp_photo INTO b_photo;

        --Find where photo's pointer is located
        f_photo := BFILENAME('EMP_PHOTO', p_photo);

        --Open the photo as read only option
        DBMS_LOB.FILEOPEN (f_photo, DBMS_LOB.FILE_READONLY);

        --Load the photo into column emp_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 your procedure');
    END;

--STEP 5
    --Execute the procedure to insert the first employee picture into 
    --EMP table
    EXECUTE insert_photo (7936, '2013\admin.jpg');

    COMMIT;

--STEP 6
    --Test to see if employees photo inserted correctly

    SELECT  empno, ename, DBMS_LOB.GETLENGTH(emp_photo) "PHOTO SIZE"
    FROM    emp;

    --You'll notice that emp_photo column has positive number
    --which indicates that the employees have pictures in the
    --emp_photo column in the EMP table