17 Manipulating Large Objects

PL/SQL
Programming
Presentation by Ayman Hamdan

Manipulating Large
20 Objects
What Is a LOB?
LOBs are used to store large, unstructured data such as text, graphic
images, films, and sound waveforms.
“Four score and seven years

Movie (BFILE)
ago, our forefathers brought
forth upon this continent, a
Photo (BLOB)
new nation, conceived in
LIBERTY, and dedicated to the
proposition that all men are
created equal.”
Text (CLOB)
What Is a LOB?
– There are three large object data types:

• BLOB represents a binary large object, such as a video clip.
• CLOB represents a character large object.
• BFILE represents a binary file stored in an OS binary file
outside the database.
Internal LOBs
The LOB value is stored in the database.
“Four score and seven years ago,

our forefathers brought forth upon
this continent, a new nation,
conceived in LIBERTY, and dedicated
to the proposition that all men
are created equal.”
CLOB BLOB
What Are BFILEs?
• BFILEs are external large objects (LOBs) stored in OS files that are external
to database tables.
• The BFILE data type stores a locator to the physical file.
• BFILEs are read-only; they do not participate in transactions
Movie (BFILE)
Using the DBMS_LOB Package
– Working with LOBs often requires the use of the Oracle-supplied
DBMS_LOB package.
– LOB data can be retrieved directly using SQL.
– DBMS_LOB provides routines to access and manipulate internal and
external LOBs.
• Modify LOB values:
APPEND, COPY, ERASE, TRIM, WRITE, LOADFROMFILE
• Read or examine LOB values:
GETLENGTH, INSTR, READ, SUBSTR
• Specific to BFILEs:
FILECLOSE, FILECLOSEALL, FILEEXISTS, FILEGETNAME,
FILEISOPEN, FILEOPEN
Managing BFILEs
The DBA or the system administrator:

1. Creates an OS directory and supplies files
2. Creates a DIRECTORY object in the database
3. Grants the READ privilege on the DIRECTORY object to the appropriate
database users
4. Creates an Oracle table with a column that is defined as a BFILE data
type
5. Inserts rows into the table by using the BFILENAME function to
populate the BFILE column
6. Writes a PL/SQL subprogram that declares and initializes a LOB
locator, and reads BFILE
Preparing to Use BFILEs
1. Create an OS directory to store the physical data files:
mkdir /home/oracle/labs/DATA_FILES/MEDIA_FILES
2. Create a DIRECTORY object by using the CREATE DIRECTORY

command:
CREATE OR REPLACE DIRECTORY data_files AS
'/home/oracle/labs/DATA_FILES/MEDIA_FILES';
3. Grant the create and READ privilege on the DIRECTORY object

to the appropriate users:
GRANT CREATE ANY DIRECTORY TO HR;
GRANT READ ON DIRECTORY data_files TO HR;

Populating BFILE Columns with SQL
– Example:
• Add a BFILE column to a table:
ALTER TABLE employees ADD video BFILE;
• Update the column using the BFILENAME function:
UPDATE employees
SET video = BFILENAME('DATA_FILES', 'Winters.avi')
WHERE employee_id = 120;
Populating a BFILE Column with PL/SQL
Copy image King.png to directory data_files
CREATE PROCEDURE set_video(

dir_alias VARCHAR2, custid NUMBER) IS
filename VARCHAR2(40);
file_ptr BFILE;
CURSOR cust_csr IS
SELECT cust_first_name FROM customers
WHERE customer_id = custid FOR UPDATE;
BEGIN
FOR rec IN cust_csr LOOP
filename := rec.cust_first_name || ‘.png';
file_ptr := BFILENAME(dir_alias, filename);
DBMS_LOB.FILEOPEN(file_ptr);
UPDATE customers SET video = file_ptr
WHERE CURRENT OF cust_csr;
DBMS_OUTPUT.PUT_LINE('FILE: ' || filename ||
' SIZE: ' || DBMS_LOB.GETLENGTH(file_ptr));
DBMS_LOB.FILECLOSE(file_ptr);
END LOOP;
END set_video;
EXECUTE set_video('DATA_FILES’, 100)

Initializing LOB Columns Added to a Table
– Add the BLOB columns to an table
CREATE TABLE images(img_id NUMBER, icon BLOB);
CREATE SEQUENCE image_id START WITH 1 INCREMENT BY 1 NOMAXVALUE;

Initializing LOB Columns Added to a Table
– CREATE PROCEDURE TO LOAD IMAGE
create or replace PROCEDURE load_file ( pfname VARCHAR2) IS

l_size number;
l_file_ptr bfile;
l_blob blob;
begin l_file_ptr := bfilename('DATA_FILES', pfname);
dbms_lob.fileopen(l_file_ptr);
l_size := dbms_lob.getlength(l_file_ptr);
insert into images ( img_id, icon ) values ( image_id.nextval, empty_blob() )
returning icon into l_blob;
dbms_lob.loadfromfile(l_blob, l_file_ptr, l_size);
commit;
dbms_lob.close(l_file_ptr);
end;
EXECUTE LOAD_FILE('test.png');

17 Manipulating Large Objects

Uploaded by

Copyright:

Available Formats

17 Manipulating Large Objects

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

17 Manipulating Large Objects

Uploaded by

Copyright:

Available Formats

PL/SQL

Presentation by Ayman Hamdan

“Four score and seven years

– There are three large object data types:

The LOB value is stored in the database.

“Four score and seven years ago,

The DBA or the system administrator:

1. Create an OS directory to store the physical data files:

2. Create a DIRECTORY object by using the CREATE DIRECTORY

3. Grant the create and READ privilege on the DIRECTORY object

GRANT CREATE ANY DIRECTORY TO HR;

GRANT READ ON DIRECTORY data_files TO HR;

ALTER TABLE employees ADD video BFILE;

• Update the column using the BFILENAME function:

Copy image King.png to directory data_files

CREATE PROCEDURE set_video(

EXECUTE set_video('DATA_FILES’, 100)

– Add the BLOB columns to an table

CREATE TABLE images(img_id NUMBER, icon BLOB);

CREATE SEQUENCE image_id START WITH 1 INCREMENT BY 1 NOMAXVALUE;

– CREATE PROCEDURE TO LOAD IMAGE

create or replace PROCEDURE load_file ( pfname VARCHAR2) IS

You might also like