Re: PL/SQL Procedures Get Invalidated

From: Norman Dunbar <norman.dunbar_at_environment-agency.gov.uk>
Date: Mon, 24 Apr 2006 08:29:41 +0100
Message-Id: <s44c8c97.092@environment-agency.gov.uk>

Morning Sam,

Norman Dunbar.
Contract Oracle DBA.
Rivers House, Leeds.

Internal : 7 28 2051
External : 0113 231 2051

>>> "Sam Bootsma" <sbootsma_at_gbrownc.on.ca> 04/21/06 05:43pm >>>
<SNIP>

>> I tried
>> to get our QA or Systems Analyst to manually run the Oracle procedure
>> from Sql*Plus, but I am told "the package can not be run directly from

>> sql*plus since some build in package such as OWA, which is heavily
>> depend on http context, is meant to be called from http
>> request/response."

With full credit to Connor McDonald (Mastering Oracle PL/SQL Practical Solutions - buy it, read it, use it - pages 468 onwards), you need to do the following :

Create a propcedure to initialise the OWA environment. Run it.
Set serveroutput on.
Execute your package that is 'for the web'. Call OWA_UTIL.SHOWPAGE to dump out the HTML.

Here's Connor's OWAInit procedure, hopefully it won't get too badly mangled in the transmission :

CREATE OR REPLACE PROCEDURE OWAInit AS   

  vCGIVarNames owa.vc_arr;
  vCGIVarValues owa.vc_arr;   

BEGIN
  htp.init;
  vCGIVarNames(1) := 'REQUEST_PROTOCOL';   vCGIVarValues(1) := 'HTTP';   

  owa.init_cgi_env(num_params => 1,

                              param_name => vCGIVarNames,
                              param_val  => vCGIVarValues);

END;
/

I've wrapped this up in my own little 'TestPage' procedure as I got fed up typing it all out. Obviously you don't need to run OWAInit each and every time, ditto the DBMS_OUTPUT_ENABLE, but it does no harm :o) :

CREATE OR REPLACE PROCEDURE TestPage(iPage IN VARCHAR2) AS BEGIN
  dbms_output.enable(1000000);
  Web_Utils.owainit;
  EXECUTE IMMEDIATE 'begin '||iPage||'; end;';   owa_util.showpage;
END;
/

And here's an example with most of the page 'verbiage' removed for briefness :

SQL> exec testpage('web.home');

Content-Type: text/html
Content-length: 1925
Content-Type: text/html

<TITLE>Database Booking System (0.01)</TITLE>
<STYLE>

....
....
</STYLE>
<H2>Database Booking System (0.01)</H2>
<HR>
<P> .... </p>
<P> .... </p>
<HR>
<TABLE >
<TR>
<TH COLSPAN="2">DB2S Home Page - Quick Links</TH>
</TR>
<TR>
<TD class=x20>Home</TD>
<TD>Return to the DB2S home page.</TD>
</TR>
<TR>
<TD class=x20><A HREF="web.UserPreferences?iStyle=DEFAULT">User
Preferences</A></TD>
<TD>Set desired preferences for this session.</TD>
</TR>
<TR>
<TD class=x20><A HREF="web.SystemMaintenance">System
Maintenance</A></TD>
<TD>Return to the System Maintenance page.</TD>
</TR>
<TR>
<TD class=x20>Data Maintenance</TD>
<TD>Go to the Data Maintenance page.</TD>
</TR>
<TR>
<TD class=x20>Bookings Maintenance</TD>
<TD>Allows you to list &amp; maintain database bookings.</TD>
</TR>
</TABLE>
<HR>
<P class="footer">

This page is optimised for adherance to the Web standards for HTML 4.0 and
Cascading Style Sheets.
This may cause some problems with appearance if you are using a non-standard
browser such as Internet Explorer. If at all possible, you are advised to use
Mozilla Firefox as this <b>is</b> standards compliant.
<HR>
</BODY>
</HTML>

PL/SQL procedure successfully completed.

This way you can usually see what's going on in a Web Page when all you get from the browser is an error or nothing at all (404 perhaps !)

Cheers,
Norman.

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 24 2006 - 02:29:41 CDT