PLSQL Large objects example

Large objects exercise

As sys create the oracle directory EMP_DATA. Grant the appropriate privileges to user HR to read and write on this directory.

Modify your employees table (_emp) add columns emp_resume clob and emp_photo bfile. For each employee that exist in your table update the new columns as follows: (a) for emp_resume add the String 32000 bytes String emp_name ||' resume is: '||plus dots(....) till 32000 characters (b) add the anonymous photo for all employee.

Write a procedure to print your table's date. For the resume print its length,for photo just print the photo size.

Solution

As sys:

create or replace directory IMG as 'C:\Libraries\Pictures';

grant read,write on directory IMG to hr;

As hr:

drop table michael_emp purge;

create table michael_emp

(

emp_id number primary key,

emp_name varchar2(30) not null

);

insert into michael_emp values ( 1,'mmmmm');

insert into michael_emp values ( 2,'ggggg');

commit;

alter table michael_emp add emp_resume clob;

alter table michael_emp add emp_photo bfile;

create or replace procedure updateEMP

as

cursor c is select emp_id,emp_name from michael_emp FOR UPDATE;

cr c%ROWTYPE;

begin

for cr in c loop

update michael_emp set emp_resume=RPAD(cr.emp_name||' resume is: ',32000,'.') ,

emp_photo=bfilename('EMP_DATA','anonymous.jpg')

where current of c;

end loop;

commit;

end;

CREATE OR REPLACE PROCEDURE PRINTEMP AS

cursor c is select * from michael_emp ;

cr c%ROWTYPE;

BEGIN

for cr in c loop

dbms_lob.fileopen(cr.emp_photo);

dbms_output.put_line('Employee ID:'||cr.emp_id||' Name:'||cr.emp_name||' Resume size: '||dbms_lob.getlength(cr.emp_resume) ||' Photo size: '||dbms_lob.getlength(cr.emp_photo));

dbms_lob.fileclose(cr.emp_photo);

end loop;

END PRINTEMP;