This chapter introduces the way different kinds of multimedia data—audio, image, video, or a combination—can be retrieved from database systems. In this chapter we will first identify the problems in querying multimedia data, and second, study general approaches adopted for dealing with multimedia data particularly by using PL/SQL. With image data it is mainly the semantic nature that is the problem, while video and audio present difficulties in terms of their real-time nature as well. At the end of the chapter the reader will understand how to apply
Attribute-based retrieval using interMedia
Text-based retrieval using metadata with interMedia
Using object properties in retrieval
In this chapter we shall look at the different approaches taken by interMedia technology to address these problems using technical and text-based metadata and the requirements of different application areas. We shall start by applying the technology to the Photo Store application and then go on to systems with more complex requirements.
A key approach is based on extracting information from media objects, attaching this information to the media object, and using it for retrieval from the database. We shall first explain how to do this with a “generic” application such as the Photo Store. Some critics would say that to achieve really effective content-based information retrieval (CBIR) (see Chapter 13) it is also necessary to go further and include specific information in the form of metadata about the domain itself and the semantics of the object.
Interacting with multimedia data is a relatively new possibility. Therefore, it is an area where little is known about users’ requirements, such as in what way would users wish to manipulate and change multimedia objects. Unlike traditional databases a key issue is how to present the results of the multimedia query to the user. The result set could vary from a single relational table to a picture gallery or a summary of a number of documents. Many queries will return a set of similar items rather than an exact match so there is the question of how to rank the results of a multimedia query. How can we judge similarity? One solution is to apply methods to estimate the distance of each item in the set from the desired criteria provided by the user. At the same time it is essential that querying the database does not involve unacceptable response times due to the enormous data volumes. With our family Photo Store we may want to answer queries like:
“Show a photo of our grandfather’s house and a map of its location”
We will need to consider how a query involving media data can be posed to a DBMS and then how the data is retrieved and subsequently presented. The interrogation of multimedia data raises both opportunities and challenges not present in traditional database systems. For example, using visual information in queries results in different ways of constructing the queries and searching the data, as illustrated in Figure 11.1. This presents the four possible combinations of different query and search modes that could be adopted by a multimedia retrieval system. For example, presenting a query in a linguistic mode such as “Find images of holiday in Mexico” but searching the database using visual attributes (visual mode). A visual attribute of an image is a numerical or logical attribute such as mean intensity or coarseness of texture.
The four combinations of modes can be described as:
Linguistic–Linguistic (LL) operates on the basis of forming a query in linguistic terms, as in SQL, and then searching textual metadata that has been stored in the form of text in order to locate and retrieve the required multimedia information. However, it is clear that the attempt to solve all queries by matching a linguistic form against linguistic identifiers in the form of titles, keywords, and captions offers limited retrieval possibilities.
Visual–Visual (VV) is generally known as retrieval by content. For example, the query is posed in the form of a sketch the user draws, with the underlying assumption, “I want something like this.” An alternative to using sketches is to provide the user with a browser based on a small selection of images from the database to use as examples in a query-by-example (QBE) style. These small images are referred to as polyphotos, icon browsers, index images, or thumbnails. A more advanced approach would use a visual thesaurus. For example, NASA developed one of the first visual thesauri to work alongside a text thesaurus for the space domain. Images from the thesaurus could be substituted for textual descriptions of the images in a retrieval system. Approaches to this type of query are explored in Chapter 13.
Visual–Linguistic (VL) provides example images that are then retrieved by linguistic metadata. In this case images are specified by sets of pixels with specific values for color, shape, texture, and geometric relations but indexed by name/title, etc. Queries use a text thesaurus. The image is included in a QBE-style query that places limitations on the variety of information needs that can be expressed by the user. This type of query is explored in Chapter 13.
Linguistic–Visual (LV). In this approach the images are indexed by visual attributes and possibly supplemented by a visual thesaurus. However, the user expresses the query in linguistic form using a standard query language.
Both the VL and LV modes involve the issue of how to create an index by mapping ideas about a subject expressed in different media.
Attribute-based retrieval (ABR) is a method that uses a set of structured attributes in the same way as traditional DBMS. This method can be particularly effective with text data. However, the method does not make use of the rich content of images to retrieve information. An implementation can easily be achieved by using Oracle interMedia itself or combined with other attributes. For example, we created a table for the Photo Store with the following structure.
ID NOT NULL NUMBER DESCRIPTION NOT NULL VARCHAR2(40) IMAGE ORDSYS.ORDIMAGE THUMB ORDSYS.ORDIMAGE
Both the IMAGE and THUMB columns are specified as the ORDImage data type that has a number of useful attributes that can be used for retrieval:
MIME type
Height
Width
ContentLength
FileFormat
CompressionFormat
Within the ORDImage object type there are attributes concerned with the media metadata, for example, file format, MIME type, and compression, which are unlikely on their own to form a basis for attribute-based retrieval.
Often the process of retrieval will be based on searching the metadata using standard SQL and using these results to locate the required data. In these situations Boolean queries are often combined with query-by-example (QBE) methods to provide feedback to narrow the search after the initial Boolean query. In order to carry out a QBE there must be a way to
Before we consider the role of metadata for separate media, it is helpful, particularly when considering design options, to classify metadata and relate this to the three types of information retrieval, as follows:
Content independent (i.e., associated with media, e.g., photographer’s name) so used in attribute-based retrieval.
Content descriptive—used in text-based retrieval.
Content dependent (e.g., features of faces from photographs or video operations in a clip) used in content-based retrieval.
In Table 11.1 we give examples of the classes for the different media to clarify the way content-independent, content-descriptive, and content-dependent features are used.
Table 11.1. Metadata Classes
Metadata Class | Example | Usage |
|---|---|---|
Content independent | Associated with media (e.g., photographer’s name) | Attribute based |
Content descriptive | The speakers and topic discussed | Text based |
Content dependent | Features of color or texture from photographs or video operations in a clip | Content based |
interMedia uses an object-relational approach. This means that the tables we create with interMedia object types will have some columns with composite data types with their own attributes and methods. We can include both the attributes and the methods in SQL query statements. The simplest way we are going to query the media is through the metadata.
In Chapter 3 we created a table, photos with image, and thumb columns with data-type ORDImage. The ORDImage object type has several attributes that we could use to query the database:
MIME type
contentLength
fileFormat
contentFormat
compressionFormat
These attributes would be accessible by normal SQL functions and could be used for retrieval purposes and for summarizing the content of the database.
When we want to query a column with this data type we need to use a correlation variable (or alias) as follows to retrieve the attributes of the object type.
SELECT p.id, p.image.mimetype, p.image.fileformat FROM photos p WHERE p.image.mimetype='image/jpeg';
The style used is columname.object_attribute and this form of attribute specification can appear complex and confusing to the user. We can add substitute headings in SQL to improve usability.
SELECT p.id, SUBSTR(p.image.mimetype,1,15) Mime_type, SUBSTR(p.image.fileformat,1,15) File_format FROM photos p WHERE p.image.mimetype='image/jpeg' ID MIME_TYPE FILE_FORMAT ---- --------------- --------------- 4313 image/jpeg JFIF 4310 image/jpeg JFIF 4311 image/jpeg JFIF
Similarly, for video object type in the video_ord table we would query as follows:
SELECT v.id, v.video.videoDuration, v.video.FrameResolution FROM video_ord v;
However, we cannot use the ORDImage attributes unless these have stored metadata extracted using the setProperties() method illustrated in Chapter 8. This setProperties() method reads the media data to get the values of the object attributes and then stores them in the object attributes. The method sets the properties for each of the attributes of the media data for which values are available (e.g., compression type, MIME type, etc.), and then for video and audio data populates the comments field of the object with a rich set of format and application properties in XML form, provided that the value of the setComments parameter is TRUE.
If we want to query using an object-type method we will have to provide the IN parameters of the method within the SQL statement. Most of the ORDImage methods do not require IN parameters and can be queried as follows:
SELECT P.image.getCompressionFormat(),
P.image.getHeight(),
P.image.getWidth()
FROM photos P
WHERE P.image.getCompressionFormat() = 'JPEG'Or using column expression substitution we would enter:
SELECT p.id id,
p.image.Height height,
p.image.Width width,
p.image.mimeType mimetype
FROM photos pHowever, Oracle recommends using the object-type methods rather than the attributes directly in case there were changes to the internal representation of the ORDImage object type so the first style of query is preferable.
Exactly the same style is used for audio examples:
SELECT t.id id,
t.audio.getMimeType() mimetype
FROM t.audio_ord t;And for a video query (MimeType Attribute)
SELECT t.id id,
t. video.getMimeType() mimetype
FROM video_ord t;Content-based retrieval (CBR) methods, which we look at in detail in Chapter 13, have been developed to try to overcome some of the limitations and problems of TBR. The idea is that the important details can be extracted from the media by automatic methods, which will be more efficient for data capture and more reliable for retrieval. Retrieval of images by manually assigned keywords is definitely not content-based retrieval. Another more fundamental difference in the three methods is that attribute-based retrieval can be used to address straight forward queries, while the text-based and content-based retrieval address more complex queries known as the semantic gap. CBR implies the ability to search based on the user’s association and impression of an image (e.g., sunset at sea). In order for this to be possible a method must exist that defines the semantic quality and similarity of images. This could mean setting up a mapping from the user’s ideas and concepts to both the raw image data and the image characteristics.
All three methods summarized in Figure 11.4 involve the use of metadata but the nature of the metadata and its source changes with each method.
Techniques Employed | |
|---|---|
Attribute based | Fixed set of structured attributes, with indexing based on B*Trees and inverted files. |
Text based | Text descriptions and structured fields. Indexing by full text (scanning, inversion files). |
Content based | Content features automatically extracted, image feature extraction. |
In this section we will review how PL/SQL can be used to provide most of the functionality required for a rich-media application. In other words, to perform
Media download
Media upload
Image processing
Image queries
To download media from the database, we need to be able to write to the image directory object:
GRANT WRITE ON DIRECTORY PHOTO_DIR TO SCOTT;
Then we can compile and execute the following procedure to download an image file into the PHOTO_DIR directory.
CREATE or replace PROCEDURE img_export AS img ORDSYS.ORDIMAGE; ctx raw(64) :=null; BEGIN SELECT image INTO img FROM photos WHERE id =4311; img.export(ctx, 'FILE', 'PHOTO_DIR', '4311.jpg'), END;To upload media into the database means importing media data from the file system into the database tablespaces. The following series of steps are typical:
Insert a new row into the table, creating new objects by using the init method of the interMedia object type.
Call the import method of the interMedia object to bring the data from the file system into the database.
Call the setProperties() method of the interMedia object to determine and populate the attributes of the object.
Update the table so that the interMedia object in the table contains the attribute values extracted in the previous step.
The PL/SQL code that implements these steps for inserting a new row in the
Family_mediatable. The Family_media table isCREATE TABLE Family_media ( id NUMBER, description VARCHAR2(30), family_photo ORDImage, family_audio ORDAudio, family_video ORDVideo)This is shown in the following procedure example:
CREATE or replace PROCEDURE family_import AS img ORDImage; aud ORDAudio; vid ORDVideo; ctx RAW(64) := NULL; BEGIN -- Insert a new row into the Family_media table DELETE FROM Family_media WHERE id = 3003; INSERT INTO Family_media (id, Family_photo, Family_audio, Family_video) VALUES (3003, ORDImage.init('FILE', 'PHOTO_DIR', 'CATS.jpg'), ORDAudio.init('FILE', 'PHOTO_DIR', 'track01.cda'), ORDVideo.init('FILE', 'PHOTO_DIR', 'my_family.mov')) RETURNING Family_photo, Family_audio, Family_video INTO img, aud, vid; -- Bring the media into the database and populate the -- attributes img.import(ctx); -- ORDImage.import also calls ORDImage.setProperties; aud.import(ctx); aud.setProperties(ctx); vid.import(ctx); vid.setProperties(ctx); --Update the table with the properties we have extracted UPDATE Family_media SET Family_photo = img, Family_audio = aud, Family_video = vid WHERE id = 3003; COMMIT; END;When handling exceptions, PL/SQL uses exception blocks. For example, in PL/SQL, the exception may appear as:
BEGIN <some program logic> EXCEPTION WHEN OTHERS THEN <some exception logic END;One of the main problems will be dealing with unrecognized formats when we want to carry out image-processing methods.
To carry out image processing, we know from Chapter 8 that if a procedure tries to use the setProperties() method with an uploaded image (it reads the image data to get the values of the object attributes so it can store them in the appropriate attributes) and the image format is not recognized, then the
setProperties()method will fail. To catch this exception and work around this potential problem, the application uses the following exception block.BEGIN new_image.setProperties(); EXCEPTION WHEN OTHERS THEN new_image.contentLength := upload_size; new_image.mimeType := upload_mime_type; END;In the next example, this exception handler sets the MIME type and length of the image. The browser sets a MIME-type header when the file is uploaded. The application can then read this header to set the ORDImage attribute.
If your program tries to process an image in cases when the image format is unknown, then the processCopy() method will always fail. To work around this potential problem, the application uses the following exception block:
BEGIN new_image.processCopy( 'maxScale=50,50', new_thumb); EXCEPTION WHEN OTHERS THEN new_thumb.deleteContent(); new_thumb.contentLength := 0; END;The thumbnail image cannot be created so this exception handler deletes the content of the thumbnail image and sets its length to zero.
To query media data, there are two main approaches - to search for keywords to identify an image or search through the image metadata attributes. The following procedure uses a cursor to fetch keyword data that can then be matched with keywords suppied by the user
CREATE OR REPLACE PROCEDURE query_img_cursor IS CURSOR photos_id IS SELECT id, location, description FROM NEW_PHOTOS; v_id NEW_PHOTOS.ID%TYPE; v_loc NEW_PHOTOS.location%TYPE; v_desc NEW_PHOTOS.description%TYPE; BEGIN OPEN photos_id; --fetch first row, this moves the cursor to the next row FETCH photos_id INTO v_id, v_loc, v_desc; --fetch second row IF photos_id%FOUND THEN FETCH photos_id INTO v_id, v_loc, v_desc; --the cursor has moved again ELSE CLOSE photos_id; END IF; CLOSE photos_id; END;
SQL developers who are familiar with the database can develop Web applications that exclusively use Oracle Application Server and Oracle Database using the PL/SQL development environment. With the PL/SQL development environment, developers can rapidly develop PL/SQL-based Web applications. Developing Web applications using PL/SQL consists of developing one or more PL/SQL packages consisting of sets of stored procedures that interact with Web browsers through HTTP. Stored procedures can be executed in several ways:
From a hypertext link that calls a stored procedure when it is selected.
By clicking Submit on an HTML form to denote the completion of a task, such as filling out a form supplied on the HTML page.
By passing parameters to a stored procedure based on user choices from a list.
Information in the stored procedure, such as tagged HTML text, is displayed in the Web browser as a Web page. These dynamic Web pages are generated by the database and are based on the database contents and the input parameters passed into the stored procedure. Using PL/SQL stored procedures is especially efficient and powerful for generating dynamic Web page content. interMedia can be employed when media data such as images, audio, video, or combinations of all three are to be uploaded into and retrieved from database tables using the interMedia object types and their respective sets of methods.
There are several ways of generating HTML output from PL/SQL:
Using function calls to generate each HTML tag for output using the PL/SQL Web Toolkit package that is part of Oracle Application Server and Oracle Database.
Embedding PL/SQL code in Web pages (PL/SQL server pages).
In Figure 11.3 the following process is shown:
A user visits a Web page, follows a hypertext link, or submits data in a form, which causes the browser to send an HTTP request for a URL to an HTTP server.
The HTTP server invokes a stored procedure on an Oracle database according to the data encoded in the URL. The data in the URL takes the form of parameters to be passed to the stored procedure.
The stored procedure calls subprograms in the PL/SQL Web Toolkit.
The subprograms pass the dynamically generated page to the Web server.
The PL/SQL gateway enables a Web browser to invoke a PL/SQL stored procedure through an HTTP listener. The gateway is a platform on which PL/SQL users develop and deploy PL/SQL Web applications. There are several implementations. There is a module called mod_plsql that is a plug-in of Oracle HTTP Server and enables Web browsers to invoke PL/SQL stored procedures. The PL/SQL Web Toolkit is basically a set of packages that enables you to use stored procedures called by mod_plsql at runtime. Oracle HTTP Server is a component of both Oracle Application Server and Oracle Database.
Media upload procedures must first perform an SQL INSERT operation to insert a row of data in the media table so that it also initializes any instances of the respective interMedia object columns with an empty BLOB. Next, an SQL SELECT FOR UPDATE operation selects the object columns for update. Finally, an SQL UPDATE operation updates the media objects in their respective columns. This is illustrated in the next procedure, which also outputs the values of the attributes.
CREATE or replace PROCEDURE img_upload
AS
img ORDSYS.ORDIMAGE;
ctx raw(64) :=null;
BEGIN
INSERT INTO photos(id, image)
VALUES (4313,ORDSYS.ORDIMAGE.INIT());
--image column is initalized to empty BLOB
SELECT p.image INTO img FROM photos p
WHERE p.id = 4313 FOR UPDATE;
img.importFrom(ctx,'file','PHOTO_DIR','cats.jpg'),
UPDATE photos SET image=img WHERE id=4313;
img.setProperties();
DBMS_OUTPUT.PUT_LINE('Image file format: ' ||
img.getFileformat);
DBMS_OUTPUT.PUT_LINE('Image Compression: ' ||
img.getCompressionFormat);
DBMS_OUTPUT.PUT_LINE('Image Content format: ' ||
img.getContentformat);
DBMS_OUTPUT.PUT_LINE('Image Mime Type: ' ||
img.getMimeType);
DBMS_OUTPUT.PUT_LINE('Image size: ' ||
img.getContentLength);
DBMS_OUTPUT.PUT_LINE('Image Height: ' || img.getHeight);
DBMS_OUTPUT.PUT_LINE('Image Width: ' || img.getWidth);
UPDATE photos p set p.image = img
WHERE p.id = 4313;
COMMIT;
END;interMedia methods are called to do the following:
Initialize the object columns with an empty BLOB.
Set attributes to indicate media data is stored internally in a BLOB.
Get values of the object attributes and store them in the object attributes.
When exceptions occur, determine the length of the BLOB content and its MIME type.
Media retrieval operations involve
Retrieving the object from the database into a local object.
Checking the cache validity of the object based on its updated time versus that of the HTTP header time.
Determining where the media object is located: in the database, in a BFILE, or at a URL location; then, getting the media and downloading it for display on an HTML page.
11.7.2 Optimizing PL/SQL
There are some routine BLOB operations that will improve the performance of PL/SQL. It is recommended to initialize a persistent LOB to EMPTY rather than NULL. Doing so enables you to obtain a locator for the LOB instance without populating the LOB with data. To set a persistent LOB to EMPTY, use the SQL function EMPTY_BLOB() or EMPTY_CLOB() in the INSERT statement:
INSERT INTO a_table VALUES (EMPTY_BLOB());
As an alternative, you can use the RETURNING clause to obtain the LOB locator in one operation rather than calling a subsequent SELECT statement.
CREATE or replace PROCEDURE img_upload
AS
Lob_loc BLOB;
BEGIN
INSERT INTO photo_BLOB(IMAGE) VALUES (EMPTY_BLOB())
RETURNING IMAGE INTO Lob_loc;
/* Now use the locator Lob_loc to populate the BLOB with
data */
END;In Chapter 3 we reviewed the basic use of OUT and INOUT parameters in PL/SQL procedures and the rules that operate them. If you use OUT or INOUT parameters, PL/SQL adds some performance overhead to ensure correct behavior in case of exceptions (assigning a value to the OUT parameter, then exiting the subprogram because of an unhandled exception, so that the OUT parameter keeps its original value).
If your program does not depend on OUT parameters keeping their values in such situations, you can add the NOCOPY keyword to the parameter declarations, so the parameters are declared OUT NOCOPY or INOUT NOCOPY.
This technique can give significant speedup if you are passing back large amounts of data in OUT parameters, such as collections, big VARCHAR2 values, or LOBS.
This technique also applies to member methods of object types. If these methods modify attributes of the object type, all the attributes are copied when the method ends. To avoid this overhead, you can explicitly declare the first parameter of the member method as SELF INOUT NOCOPY, instead of relying on PL/SQL’s implicit declaration SELF INOUT. For information about design considerations for object methods, see Oracle Database Application Developer’s Guide: Object-Relational Features.
Oracle Database uses two engines to run PL/SQL blocks and subprograms. The PL/SQL engine runs procedural statements, while the SQL engine runs SQL statements. During execution, every SQL statement causes a context switch between the two engines, resulting in performance overhead. Performance can be improved substantially by minimizing the number of context switches required to run a particular block or subprogram.
PL/SQL applications are often built around loops, so it is important to optimize the loop itself and the code inside the loop. For example, the FORALL keyword can improve the performance of INSERT, UPDATE, or DELETE statements that reference collection elements. To issue a series of DML statements, replace loop constructs with FORALL statements.
CREATE or replace PROCEDURE image_loop TYPE Numlist IS VARRAY (100) OF NUMBER; List_Id NUMLIST := NUMLIST(4310,4311,43112,4313); -- BEGIN -- Efficient method, using a bulk bind FORALL i IN Id.FIRST..Id.LAST -- bulk-bind the VARRAY UPDATE photos SET image = img WHERE id= List_Id (i); -- Slower method, running the UPDATE statements within a regular loop FOR i IN Id.FIRST..Id.LAST LOOP UPDATE photos SET image = img WHERE id= List_Id (i); END LOOP; END;
To loop through a result set and store the values, use the BULK COLLECT clause on the query to bring the query results into memory in one operation. If you need to bring a large quantity of data into local PL/SQL variables, rather than looping through a result set one row at a time, you can use the BULK COLLECT clause. When you query only certain columns, you can store all the results for each column in a separate collection variable. When you query all the columns of a table, you can store the entire result set in a collection of records, which makes it convenient to loop through the results and refer to different columns.
If you have to loop through a result set more than once, or issue other queries as you loop through a result set, you can probably enhance the original query to give you exactly the results you want. Some query operators to explore include UNION, INTERSECT, MINUS, and CONNECT BY.
You can also nest one query inside another (known as a subselect) to do the filtering and sorting in multiple stages. For example, instead of calling a PL/SQL function in the inner WHERE clause (which might call the function once for each row of the table), you can filter the result set to a small set of rows in the inner query, and call the function in the outer query.
PL/SQL stops evaluating a logical expression as soon as the result can be determined. This functionality is known as short-circuit evaluation. Short-circuit evaluation applies to IF statements, CASE statements, and CASE expressions in PL/SQL.
When evaluating multiple conditions separated by AND or OR, put the least expensive ones first. For example, check the values of PL/SQL variables before testing function return values, because PL/SQL might be able to skip calling the functions.
You might need to allocate large VARCHAR2 variables when you are not sure how big an expression result will be. You can actually conserve memory by declaring VARCHAR2 variables with large sizes, such as 32,000, rather than estimating just a little on the high side, such as by specifying 256 or 1,000. PL/SQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. Specify a size of more than 4,000 characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed.
As you develop larger and larger PL/SQL applications, it becomes more difficult to isolate performance problems. PL/SQL provides a Profiler API to profile runtime behavior and to help you identify performance bottlenecks. PL/SQL also provides a Trace API for tracing the execution of programs on the server. You can use Trace to trace the execution by subprogram or exception.
Once the LOB data is stored in the database, a modified strategy must be used to improve the performance of retrieving and updating the LOB data. The following guidelines should be considered:
Use the CACHE option on LOBS if the same LOB data is to be accessed frequently by other users since then Oracle places LOB pages in the buffer cache for faster access whereas NOCACHE specifies that LOB values are not brought into the buffer cache.
Increase the number of buffers if you are going to use the CACHE option.
Have enough buffers to hold the object. Using a small number of buffers for large objects is not good. Set the DB_CACHE_SIZE parameter to a value that you know will hold the object. DB_CACHE_SIZE is the size in bytes of the cache of standard blocks. To help you specify an optimal cache value, you can use the dynamic DB_CACHE_ADVICE parameter with statistics gathering enabled to predict behavior with different cache sizes through the V$DB_CACHE_ADVICE performance view.
Ensure that your redo log files are much larger than they usually are; otherwise, you may be waiting for log switches, especially if you are making many updates to your LOB data.
Ensure that you use a larger page size (DB_BLOCK_SIZE), especially if the majority of the data in the database is LOB data.
The DB_CACHE_SIZE parameter specifies the size of the DEFAULT buffer pool for buffers in bytes. This value is the database buffer value that is displayed when we issue an SQL SHOW SGA statement. Since the value of the DB_BLOCK_SIZE parameter cannot be changed without recreating the database, we can change the value of the DB_CACHE_SIZE parameter instead to control the size of the database buffer cache by using the ALTER SYSTEM...SET clause statement. The DB_CACHE_SIZE parameter is dynamic.
It is possible to embed two different kinds of scripts in HTML pages: client-side scripts and server-side scripts. Client-side scripts are returned as part of the HTML page and are run in the browser. These are mainly used for navigation between HTML pages or data validation carried out on the client side. Server-side scripts, while also embedded in the HTML pages, are run on the server side. They fetch and manipulate data from the database and produce HTML content that is returned as part of the page.
PL/SQL server pages (PSP) are server-side Web pages (in HTML or XML) with embedded PL/SQL scripts marked with special tags. Previously, developers would produce dynamic Web pages by usually writing CGI programs in C or Perl to fetch data and produce the entire Web page within the same program. The development and maintenance of such dynamic pages are costly and time consuming.
Scripting fulfills the demand for rapid development of dynamic Web pages. Small scripts can be embedded in HTML pages without changing their basic HTML identity. The scripts contain the logic to produce the dynamic portions of HTML pages and are run when the pages are requested by the users.
The separation of HTML content from application logic makes script pages easier to develop, debug, and maintain. Figure 11.2 shows how a browser calls the PSP-stored procedure. A PL/SQL gateway receives HTTP requests from an HTTP client, invokes a PL/SQL stored procedure as specified in the URL, and returns the HTTP output to the client. A PL/SQL server page is processed by a PSP compiler, which compiles the page into a PL/SQL stored procedure. When the procedure is run by the gateway, it generates the Web page with dynamic content. PSP is built on one of two existing PL/SQL gateways: PL/SQL cartridge or Oracle Application Server.
Developing a PSP application has the following requirements:
Oracle Database (version 8.1.6 or higher)
Oracle PL/SQL gateway running
Web server, such as Apache, up and running and correctly configured to send requests to the Oracle Database server
The Web server must be configured to accept client PSP requests as a URL. The Web server forwards these requests to the Oracle Database server and returns server output to the browser. Here is a sample HTML file with PSP to search for matching documents.
<%@ plsql procedure="search_html" %>
<%@ plsql parameter="query" default="null" %>
<%! v_results numeric := 0; %>
<html>
<head>
<title>search_html Search </title>
</head>
<body>
<%
If query is null Then
%>
<center>
<form method=post action="search_html">
<b>Search for: </b>
<input type=text name="query" size=30>
<input type=submit value=Search>
</center>
<hr>
<%
Else
%>
<p>
<%!
color varchar2(6) := 'ffffff';
%>
<center>
<form method=post action="search_html">
<b>Search for:</b>
<input type=text name="query" size=30
value="<%= query%>">
<input type=submit value=Search>
</form>
</center>
<hr>
<p>
<%
-- select statement
for doc in (
select /*+ FIRST_ROWS */ rowid, tk, title,
score(1) scr
from search_table
where contains(text, query,1) >0
order by score(1) desc
)
loop
v_results := v_results + 1;
if v_results = 1 then
%>
<center>
<table border="0">
<tr bgcolor="#6699CC">
<th>Score</th>
<th>Title</th>
</tr>
<% end if; %>
<tr bgcolor="#<%= color %>">
<td> <%= doc.scr %>% </td>
<td> <%= doc.title %>
[<a href="search_htmlServices.showHTMLDoc?p_id=
<%= doc.tk %>">HTML</a>]
[<a href="search_htmlServices.showDoc?p_id=
<%= doc.tk %>&p_query =
<%=query%>">Highlight</a>]
</td>
</tr>
<%
if (color = 'ffffff') then
color := 'eeeeee';
else
color := 'ffffff';
end if;
end loop;
%>
</table>
</center>
<%
end if;
%>
</body></html>Text-based retrieval (TBR) methods work by adding annotation, usually brief descriptions combined with some structured data. The disadvantage of text-based systems is that they are very difficult in practice. The following example gives the definition of a table to include audio media.
CREATE TABLE audio_example ( id CHAR(8) PRIMARY KEY, description VARCHAR2(40), speakers VARCHAR2(30), speach_text CLOB, audio_data ORDSYS.ORDAUDIO)
In fact ORDSYS.ORDAUDIO contains two attributes already, description (VARCHAR2(4000)) and comments (CLOB), which could be used for ABR and TBR, respectively. We can use TBR by exploiting the DBMS_LOB routines with the CLOB data or use Oracle Text capabilities.
The annotations usually have to be added manually. However, there is a major issue with reliability. Would a typical user be able to come up with the same description of the audio example in the table above as the person doing the annotation? In addition, for images this is a resource-intensive process that is difficult and costly to achieve. Some images, such as abstract art and computer graphics, are very difficult to describe in text. For successful retrieval the user who searches this material must be able to use the same descriptive terms as the annotator. However people may give quite different descriptions of the same image. In addition, if large amounts of descriptive text are involved the retrieval methods become more complicated.