0194-0196

Previous Table of Contents Next

Page 194

the dispatcher of the instance it should use. The dispatcher then passes the request to the instance of the PL/SQL cartridge. The PL/SQL cartridge executes the stored procedure, matching parameters received to the stored procedure parameters by name . It then returns a response to the dispatcher. In the context of the example, the response might be an HTML page formatted by the stored procedure indicating an incident number for the problem submitted.

Multivalued parameters require different handling. For example, you might use checkboxes on an HTML form to represent the possible values of a particular database column, where the selected checkboxes are used in the WHERE clause of a SQL query. To illustrate the handling of multivalued columns , assume that an HTML form used to request product information displays a checkbox for each product.

The HTML might contain a section similar to the following segment:

 <INPUT TYPE=CHECKBOX NAME="product" value="1">Acme Widgets<BR> <INPUT TYPE=CHECKBOX NAME="product" value="2">Acme Gadgets<BR> <INPUT TYPE=CHECKBOX NAME="product" value="3">Acme Thingamajigs<BR> 

Because each checkbox has the same name, the parameter values are mapped to a single parameter in the PL/SQL procedure that processes the request. You can use a special datatype defined in the owa_util package, ident_arr, as the PL/SQL parameter type:

 CREATE PROCEDURE request_info (product IN OWA_UTIL.IDENT_ARR ...(more parms)) IS     counter NUMBER;     req_id   NUMBER; BEGIN    counter := 1;    -- get a unique ID for the requester and insert name and address information    FOR counter IN 1 .. product.COUNT LOOP     -- insert the combination of product id and requestor id into another table        INSERT INTO prod_info_req VALUES (req_id, product(counter));     END LOOP;     COMMIT; 

Note that an ident_arr has a COUNT property and that its individual elements can be accessed by index. In the context of this example, the procedure might format an HTML confirmation page after committing the transaction.

The PL/SQL cartridge is an example of the request-response programming model. This means that it cannot maintain state information for a client from one execution to the next, which precludes transactions that span multiple executions. However, if the Transaction Service included in the advanced version of Web Application Server is configured for the specified PL/SQL Agent, this shortcoming can be circumvented.

For the sake of example, assume that a Web-based application needs to support a transaction consisting of any number of inserts and updates. You can accomplish this through the use of a PL/SQL Agent configured to use the Transaction Service by following a simple protocol. To implement a transaction spanning multiple executions of the PL/SQL cartridge, you must

Page 195

implement at least four procedures: a procedure to mark the beginning of the transaction, a procedure to apply the statements that make up the transaction, a procedure to commit the transaction, and a procedure to roll back the transaction. You can implement more than one procedure to apply the Data Manipulation Language (DML) statements that make up the transaction, provided that the order in which the individual procedures are called is known in advance. However, some applications might not have strict requirements on the ordering of the individual operations. Based on the assumption that the sample transaction allows inserts and updates to be applied in any order, you can implement this in one of two ways. You can implement a single procedure with a transaction code parameter to indicate whether the insert or the update is applied ( assuming that the number and types of arguments are the same). If the number of arguments is different, you can use an overloaded procedure to implement the different components of the transaction. Using the transaction code implementation, the procedures used to implement the transaction might be encapsulated in a package:

 CREATE PACKAGE web_trans1 AS     PROCEDURE wt1_begin(usersname IN VARCHAR2, password IN VARCHAR2);     PROCEDURE wt1_apply(tcode IN NUMBER, custno IN NUMBER, orderno IN NUMBER, itemno IN NUMBER, quantity IN NUMBER);     PROCEDURE wt1_rollback;     PROCEDURE wt1_commit; END web_trans1; 

You must call the web_trans1.wt1_begin procedure first, possibly from a form that requests user identification. It might locate the customer in the database, assign an order number, and dynamically generate an HTML form. The form might contain a list of items in a table with hyperlinks defined for column identifiers (to support editing), hidden form elements to store the current transaction code, the customer ID and order number, and an insert button to add new items. The insert and update procedures, applied through the same interface, would format new HTML pages with the modified list of items. The full details of implementing the dynamic HTML interface are not relevant to understanding how to use the Transaction Service. The key points to keep in mind when using the Transaction Service are as follows :

  • Each transaction starts when the begin procedure is called (as defined in the Begin Transaction URL parameter of the PL/SQL Agent configuration).
  • Only procedures defined in the Transactional Boundaries section of the PL/SQL Agent configuration are considered part of the transaction.
  • If more than one procedure is used to execute the transaction, the individual requests must follow a specific order.
  • The transaction is committed when the commit procedure is called (as defined in the Commit Transaction URL parameter of the PL/SQL Agent).
  • The transaction is rolled back when the rollback procedure is called (as defined in the Rollback Transaction URL parameter of the PL/SQL Agent).

Page 196

Assuming that the virtual path of the PL/SQL Agent used to execute the transaction is web_users, and that the transaction code is 1 for INSERTs and 2 for UPDATEs, navigation of the browser interface might result in a series of URL requests such as these:

 http://www.acme.com:80/web_users/plsql/web_trans.wt1_begin?USERSNAME=scott& PASSWORD=tiger http://www.acme.com:80/web_users/plsql /web_trans.wt1_apply?TCODE=1&CUSTNO=2& ORDERNO=10232&ITEMNO=712&QUANTITY=1 http://www.acme.com:80/web_users/plsql /web_trans.wt1_apply?TCODE=1&CUSTNO=2& ORDERNO=10232&ITEMNO=209&QUANTITY=5 http://www.acme.com:80/web_users/plsql /web_trans.wt1_apply?TCODE=2&CUSTNO=2& ORDERNO=10232&ITEMNO=712&QUANTITY=5 http://www.acme.com:80/web_users/plsql /web_trans.wt1_commit 
NOTE
The full responsibility of transaction control is borne by the Transaction Manager. You should not issue commits and rollbacks from the PL/SQL procedures that participate in transactions managed by the Transaction Service under any circumstances. If a rollback is in the PL/SQL procedure, you should use the redirect_url procedure of the owa_util package to cause the URL of the rollback procedure to be accessed via HTTP. This is the only way that the Transaction Service will know that the transaction is completed. In the context of the previous example, the web_trans1.wt1_apply procedure might have an exception handler such as this one:
 EXCEPTION WHEN OTHERS THEN OWA_UTIL.REDIRECT_     URL("http://www.acme.com:80/web_users/plsql /web_trans.wt1_rollback"); 

This example assumes that web_users is the virtual path of PL/SQL Agent used to execute the transaction.

The PL/SQL cartridge is, in many respects, the centerpiece of the Oracle-supplied cartridge solutions. It can be used for everything from dynamic HTML to database transactions that span multiple executions. The PL/SQL cartridge supplies database connectivity and its full set of features to other cartridges through the intercartridge exchange, and its advantages over CGI scripts or other Web server API extensions should be readily apparent. Multiple configurations are easily managed, providing varying levels of application security or database security through the use of PL/SQL Agents and virtual-path management. Accessing program units stored in the database gives the PL/SQL cartridge the additional advantages of improved performance, ease of maintenance, and the capability to leverage the PL/SQL language to provide full native access to Oracle databases.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

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