Lab 21.1 Make Use of Oracle Supplied Packages


Lab Objectives

After this Lab, you will be able to:

Access Files with UTL_FILE

Schedule Jobs with DBMS_JOB

Generate an Explain Plan with DBMS_XPLAN

Create an HTML Page with the Oracle Web Toolkit


Accessing Files within PL/SQL with UTL_FILE

The UTL_FILE package provides text file input and output capabilities within PL/SQL. Oracle introduced the UTL_FILE package with the database version 7.3. This means that you can either read input from the operating system files or write to operating system files. This could be useful if you have data from another system that you want to load into the database. For instance, if you have logs from a web server that you want to place in your data warehouse, the UTL_FILE package would allow you to read the text file logs and then parse them to load the data in the correct tables and columns in the data warehouse. The package also allows you to write data out to a file. This is useful if you want to produce logs or capture current information about the database and store it in a text file, or extract data into a text file that another application can process.

It is important to note that this is a server side text file access, binary files cannot be read by the UTL_FILE; for that use the DBMS_LOB package. The files that you access must be mapped to a drive on the server. The security on what directories you can access is controlled by a setting in the INIT.ORA file; set the drives that can be accessed with the UTL_FILE_DIR initialization parameter.

FOR EXAMPLE

 
 UTL_FILE_DIR = 'C:\WORKING' 

You can also bypass all server side security and allow ALL files to be accessed with the UTL_FILE package with the following setting:

 
 UTL_FILE_DIR = * 

If you do not have access to the INIT.ORA file on the database server, you can query the Data Dictionary to find the value that has been set in your database with the following SQL:

 
 SELECT name, value FROM   V$SYSTEM_PARAMETER WHERE  name = 'utl_file_dir' 
graphics/error_icon.gif

It is not advisable to allow UTL_FILE access to all files in a production environment. This means that all files, including important files that manage the operation of the database are accessible. This allows developers to write a procedure that corrupts the database.


The method for using the UTL_FILE file package is to open the text file, process the file by writing to the file and getting lines from the file, and close the file. If you do not close the file your operating system will think that the file is in use and will not allow you to write to the file until it is closed. A list of the major functions, procedures, and datatypes in the UTL_FILE packages are listed in Table 21.1. A list of exceptions in this package are listed in Table 21.2.

The following example demonstrates a procedure that writes to a log file the date, time and number of users that are currently logged on. In the exercises you will create a more involved procedure that makes use of UTL_FILE.

FOR EXAMPLE

 
 -- ch21_1a.sql CREATE OR REPLACE PROCEDURE LOG_USER_COUNT    (PI_DIRECTORY  IN VARCHAR2,     PI_FILE_NAME  IN VARCHAR2) AS    V_File_handle  UTL_FILE.FILE_TYPE;    V_user_count   number; BEGIN    SELECT count(*)    INTO   V_user_count    FROM   v$session    WHERE  username is not null;    V_File_handle  :=       UTL_FILE.FOPEN(PI_DIRECTORY, PI_FILE_NAME, 'A');    UTL_FILE.NEW_LINE(V_File_handle);    UTL_FILE.PUT_LINE(V_File_handle  , '---- User log -----');    UTL_FILE.NEW_LINE(V_File_handle);    UTL_FILE.PUT_LINE(V_File_handle  , 'on '          TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI'));    UTL_FILE.PUT_LINE(V_File_handle  ,          'Number of users logged on: ' V_user_count);    UTL_FILE.PUT_LINE(V_File_handle  , '---- End log -----');    UTL_FILE.NEW_LINE(V_File_handle);    UTL_FILE.FCLOSE(V_File_handle); EXCEPTION    WHEN UTL_FILE.INVALID_FILENAME THEN       DBMS_OUTPUT.PUT_LINE('File is invalid');    WHEN UTL_FILE.WRITE_ERROR THEN       DBMS_OUTPUT.PUT_LINE('Oracle is not able to write to file'); END; 
Table 21.1. UTL_FILE Functions, Procedures and Data Types

Function, Procedure or Datatype

Description

FILE_TYPE

Datatype for a file handle.

IS_OPEN

This function has a return data type of BOOLEAN, it returns true if the file is open and false if the file is closed.

FOPEN

This function is used to open a file for input or output, the function return value is the form handle in the FILE_TYPE data type.

The modes to open a file are:

'R' Read Mode

'W' Write Mode

'A' Append Mode

FCLOSE

This procedure closes a file that is open.

FCLOSE_ALL

This procedure closes all files that are open in the current session. (It is a good idea to place this procedure in your exception to make sure you don't leave any files locked.)

FFLUSH

This procedure takes all the data buffered in memory and writes it to a file.

GET_LINE

This procedure gets one line of text from the opened file and places the text into the OUT parameter of the procedure.

PUT_LINE

This procedure writes a string of text from the IN parameter to the opened file. Afterwards a line terminator is placed into the text file.

PUT

This procedure is the same as PUT_LINE but no line terminator is placed in the open file.

PUTF

This procedure puts formatted text into the opened file.

NEW_LINE

This procedure inserts a new line terminator in the opened text file.

Table 21.2. UTL_FILE Exceptions

Exception Name

Description

INVALID_PATH

The file location or the filename is not valid.

INVALID_MODE

This exception is for FOPEN only, the mode for the OPEN_MODE parameter is not valid.

INVALID_FILEHANDLE

The file handle is not valid.

INVALID_OPERATION

The file could not be opened or operated on in the manner requested .

READ_ERROR

There is an operating system error that prevented the read file from occurring.

WRITE_ERROR

There is an operating system error that prevented the write file operation from occurring.

INTERNAL_ERROR

An unspecified PL/SQL error occurred.

The LOG_USER_COUNT procedure can be executed to log the number of users into the file c:\working\user.log.

FOR EXAMPLE

 
 SQL> exec LOG_USER_COUNT('D:\WORKING', 'USER.LOG'); PL/SQL procedure successfully completed.  USER.LOG contents:  ---- User log ----- on 07/05/03 13:09 Number of users logged on: 1 ---- End log ----- 

Job Scheduling with DBMS_JOB

The Oracle Supplied Package DBMS_JOB allows you to schedule the execution of a PL/SQL procedure. It was first introduced in PL/SQL version 2.2. DBMS_JOB is an Oracle PL/SQL package provided to users. A job is submitted to a job queue and runs at the specified time. The user can also input a parameter that specifies how often the job should run. A job can consist of any PL/SQL code. The DBMS_JOB package has procedures for submitting jobs for scheduled execution, executing a job that has been submitted outside of its schedule, changing the execution parameters of a previously submitted job, suspending a job, and finally, removing jobs from the schedule (Table 21.3). The primary reason you would want to use this feature would be to run a batch program during off times when there are low numbers of users, or to maintain a log.

The Job queue is governed by the SNP process that runs in the background. This process is used to implement data snapshots as well as job queues. If the process fails the database will attempt to restart the process. The database initialization parameter (set in the INIT.ORA file and viewable in the DBA view V$SYSTEM_PARAMETER) JOB_QUEUE_PROCESSES determines how many processes can start. It must be set to a number greater than 0 (note the default is 0).

graphics/error_icon.gif

SNP background processes will not execute jobs if the system has been started in restricted mode. It is expected behavior for jobs not to be executed while the database is in restricted mode. However, you can use the ALTER SYSTEM command to turn this behavior on and off as follows :

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM DISABLE RESTRICTED SESSION;


Table 21.3. The Main Procedures in the DBMS_JOB Package

Procedure Name

Description

SUBMIT

This procedure enters a PL/SQL procedure as a job into the job queue.

REMOVE

This procedure removes a previously submitted PL/SQL procedure from the job queue.

CHANGE

This procedure changes the parameters that have been set for a previously submitted job (description, next run time or interval).

BROKEN

This procedure disables a job in the job queue.

INTERVAL

This procedure is used to alter the interval set for an existing job in the job queue.

NEXT_DATE

This procedure is used to change the next time an existing job is set to run.

RUN

This procedure forces the run of a job in the job queue regardless of the schedule for the job.

Submitting Jobs

An important first step when submitting jobs to the queue is to be sure that your PL/SQL procedure is valid and executes the way you expect it to run. Prior to submitting a PL/SQL procedure, make sure you have thoroughly tested the functionality. Job submission assumes your job is valid. The SUBMIT procedure will take four in parameters and return one out parameter (Table 21.4), The out parameter is the job number of the job you have submitted. This job number will also be visible in the DBA_JOBS view.

The following example will submit the LOG_USER_COUNT procedure (created with ch21_1a.sql) to run every 6 hours.

FOR EXAMPLE

 
 DECLARE    V_JOB_NO NUMBER;  BEGIN   DBMS_JOB.SUBMIT(JOB       => v_job_no,                    WHAT      => 'LOG_USER_COUNT                              (''D:\WORKING'',                              ''USER.LOG'');',                    NEXT_DATE => SYSDATE,                    INTERVAL  => 'SYSDATE + 1/4 ');   Commit;   DBMS_OUTPUT.PUT_LINE(v_job_no);  END; 

In order to see the job in the queue, query the DBA_JOB view.

Table 21.4. Parameters for the DBMS_JOB.SUBMIT Procedure

Parameter Name

Mode

Description

JOB

OUT

The unique number that identifies the job in the job queue

WHAT

IN

The PL/SQL procedure and parameters that will execute as part of this job

NEXT_DATE

IN

The next execution date for the job

INTERVAL

IN

The calculation to compute the next date of the job (This can make use of SYSDATE and any date function.)

NO_PARSE

IN DEFAULT: FALSE

A Boolean indicator as to whether to run the job at job submission

FOR EXAMPLE

 
 SELECT JOB, NEXT_DATE, NEXT_SEC, BROKEN, WHAT FROM   DBA_JOBS; JOB NEXT_DATE NEXT_SEC B WHAT ---- --------- -------- - --------------------------------------------    1 05-JUL-03 16:56:30 N LOG_USER_COUNT('D:\WORKING', 'USER.LOG'); 

In order to force job number 1 to run or to change, use the RUN or CHANGE procedure; to remove job number 1 from the job queue, use the REMOVE procedure.

FOR EXAMPLE

 
 -- execute job number 1 exec dbms_job.run(1); -- remove job number 1 from the job queue exec dbms_job.remove(1); -- change job #1 to run immediately and then every hour of -- the day exec DBMS_JOB.CHANGE(1, null, SYSDATE, 'SYSDATE + 1/24 '); 

Once the job has failed, it will be marked as broken in the job queue. Broken jobs do not run. You can also force a job to be flagged as broken. You may want to do this if you have entered all the parameters correctly yet you don't want the job to run its normal cycle while you work on altering one of its dependencies. You can then comment the job again by forcing the broken flag off.

FOR EXAMPLE

 
 -- set job 1 to be broken exec dbms_job.BROKEN(1, TRUE); -- set job 1 not to be broken exec dbms_job.BROKEN(1, FALSE); 

When jobs are running you will see their activity in the view DBA_JOBS_RUNNING; once the run has completed it will no longer be visible in this view.

Explain Plan with DBMS_XPLAN

The DBMS_XPLAN package became available in Oracle version 9.2. This package helps to display the execution plan of an SQL statement that is displayed as the output of the explain plan command. This package displays the output in an easier manner than was possible in prior versions of Oracle. The SQL execution plan and runtime statistics are stored in the V$SQL_PLAN, V$SQL and PLAN_STATISTICS are displayed with the DBMS_XPLAN package. The SQL command for creating an explain plan takes this information and populates the PLAN_TABLE. There is a great deal to know about query optimization in order to make use of an explain plan.

graphics/trick_icon.gif

For details on the SQL optimization and on how to use the results in an explain plan, see Chapter 16: "SQL Optimization" in Oracle SQL by Example by Alice Rishert (available December 2003).


The DBMS_XPLAN depends on a table called the PLAN_TABLE. This table holds the results from running an explain plan on a SELECT statement. The DDL to create the PLAN_TABLE is as follows:

 
 -- ch21_1a.sql create table PLAN_TABLE (statement_id       varchar2(30),         timestamp          date,         remarks            varchar2(80),         operation          varchar2(30),         options            varchar2(255),         object_node        varchar2(128),         object_owner       varchar2(30),         object_name        varchar2(30),         object_instance    numeric,         object_type        varchar2(30),         optimizer          varchar2(255),         search_columns     number,         id                 numeric,         parent_id          numeric,         position           numeric,         cost               numeric,         cardinality        numeric,         bytes              numeric,         other_tag          varchar2(255),         partition_start    varchar2(255),         partition_stop     varchar2(255),         partition_id       numeric,         other              long,         distribution       varchar2(30),         cpu_cost           numeric,         io_cost            numeric,         temp_space         numeric,         access_predicates  varchar2(4000),         filter_predicates  varchar2(4000),         projection         varchar2(4000),         time               numeric); 

By default, if several plans in the plan table match the statement_id parameter passed to the display table function (default value is NULL ), only the plan corresponding to the last EXPLAIN PLAN command is displayed. Hence, there is no need to purge the plan table after each EXPLAIN PLAN . However, you should purge the plan table regularly (for example, by using the TRUNCATE TABLE command) to ensure good performance in the execution of the DISPLAY table function.

In prior versions of Oracle there were a number of options available. You could use the SQL*Plus command SET AUTOTRACE TRACE EXPLAIN ON to generate an immediate explain plan.

FOR EXAMPLE

 
 SQL> SET AUTOTRACE TRACE EXPLAIN ON             1  SELECT s.course_no,             2             c.description,             3             i.first_name,             4             i.last_name,             5             s.section_no,             6             TO_CHAR(s.start_date_time, 'Mon-DD-YYYY HH:MIAM'),             7             s.location             8      FROM section s,             9           course c,            10           instructor i            11      WHERE s.course_no    = c.course_no            12*     AND   s.instructor_id= i.instructor_id Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=78 Bytes=4368)    1    0   HASH JOIN (Cost=9 Card=78 Bytes=4368)    2    1     HASH JOIN (Cost=6 Card=78 Bytes=2574)    3    2       TABLE ACCESS (FULL) OF 'INSTRUCTOR' (Cost=3 Card=10 Bytes=140)    4    2       TABLE ACCESS (FULL) OF 'SECTION' (Cost=3 Card=78 Bytes=1482)    5    1     TABLE ACCESS (FULL) OF 'COURSE' (Cost=3 Card=30 Bytes=690) 

You can also generate an explain plan that would be stored in the PLAN_TABLE and then query the results of an explain plan.

FOR EXAMPLE

 
 SQL> explain plan for   2  SELECT s.course_no,   3             c.description,   4             i.first_name,   5             i.last_name,   6             s.section_no,   7             TO_CHAR(s.start_date_time,'Mon-DD-YYYY HH:MIAM'),   8             s.location   9      FROM section s,  10           course c,  11           instructor i  12      WHERE s.course_no    = c.course_no  13      AND   s.instructor_id= i.instructor_id; Explained. -- ch21_1b.sql select rtrim (lpad  (' ', 2*level)                   rtrim (operation)      ' '                  rtrim (options)        ' '                  object_name              ' '                  partition_start          ' '                  partition_stop           ' '                  to_char (partition_id)) the_query_plan    from plan_table    connect by prior id = parent_id    start with id = 0; THE_QUERY_PLAN -----------------------------------------   SELECT STATEMENT     HASH JOIN       HASH JOIN         TABLE ACCESS FULL INSTRUCTOR         TABLE ACCESS FULL SECTION       TABLE ACCESS FULL COURSE 

To make use of the DBMS_XPLAN procedure use the SELECT * FROM TABLE(DBMS_XPLAN>DISPLAY) command to generate the explain plan.

FOR EXAMPLE

 
 SQL> explain plan for   2  SELECT s.course_no,   3             c.description,   4             i.first_name,   5             i.last_name,   6             s.section_no,   7             TO_CHAR(s.start_date_time,'Mon-DD-YYYY HH:MIAM'),   8             s.location   9      FROM section s,  10           course c,  11           instructor i  12      WHERE s.course_no    = c.course_no  13      AND   s.instructor_id= i.instructor_id; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ----------------------------------------------------------------------------------  Id   Operation            Name        Rows   Bytes  Cost (%CPU) Time      ----------------------------------------------------------------------------------    0  SELECT STATEMENT                    78   4368      9  (34) 00:00:01  *  1   HASH JOIN                          78   4368      9  (34) 00:00:01  *  2    HASH JOIN                         78   2574      6  (34) 00:00:01     3     TABLE ACCESS FULL INSTRUCTOR     10    140      3  (34) 00:00:01     4     TABLE ACCESS FULL SECTION        78   1482      3  (34) 00:00:01     5    TABLE ACCESS FULL  COURSE         30    690      3  (34) 00:00:01  ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("S"."COURSE_NO"="C"."COURSE_NO")    2 - access("S"."INSTRUCTOR_ID"="I"."INSTRUCTOR_ID") 17 rows selected. 

Creating Web Pages with the Oracle Web Toolkit

Oracle Application Server 10 g integrates many technologies required to build and deliver an e-business Web site. Oracle Application Server 10 g generates dynamic Web content from PL/SQL procedures and delivers it to a client's Web browser. Oracle Application Server 10 g provides the middleware component of the Oracle Internet Platform and delivers and manages applicat0..ions and data requested by client browsers. The two other components of the Oracle Internet Platform are the Oracle Database 10 g and the Oracle Internet Developer Suite.

In June of 2000 Oracle released a revamped version of their Application Server called Oracle 9 i Application Server. The earlier version had less features and was called the Oracle (Web) Application Server [OAS]. The first release of OAS came out in 1995. The last production version of the OAS was released as version 4.0.8.2 in 1999. OAS was de-supported by Oracle in October 2002 because the new Oracle 9 i Application Server had become the standard. The basic functionality of OAS and the current version of the Oracle Application Server 10 g are similar but the back end architecture and configuration is considerably different. Oracle Application Server 10 g has a much larger array of technologies and languages it can support. You can generate web pages using the PL/SQL Web Toolkit with the OAS but you cannot make use of PL/SQL Server Pages (PSPs).

In Oracle's multi- tier architecture, Oracle Application Server 10 g is the middleware. It incorporates both a web server and an application server. Oracle Application Server 10 g resides between the client and the back-end database, moving application logic from the client. It is the central, middle tier in shared enterprise applications, providing such services as security, message brokering, database connectivity, transaction management, and process isolation.

Oracle Application Server 10 g enables users to deploy applications on the web. Web browsers are "thin" clients that do not need any additional software installation because they are accessing the middle tier via HTTP protocol. The only thing the user needs is a URL (Uniform Resource Locator) to launch the application. A server tier houses the original database so that transaction processing can be optimized on the database. This multi-tiered model offers great savings in administration and maintenance costs when deploying applications.

The HTTP entry point to Oracle Application Server 10 g is the Oracle HTTP Server powered by the Apache Web server. Oracle Application Server 10 g functions both as a simple web server and as an application server. The function of a web server is to translate a URL into a filename on the server and send that file back to the client's web browser over the Internet or an intranet. The function of an application server is to run a program or a component and to generate dynamic content. This dynamic content results in an HTML file being sent back to the client's browser. The output is the result of running a program or a script.

The Oracle HTTP Server functions as an HTTP listener and request dispatcher. Based on the Apache Server, the Oracle HTTP Server is mostly C code that runs on top of the operating system. The Oracle HTTP Server receives HTTP requests from clients and is able to serve static files from the file system. It routes those requests that are not static to other services through modules (for example, mod_plsql). These modules, often referred to as simply mods , are plug-ins to the HTTP Server. A plug-in is a program that extends the functionality of another program, and could be considered a subprogram. The mods are plug-ins that offer native services (e.g. mod_ssl which handles a secure socket layer) or serve as a dispatcher for requests requiring external processes (e.g. mod_jserv which dispatches requests to the Apache JServ). In addition to the compiled Apache mods provided with Oracle HTTP Server, Oracle has enhanced several of the standard mods and has added Oracle-specific mods such as mod_plsql.

The server is able to determine to which module to hand the request based on the URL. The first section of the URL is the name of the server and the next section is the name of the module. For example, a request for mod_plsql will have a URL beginning as follows: http://ServerName/pls/The PLS portion indicates to the Oracle HTTP Server that this is a request for the module mod_plsql.

The Oracle Application Server 10 g Communication Services are responsible for handling requests from the different clients. The Oracle HTTP Server may directly process a portion of the client requests while other requests may be routed to other components of Oracle Application Server 10 g for processing. Oracle Application Server 10 g can be used to support wireless technologies as well, although this book focuses predominantly on the HTTP services of Oracle Application Server 10 g .

Oracle Application Server 10 g provides multiple features and capabilities that are commonly supplied via separate products. An example of a recent impressive addition to the array of components is Oracle Application Server 10 g Unified Messaging, which gives access to e-mail, voice mail, and fax messages from any device, including computers, telephones, personal digital assistants and pagers . Oracle Application Server 10 g is under constant development and you will see many additional services being added and modified in the coming years .

The Client Tier

Clients access PL/SQL Web Applications through a browser using the Web protocol HTTP. Oracle Application Server 10 g application components generate HTML, which is returned to the browser and displayed as Web pages. Since web browsers behave in a similar manner across platforms and they all read HTML and JavaScript, it does not matter what type of operating system a client's web browser is operating on.

The Database Tier

PL/SQL Web Applications are developed as PL/SQL packages and procedures and stored in an Oracle database. You can access database tables through these packages and present the data as dynamic information in your generated Web pages. First introduced with the Oracle Application Server available with Oracle 8 i , Oracle Application Server 10 g provides a collection of PL/SQL packages called the PL/SQL Web Toolkit. These packages are also stored in the database and are used in web-based application packages to generate Web page components and other related functionality.

The Application Server Tier: The PL/SQL Gateway

The PL/SQL Gateway enables you to call PL/SQL programs from a web browser. The PL/SQL programs run on the server and return HTML to the browser. Application Server 10 g acts as the intermediary between the database and the browser.

Oracle HTTP Server Modules (MODS)

In addition to the compiled Apache modules (referred to here as simply mods ) provided with Oracle HTTP Server, which support current Internet application technologies to deliver dynamic Web pages, Oracle has enhanced several of the standard Apache mods and has added Oracle-specific mods. For more information, refer to http://www.apache.org/docs/mod/index.html. The mod that makes use of the Oracle Web Toolkit is the MOD_PLSQL. This module is an HTTP Server plug-in that dispatches requests for PL/SQL and Java stored procedures to an Oracle database. MOD_PLSQL is the most efficient SQL interface for generating HTML. The HTTP Server identifies the request as belonging to this module based on the URL from the client HTTP requests that are identified are handed from the HTTP Server to mod_plsql. These requests are then mapped to database stored procedures. The module maintains database connections specified by database access descriptors (DAD).

graphics/trick_icon.gif

For information on how to configure Oracle 9 i AS, instruction in HTML and JavaScript, and detailed instructions on how to use the Oracle Web Toolkit (with hundreds of pages of examples), see Oracle Web Application Programming for PL/SQL Developers by Susan Boardman, Melanie Caffrey, Solomon Morse, and Benjamin Rosenzweig.


Generate HTML from Web Toolikit without Oracle Application ServeR 10 G

The Oracle Web Toolkit Packages are intended to generate HTML pages over an Internet or intranet with Oracle Application Server 10 g acting as the Web server. In testing mode you can generate the HTML as text files using SQL*Plus. For the purposes of this book the exercises will be done in the testing mode; this way you do not have to address all the set up issues involved with Oracle Application Server 10 g .

Web Toolkit Packages

Table 21.5 is a list of all the Web Toolkit packages and a brief description of each.

Overview HTP Procedures

The HTP package is the principal package used to generate HTML. The P or PRN procedure generates HTML in much the same manner as the DBMS_OUTPUT.PUT_LINE procedure takes its IN parameter and generates display in SQL*Plus. All text in the IN parameter of HTP.P will transform into HTML. Additionally there are many other procedures to generate more complex HTML structures.

Table 21.6 is a list of some of the commonly used HTP procedures and output. For a comprehensive list of HTP procedures please check Oracle's online documentation.

A simple web page can be generated by making use of the procedure in the HTP package.

Table 21.5. Web Toolkit Packages

Package Name

Description

HTP

Generates HTML through procedures

HTF

Generates HTML through functions

OWA_CACHE

Caches Web pages for improved performance using the PL/SQL Gateway cache

OWA_COOKIE

Sends cookies, retrieve cookies

OWA_IMAGE

Creates an image map

OWA_OPT_LOCK

Handles optimistic locking of data

OWA_PATTERN

Searches for/replace values in text strings, "Pattern Matching"

OWA_SEC

Security subprograms

OWA_TEXT

Other types of string manipulation

OWA_UTIL

Retrieves environment variables ; redirects users to another site; other utilities like printing query results directly in a table

FOR EXAMPLE

 
 CREATE OR REPLACE PROCEDURE my_first_page   AS BEGIN    htp.htmlOpen;    htp.headOpen;    htp.title('My First Page');    htp.headClose;    htp.bodyOpen;    htp.p('Hello world.<BR>');    htp.bodyClose;    htp.htmlClose; EXCEPTION    WHEN OTHERS THEN    htp.p('An error occurred on this page.               Please try again later.'); END; -- This will generate the following HTML: <HTML> <HEAD> <TITLE>My First Page</TITLE> </HEAD> <BODY> Hello world.<BR> </BODY> </HTML> 
Table 21.6. HTP Procedures

HTP Procedure

Output

htp.p('<P> text goes here </P>');

<P> text goes here </P>

htp.htmlOpen;

<HTML>

htp.headOpen;

<HEAD>

htp.title('My Title');

<TITLE> My Title<TITLE>

htp.headClose;

</HEAD>

htp.bodyOpen;

<BODY>

htp.header(1, 'My Heading');

<H1> My Heading</H1>

htp.anchor('url''Anchor Name') 'Click Here';

<A HREF="url" NAME="Anchor Name"> Click Here</A>

htp.line;

<HR>

htp.bold;

<B></B>

htp.paragraph;

<P>

htp.tableOpen;

<TABLE>

htp.tableCaption;

<CAPTION></CAPTION>

htp.tableRowOpen;

<TR>

htp.tableHeader('Emp ID');

<TH>Emp ID</TH>

htp.tableData('data');

<TD>data</TD>

htp.tableRowClose;

</TR>

htp.tableClose;

</TABLE>

htp.bodyClose;

</BODY>

htp.htmlClose;

</HTML>

htp.script('alert("This is an alert!");','JavaScript');

<SCRIPT LANGUAGE="JavaScript">

alert("This is an alert!");

</SCRIPT>

In testing, the procedure can be executed from SQL*Plus to generate an HTML file by making use of the utility in the OWA_UTIL package. The procedure SHOWPAGE will be used to display the HTML.

FOR EXAMPLE

 
 SQL> set serveroutput on SQL> spool mypage.htm SQL> execute my_first_page; PL/SQL procedure successfully completed. SQL> execute owa_util.showpage; <HTML> <HEAD> <TITLE>My First Page</TITLE> </HEAD> <BODY> Hello world.<BR> </BODY> </HTML> PL/SQL procedure successfully completed. 

Some procedures such as HTP.HEADER take more than one parameter in order to generate varieties of similar HTML codes (multiple levels of headers). Other procedures such as HTP.TABLEDATA enclose the IN parameter with all the HTML codes required for a table row in HTML. The next example shows first the HTML page that needs to be generated from the database (a list of instructor names ), and then the PL/SQL code that is used to generate the web page.

FOR EXAMPLE

 
 <HTML> <HEAD> <TITLE>Instructor List</TITLE> </HEAD> <BODY> <H1>List of Instructors</H1> The time is           11:36 <TABLE  BORDER=1 BORDERCOLOR="teal" CELLPADDING=5> <TR> <TH>First Name</TH> <TH>Last Name</TH> </TR> <TR> <TD>Rick</TD> <TD>Chow</TD> </TR> <TR> <TD>Marilyn</TD> <TD>Frantzen</TD> </TR> <TR> <TD>Fernand</TD> <TD>Hanks</TD> </TR> <TR> <TD>Charles</TD> <TD>Lowry</TD> </TR> <TR> <TD>Anita</TD> <TD>Morris</TD> </TR> <TR> <TD>Gary</TD> <TD>Pertez</TD> </TR> <TR> <TD>Nina</TD> <TD>Schorin</TD> </TR> <TR> <TD>Todd</TD> <TD>Smythe</TD> </TR> <TR> <TD>Irene</TD> <TD>Willig</TD> </TR> <TR> <TD>Tom</TD> <TD>Wojick</TD> </TR> </TABLE> </BODY> </HTML> 

FOR EXAMPLE

 
 CREATE OR REPLACE PROCEDURE instructor_list IS    v_string VARCHAR2(100);    cursor c_instruct is     SELECT first_name, last_name     FROM  instructor     ORDER  by 2;  BEGIN     htp.htmlOpen;     htp.headOpen;     htp.title('Instructor List');     htp.headClose;     HTP.bodyOpen;     htp.header(1,'List of Instructors');     HTP.P('The time is           'to_char(sysdate, 'HH:MI'));     -- Open Table.     htp.tableOpen('BORDER=1 BORDERCOLOR="teal" CELLPADDING=5');     htp.tableRowOpen;     htp.tableHeader('First Name');     htp.tableHeader('Last Name');     htp.tableRowClose;     FOR rec in c_instruct LOOP       htp.tableRowOpen;          htp.tableData(rec.first_name);          htp.tableData(rec.last_name);       htp.tableRowClose;     END LOOP;     htp.tableClose;     htp.bodyClose;     htp.htmlClose;  EXCEPTION     WHEN OTHERS THEN       HTP.P('An error occurred: 'SQLERRM'.  Please try again later.');  END; 
HTP vs. HTF

For every HTP procedure that generates HTML tags, there is a corresponding HTF function with identical parameters. The function versions do not directly generate output in your web page. Instead, they pass their output as return values to the statements that invoked them. Use these functions when you need to nest calls. To learn more about HTF functions just look up the corresponding HTP procedures in your Oracle Software Documentation. They respond in similar ways.

FOR EXAMPLE

 
 htp.tableData (htf.formOpen('pr_update_class')                      htf.formSubmit()htf.formClose); 

will generate:

 
 <TD><FORM ACTION="pr_update_class" METHOD="POST"> <INPUT TYPE="submit" VALUE="Submit"></FORM></TD> 
Web Toolkit Frameset Procedures

Oracle provides procedures specifically for generating framesets in the HTP package.

Table 21.7 is a list of some of the commonly used frame-related procedures and output. For a comprehensive list of HTP procedures please check Oracle's online documentation.

Table 21.7. Additional HTP Procedures for Frames and Framesets

HTP Procedure

HTML Output

htp.frame('instructors_left_nav', 'instructors_left');

<FRAME src="instructors_left_nav" NAME="instructors_left">

htp.frame('instructors_left_nav', 'instructors_left',

<FRAME src="instructors_left_nav" NAME="instructors_left"

'0',

MARGINWIDTH="0"

'0',

MARGINHEIGHT="0"

'AUTO',

SCROLLING="AUTO"

'Y');

NORESIZE>

htp.framesetOpen(NULL, '125,*');

<FRAMESET COLS="125, *">

htp.framesetOpen('*,65%', NULL);

<FRAMESET ROWS="*,65%">

htp.framesetOpen('*,65%');

<FRAMESET ROWS="*,65%">

htp.framesetClose;

</FRAMESET>

htp.noframesOpen;

<NOFRAMES>

htp.noframesClose;

</NOFRAMES>

These frame-related Web Toolkit procedures and the HTP procedures covered in Chapter 10, "Web Toolkit I: HTML and JavaScript with PL/SQL," can be used to rewrite the above procedure, instructors_frame:

Web Toolkit Form Procedures

Oracle has supplied a number of procedures for creating form elements. You can use HTP.P with the HTML as you see in the above example or you can use the HTP procedures listed in Table 21.8. The resulting HTML will be the same and the performance will not be affected by which one you choose. As with the frameset procedures listed above, it is a matter of style which you use.

HTML Forms as a Container for Sending Data

HTML Forms are containers for collecting data. The most common tag used in forms, <INPUT>, points to the purpose of form elements: to collect user input and send it off for processing. As described in Chapter 5, "Introduction to HTML: Basic Tags, Tables, Frames," of the companion book Oracle Web Application Programming for PL/SQL Developers by Susan Boardman, Melanie Caffrey, Solomon Morse, and Benjamin Rosenzweig, the HTML form's ACTION attribute indicates where the form data will be sent, and therefore how it will be acted upon. Without a value for the ACTION attribute, a form will do nothing. Similarly, a completed paper job application for an employment agency will accomplish nothing sitting on one's own desk; it must be sent to the agency, who can act upon the data collected in the form. The data collected in an HTML form needs a destination in order for meaningful action to take place. It is important to consider where form data should be sent, and what the consequences will be.

Table 21.8. Additional HTP Procedures for Forms and Form Elements

HTP Procedure

Output

htp.formOpen('show_zipcode');

<FORM ACTION="show_zipcode" METHOD="POST">

htp.formOpen('show_zipcode','GET', 'main_window',null,'NAME="my_form"');

<FORM ACTION="show_zipcode" METHOD="GET" TARGET="main_window" NAME="my_form">

htp.formText('p_name','20');

<INPUT TYPE="text" NAME="p_name" SIZE="20">

htp.formHidden('p_id','101');

<INPUT TYPE="hidden" NAME="p_id" VALUE="101">

htp.formCheckbox('cname', 'cvalue');

<INPUT TYPE="checkbox" NAME="cname" VALUE="cvalue">

htp.formCheckbox('cname', 'cvalue', 'CHECKED');

<INPUT TYPE="checkbox" NAME="cname" VALUE="cvalue" CHECKED>

htp.formRadio('p_salutation','Mr.');

htp.p('Mr.');

<INPUT TYPE="radio" NAME="p_salutation" VALUE="Mr.">

Mr.

htp.formRadio('p_salutation','Mrs.', 'CHECKED');

htp.p('Mrs.');

<INPUT TYPE="radio" NAME="p_salutation" VALUE="Mrs." CHECKED>

Mrs.

htp.formSelectOpen('p_salary','Select a Salutation:','1');

Select a Salutation:<SELECT NAME="p_salary" SIZE="1">

htp.formSelectOption('Less than 5000', cattributes => 'VALUE="low"');

<OPTION VALUE="low">Less than 5000

htp.formSelectOption('5001 to 20000', cattributes => 'VALUE="medium" SELECTED');

<OPTION VALUE="medium" SELECTED>5001 to 20000

htp.FormSelectOption('Greater than 20000', cattributes => 'VALUE="high"');

<OPTION VALUE="high">Greater than 20000

htp.formSelectClose;

</SELECT>

htp.FormSubmit(null, 'Save', 'cattributes');

<INPUT TYPE="submit" VALUE="Save" cattributes>

htp.formReset('Reset the Form', 'cattributes');

<INPUT TYPE="reset" VALUE="Reset the Form" cattributes>

htp.FormClose;

</FORM>

The values that are collected in HTML form elements must be passed to a program that can handle them. This could be a CGI ( Common Gateway Interface ) script, Perl script, ASP, or JSP. In the example used here, where all HTML files are being generated by PL/SQL stored procedures by means of Oracle Application Server 10 G , it is another PL/SQL procedure that is the ACTION of the HTML form and receives the form's data. PL/SQL can read these incoming values and use them to update a database or to help build the next screen the user sees.

The reason why it is so important to name your HTML form elements, is that only named form elements are sent to the form handler procedure. If an HTML form element is not given a name, then it will not be sent to the form handler.

The HTML form handler procedure must have an IN parameter that corresponds to each named form element. These IN parameters must have exactly the same names as the form elements. If a form element is named p_first_name, then the form handler procedure must have an IN parameter called p_first_name. The IN parameters must have datatypes that correspond to the type of data being passed in.

Web Toolkit Image Procedures

The Oracle Web Toolkit has a number of procedures to handle HTML image tags. Images that have clickable areas with hyperlinks are handled with HTML image maps. The Oracle Web Toolkit has procedures to handle both Server Side HTML Image maps and Client Side HTML Image maps.

graphics/trick_icon.gif

For information on how to handle HTML Images (with extensive examples and exercises) see Chapter 13 of Oracle Web Application Programming for PL/SQL Developers by Susan Boardman, Melanie Caffrey, Solomon Morse, and Benjamin Rosenzweig.


Server-Side HTML Image MapS

In a server-side HTML image map the image displayed on the client (the HTML file) is a form input of the type IMAGE. This means that when the user clicks on the image the form is submitted.The x and y coordinates where the user clicked are received as IN parameters by the form handler procedure. Note that you do not need a submit button for this type of form. The <INPUT> tag with TYPE="image" is the only required input element in the form. This input type creates an image field on which the user can click and cause the form to be submitted immediately. The coordinates of the selected point are measured in pixels and returned (along with other contents of the form) in two named value pairs. The x coordinate is submitted under the name of the field with ".x" appended, and the y coordinate with ".y" appended. Any VALUE attribute is ignored. The image input HTML syntax is as follows:

 
 <INPUT TYPE="image" NAME="p_image" src="/images/picture1.jpg"> 

The type here is "image". The name is required as this will be the name of the parameter that is being sent to the action of the form.

There are a number of elements of the OWA_IMAGE package for generating this HTML. The example above can be generated by the use of the Oracle-supplied HTP.formImage procedure. The syntax for this procedure is as follows:

 
 htp.formImage (cname in varchar2                 csrc in varchar2                 calign in varchar2 DEFAULT NULL                 cattributes in varchar2 DEFAULT NULL); 

The parameters for this procedure are detailed in Table 21.9. Here is an example (only the first two parameters are passed in here):

 
 htp.formImage('v_image','/images/location.gif'); 

generates the following HTML:

 
 <INPUT TYPE="image" NAME="p_image" src="/images/location.gif"> 

An HTML form needs a form handler procedure that can be used as the action of the form. This procedure must be able to accept what is sent by the image-input item. The IN parameter for the image supplied to the form handler procedure must have the same name as the image input, and a datatype of OWA_IMAGE.POINT, which is supplied by Oracle. This data type contains both the X and Y values of a coordinate, so there will only be one IN parameter for the image.

There are two more functions in the OWA_IMAGE package that can extract the X or Y coordinate from an OWA_IMAGE.POINT data type. These are the functions, OWA_IMAGE.GET_X, for the X coordinate, and OWA_IMAGE.GET_Y, for the Y coordinate.

Table 21.9. Parameters for the htp.formImage Procedure

Parameter

Usage

CNAME

The VALUE for the NAME attribute, the name of the parameter to be submitted

CSRC

The value for the SRC attribute, which specifies the image file

CALIGN

The value for the ALIGN attribute, this is optional

CATTRIBUTES

Any other attributes to be included as-is in the tag

Using the OWA_IMAGE.GET_X and OWA_IMAGE.GET_Y functions, the form handler procedure will be able to access the coordinates the user clicked, and be able to work with these numbers.

In the following example, when the user clicks anywhere on the image, a new page displays, showing the X and Y coordinates where the user clicked. There are two procedures in the sample package below called find_coords. The first one is display_image. It makes use of the procedure htp.formImage to create the image input. The next procedure, show_cords, is the action of the display_image procedure. This means that the IN parameter named for the image must be of OWA_IMAGE.POINT data type. The show_coords procedure uses the functions OWA_IMAGE.GET_X and OWA_IMAGE.GET_Y to determine the X and Y coordinates, and then displays them on a new Web page.

FOR EXAMPLE

 
 CREATE OR REPLACE Package find_coords AS   PROCEDURE  display_image;   PROCEDURE  show_coords (p_image   IN  owa_image.Point); END find_coords; / CREATE OR REPLACE PACKAGE BODY find_coords    AS PROCEDURE display_image    IS BEGIN    htp.headOpen;    htp.title('Display the Image');    htp.headClose;    htp.p('<BODY bgcolor="khaki">');    htp.header(1,'Find the Coordinates');    htp.p('Click on the image and you will see the x,y           coordinates on the next page');    htp.formOpen('find_coords.show_coords');    htp.formImage('p_image','/images/location.gif');    htp.formClose;    htp.p('</BODY>');    htp.p('</HTML>'); EXCEPTION    WHEN OTHERS THEN      htp.p('An error occurred: 'SQLERRM'.  Please try again later.'); END display_image; Procedure show_coords   (p_image IN owa_image.Point) IS   x_in NUMBER(4) := owa_image.Get_X(v_image);   y_in NUMBER(4) := owa_image.Get_Y(v_image); BEGIN    htp.headOpen;    htp.title('Find Your coordinates');    htp.headClose;    htp.p('<BODY bgcolor="khaki">');    htp.header(1,'These are the Coordinates you clicked on:');    htp.p('<P>           You have selected 'x_in'  as your X coordinate </p>');    htp.p('<P>           You have selected 'Y_in'  as your Y coordinate </p>');    htp.p('</BODY>');    htp.p('</HTML>'); EXCEPTION    WHEN OTHERS THEN      htp.p('An error occurred: 'SQLERRM'.  Please try again later.'); END ; END find_coords; 

The display_image procedure creates an HTML file as follows:

 
 <HTML> <HEAD> <TITLE>Display the Image</TITLE> </HEAD> <BODY bgcolor="khaki"> <H1>Find the Coordinates</H1> Click on the image and you will see the x,y coordinates on the next page <FORM ACTION="find_coords.show_coords" METHOD="POST"> <INPUT TYPE="image" NAME="p_image" src="/images/location.gif"> </BODY> </HTML> 

Client-Side Image Maps

There are two steps involved in creating a client side image map in HTML.

  1. Set up an image map

  2. Show an image and use the image map

You can think of the initial image map as being similar to a JavaScript function that is defined in the beginning of an HTML file, then used later.

Create the Image Map

The first tag in an image map is the <MAP> tag. This tag must have a name attribute or it cannot be referenced later on in the file. The image map contains a number of areas that are each a hyperlink. Each area uses an <AREA> tag. Each <AREA> tag must have a SHAPE attribute to indicate the shape of the area and an HREF attribute to indicate the hyperlink to where clicking on the map will direct the user. The various types of shapes have different sets of coordinates used to define the shape. The coordinates used to define an image map's shape are supplied in the value for the COORDS attribute. The following HTML would create an image map for a square with four inner squares, each one hyperlinking to a different web page. The shape that is called "default" indicates the hyperlink for any area of the image that is not covered by one of the shapes . The coordinates used here are meaningless and just used to complete the example.

 
 <map name="MyMap"> <area shape="rect" href="first.htm" coords="20,20,70,60"> <area shape="rect" href="second.htm" coords="90,20,140,60"> <area shape="rect" href="third.htm" coords="20,80,70,120"> <area shape="rect" href="fourth.htm" coords="90,80,140,120"> <area shape="default" href="default.htm"> </map> 
Image Maps in PL/SQL

The method to generate this in PL/SQL, using the supplied Oracle packages within the Oracle Web Toolkit, is to take the following steps:

  1. Name the map

  2. Divide it into clickable areas

  3. Specify the Image to be used

Using PL/SQL, you make use of the HTP.mapOpen and HTP.mapClose procedures to open and close the map definition. You use the HTP.AREA procedure to define the areas within the map. Then, when you display the image, you make use of the HTP.IMG2 procedure to create the HTML <IMG> tag with the corresponding imagemap (Table 21.10).

The IS MAP that is generated in the IMG tag indicates that this image is going to use an image map, the USEMAP= determines the name of the image map to be used. The image map must have been previously defined in the HTML for the page or the image map will not function.

At run-time:

  1. Click on the image

  2. The browser processes the coordinates

Table 21.10. Parameters for the htp.formImage Procedure

Procedure

Resulting HTML

Purpose

htp.mapOpen('map1');

<MAP NAME="map1" >

Name the map

htp.area ('0,0,50,50', rect, 'www.prenhall.com');

<AREA SHAPE="rect" COORDS="0,0,50,50" HREF="www.prenhall.com">

Specify the regions

htp.mapClose;

</MAP>

Close the map

htp.img2('MyImage.gif', cismap=>'1', cusemap=>'#map1');

<IMG src="MyImage.gif" ISMAP USEMAP="#map1">

Specify the image and link to the region



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net