Don't bother. The link Tom provided is, as with many Oracle products and services, no longer supported. Even this review submittal doesn't work.

I thought what he was referring to was the built in pl/sql toolkit that is included with the database itself which also has an apache listener? I know when I navigate to the db machine via a URL and port, I can get to an admin page and configure a DAD from there. I use this to test all of your sample code for mod_plsql against a DAD setup for SCOTT/TIGER.

The URL you provided for OSE does work.

The architecture you are asking about is as follows:
Web Browser > Apache (comes with 8i and up) > mod_plsql (comes built into Apache from Oracle) > db > PL/SQL Web Toolkit (comes with db). Yes, you can write your own custom PL/SQL packages to spit out dynamic html, but it's tedious and not very productive given the alternatives...

HTML DB is a new feature of the 10g database and is supported back to 9iR2. It uses the exact architecture above, but is infinately more productive. It takes care of the details, like session state management, report pagination, security, etc., so you can focus on the impoert aspects of your app, like business logic, user interface, and navigation.

Check it out at </code> http://htmldb.oracle.com, <code>I doubt you'll be disappointed. You can also search this site for "html db" and see what you come up with. Speaking of this site, did I mention it's written in HTML DB?

The process of creating a DAD with mod_plsql is simple. You can either do it through the web interface at yourserver.com/pls/ or modify the $ORACLE_HOME/Apache/modplsql/conf/wdbsvr.app and copy one of the example DADs.

AskTom uses Apacha and mod_plsql. If you look at the URL above: .../pls/ tells Apache to use mod_plsql, "ask" is the DAD, f?p=4950:8 is HTML DB syntax (f is package with a procedure called p, 4950 is the application number, and 8 is the page number, just in case you were interested)

> AskTom...."ask" is the DAD, f?p=4950:8 is HTML DB syntax
>(f is package with a procedure called p, 4950 is the
> application number, and 8 is the
> page number, just in case you were interested)


Heh Tyler, maybe you're a bit too deep into HTML DB to realize this...but I am still in the puddle here :)

There's no "f" package...f is a procedure, p is the first parameter...

>>The architecture you are asking about is as follows:
>>Web Browser > Apache (comes with 8i and up) > mod_plsql
>>(comes built into Apache
>>from Oracle) > db > PL/SQL Web Toolkit (comes with db).
>>Yes, you can write your
>>own custom PL/SQL packages to spit out dynamic html, but
>>it's tedious and not very productive given the
>> alternatives...

Is it possible to find anywhere how to set up some simple working example, if I have 8.1.7 (and packages are installed there ) ?
[ I mean with no iAS ]

Thanks

Actually, setup is simple.
I find documentation a bit confusing.
In database you have all you need to do it, but docs are not clear in that.
Let's say for htp I found somewhere in database docs "if you want to know more do desc htp" or something like this.
You schould consult also ias docs for different things and there you are never sure if it still counts for database only...

Hi,

We are running our application using OWS/OAS.

Users will give URL "</code> http://hostname-tr0/at/por.html" <code>

Now we are gettinh error like page not found.

If we give "ping hostname-tr0"
it is failing saying this interface is disabled.

Now new interface will be like "hostname-eee".

So in Orace Web server which files we need to edit to change
the entry from "hostname-tr0" to "hostname-eee".


Thanks & Regards,
Atul

Tom,

Since Oracle do not normally provide a function where there is no benefit to it, are there any benefits to using the webserver embedded in the database or would you always recommend using the Apache webserver [ias] - this is licensed as part of the database licence isn't it?

Secondly, do you know of any good documentation regarding configuring the Oracle HTTP server. Specifically
1. Security - removing unused modules like mod_ose,mod_perl
2. SSL - how to set up mod_plsql for ssl
3. Virtual hosting multiple mod_plsql sites [I understand you run multiple html db apps on this machine - how do you get round the fact that each instance want the dad /ask/?]

Thanks for your help

Thanks Tom,

Glad to know that an Oracle licence is all I need to run mod_plsql, not iAS as well.

Thanks for the link to the docs, what always confuses me is that those are the iAS docs, and I am never sure whether the HTTP server that comes with iAS is the same as the one which comes with the database.

As for the /ask/? question, what I meant is that html db uses the dad /ask/ for its applications. If you have multiple sites on one box using html_db you would have

www.app1.com/ask/f?
www.app2.com/ask/f?

running off the same http server which I didn't think was possible [dad's have to be unique]. Do you use virtual hosting to do this, or are all the applications stored in the same instance of html db and then just partitioned using VPD?

Tom,
We are currently setting up 10G for our company's new database. We plan on having it run entirely in HTML DB, however we want to use the database authentication, not the HTML DB authentication. I read through the document you have posted above about DAD set up. It is for Oracle 8i. Will this work for 10g? On our 10G database I cannot find the $ORACLE_HOME/Apache/modplsql/conf/wdbsvr.app file, it does not exist in that directory. Is there a 10g document to setup DAD? I have not been able to find one in my searches.

Thanks,
Scott

create or replace procedure prog1 as
v_tbl varchar2(50);
sql_stmt varchar2(100);
v_xm varchar2(50) ;
type cur_type is ref cursor;
c cur_type;
begin
htp.htmlopen;
htp.p('<p>');
H htp.p('<p><div align="centre">');
htp.bold('<H2>list of Tablespaces </H2>');
htp.p('<p>');
htp.p('<table border=2 cellpadding=2 cellspacing=0');
htp.tablerowopen;
htp.tableheader('Tablespace Name');
htp.tableheader('Extent Management');
htp.tablerowclose;
sql_stmt := 'select tablespace_name,extent_management from dba_tablespaces where rownum < 10 ';
open c for sql_stmt;
loop
fetch c into v_tbl,v_xm ;
exit when c%notfound;
htp.tablerowopen;
htp.p('<td align="left">'||v_tbl||'</td>');
htp.p('<td align="right">'||v_xm||'</td>');
htp.tablerowclose;
end loop;
close c;
end;


The above pl/sql code gives me a list of tablespaces .
I now wish to click on the tablespace and find the details about the datafiles.

How can I do this ?

Thanks ,

Thanks for your answer .

Can I safely conclude that in the coming years pl/sql toolkit will be happily replaced by Htmldb .

I am inquisitive to know that in plsql toolkit can you do without dynamic sqls .

Thanks again ,

When I posted the following :

htp.p('<p>');
H htp.p('<p><div align="centre">');
htp.bold('<H2>list of Tablespaces </H2>');
htp.p('<p>');
htp.p('<table border=2 cellpadding=2 cellspacing=0');
htp.tablerowopen;
htp.tableheader('Tablespace Name');
htp.tableheader('Extent Management');
htp.tablerowclose;
sql_stmt := 'select tablespace_name,extent_management from dba_tablespaces
where rownum < 10 ';
open c for sql_stmt;
loop
fetch c into v_tbl,v_xm ;
exit when c%notfound;
htp.tablerowopen;
htp.p('<td align="left">'||v_tbl||'</td>');
htp.p('<td align="right">'||v_xm||'</td>');
htp.tablerowclose;
end loop;
close c;
end;


You replied --

"That is the worst way to code here -- dynamic sql where you don't need it. "

I would appreciate if you could tell me another way of doing it without using Dynamic Sql .


Thanks again ,

CREATE OR REPLACE PACKAGE BODY export_excel AS
function valid_date(p_start_dt date,p_end_dt date) return varchar is
Flag Varchar(1);
begin

if p_start_dt > p_end_dt or p_start_dt > trunc(sysdate) then
Flag:='N';
else
Flag :='Y';
end if;
RETURN FLAG;

EXCEPTION
WHEN NO_DATA_FOUND THEN
htp.p('Data not found !');
WHEN OTHERS THEN
htp.p('Error unknown. SQLCODE:'||SQLCODE||',SQLERRM:'||SQLERRM);

END Valid_date;


FUNCTION isdate (p_date_str varchar) RETURN varchar2

IS

temp_date varchar2(20);
str_len integer;

BEGIN

str_len := length(p_date_str);
IF (str_len = 8) THEN
temp_date := to_date(p_date_str, 'MMDDYYYY');
RETURN 'Y';
ELSE
RETURN p_date_str ||' is a invalid date input !';
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
htp.p('Data not found !');
WHEN OTHERS THEN
htp.p('Error unknown. SQLCODE:'||SQLCODE||',SQLERRM:'||SQLERRM);

END isdate;


PROCEDURE ns_test2 IS

bgdate date := add_months(sysdate,-1);

BEGIN




htp.p('<style type="text/css">');
htp.p('td {font-family: arial, helvetica; font-size: small;}');
htp.p('th {font-family: arial, helvetica; font-size: small;}');
htp.p('</style>');
htp.p('</head>');
htp.p('<body topmargin="3" leftmargin="3" marginheight="3" marginwidth="3"><center>');
htp.p('<BR><A>REPORT DOWNLOAD</A>');

htp.p('<br><br>');
htp.p('<table bgcolor="CDCDCD" border=0 width="650" cellpadding=1 cellspacing=0>');
htp.p('<tr><td>');
htp.p('<table bgcolor="FFFFFF" border=0 width="100%" cellpadding=2 cellspacing=0>');

htp.p('<form action="sample_report.xls" name="sample_form" method="get">');

htp.p('<tr><td colspan=3>&nbsp;</td></tr>');
htp.p('<tr><td colspan=3><center>&nbsp;Enter Date Range</td></tr>');
htp.p('<tr><td colspan=3><center>&nbsp; </td></tr>');

htp.p('<tr><td>&nbsp;</td><td>');
htp.p('<font color="000080"> Beginning Date:</font>');
htp.p('</td><td>');
htp.p('<select name="p_from_month">');
it_util.dd_month(to_char(bgdate ,'mm'));
htp.p('</select>');
htp.p('<input type="TEXT" name="p_from_day" SIZE="3" MAXLENGTH="3" value="'||to_char(bgdate,'DD')||'">');
htp.p('<input type="TEXT" name="p_from_year" SIZE="4" MAXLENGTH="4" value="'||to_char(bgdate,'YYYY')||'">');
htp.p('</td></tr>');
htp.p('<tr><td>&nbsp;</td><td>');
htp.p('<font color="000080">');
htp.p('Ending Date:</font>');
htp.p('</td><td>');
htp.p('<select name="p_to_month">');
it_util.dd_month(to_char(SYSDATE,'MM'));
htp.p('</select>');
htp.p('<input type="TEXT" name="p_to_day" SIZE="3" MAXLENGTH="2" value="'||to_char(SYSDATE,'DD')||'">');
htp.p('<input type="TEXT" name="p_to_year" SIZE="4" MAXLENGTH="4" value="'||to_char(SYSDATE,'YYYY')||'">');
htp.p('</td></tr>');
htp.p('<tr><td colspan=2>&nbsp;</td><td>');
htp.p(' ');

htp.p('<input type="SUBMIT" value=" Query ">');
htp.p('&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;');
htp.p('<input type="reset" value=" Clear ">');
htp.p('</td></tr>');
htp.p('</form>');
htp.p('<tr><td colspan=4>&nbsp;</td></tr>');
htp.p('</table>');
htp.p('</td></tr></table><br>');


EXCEPTION
WHEN OTHERS THEN
htp.p('Error unknown. SQLCODE:'||SQLCODE||',SQLERRM:'||SQLERRM);

END;

PROCEDURE xls (p_from_month VARCHAR2,
p_from_day VARCHAR2,
p_from_year VARCHAR2,
p_to_month VARCHAR2,
p_to_day VARCHAR2,
p_to_year VARCHAR2)
IS

from_yn VARCHAR2(100);
to_yn VARCHAR2(100);

temp_from_date DATE;
temp_to_date DATE;

v_rma_num NUMBER;
v_pu_date DIS_ORD_FEED.HEADERS_INTERFACE.sch_pickup_date%TYPE;
v_spec_instr1 ORD_FEED.HEADERS_INTERFACE.special_instructions%TYPE;
v_spec_instr2 ORD_FEED.RMA_HEADERS_INTERFACE.special_instruction%TYPE;
v_notes DIS_ORD_FEED.RMA_HEADERS_INTERFACE.NOTES%TYPE;
v_pickup_req DIS_ORD_FEED.RMA_HEADERS_INTERFACE.pickup_not_required%TYPE;
v_cust_name FEED.RMA_HEADERS_INTERFACE.CUSTOMER_NAME%TYPE;
v_cust_num NUMBER;
v_contact_name DIS_ORD_FEED.RMA_HEADERS_INTERFACE.CONTACT_NAME%TYPE;
v_contact_phone VARCHAR2(100);
v_pickup_addr ORD_FEED.RMA_HEADERS_INTERFACE.pickup_addr%TYPE;
v_pickup_attn DIS_ORD_FEED.RMA_HEADERS_INTERFACE.pickup_attn%TYPE;
v_pickup_city VARCHAR2(75);
v_pickup_state VARCHAR2(20);
v_pickup_zip VARCHAR2(20);
v_comments DIS_ORD_FEED.RMA_HEADERS_INTERFACE.comments%TYPE;
v_approved_by VARCHAR2(75);
v_request_line NUMBER;
v_cat_number VARCHAR2(50);
v_item_desc SYSTEM_ITEMS.DESCRIPTION%TYPE;
v_item_qty NUMBER;
v_serial_number VARCHAR2(50);
v_biz_unit NUMBER;
v_return_reason VARCHAR2(25);



c_tab char := chr(9);

TYPE cv_type IS REF CURSOR;
cv cv_type;

q_string varchar2(2000);
Validate_date boolean;
counter number;

BEGIN


owa_util.mime_header('text/csv',TRUE);


from_yn := isdate(p_from_month||p_from_day||p_from_year);
to_yn := isdate(p_to_month||p_to_day||p_to_year);



IF (from_yn = 'Y' AND to_yn = 'Y') THEN
IF valid_date(to_date(p_from_month||p_from_day||p_from_year, 'MMDDYYYY'),to_date(p_to_month||p_to_day||p_to_year, 'MMDDYYYY'))='Y' then

temp_from_date := to_date(p_from_month||p_from_day||p_from_year, 'MMDDYYYY');
temp_to_date := to_date(p_to_month||p_to_day||p_to_year, 'MMDDYYYY');


q_string := 'select
rownum counter,
hdr.hdr_rma_number RMA_NUM,
hdr.sch_pickup_date PICKUP_DATE,
hdr.pickup_not_required PICKUP_NOT_REQ,
hdr.customer_name CUST_NAME,
hdr.customer_number CUST_NUM,
hdr.contact_name CONTACT_NAME,
hdr.contact_phone CONTACT_PHONE,
hdr.pickup_addr ADDRESS1,
hdr.pickup_attn ADDRESS2,
hdr.pickup_city CITY,
hdr.pickup_state STATE,
hdr.pickup_zip ZIP,
hdr.comments COMMENTS,
hdr.approved_by APPROVED_BY,
ln.request_line LINE,
ln.catalog_number ITEM,
mtl.description DESCRIPTION,
ln.quantity QTY,
ln.serial_number SER_NUM,
hdr.bus_unit BIZ_UNIT,
hdr.special_instructions SPEC_INSTR,
hdr.special_instructions2 SPEC_INSTR2,
hdr.notes NOTES,
ln.return_reason RETURN_REASON
from DIS_ORD_FEED.RMA_HEADERS_INTERFACE HDR,
DIS_ORD_FEED.LINES_INTERFACE LN,
SYSTEM_ITEMS MTL
where hdr.hdr_rma_number = ln.rma_number
and ln.catalog_number = mtl.segment1
and mtl.organization_id = 402 ';

q_string := q_string ||' and trunc(hdr.request_date) between '||chr(39)||temp_from_date||chr(39)||' and '||chr(39)||temp_to_date||chr(39)||' ';

htp.p(c_tab||c_tab||c_tab||c_tab||c_tab||c_tab||c_tab||c_tab||c_tab||c_tab||c_tab||c_tab||c_tab||c_tab||'RMA REPORT DOWNLOAD');
htp.p(' ');

htp.p('Sno'||c_tab||'RMA Number'||c_tab||'PickUp Date'||c_tab||'PickUp Not Req'||c_tab||'Cust Name'||c_tab||'Cust Number'||c_tab||'Contact Name'||c_tab||'Contact No'||c_tab||
'PickUp Add1'||c_tab||'PickUp Add2'||c_tab||'City'||c_tab||'State'||c_tab||'Zip'||c_tab||'Comments'||c_tab||'Approved By'||c_tab||
'Line'||c_tab||'Item'||c_tab||'Description'||c_tab||'Qty'||c_tab||'Serial No'||c_tab||'Business Unit'||c_tab||'Special Instr'||c_tab||'Special Instr2'||c_tab||'Notes'||c_tab||
'Return Reason');

OPEN cv FOR q_string; LOOP

FETCH cv INTO
counter,v_rma_num,v_pu_date,v_pickup_req,v_cust_name,v_cust_num,v_contact_name,
v_contact_phone,v_pickup_addr,v_pickup_attn,v_pickup_city,v_pickup_state,v_pickup_zip,v_comments,v_approved_by,
v_request_line,v_cat_number,v_item_desc,v_item_qty,v_serial_number,v_biz_unit,v_spec_instr1,v_spec_instr2,v_notes,v_return_reason;

EXIT WHEN cv%NOTFOUND;

htp.p(counter||c_tab||v_rma_num||c_tab||v_pu_date||c_tab||
v_pickup_req||c_tab||v_cust_name||c_tab||v_cust_num||c_tab||v_contact_name||c_tab||
v_contact_phone||c_tab||v_pickup_addr||c_tab||v_pickup_attn||c_tab||v_pickup_city||c_tab||v_pickup_state||c_tab||
v_pickup_zip||c_tab||v_comments||c_tab||v_approved_by||c_tab||v_request_line||c_tab||v_cat_number||c_tab||
v_item_desc||c_tab||v_item_qty||c_tab||v_serial_number||c_tab||v_biz_unit||c_tab||v_spec_instr1||c_tab||v_spec_instr2||c_tab||replace(v_notes,chr(13)||chr(10),' ')||c_tab||v_return_reason);



END LOOP;
CLOSE cv;

ELSE
htp.p('Invalid Date!');

END IF;

END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
htp.p('Data not found !');
WHEN OTHERS THEN
htp.p('Error unknown. SQLCODE:'||SQLCODE||',SQLERRM:'||SQLERRM);

END xls;

END ns;


Look at the above code this is written to populate Data into excel file which is achived sucesssfullly definaetly some with your site's help further i have following questions

1) How can i Put up the Heading in Center of the Excel sheet look at the above code i have achived it by using c_tab number of times guess this is not the right way??

2) how can i change the Font to bold for Column headings there are about 24 columns and respective headings

3) I need to put up a link on the Excel sheet which says HOME PAGE and on clicking it takes user to the Under lying URL

Please guide me on the 3 queries i have

Tom:

Is there a way to incorporate a global header mark in each html page generated using mod_plsql.

We would like to have "9i DB" on each page for 200+ procedures. Is there a way to set this up at the DAD or mod_plsql or you have to go to each single procedure and manually do it.

Tom:

YOu don't have any comment on the above question.

Tom:

1. How can I have my pl/sql procedure insert a record for the URL that called it with the parameter value. For example, if I have a procedure " http://www.xx.com:7779/pls/xxx/student_list" that gets called using mod_plsql, I want to store that URL in a MESSAGE table (user,URL, create_Date) everytime a user calls it. DO I need to access a certain environment variable.

2. Is there a way to block the above procedure (ip blocking) so that it will only run when called from a certain machine? I want the procedure to be allowed to only run from a perl program that runs on machine A. So if anyone calls it from anywhere else it will not run?

3. I need also to track the http message outputed by the oracle procedure. Does that mean I need to save the whole output (html/XML) into variable and then print that variable (output) and store it?

Tom:

1. Since mod_plsql is always the client to the stored procdure, can I create another schema in the DB and add those procdures and then configure mod_plsql to only accept certain IP addresses that can call this schema?


2. For authentication, do you suggest like have perl/http client send an encrypted code (i.e todays date) with each http call and then have the oracle stored procedure check if the parameter had today date before it will run. Would that slow down the execution since if we have about 20 perl programs.

3. The database sits behind a firewall. You can only use an http port for access. Perl client sitting on remote machine can not connect to the database because the DBI module wont be using the http. Perl can use LWP or CURL to place http requests.

I do not understand what the problem would be with the API based on http. It is like ¿Web Serices/SOAP¿. Perl makes an http call to the database requesting information, and the oracle stored procedure returns an XML file for the answer. Then perl parses the data and does whatever it wants to do on the other server file system.

1. Web services are based on SOAP, XML, SOAP messages, WSDL , UDDI and HTTP. HTTP is the transport standard that is used to transfer SOAP messages and WSDL documents from one computer to another computer.

SO I do no understand oracle has a lot of stuff too like web services. We are talking about the same transfer protocol being the same.


Also, the configuration is locked we cant change that. the oracle server is behind a firewall and oracle app server (mod_plsql) is on a machine outside a firewall. External machine can only connect using the web.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
http://www.awprofessional.com/articles/article.asp?p=27401&seqNum=5&rl=1

For Web services, on the other hand, the default transport protocol (HTTP) already exists and is in widespread use, although for a different purpose. HTTP was designed to transfer hypertext documents, not invoke objects or pass messages via queues, as Web services can.

Web services create a generic way to connect programs to each other across the network, including (perhaps first and foremost) across the Internet. But the Internet was developed for the purpose of transporting hypertext, not for program-to-program communication, at least not in the same sense as existing distributed technologies would think of it.

Actually, Web services are defined to use the Web, which is a huge application on top of the Internet. HTTP and HTML are already application layers on top of the Internet transport TCP/IP. So Web services end up being defined as applications on top of applications, and huge debates ensue about the propriety of this. But it seems like there's no other way to accomplish the goal of "non-human" interaction over the Web. The Web is out there, and adapting distributed concepts to it represents the best way of achieving the goal. At least, that's what the distributed computing community says.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

2. Are you implying that building an API using http (tunneling everything) will never work at all OR will work but is not a good idea. what options you have with the current network we have (it cannot change)

TOm:

Just to exaplain what is forcing the above environment.

We have two remote servers. One server has the oracle database and is used for internal processing and sits behind the firewall. Another remote server outside firewall is used to store all DIgital files that are being processed.

Ther perl scripts are used to move and delete files on that second remote machine. However, those perl scripts are getting instructions from the database on what needs to be done.

For example, a user marks a book to be deleted in the database. THe perl job every hour runs and checks if a user marked any book number to be deleted. If database gives it a book number it will physically delete it and send a massage back to the database saying it was deleted.

Tom:

Please do not give up, as I am learning from you a lot. Just a comment on yours

1. <http provides nothing a database needs to perform transactions, it is just a protocol>

I understand that http is the carrier of the message which invokes the database to do something. We are primarily doing the same thing a browser will do. You enter a URL, fill out a form and send it to the database to save data and then you get a confirmation response.

Amazon.com is all running based on internet/http. All the orders and confirmations and searches, data are tunneled by http.

YOu have milions of users sending messages (orders/data) from the order form to the database.

What is different here?

2. <tcp-ip would be a reliable database protocol>

Can I use that same http port on db server for tcp/ip messages or we need to open another port. I assume the perl program will be sending tcp/ip messages and oracle sending back tcp/ip messages. correct?

well if I hit "Save" on the amazon site and that wheel keeps spinning or I get a timeout, I would keep trying to login and check if my account shows an order was created. If there was not an order, I would submit the data again.


I think your point is that http is not suitable for messaging because how would the client program handle timeouts and database errors? correct

IF you are using tcp/ip protocol do not you have to handle those errors the same way. Let us say your perl API uses DBI to connect to database, you run the procedure and then server goes down or some error occurs. What do you do then?

is tcp/ip better than http because it maintains connection state or it is a better protocol for communication.

Tom:

Let me confirm something with you before I explore this tcp/ip option.

the remote server (File system) talks to mod_plsql via http. Mod_plsql talks to the database via NET*8 which is oracle protocol running over tcp/ip.

So in reality the tcp/ip port that is required to be open would be on the app server where the mod_plsql is running and then mod_plsql continues talking to db as usual.

Am i correct here or we need a different setup on app server or db server for this tcp/ip communication? if yes, does the firewall create a problem.

Tom:

Do not you think setting up web services is an overkill and little complex for what i am trying to accomplish, similar to setting a J2ee architecture for small shop web site.

Let me ask this logical question.

Ley us say I have a requirement for you to provide me a list of all names that sign up for asktom (assuming there is a registration) every 1 hour. I send a URL every 1 hour and you will provide me a web page or xml page with all the names.

Then I take the list of names, do background checks and tell you to delete certain accounts from the database. I send you another URL (oracle sp) that provides the list and your program will delete those accounts.

Do I really need the set up of web services for this? or I can accomplish this with a few HTML/XML pages.

thank you,

Tom:

1. The human being involvement is to type in the URL in a browser and making a decision on what to do if
a. asktom did not send a response. Most likely the human will keep trying again.
b. asktom printed an error message or message saying data base was not updated.

You can code the client program to make the same human decisions. Correct?

2.How would the use of a web service handle transaction management much better in the above case? Unless you use JMS you will have the same issues with HTTP.


I think your main issues with HTTP are below. However, these are listed too in a a book for "Pitfalls of Web Services".

- Guranteeed Execution - The whole idea behind having a computer program instead of doing a job by hand is that a program can run unattended. HTTP is not a reliaable protocol in that it does not gurantee delivery or a response. If you need this kind of guarnatee, either write the code to retry requests yourself or arrange to send your requests through an intermediary whol will perform these retries for you. Newer versions of the specification allow for using protocols such as JMS to resolve this issue, but the majority of web services out there still utilize HTTP which does not.
- Performance - will vary from time to time. Performance critical systems are not suited to become Web Services
- Availability - Internet is not 100% available


3. Would you set up a web service (SOAP engine, Web service) on the application server using Java or in the database using PL/sql.

Tom:

I did not realize that web services was an infrastructure by itself that you can use. I thought a web service is a piece of code you develop in JAVA or PL/SQL and you call it via XML message inside a SOAP envelope. The article I cut from the Web services book implies Web services has same drawback as HTML since it runs on HTTP.

I think You are implying that ORacle has developed a lot of packages that can support messaging/error handling by using web services instead of writing code to handle timeouts and error handling.

Do you recommend any article or book that demostrates the oracle setup and how to use this to support app to app messaging.

Tom:

You seem to be very optimistic about IT teams knowledge these days. How about if I told you that the team does not even know what a web serivce is? There are people working in IT industry who do not even know the difference between a table and a row!

There are VB and PB programmers who only know VB and PB.

You can rarely find a good IT person with a wide variety of skills.

Research should be straight forward "reading and understanding". This is why I asked you for reference to start with. Implementation requires experience. It depends how complex the issue is.

Tom:

Do you know if Oracle supports using 10g IAS (mod_plsql) with oracle 9i database?

Does it make sense to have a DAD in 10GIAS accessing a 9i database

Tom:

I want to save the URL that a Perl client/LWP calls to mod_plsql either using "get" or "post form" into a database table/column.

1. Do i have to build a string from the cgi variables like this or there is an easier way?


http://xx9.abc.com:7778/pls/masd/test_cgi?p_x=10


v_URL := owa_util.get_cgi_env('REQUEST_PROTOCOL')||'://'||
owa_util.get_cgi_env('HTTP_HOST')||
owa_util.get_cgi_env('SCRIPT_NAME')||
owa_util.get_cgi_env('HTTP_POST')||
owa_util.get_cgi_env('PATH_INFO')||
owa_util.get_cgi_env('QUERY_STRING')


insert into url_table(url) values (v_url);


PLSQL_GATEWAY = WebDb
GATEWAY_IVERSION = 2
SERVER_SOFTWARE = Oracle HTTP Server/1.3.22 (Unix) mod_plsql/9.0.2.0.0 mod_oc4j/3.0 mod_ossl/9.0.2.0.0 mod_fastcgi/2.2.10 mod_perl/1.26 mod_oprocmgr/1.0
GATEWAY_INTERFACE = CGI/1.1
SERVER_PORT = 7777
SERVER_NAME = xx9.abc.com
REQUEST_METHOD = GET
QUERY_STRING = p_x=10
PATH_INFO = /test_cgi
SCRIPT_NAME = /pls/masd
REMOTE_ADDR = 11.111.111.11
SERVER_PROTOCOL = HTTP/1.1
REQUEST_PROTOCOL = HTTP
REMOTE_USER = masdadd
HTTP_USER_AGENT = Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; InfoPath.1; .NET CLR 2.0.50727)
HTTP_HOST = xx9.abc.com:7778
HTTP_ACCEPT = */*
HTTP_ACCEPT_ENCODING = gzip, deflate
HTTP_ACCEPT_LANGUAGE = en-us
WEB_AUTHENT_PREFIX =
DAD_NAME = masd
DOC_ACCESS_PATH =
DOCUMENT_TABLE =
PATH_ALIAS =
REQUEST_CHARSET = WE8MSWIN1252
REQUEST_IANA_CHARSET = WINDOWS-1252
SCRIPT_PREFIX = /pls




2. Also, what about if the URL is invalid and procedure fails. How do i get a log of that into the table?

Tom:

So you are saying to log the name of the procedure + the parameter values and not look anything in the CGI?

What about if the procedure has 10 input parameters and the user sends one parameter with one value? would the URL you save have the 10 parameters with null values.

2. What about if user passed invalid values which cause procedure to fail? is there a way to track that.

3. would the above work the same if user post an HTML form and some parameters are arrays?

Tom:

1. It seems capturing the URL can be either done using the CGI or procedure input parameters. Just a matter of choice. correct?

2. I am trying to log everything sent by the client program to debug errors. If the client says I called your procedure "save_order" and I sent parameters "A,B" on Tuesday at 10:00 AM, i can go to the table and verify that he did or did not send. He might have send an extra parameter that is not handled or wrong value, etc.

What I was asking you above is that if the procedure fails, it will not log anything and I can not really tell what was sent. Correct? is there a way around this.

<you rebuilding a url is 'fake', it isn't the url they sent, it is the post processed stuff you received. >

Is this a true statement if I am building the URL from the CGI variables? It cant be.




<so, I'm personally back to LOG IT in a structured format. >

You mean create a column per parameter in the table.



<You always have your web logs for the original URL if you want. >

Where do you get this from? is there a log in the web server.


<I told you how to avoid the procedure issue - use varchar2 for all inputs. Convert to your datatypes IN YOUR PROCEDURE, then call the "real procedure" with the real datatypes.>

I do that all the time. I never use other than VARCHAR2. But this does not mean the URL sent will not fail for
some reason. Let us say he sent a new parameter that is not in the procedure.

Tom:

How do you run a pl/sql procedure that prints html tags in sql*plus.

can you see the output just likw a browser?

ops$tkyte%ORA10GR2> declare
  2          nm      owa.vc_arr;
  3          vl      owa.vc_arr;
  4  begin
  5          nm(1) := 'WEB_AUTHENT_PREFIX';
  6          vl(1) := 'WEB$';
  7          owa.init_cgi_env( nm.count, nm, vl );
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec htp.p( 'hello world' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec owa_util.showpage
Content-type: text/html
Content-length: 12
hello world

PL/SQL procedure successfully completed.

Tom:

in addition to above question, is there a way I can actually see the HTTP header getting sent with output.

Tom:

I did exactly the steps you had above but i did not get the header like you do. I am using sql*plus for windows.
I do not like the unix one.

SQL> exec owa_util.showpage
hello world

PL/SQL procedure successfully completed.

Tom:

How can i pass parameters of type array to an API that saves them into another table. Basically passing parameters to save_video_files. Do i need to set up a loop and save ne file at a time or there is a way to pass all of them.

The message coming in is for a status of a video on servers. If it was copies to 3 servers then i need to store one record in parent table and all files in each child record for each server.



PROCEDURE SAVE_VIDEO
(p_video_id IN VARCHAR2 DEFAULT NULL,
p_server_1 IN VARCHAR2 DEFAULT NULL,
p_server_2 IN VARCHAR2 DEFAULT NULL,
p_Server_3 IN VARCHAR2 DEFAULT NULL,
p_file_name IN TYPES.ARRAY DEFAULT TYPES.EMPTY_ARRAY,
p_file_size IN TYPES.ARRAY DEFAULT TYPES.EMPTY_ARRAY
)

BEGIN


IF (p_server_1 is not null) THEN
SELECT VIDEo_FILES_SEQ.nextval into v_Seq from dual;
SAVE_VIDEO(v_seq,server_id,video_id);
SAVE_VIDEO_FILES(v_seq,p_video,p_file_name,p_file_size);
END IF;

IF (p_server_2 is not null) THEN
SELECT VIDEo_FILES_SEQ.nextval into v_Seq from dual;
SAVE_VIDEO(v_seq,server_id,video_id);
SAVE_VIDEO_FILES(v_seq,p_video,p_file_name,p_file_size);
END IF;

IF (p_server_3 is not null) THEN
SELECT VIDEo_FILES_SEQ.nextval into v_Seq from dual;
SAVE_VIDEO(v_seq,server_id,video_id);
SAVE_VIDEO_FILES(v_seq,p_video,p_file_name,p_file_size);
END IF;


END;

Tom:

I have not wrote this procedue yet

SAVE_VIDEO_FILES(v_seq,p_video,p_file_name,p_file_size);

How can i pass this as an array to the other API that saves multiple files to the table. I think the above will not work or only pass one file.

declare
   l_files types.array;
begin
   l_files(1) := 'x';
   l_files(2) := 'y';
   proc( l_files );
end;

Tom:

Would this pass my array from P1 to P2 and get them saved?
I am trying to create an API instead of doing the INSERT in P1 because there are several conditions. I thought it might be cleaner to have an API to pass the parameters for each condition.

PROCEDURE SAVE_VIDEO
(
p_file_name IN TYPES.ARRAY DEFAULT TYPES.EMPTY_ARRAY,
p_file_size IN TYPES.ARRAY DEFAULT TYPES.EMPTY_ARRAY
)

BEGIN

SAVE_FILES(p_file_name,p_file_size);


END;


PROCEDURE SAVE_FILES (
p_file_name IN TYPES.ARRAY DEFAULT TYPES.EMPTY_ARRAY,
p_file_size IN TYPES.ARRAY DEFAULT TYPES.EMPTY_ARRAY

begin


FOR i IN 1..p_file_name.count
LOOP

INSERT into FILES(col1,col2( VALUES (p_file_name(i),p_file_size(i) );


END.

Tom:

What would you say to a perl client code that is trying to post data to an oracle stored procedure without using an array?

so it is like passing an html table with 5 columns and 100 rows, you have to define 500 different parameters in oracle sp.

is not much better to have same element name in html and just submit the form as an array.

Tom:

So would you force them to use same parameters names for List of repeating objects? What would be your argument to force this instead of a unique parameter name per object.

With arrays the orders have to be consistent meaning
p_item,p_part,p_qty have to be passed in groups, otherwise the array will not be processed correctly.

create procedure p( p_items in array, p_part in array, p_qty in array )

create procedure p(
p_item_001, p_part_001, p_qty_001,
p_item_002, p_part_002, p_qty_002,
p_item_003, p_part_003, p_qty_003,
p_item_004, p_part_004, p_qty_004,
p_item_005, p_part_005, p_qty_005,
p_item_006, p_part_006, p_qty_006,
....
......
.........
.......................
)

Tom:

The other argument says that "arrays" are used to pass data from multi selected picklists where order is not an issue. There is no standard for saying that order must be preserved in an argument list. what do you say to that?

Tom:

yes, the client program can't gurantee that array values posted to server (plsql) can be ordered meaning it can send

p_item=123&p_part=333&p_qty=5

However, the part and qty may not be related to item 123

this i why it wants to send data in this format
p_item_1=123&p_part_2=333&p_qty_3=5&p_item_2=222&p_part_3=

I think PLSQL requires ordered values to process array parameters and save the data correctly. what are your comments? is there anything in HTTP that does not gurantee ordering of data posted.

Tom:

<data is posted sequentially by the client>

this is the argument. is this part of the HTTP standard for posting data. I am trying to find that reference.

Tom:

How do you use the toolkit to print an oracle error cause by raise_application_error(....) in an expceion handler.

Right now, the user sees that long oracle error page.

I want to show an HTML version (background color and title) with oracle error number and message but without showing the list of parameters and values and mod_plsql info.

Tom:

How do you do it in your web toolkit apps?

1. Do you ever catch an error (ie invalid foreign key, no data found, etc) and print the error using sqlerrm.

I think you always wanted to use RAISE in your exception handling. This would block printing a user friendly page to the user. It is certainly better for debugging the error as it will tell you where and what the failure was.

2. can i use raise_application_error(...) and then print that using htp statement or I have to remove that and just use the htp statement with the error number printed.

Tom:

what do you recommend for this situation.

I have an end of month process that is triggered by a user at the end of each month. the process depending on number of orders processed may take 5 - 15 minutes. I notices that IE sort of hangs and sometimes returns an error. I assume because it did not hear back from oracle.

what would you do. Would you schedule this job and tell user upon completion he will get an email of status (pass or fail) or would you keep showing something on the browser until it ends or else??

2. In your code would you ever use LOOP within LOOP. First loop get a list of qualified customers and 2nd loop looks at each customer orders by order type and then update those rows.

i did not write it but i think this might slow it down.

1. For the job/email/link solution do you let user log in to the system.
and then go to a job status page or you create unprotected page that shows him the status
directly. this might be unsecure solution without login.

2. The first part does not need a loop and can be done with one update

UPDATE orders SET Ord_Qty = NVL(Sel_Qty,Def_Qty)
WHERE ord_Date between '01-JAN-2009' and '30-JAN-2009'

However after the update, I have to SUM(Ord_Qty) for each customer/media and
compare that to his monthly quota.

Is there any better way to do that than this or speed it up?

Orders
----------
Ord_Date date,
cust_id varchar2(10),
media_type varchar2(5),
Def_Qty number(5),
Sel_qty number(5),
Ord_Qty number(5)


For x in (SELECT cust_id,media_type,SUM(Ord_Qty) Total_Qty from orders
where ord_date between to_Date('01-JAN-2009') and to_date ('30-JAN-2009')
group by cust_id,media_type
LOOP

CMQ := CALC_QUOTA(cust_id,media_type,'01-JAN-2009')

IF (Total_Qty > CMQ) then
....log those customers
else
..do nothing
END IF;
END LOOP;

insert into something
select * 
  from (
select cust_id, media_type, total_qty
  from orders 
 where ord_date between to_date( '01-jan-2009', 'dd-mon-yyyy' )
                    and to_date( '30-jan-2009', 'dd-mon-yyyy' )
 group by cust_id, media_type
       )
 where total_qty > 
       calc_quota(cust_id,media_type,to_date( '01-jan-2009', 'dd-mon-yyyy' )

Tom:

1. Is it possible to give a company XYZ a URL (for oracle stored procedure) and they can use that to uplaod an XML file to an oracle table? The file will be somewhere on their local PC.

I am thinking that file uploads has to be using "POST" method and not "GET". SO the only way to do it is via an html form and they select the file there but someone said a URL can do it too.

2. Would you implement the above idea if you want to get an XML file for shipping data every night or would you let XYZ log in to a system and upload file via a clean HTML form. IT sounds like flinging a URL not a clean way of doing it.

3. If the data required is at high level (i.e shipment date, number of CDs, shipped address, etc) does not make mroe sense to let them enter the daa into a web form instead of creating an XML file from their system and uplaoding it and then parsing it at our side and storing it in a relational table?

Tom:

you misunderstood me on ths issue. This is a different application. IT WILL involve a human being.

This human will either hit a URL that will do file upload OR log into a system, go to a web html form to select a file and then press upload.

Unless i am wrong i do not think there is a way for this PERSON to upload a file automatically by hitting a URL. The URL can not select the file on your machine. There needs to be an HTML form that uses a POST method for doing that. My point is that option A (hitting one URL for uploading and saving file into a table) can not be done to upload a file unless you can somehow a POST but i cant see how. Option B will required TWO URLs. One for HTML form to select the file and another URL to post the data to.

am i wrong

Tom:

This is an easy question.

We have a few oracle pl/sql web applications that use 9iR2 database server and 9iAS (http server). the 9ias has mod_plsql and is on different machine than the DB.

They want us to use 10g IAS. it is on same machine where the 9ias. We use relative URLs mostly in the procedures but the login page may need to change. We are thinking of creating a redirectror on the 9i web server that routes the link to the 10g OR just have everyone log in to the 10g.

I do not forsee any issues as i have another application that uses 10g IAS with 9IAS. Do you see any potential problems or have any suggestions.

Tom:

as a followup to the above, would it also make sense to use https or secure port instead of migrating to regular differetn web server. i assume no extra work is involved.

Tom:

switching from http on 9IAS to 10g IAS and using https.

would there be any issues with the switch.

the db is 9iR2.

it is only an http lisener and mod_plsql - so i doubt it affects anything.

Tom:

i can't beleive you would go from XP Pro to Vista.

Vista is crappy - too many bugs - not good for business applications. MS says now the new Windows 7 will be powerful. But if MS who has 95,000 employees + 40,000 contractors still can't produce bug-free and secure software it makes me wonder.

So you think the (APACHE ENGINE+MOD_PLSQL) in 9IAS might be different than 10g IAS. I doubt oracle will touch the apache listener. mod_plsql is probably maintained by oracle.

Tom:

yes it is very hard to find one in stores. However many web stores sell laptops with XP pro. Dell and HP will build you one too.

http://www.nextag.com/windows-xp-professional-notebook/compare-html

I can't change anything in the current environment that exists. They have the 10g IAS on a different box than database ( you know security - do not install webb server on same machine as DB) . But all we are using in that are the listener and mod_plsql.

My currect environment is:
I have 3 production databases (A, B , C) (9iR2) running on SERV1.

I have 10g production iAS(webserver/mod_plsql) running on SERV2 with 3 DADs (database access descriptor) :
adad = aadmin/aadmin@tns_A
bdad = badmin/badmin@tns_B
cdad = cadmin/cadmin@tns_C

I want to test the apps under 11g with identical environment but there is a limitation is that I have to use the same 10g app production server for testing too.

They only setup a new box (SERV3) that will have 11g database server.


a) Do i need to setup 3 new DAD names on SERV2 and 3 new TNS service names on SERV 3 like

11gadad = aadmin/aadmin@11g_tns_A

I assume database names on test box can be the same but the TNS names MUST change due to the old production DADs.
Please advice on the best setup. I cant change much in the hardware environment and what is available.

Tom:

Is it possible to install a second instance of the web http server/mod_plsql on same machine where production web server is running or use a different port for testing?

Why do you think it is stupid.

Tom:

Box A that has 9i oracle database server with many oracle apps (stored procedures) - behind firewall.
Box B has oracle http server and modplsql - outside firewall
Box C has pure apache web server - outside firewall.

An NFS mounts exists between Box C and Box A so that oracle can write files to directory on A.

Oracle application uses Box C web server to display list of files in a directory on Box C generated by oracle.
Most application calls however are done using http server on Box B.

I want to get rid of Box C dependency completely.

If i create the files (UTL_FILE) on Box A, is there a way external users can see them displayed like publich directory display.

Do i need like an oracle stored procedure that reads/displays all the files in a directory so that users can download from Box A direct?

would you consider storing files in table too instead of filesystem ?

Can you advise?

Tom:

yes i meant i have some code(stored procedure) that uses UTL_FILE and writes files on disk on Box "C".

Application is on Box "A". Box "B" has the oracle http server/modplsql.

1. Is it possible to write something that can list files in a directory on Box A just like they do it on Box C now?

2. To do the table solution would you keep creating files on disk and then upload file from disk to table and then create a web page to display content from table?

Hi Tom,

Thanks for your feedback. We are working with 2 projects. One requires WSDL, PL/SQL and another is for Bing Application Programming Interface (API), Version 2 ( http://msdn.microsoft.com/en-us/library/dd251056.aspx ) and PL/SQL.

Do let me know whether we can access WSDL and BING application through PL/SQL and How we can achieve?

Example will help us.

Oracle Database Version :
Oracle9i Enterprise Edition Release 9.0.1.3.0 - 64bit Production
PL/SQL Release 9.0.1.3.0 - Production
CORE 9.0.1.2.0 Production
TNS for Solaris: Version 9.0.1.3.0 - Production
NLSRTL Version 9.0.1.3.0 - Production

Thanks & Regards,
Bipin Ganar

Tom:

We are upgrading the db from 9i to 11g.
The application server is staying at 10g App server of which we are only using oracle http server and mod_plsql.

Is there a difference between the apache and mod_plsql between the 10g AS and 11g AS? What do you recommend to use.

That means you are using the web server on the same machine as your database server. It ia big no-no for security in some companies.

The 11g app server is based on apache 2.2 while the 10g app server is based on apache 1.3.

http://www.oracle.com/technetwork/middleware/ias/index-091236.html

I think the newer release of mod_plsql requires apache 2.2

do you think this would make a difference (version of apache and modplsql).

Tom:

Can this mod_owa do everything mod_plsql do.

http://rpbouman.blogspot.com/2006/02/webenabling-oraclexe-you-can-free-of.html

Do you recommend it?

Tom:

so in your opinion having the http server and database run on same machine does not really create any security concerns and it is more of a mental feeling of security.

You can still have a software firewall between both and it would be same as if web server on a different machine

Tom:

Is there a way to do this using mod_plsql.

we have a website that has files for users to download. Currently user accounts are stored in oracle table and there is nightly cron job that updates the apache users and password files based on that.

The website authentication is done and protected using apache basic authentication.

I was thinking of cerating a pl/sql web page for authentication against the oracle table but there is no way oracle can protect the static files on the filesystem that users download.

can you advise?

Hi Tom
The HTP and HTF packages are generating the tags in UPPERCASE (for e.g. <HTML>). But, if I want to generate XHTML 1.1 document, this is not helping me. -- Tested in 9.2 and 10.1 releases. Is it still like that in 11.2 ?
Also, is there any support through these packages for HTML 5 in future?

Tom:

<<why would you have files on a file system >>

This is old legacy system I inherited. The choice was not mine.

Is there really a way to use a stored procedure/table/mod_plsql user authentication instead of apache basic authentication and protect the files on the directory from public access.

I do not think there is a way it but i want it to confirm that with you.

A QUICK NOTE ON THE ABOVE.

use HTP.PRINT and enter any XHTML tag.

HTP.PRINT('<html xmlns=" http://www.w3.org/1999/xhtml" >');


That is what i use mostly instead of built-in functions.

Tom:

Is the BFILE solution practical and easy?

Are not you leaving your file system open though for anyone who can find the link or would you still use apache to blick any direct access to that directory

Tom:

Is it true that oracle is discontinuing support and development on the 10g iAS and bundling everything now into the WEBLOGIC application server?

Tom:

I have this web procedure that save HTML form data submitted by user.

I want to check the array data before I try to save it because i have many HTML statements that i want to avoid printing if the exception is raised. Is there a better way to do it than this (at beggining of program before any saves).

PROCEDURE SAVE_ORDER (
p_ord_date IN VARCHAR2 DEFAULT NULL,
p_stockno IN TYPES.ARRAY DEFAULT TYPES.EMPTY_ARRAY,
p_flag IN TYPES.ARRAY DEFAULT TYPES.EMPTY_ARRAY)

AS

missing_Y_items exception;
records_found boolean default false;


BEGIN

/*
I need to check if there are any records submitted with flag set to "Y".
If there are no records, raise an exception with alert that no records are eligible.
*/

HTP.p(....HTML starts here);
....

FOR i IN 1..p_stockno.COUNT
LOOP

IF p_flag(i) = 'Y' THEN
display records.....HTP.P(...);
records_found := TRUE;
END IF;

END LOOP;

IF (records_found := FALSE) THEN
RAISE missing_Y_items;
END IF;

END;

Tom:

Do you mean having one loop for data validation first before running the 2nd loop that prints the HTML tabular data?

I was thinking about that but thought it might take some time and there could be another faster method.

Is this the best method?


If i did what you suggest can I STOP the processing inside the LOOP and continue program after I see the first record with "Y". There is no need to read 100 records if the first record we process has a "Y" in it (99.9% of cases).


would casting an array to a table and then querying the table be more efficient.

Tom:

Is it possible to write a Stored Procedure (DB machine) that calls ro redirects to a static file on OHS apache filesystem (Web server machine) so that the file is only opened for IP address of the DB machine.

I am wondering when user runs this SP, whether the web server will see the IP address of DB server or user client machine.

My goal is to protect these file so only DB application users can see it.

I think the SP can also change the HTTP header and include any ip address.

Tom:

1) Do you mean reading the static file into an external LOB and just printing it using HTP and MOD_PLSQL as show in the SP below?

That way I do not even need to store the file on the apache web server and can be stored on the DB server.


I tried this but the file shown by the SP did not look 100% identical to the original one. Some colors and formats were missing. One character "single quote" did not display correctly too and was showing as 2 inverted question marks. Wondering whethr it is because file is in UTF-8 and DB is in WE8ISO8859P1 CS.


CREATE OR REPLACE PROCEDURE htpbfile
AS
v_bfile BFILE := BFILENAME('TEMP_DIR', 'index.html');
v_clob CLOB;
v_len INTEGER;
v_index INTEGER;
BEGIN
DBMS_LOB.createtemporary(v_clob, TRUE);
DBMS_LOB.open(v_bfile, DBMS_LOB.lob_readonly);
DBMS_LOB.loadfromfile(v_clob, v_bfile, DBMS_LOB.lobmaxsize);
DBMS_OUTPUT.put_line(v_clob);
v_len := DBMS_LOB.getlength(v_clob);
v_index := 1;

WHILE v_index <= v_len
LOOP
HTP.PRINT(DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
v_index := v_index + 32000;
END LOOP;
END;



2) what do you think of the idea of using PSP script with include directive for the filename?


<%@ include file="filename" %>

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_psp.htm#BHAEFHGG

.

Tom,

Greetings!

I need your expertise to help me in deciding whether to upgrade the current OHS (web tier) from 11.1.1.6 to 11.1.1.9 or 12c (12.1).

My database applications are based on mod_plsql mostly and some APEX.

I upgraded the database from 11.2.0.3 to 12.1.0.2.0.

I am upgrading the webs server due to security vulnerabilities in the 11.1.1.6.

Oracle seems to have made some design changes to OHS in 12c. They removed web cache and opmnctl.

It seems also oracle will stop supporting MOD_PLSQL in OHS 12.2 and it wont be bundled.

I am trying to make the best decision based on security fixes, compatibility with existing applications and database, licensing, technical support, existing tools such as web cache, opmnctl, etc.


Which upgrade path you would recommend and why?

Thanks,

This is unknown to me right now and that is why I am asking you if you have any work experience or previous migrations with both OHS releases the 11.1.1.9 and 12c.

I do not expect any database application code rework using either release unless there are some changes in MOD_PSQL or OHS I am not aware of.

I have seen a document too that MOD_PLSQL is deprecated in 12.1.3 and some that says it will be the last supported release for it.

DO you know if there are any major differences in terms if security features or internals for these two version of OHS?

Are you using OHS 11.1 or 12c with asktom or other database application?