Re: OAS performance questions

Joseph Babad <joseph_babad_at_my-deja.com> writes:

> Thanks for the reply...
>
> Can I see if I've got this right?
> You're using perl to generate HTML, but calling PL/SQL
> procedures from within the perl. Have I got this
> right? Are you doing this across different "cartridges"?

Sorry for not being very clear about what we did. Let me try to explain a bit more. (PS: this is a proven method, but as always, there may be better or easier ways of doing it, depending on your specific situation).

We have defined some special HTML-like tags (but could be anything), and placeholders for variables, that we use in the HTML files. This makes the HTML files into HTML templates. The most important new tags are :

	<query name="xxxx">.....<norows>....</norows></query>
	<if yyy="zzzz">....<else>....</fi>

In the HTML, placeholders of the form;

        %xxxx.wwww%

can be used, where "xxxx" is the name of a query, or "global" for "global"-variables, and "wwww" is the name of a column from the query (or technically actually a field in a PL/SQL record, as explained below).

Using some perl scripting we translate the HTML-like templates into PL/SQL procedures. Designers can thus handle HTML like files, without worrying about PL/SQL syntax.
The perl scripting does mostly straightforward translations. Normal HTML text becomes just a string printed with htp.print(). The placeholders are just PL/SQL variables within records, if-expressions are just PL/SQL expressions. Query is a little more complicated, but basically it translates to 3 function calls: xxxx_start(), xxxx_nextrow() and xxxx_stop(), which resp. setup a cursor, get the next row from this cursor, and close the cursor, and a loop around the HTML in the <query>....</query> block. You can probably figure something out. These functions could also do additional processing on retieved database rows, e.g. compute something from several columns, or combine results of several queries on the fly, or even not use any database query but e.g. loop over an internal PL/SQL table.

Within a PL/SQL package, we define a record-type for each query's result, and a record variable with the name "xxxx" (whatever). This package also contains the start/nextrow/stop functions for each query in the HTML page, and includes (with _at_) the file with the PL/SQL procedure generated from the HTML file. This way we clearly separate business logic from graphic design: The database developers maintain the PL/SQL package with the SQL query and any special business logic, the web-designers maintain the HTML templates.

> I was thinking along the lines of using things like
> htp functions TOGETHER with SQL queries in a procedure -
> so that you have both the content and SQL together.
> Hence my question about pushing the HTML-generation into the
> database.
> We're looking to solve the problem of having a lot
> of SIMILAR pages with embedded sql and we want to
> a) reduce the amount of new coding to a minimum; and
> b) allow non-experts to write the HTML.( so heavy perl scripting
> is out)
>
> This was why the PL/SQL stored procedures seemed ideal.
>
> In article <Fsn8q9.Lsu_at_west.nl>,
> marco_at_West.NL (Marco Nijdam) wrote:
> > Some things to watch out for:
> >
> > - separate HTML (page layout) from PL/SQL (logic).
> > We're using some conventions and perl scripts
> > (that convert HTML files with some placeholders to
> > PL/SQL procedures) to accomplish this.

Kind regards,

--
-- Marco Nijdam,       marco_at_west.nl
-- West Consulting bv, Bagijnhof 80,  2611 AR  Delft, The Netherlands
--                     P.O. Box 3318, 2601 DH  Delft
-- Tel: +31 15 219 1600, Fax: +31 15 214 7889