Accessing Files within PL/SQL with UTL_FILEThe 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'
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
Table 21.2. UTL_FILE Exceptions
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_JOBThe 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).
Table 21.3. The Main Procedures in the DBMS_JOB Package
Submitting JobsAn 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
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_XPLANThe 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.
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 ToolkitOracle 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 TierClients 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 TierPL/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 GatewayThe 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).
Generate HTML from Web Toolikit without Oracle Application ServeR 10 GThe 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 PackagesTable 21.5 is a list of all the Web Toolkit packages and a brief description of each. Overview HTP ProceduresThe 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
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
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. HTFFor 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 ProceduresOracle 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
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 ProceduresOracle 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 DataHTML 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
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 ProceduresThe 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.
Server-Side HTML Image MapSIn 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
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 MapsThere are two steps involved in creating a client side image map in HTML.
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 MapThe 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/SQLThe method to generate this in PL/SQL, using the supplied Oracle packages within the Oracle Web Toolkit, is to take the following steps:
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:
Table 21.10. Parameters for the htp.formImage Procedure
|