A few other points to notice:

•  For numeric data, you need to specify right justification in the tableData tags, something that SQL*Plus and Reports normally do automatically.

•  Arguments for tableData and other HTP procedures are generally VARCHAR2 datatypes. In this case, DONOR_ID and AMOUNT are stored in the database as numeric values. For AMOUNT, this listing did the TO_CHAR conversion in the SELECT statement itself, formatting the data as it extracted it. You could SELECT it as numeric, but you would have to do a TO_CHAR conversion to get the HTP procedure, which in this case is tableData, to accept it.

•  Each record for FULL_NAME is hyperlinked to another stored procedure, called DETAIL_DONORS. DETAIL_DONORS takes a single parameter, DONOR_ID. This parameter is embedded in the hyperlink, with each record’s appropriate DONOR_ID. This way, when the output displays in the client browser, the user can click the name of a particular donor. The hyperlink will pass the appropriate DONOR_ID to the procedure as a parameter and display details on only that donor.

---

Tip:  
If you want to incorporate client-side JavaScript in your application, you have two choices. You can embed the actual JavaScript in the PL/SQL procedures to write the HTML output—in other words, write JavaScript code into the HTML file. Or you can store the JavaScript code in separate files on the server, and use HTML tags to reference the files. This second method is actually easier if you want to debug and modify the JavaScript without having to recompile your packages and procedures.

---

Oracle provides a series of other packages to deal with the special Web-related issues of a PL/SQL Web application. Several are described in Table 27.4.

Stored Procedures and Cookies

Since users who log in to the Web are not necessarily using Oracle schema names (see the discussion on Security), you cannot use the USER pseudo-column to identify your users. Furthermore, HTTP is stateless; login “sessions” do not exist on the Web as they do on a client-server system (see the earlier discussion on Cookies). So how can you identify who a user is when a procedure is invoked?

One approach is to build some extra functionality into the login process. When users log in, create a cookie—some lengthy number you create internally will work (you could use a SEQUENCE). Using the OWA_COOKIE package, you download this number to the user’s client, the number is stored on their machine and you keep a copy of it in an internally stored Web session table. You can also use the OWA_SEC and OWA_UTIL packages to confirm the user’s physical IP address at the time of log in, and store that in the Web session table with the cookie number, along with any user specific runtime options relevant to the application.

After that, any procedure that the client requests could include a cookie check (using OWA_COOKIE) to do the following:

•  Get the client’s cookie.

•  Compare the cookie with the internal Web session table.

•  Identify any relevant runtime options or other variables concerning the user’s transaction.

A check to a procedure like this should occur at the beginning of every single stored procedure that needs to know who the user is.

In this fashion, cookies can be used to maintain a record of a user’s transactions across several procedure calls, as in the “shopping cart” application used by many Web stores.