Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Gediminas.

Asked: June 23, 2000 - 9:15 am UTC

Last updated: January 29, 2016 - 1:16 am UTC

Version: 4.0.8 and 8.1.5

Viewed 10K+ times! This question is

You Asked


We are building dynamic HTML pages using PLSQL.
Is any easy way to capture resulting pages to files
(UTL_HTTP doesn't work, because we are using SSL).


and Tom said...

Sure, you can run the pages in SQLPlus and then use UTL_FILE to write the result to disk.

So, lets say you have setup UTL_FILE (see the supplied packages guide </code> http://docs.oracle.com/cd/F49540_01/DOC/server.815/a68001/utl_file.htm#998101 <code>for setup steps -- you need an init.ora parameter set up). You can write a procedure:

create or replace procedure dump_page( p_dir in varchar2,
p_fname in varchar2 )
is
l_thePage htp.htbuf_arr;
l_output utl_file.file_type;
l_lines number default 99999999;
begin
l_output := utl_file.fopen( p_dir, p_fname, 'w' );

owa.get_page( l_thePage, l_lines );

for i in 1 .. l_lines loop
utl_file.put( l_output, l_thePage(i) );
end loop;

utl_file.fclose( l_output );
end dump_page;
/

Now, you log into sqlplus (or some other command line sql environment) and run the procedure you normally run from the WEB. For example, say you have a procedure:

create or replace procedure pretend_this_is_a_webdb_proc
as
begin
htp.p( 'This is a webdb procedure, trust me' );
htp.p( 'The end' );
end;
/

You would just run that and then run:

begin
pretend_this_is_a_webdb_proc;
dump_page( '/tmp', 'page.html' );
end;
/

the page would now be stored in the file page.html in /tmp. If your procedures make use of the Owa_Util.get_cgi_env() call, you may have to setup an appropriate environment (if you get a numeric or value error with respect to the owa_util package -- this is the cause). To set up an environment you would simply run a block like:

declare
nm owa.vc_arr;
vl owa.vc_arr;
begin
nm(1) := 'SERVER_PORT';
vl(1) := '80';
owa.init_cgi_env( 1, nm, vl );
end;
/

first. To cause this page to be extracted on a recurring basis, you can take all of the above, put it into a stored procedure that sets up the environment, runs the procedure and dumps the page -- then use dbms_job to schedule that procedure to run every X minutes/hours/days whatever.








Rating

  (45 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 11, 2002 - 6:09 am UTC


query server and dump results to another server

mo, February 21, 2003 - 12:32 pm UTC

Tom:

1. I have two unix servers "R7" and "R20". There is a static homepage on "R7" that visitors access using web server only.. THe page will have search button for things to search. Now the database to search is on "R20" which is accessed using MOD_PLSQL.

I want visitors to get a result page off "R7". I do not want them to see the link "procedure" running on the R20.

Can I do this with your above procedure that you also cover in your book page 1203. Can I have the static page on R7 submit data to a procedure on R20 which in tuns uses utl_file to dump results on r7 and then open that page for the user?

THanks,




Tom Kyte
February 21, 2003 - 3:18 pm UTC

why why why? what is the goal here -- security via obscurity again?

or -- is your goal to make this website as slow as you possibly can - there are easier ways to do that.

So -- tell me -- what is the goal here.

dump page

mo, February 21, 2003 - 3:26 pm UTC

Tom:

1. Yes to make the server 20 link invisible. It is not that big of a system to create a problem here. we are only looking up addresses. I do understand it does not implement security in any way, but it is a physcological thing that makes client happy. more like making your phone unlisted that is all. If it is possible please tell me so?

2. You say there are easier ways to do this? How.

Thanks

Tom Kyte
February 21, 2003 - 3:53 pm UTC

But the link is there -- it is security via obscurity -- the two systems are totally 100% linked network wise. You are kidding yourself if this is a "security thing".

it is a dis-service, false sense of security...


there are easier ways to make a system dog slow, that was my point:

...
or -- is your goal to make this website as slow as you possibly can - there are
easier ways to do that......


that was tongue in cheek, sarcasm :)


You would set up a program on the r7 server (cgi-bin or module or whatever) that runs a url on r20 and sends the data back there should be no files anywhere, just something that can run a URL and return the results

but.... this is sort of beyond "how do I do this in the database" so -- you'll need to find a webserver guy to go further.

search

mo, February 21, 2003 - 3:39 pm UTC

Tom:

Just to let you know I searched on he exact title of this link and your search will not find anything:

try it search for

storing dynamic HTML pages to files

Thanks

Tom Kyte
February 21, 2003 - 3:54 pm UTC

yup, we are workig on it

link

mo, February 21, 2003 - 4:34 pm UTC

Tom:

Well let us say people wants to bug you by making phone calls. They call 411, you phone is unlisted. they open the white pages and your phone is still not found. Do not you think you enhanced your privacy/security more by having your phone unlisted.

anyway, what would be the problem if i had the static page on the first server submit to a procedure on the second server. The second procedure creates the results dynamic page using dump_page and saves it into the first server directory. then the procedure would change HREF value to the open the file on the first server. Is not this a feasible solution before i jump into it?

Thanks,




Tom Kyte
February 21, 2003 - 6:37 pm UTC

nope cause the autodialers that the pesky telemarketers use still find me. believe me -- I know, my number IS unlisted and i STILL get calls.

the people that want to get into the system -- will use tools to do so. they will find this easily.

The ONLY thing this gives (and -- you and I have had this discussion before mo) is a false sense of "my system is secure because I've obscured things"....


you tell me -- on that last paragraph. You tell me -- how does a "static page" do anything??? it is (after all) static.

do what you will.

re-direct

mo, February 21, 2003 - 8:46 pm UTC

Tom:

Static page is like a homepage. It is always the same - static in nature. I can still have a dropdown list on a static page and click a button "search" to do something. It does not affect the page in any way. It result in another page that is dynamic. second page depends on what is in the database and what user selected in first page.

Client has a static page on server that simply looks up addresses, emails from a database on the second server.

Since the second server has other databases on it, he does not want people to see the link name "www.hotserver.com" which enables them to find the ip address and try to hack or get through unsecured oracle applications using the browser. He says that hackers can not find out about ip addresses unless they were give the address or have a DNS name?

Anyway, think of the static page as a page that lists all 51 states. User opens up the page, selects a state from a dropdown list, presses "Search" then he sees all info related to state as size, population, number of counties etc.

Can the "search" button submit data to a pl/sql procedure on a second server which uses you dump_page routine which writes an html file "page.html" to the first server, and then I re-direct the URL to open up the file. So end result user will see a URL to same public server with data results.

Thank you,

Tom Kyte
February 22, 2003 - 10:42 am UTC

a static page cannot "lookup addresses, emails" -- a static page may send you to a dynamic page that does that.

I'll say this one last time and then will just stop responding:

security through obscurity is worse then having no security at all. got it? it does one thing and one thing only -- it makes the pointy haired managers feel "better". You haven't done a single thing to secure ANYTHING, in fact, since people have this false sense of security -- you've actually made the problem worse. Got it? Make sense? If not, I sort of "give up"....

i don't know how else to say it -- people who want to find that server WILL FIND it. they are not stupid, there are tools to do it. Just like a call center finds my phone number -- which is unlisted.


You can do whatever you want. The search button can certainly do that -- HOWEVER guess what URL will appear in the browser??? The BROWSER is the guy that submits the page (not this "static page" -- it isn't even a teeny tiny bit relevant in this entire discussion. Ignore it, pretend IT DOES NOT EXIST).

So, your static page submits to server r20, r20 appears in the URL -- r20 dumps the page, r20 sends a redirect back to the client (browser) the browser gets the static page from r7.

big deal -- so what -- r20 appeared, has to appear, will appear.


This is like a bad joke and you are not getting the punchline.

dump page

mo, April 27, 2003 - 1:41 pm UTC

Tom:

If you want to dump HTML (htp type procedure output) file into a long variable instead of a file Would you still be using UTL_FILE functions for that or not?

Tom Kyte
April 27, 2003 - 4:30 pm UTC

think about it...

Why not use mod_rewrite from apache to hide the url or maybe reverse proxy.

A reader, April 28, 2003 - 7:06 am UTC


Tom Kyte
April 28, 2003 - 8:29 am UTC

how does that enhance or add to the security of the system?

Handling Cookies

Jack Wells, June 08, 2003 - 3:07 pm UTC

Tom,

I would like to use the "dump_page" procedure to call a mod_plsql procedure which sets a cookie before producing HTML:

--
-- Write the cookie in the http header BEFORE any HTML is produced
--
owa_util.mime_header ('text/html', FALSE);
owa_cookie.send ('APPID', TO_CHAR (c_wgn_rec.application_id), SYSDATE + 1);
owa_util.http_header_close;

However, when I call the dump_page procedure:

begin
procedure_that_sets_cookie;
dump_page;
end;

I get the following error:

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 323
ORA-06512: at "SYS.OWA_UTIL", line 364
ORA-06512: at "PROCEDURE_THAT_SETS_COOKIE", line 179
ORA-06512: at "PROCEDURE_THAT_SETS_COOKIE", line 147
ORA-06512: at line 2

Line 179 is the "--" line before the "owa_util.mime_header..." line above. Is there a work-around?

Tom Kyte
June 08, 2003 - 4:04 pm UTC

declare
nm owa.vc_arr;
vl owa.vc_arr;
begin
nm(1) := 'DUMMY_JUST_TO_SET_UP_OWA_UTIL';
vl(1) := 'WHATEVER';
owa.init_cgi_env( nm.count, nm, vl );
end;
/


you need to initialize the "cgi-environment". if your procedure uses an OWA_UTIL calls, you need to do this.

This is what exactly I was looking for...

Sami, February 09, 2004 - 11:38 am UTC

Dear Tom,

This is what exactly I was looking for.

We used to generate reports using mod_plsql. Now user wants to get the reports in email. so I can dump the result page into flatfile (using UTl_FILE package) and email it to them using cron job.




like to use CLOB datatype instead of UTL_FILE?

Sami, February 10, 2004 - 10:51 am UTC

Dear Tom,

I hope you like this question coz you always recommend to store things inside the DB.

Due to security reason we do not have UTL_FILE parameter setup in our shop. Instead of "dump_page (into file system), is it possible to store the html output of "pretend_this_is_a_webdb_proc" into table CLOB datatype.

So that using Java program I can read it from client machine and dump into a file system.

Will it be better alternative for UTL_FILE? If so kindly throw some light how to capture the HTML output and store in in CLOB data type.

Your help is always appreciated.


Tom Kyte
February 10, 2004 - 3:34 pm UTC

sure, you can write it to a clob.

but why not just have a java program
a) run the procedure
b) get the results (just like dump_page does)
c) write the file?

and be done with it?

to store as a clob, change utl_file to dbms_lob, insert an empty_blob() into a table and return it into a local variable of clob type, use writeAppend or something instead of utl_file.put to write to it.

Need your opinion

Sami, February 10, 2004 - 2:50 pm UTC

Dear Tom,

I wrote dump_clob to store the html output into CLOB column. Is there any better way to do the same?


PROCEDURE dump_clob
AS
l_thePage htp.htbuf_arr;
l_lines NUMBER DEFAULT 99999999;
g_clob CLOB;

BEGIN
owa.get_page( l_thePage, l_lines );
FOR i IN 1 .. l_lines
LOOP
IF (i=1) THEN
INSERT INTO DUMP_HTML VALUES (1, SYSDATE,EMPTY_CLOB() ) returning html_page INTO g_clob;
dbms_lob.WRITE( g_clob,LENGTH(l_thePage(i)), 1,l_thePage(i));
END IF;
dbms_lob.writeappend( g_clob, LENGTH(l_thePage(i)), l_thePage(i));
END LOOP;
END dump_clob;


Tom Kyte
February 10, 2004 - 3:56 pm UTC

well, you could

   if ( i=1 ) 
   then
     insert into dump_html vlaues ( 1, l_thePage(i) ) returning html_page into ...
   else
     writeappend...
   end if

you see to print the first line twice as well...

ops$tkyte@ORA9IR2> create table dump_html( id number, dt date, html_page clob );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace  PROCEDURE dump_clob
  2  AS
  3  l_thePage       htp.htbuf_arr;
  4  l_lines         NUMBER DEFAULT 99999999;
  5  g_clob             CLOB;
  6
  7  BEGIN
  8     owa.get_page( l_thePage, l_lines );
  9     FOR i IN 1 .. l_lines
 10     LOOP
 11        IF (i=1) THEN
 12          INSERT INTO DUMP_HTML VALUES (1, SYSDATE,l_thePage(i) )
 13                  returning html_page INTO g_clob;
 14        else
 15              dbms_lob.writeappend( g_clob, LENGTH(l_thePage(i)), l_thePage(i));
 16            end if;
 17     END LOOP;
 18  END dump_clob;
 19  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec htp.p( 'hello' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec htp.p( 'world' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dump_clob;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from dump_html;
 
        ID DT        HTML_PAGE
---------- --------- --------------------------------------------------------------------------------
         1 10-FEB-04 hello
                     world
 

Thanks Tom.

A reader, February 10, 2004 - 4:15 pm UTC

Silly logical mistake.

After package modification...

Sami, February 10, 2004 - 5:04 pm UTC

Dear Tom,

After Modifying the package I executed modified package 3 times.

1 st time 
==========
I am getting "ORA-04068: existing state of packages has been discarded"

This is OKAY, Understandable.

2nd time 
==============
I am getting "ORA-06502: PL/SQL: numeric or value error"

I DON"T KNOW WHY?

3rd Time
================

Running fine.

Nothing got modified between step (1) and Step (3).

Could you please enlighten me why Step(2) is giving ORA-06502 and step(3) is NOT.

SQL> @1
begin
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "MYUSER.MY_REPORTS" has been invalidated
ORA-04065: not executed, altered or dropped package body "MYUSER.MY_REPORTS"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
 
 
SQL> @1
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 315
ORA-06512: at "SYS.HTP", line 859
ORA-06512: at "SYS.HTP", line 974
ORA-06512: at "SYS.HTP", line 992
ORA-06512: at "SYS.HTP", line 40
ORA-06512: at "MYUSER.MY_REPORTS", line 257
ORA-06512: at line 2
 
 
SQL> @1
 
PL/SQL procedure successfully completed.
 
SQL> l
  1  begin
  2  olpr_reports.bib_mqcnt;
  3  olpr_reports.dump_clob('olpr_reports.bib_mqcnt');
  4* end;
SQL> 
 

ORA-06502 with owa package

Mark A. Williams, February 10, 2004 - 7:23 pm UTC

The issue is that in the owa package header (around line 8 or so) the variable 'num_cgi_vars' is declared but not given an initial value. One option is presented in note #202017.1 on MetaLink. The bug is #1821493 on MetaLink as well. Changing the declaration to 'num_cgi_vars number := 0;' will resolve the issue, but I don't normally recommend changing things in the 'SYS' schema.

HTH,

Mark

Thanks a lot Mark A. Williams

Sami, February 10, 2004 - 7:43 pm UTC


You're Welcome

Mark A. Williams, February 10, 2004 - 7:57 pm UTC

You're welcome - I ran across the same problem while doing some research on mod_plsql a few months ago.

- Mark

Thanks for the info - one more question

Paul, February 10, 2004 - 11:52 pm UTC

Tom,
after i use the owa.get_page, i'm not able to see the HTML output in the browser. Is there a mechanism to save the html to a file and also display output in the browser.

for example

create or replace procedure test
as
l_thePage htp.htbuf_arr;
l_output utl_file.file_type;
l_lines number default 99999999;
begin
for i in 1..100
loop
htp.p('hello') ;
htp.br ;
end loop;
owa.get_page( l_thePage, l_lines );
dump_page( '/tmp', 'page.html' );
end ;

Now, due to the owa.get_page i do not see anything
when invoking this proc. thru the browser.
I tried htp.p(l_thePage(i)) but broken up HTML tags cause problems. Please suggest another way.

Thanks
Paul

Tom Kyte
February 11, 2004 - 9:09 am UTC

you cannot have your cake and eat it too.

once YOU get the page -- others cannot get the page.

Use htp.prn instead of p. prn will not inject the newlines.

A reader, April 20, 2004 - 10:05 pm UTC

Tom,

I have a similar requirement in which i have to generate report daily for management ( dont have to be a technical one)which should display tablespace info file size physical io cpu utilization for different database. The report can be in any format i mean word or spreadsheet or html.

I am thinking about htmldb. am not sure if this is the right choice, can you please provide feedback.

Tom Kyte
April 21, 2004 - 7:39 pm UTC

htmldb would be excellent as it can show you the data in HTML, but allow you to download it in excel at the push of a button.

works in browser but dump_clob is giving ORA-01843: not a valid month error

Sami, April 21, 2004 - 10:01 am UTC

SQL> begin
my_pkg.my_report;
my_pkg.dump_clob('my_report');
end;
/
  2    3    4    5  begin
*
ERROR at line 1:
ORA-01843: not a valid month


but the same time 
http://my_machine/pls/my_dad/my_pkg.my_report
works fine.


In my_report procedure I am trying to get 1st day of the month and LAST_DAY of the month.

    AND       CREATEDATE BETWEEN        TO_DATE(TRUNC(SYSDATE,'MM'),'DD-MON-YY HH24:MI:SS') AND  TO_DATE(TRUNC(LAST_DAY(SYSDATE)+1),'DD-MON-YY HH24:MI:SS');

-------------

PROCEDURE dump_clob(i_repname VARCHAR2)
AS
l_thePage       htp.htbuf_arr;
l_lines         NUMBER DEFAULT 99999999;
g_clob             CLOB;


BEGIN
owa.get_page( l_thePage, l_lines );
FOR i IN 1 .. l_lines
LOOP

IF (i=1) THEN
   INSERT INTO HTML_REPORTS VALUES (rep_seq.NEXTVAL, i_repname,SYSDATE,EMPTY_CLOB()) returning html_page INTO g_clob;
   dbms_lob.WRITE( g_clob,LENGTH(l_thePage(i)), 1,l_thePage(i));
ELSE
    dbms_lob.writeappend( g_clob, LENGTH(l_thePage(i)), l_thePage(i));
END IF;

END LOOP;
END dump_clob;

------------- 

Tom Kyte
April 21, 2004 - 8:46 pm UTC

TO_DATE(TRUNC(SYSDATE,'MM'),'DD-MON-YY
HH24:MI:SS') AND TO_DATE(TRUNC(LAST_DAY(SYSDATE)+1),'DD-MON-YY HH24:MI:SS');

is your problem.

you are relying on default date masks. yours in sqlplus must be differen than it is on the web....


you just need

trunc(sysdate,'mm') and trunc(last_day(sysdate))+1


don't know why you have the to_dates in there at all.

but the to_date is implicitly converting the trunc(sysdate,'mm') into a string and that string is NOT in the dd-mon-yy format!

if you select trunc(sysdate,'mm') from dual, you'll see what format it is in.

Have your cake and eat it too!

Scott, January 10, 2005 - 10:11 pm UTC

G'day Tom.

In response to Paul's request, I've found a way to have your cake and eat it too.

After the
owa.get_page(v_page,irows);
I called
htp.init;
to flush the buffer and then you can call htp.p again with a string obtained from v_page.

Either that or repeat this to build your page
v_temp := v_temp||htf.<your_page_build_commands>;
with a final once-off
htp.p(v_temp);

Scott.

dump_page has a 32k limit but dump_csv doesn't?

ht, August 02, 2005 - 3:13 pm UTC

Tom,
I'm a content user of your dump_csv code and am now using your dump_page code. I'm running into a 32k limit in dump_page but not in dump_csv.

How would I adjust dump_page to write more than 32k to a static html page?

TIA

ht

Tom Kyte
August 02, 2005 - 4:33 pm UTC

tell me how you are running into a 32k limit?

it writes a line at a time? only if you had a line >32k would you have an issue (and then, I would suggest "break that line")


the fopen call might need to have a "wider" default line passed to it (1022 is the default max line width) so if you are hitting that, just modify the fopen call

File write error occurs when running pretend_this_is_a_webdb_proc.

ht, August 02, 2005 - 7:45 pm UTC

Tom,
Below is a test case that seems to indicate there is a limit to the amount of html that can be written by pretend_this_is_a_webdb_proc.

I modified pretend_this_is_a_webdb_proc to accept the number of lines to be printed. Passing 10 lines works, passing 1000 fails. Is there a workaround?

select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

5 rows selected.


create or replace procedure dump_page( p_dir in varchar2,
2 p_fname in varchar2 )
3 is
4 l_thePage htp.htbuf_arr;
5 l_output utl_file.file_type;
6 l_lines number default 99999999;
7 begin
8 l_output := utl_file.fopen( p_dir, p_fname, 'w',32767 );
9
10 owa.get_page( l_thePage, l_lines );
11
12 for i in 1 .. l_lines loop
13 utl_file.put( l_output, l_thePage(i) );
14 end loop;
15
16 utl_file.fclose( l_output );
17 end dump_page;
18 /

Procedure created.


create or replace procedure pretend_this_is_a_webdb_proc(l_lines number)
2 as
3 nm owa.vc_arr;
4 vl owa.vc_arr;
5 begin
6 nm(1) := 'DUMMY_JUST_TO_SET_UP_OWA_UTIL';
7 vl(1) := 'WHATEVER';
8 owa.init_cgi_env( nm.count, nm, vl );
9 for i in 1 .. l_lines loop
10 htp.prn( 'This is a webdb procedure, trust me' );
11 end loop;
12 htp.prn( 'The end' );
13 end;
14 /

Procedure created.


-- run the proc and generate 10 lines
begin
2 pretend_this_is_a_webdb_proc(10);
3 dump_page( '/tmp', 'page1.html' );
4 end;
5 /

PL/SQL procedure successfully completed.


!ls -ltr /tmp/page1.html
-rw-r--r-- 1 oracle oracle 403 Aug 2 16:31 /tmp/page1.html


-- run the proc and generate 1000 lines
begin
2 pretend_this_is_a_webdb_proc(1000);
3 dump_page( '/tmp', 'page2.html' );
4 end;
5 /
begin
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 562
ORA-06512: at "DUMP_PAGE", line 16
ORA-06512: at line 3



!ls -ltr /tmp/page2.html
-rw-r--r-- 1 oracle oracle 0 Aug 2 16:31 /tmp/page2.html


exit;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production


Tom Kyte
August 02, 2005 - 8:04 pm UTC

prn does not add "newlines", the lines are >32k as described above which will be a problem (even with dump_csv)

Used htp.p instead of htp.prn but now it seems like there is a 32k file limit.

ht, August 02, 2005 - 7:50 pm UTC

Tom,
I changed the proc to use htp.p instead of htp.prn but now I run into an issue that seems to indicate that only 32k can be written to the page*.html file.
The second and third test below seem to generate pages that are only 32k in length.

-- run the proc and generate 10 lines
begin
2 pretend_this_is_a_webdb_proc(10);
3 dump_page( '/tmp', 'page1.html' );
4 end;
5 /

PL/SQL procedure successfully completed.


!ls -ltr /tmp/page1.html
-rw-r--r-- 1 oracle oracle 413 Aug 2 16:40 /tmp/page1.html


-- run the proc and generate 1000 lines
begin
2 pretend_this_is_a_webdb_proc(1000);
3 dump_page( '/tmp', 'page2.html' );
4 end;
5 /

PL/SQL procedure successfully completed.

!ls -ltr /tmp/page2.html
-rw-r--r-- 1 oracle oracle 32768 Aug 2 16:40 /tmp/page2.html


-- run the proc and generate 1200 lines
begin
2 pretend_this_is_a_webdb_proc(1200);
3 dump_page( '/tmp', 'page3.html' );
4 end;
5 /

PL/SQL procedure successfully completed.


!ls -ltr /tmp/page3.html
-rw-r--r-- 1 oracle oracle 32768 Aug 2 16:40 /tmp/page3.html



Tom Kyte
August 02, 2005 - 8:05 pm UTC

is it maxing out the "page" returned? are there more lines perhaps?

More lines?

A reader, August 02, 2005 - 8:11 pm UTC

Hi Tom,
Do you want me to run the proc and pass in more lines? I believe its max'ing out because passing 1000 or 1200 lines shows the same amount of kb in the "ls -ltr" command.

Tom Kyte
August 03, 2005 - 9:57 am UTC

try debugging the code a bit -- it is rather "small" and the concept is pretty easy.



Dumping large files

John Russell, August 12, 2005 - 6:23 pm UTC

Tom, the followup discussion solved my problem which was to save the contents of the page and display it in the browser. (Solution: htp.init.)

When I have dumped large files using this technique, I've found it necessary to include periodic "flush" calls to avoid lost data:

-- Flush the buffer every 50 lines. If the buffer goes past about 33,000
-- characters, it overflows and data is lost.
if mod(i,100) = 0 then
utl_file.fflush(outfile);
end if;

In this case, i is the iterator variable stepping through the collection elements.

ORA-06502 when invoking a procedure that uses HTP.P()

Anthos, June 05, 2008 - 2:39 am UTC

Tom

I have this procedure used in our portal application which generates dynamic pages and streams them to HTP.P(). I am trying to build a java interface to this where I call the procedure to generate the dynamic page and then calling HTP.GET_PAGE(..) to retrieve the page.

I get following error at the point i invoke the procedure.
javax.servlet.ServletException: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.HTP", line 1270
ORA-06512: at "SYS.HTP", line 1496
ORA-06512: at "SYS.HTP", line 1513

Thoughts ? Thanks in advance.
Tom Kyte
June 05, 2008 - 10:58 am UTC

hmmm

might you just consider showing us the call, the thing that raises this error, with a description of the inputs.

other things like VERSIONS would probably be useful too.

anthos, June 10, 2008 - 2:05 am UTC

Sorted out for now by calling a procedure first that wraps following
    owa.num_cgi_vars := 0;
    owa.cgi_var_name (1) := 1;
    owa.cgi_var_val (1) := 1;
    owa.init_cgi_env (1, owa.cgi_var_name, owa.cgi_var_val);

store webpage

Sam, March 01, 2011 - 10:21 pm UTC

Tom:

I need to store some webpages into a CLOB column in a table. If i need to download these pages again via URL using MOD_PLSQL, I think the CLOB will not work.

Shall i store the webpage in BLOB field instead or shall I convert the CLOB to BLOB when downloading.

blob

sam, March 02, 2011 - 2:55 pm UTC

Tom:

On a 2nd thought, I do not need to do any conversion.

Just store the HTML file in BLOB and download the file via mod_plsql as is and it should display fine in the browser.

Correct?
Tom Kyte
March 02, 2011 - 5:38 pm UTC

you could do that as long as you need no character set conversions to ever take place.

blob

sam, March 02, 2011 - 8:12 pm UTC

There might be some conversion. Would that be an issue.

DB = WE8ISO8859P1
MOD_PLSQL = WEMSWIN1252
Browser = Could be different

DO you recommend to store the HTML page as STRING in CLOB and just print it (instead of doing file download)?
Tom Kyte
March 03, 2011 - 7:34 am UTC

I'd probably be tempted to just htp.prn it.

dump page

sam, March 10, 2011 - 11:48 am UTC

Tom:

I am running into weird problem. I am using htp.p to prin the clob.

Do you always have to set into the web procedure if you have a weekly job that does the archive.
I am dumping page into CLOB.

nm(1) := 'DUMMY_JUST_TO_SET_UP_OWA_UTIL';
vl(1) := 'WHATEVER';
owa.init_cgi_env( nm.count, nm, vl );

The problem is that i sometimes see "Content Type: and Content size" in the page dump.


I removed these initialization lines and it got rid of the content type and size in the page dumped but i noticed job failed few times overnight. When i troubleshooted today it keeps running fine. Kind of strange.




-- run the proc and generate 10 lines
begin
2 pretend_this_is_a_webdb_proc(10);
3 dump_page( '/tmp', 'page1.html' );
mail_pkg.send_me_email_pass;
exception
mail_pkg.send me_email_fail;
4 end;
5 /
Tom Kyte
March 10, 2011 - 1:12 pm UTC

ops$tkyte%ORA11GR2> @owainit
ops$tkyte%ORA11GR2> 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%ORA11GR2> 
ops$tkyte%ORA11GR2> exec htp.p( 'hello world' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_thePage       htp.htbuf_arr;
  3      l_lines         number default 99999999;
  4  begin
  5      owa.get_page( l_thePage, l_lines );
  6  
  7      for i in 1 .. l_lines loop
  8          dbms_output.put_line( l_thePage(i) );
  9      end loop;
 10  end;
 11  /
Content-type: text/html

Content-length: 12



hello world


PL/SQL procedure successfully completed.


I would be surprised NOT to see it, it is sort of necessary stuff for a web page.

If you do not set the owa environment, you will get:

ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> exec htp.p( 'hello world' );
BEGIN htp.p( 'hello world' ); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.HTP", line 1368
ORA-06512: at "SYS.HTP", line 1443
ORA-06512: at "SYS.HTP", line 1735
ORA-06512: at line 1


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_thePage       htp.htbuf_arr;
  3      l_lines         number default 99999999;
  4  begin
  5      owa.get_page( l_thePage, l_lines );
  6  
  7      for i in 1 .. l_lines loop
  8          dbms_output.put_line( l_thePage(i) );
  9      end loop;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.HTP", line 665
ORA-06512: at "SYS.HTP", line 759
ORA-06512: at "SYS.HTP", line 845
ORA-06512: at "SYS.OWA", line 89
ORA-06512: at line 5




the setting of the environment doesn't impact the generation of the content type headers.


And as a programmer, you should be able to easily solve this in your code - right?

dump page

sam, March 10, 2011 - 12:37 pm UTC

Tom:

I did a trace on he problem and indeed job was failing somtimes due to initialization of cgi

Errors in file /ora920/admin/istt/bdump/istt_j001_9785636.trc:
ORA-12012: error on auto execute of job 277
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 323
ORA-06512: at "SYS.HTP", line 860
ORA-06512: at "SYS.HTP", line 975
ORA-06512: at "SYS.HTP", line 993
ORA-06512: at "IST.STOCK_REPORTS", line 845
ORA-06512: at "IST.STOCK_REPORTS", line 1150
ORA-06512: at line 1



I added it back and job no longer fails (even though it would run sometimes in that case).



The issue is why the dump has the this header before report. I tried adding close header and it did not help.

Content-type: text/html Content-type: text/html Content-length: 15812 DATE: 10-MAR-2011 13:32:42

Tom Kyte
March 10, 2011 - 1:15 pm UTC

it always has had this header Sam.

It is supposed to have this header.

It will have this header.

Now, as a programmer - can you figure out a way to get rid of it programatically?

dum page

sam, March 10, 2011 - 1:34 pm UTC

Tom:

html_page is a CLOB type.

would you use INSTT, SUBTR and REPLACE like this to chop it off in the insert statement.


 1* select report_id,substr(html_page,1,instr(html_page,'<HTML>',1,1)-1) from reports_archive
 SQL> select report_id,substr(html_page,1,instr(html_page,'<HTML>',1,1)-1) from reports_archive
  2  /

 REPORT_ID SUBSTR(HTML_PAGE,1,INSTR(HTML_PAGE,'<HTML>',1,1)-1)
---------- --------------------------------------------------------------------------------
        44 Content-type: text/html
           Content-type: text/html
           Content-length: 15811


        45 Content-type: text/html
           Content-type: text/html
           Content-length: 15812




2 rows selected.

SQL> edi
Wrote file afiedt.buf

  1* select report_id,replace(html_page,substr(html_page,1,instr(html_page,'<HTML>',1,1)-1),'') from reports_archive SQL> /

 REPORT_ID REPLACE(HTML_PAGE,SUBSTR(HTML_PAGE,1,INSTR(HTML_PAGE,'<HTML>',1,1)-1),'')
---------- --------------------------------------------------------------------------------
        44 <HTML>
           <HEAD>
           <TITLE> Inventory Report</TIT

        45 <HTML>
           <HEAD>
           <TITLE> Inventory Report</TIT


2 rows selected.

Tom Kyte
March 10, 2011 - 1:57 pm UTC

you are using owa.get_page right.

what does that return that you *might* fine useful?

does it return a clob?
does it return an array you are iterating over and printing out line by line?

Now, can you see a way????

page

sam, March 10, 2011 - 2:11 pm UTC

Tom:

If i understand you correctly you are saying to not print the first 3 line of the array. right?

it is kind of risky because I noticed sometimes it repeats one line twice and sometimes does not.

I am worried it will chop the actual HTML tags unless you know the number of lines for sure.



ops$tkyte%ORA11GR2> declare
2 l_thePage htp.htbuf_arr;
3 l_lines number default 99999999;
4 begin
5 owa.get_page( l_thePage, l_lines );
6
7 for i in 4 .. l_lines loop
8 dbms_output.put_line( l_thePage(i) );
9 end loop;
10 end;
11 /

Tom Kyte
March 10, 2011 - 2:31 pm UTC

.. it is kind of risky because I noticed sometimes it repeats one line twice and
sometimes does not. ...

it should not, and if it does, it should do it REPEATEDLY not randomly. So if it does - prove it, give us an example.


If i understand you correctly you are saying to not print the first 3 line of
the array. right?


you misunderstand me entirely. I was asking you to "think" - how might you - a programmer, a coder, a developer, a computer IT professional, someone that gets paid to make these computer things do this in a bullet proof manner??

What might YOU do in YOUR code to make it so you that you can definitely tell when the headers are done and the data is coming? What might YOU do right after you call owa_init to let yourself know that "hey - data be coming after this"

dum page

sam, March 10, 2011 - 3:18 pm UTC

Tom:

Not sure.

Do you flush the buffer htp.init; after you initialize

or do you check for the 1st blank line after header.

or check for the word <HTML> to know data starts here

or check for certain http status code.
Tom Kyte
March 10, 2011 - 3:46 pm UTC

sigh...

maybe you htp.p something, like a boundary - a tag - something special - right after you owa_init - so that you can recognize "everything above this is junk, everything after this is what I want"

just maybe....

just saying....

I thought I gave plenty of clues.

What might YOU do in YOUR code to make it so you that you can definitely tell when the headers are done and the data is coming? What might YOU do right after you call owa_init to let yourself know that "hey - data be coming after this"

just about spec'ed it out for you...

ops$tkyte%ORA11GR2> 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%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec htp.p( '---- 123abc this is me bca321 ----' );

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_thePage       htp.htbuf_arr;
  3      l_lines         number default 99999999;
  4          l_found         boolean := false;
  5  begin
  6      owa.get_page( l_thePage, l_lines );
  7  
  8      for i in 1 .. l_lines
  9          loop
 10                  if l_found
 11                  then
 12                  dbms_output.put_line( l_thePage(i) );
 13                  else
 14                          l_found := l_thePage(i) like '---- 123abc this is me bca321 ----%';
 15                  end if;
 16      end loop;
 17  end;
 18  /
hello world


PL/SQL procedure successfully completed.

dump page

sam, March 10, 2011 - 4:02 pm UTC

Tom:

This is what i see under view source. I can add a special tag to tell me <DATA_STARTS_HERE> but why not just search for the <HTML> tag where data starts.


.without htp line

Content-type: text/html
Content-type: text/html
Content-length: 15811

<HTML>
<HEAD>


...with an htp line after initalize

this is where report data starts
this is where report data starts
<HTML>
<HEAD>


I assume i need to add a line in the LOOP after GETPAGE to check if each line = <SPECIAL_TAG> and ignore it and not write append it to the CLOB variable.

is that how you would do it.
Tom Kyte
March 10, 2011 - 4:25 pm UTC

sam, you can use whatever the heck you feel like using. go for it. You are actually the developer right? do what you want.

you know what you need to do. go for it.

dump page

sam, March 10, 2011 - 5:35 pm UTC

Tom:

i am getting brain lock. Do you use INSTR() and SUBSTR() function on the first line stored in the array to capture everything after the fake tag <DATA_STARTS_HERE>.

Another problem is that this archived report can also be run by users real-time when they click on it. It would show the fake tag then.


I added a line here

create or replace procedure pretend_this_is_a_webdb_proc(l_lines number)
2 as
3 nm owa.vc_arr;
4 vl owa.vc_arr;
5 begin
6 nm(1) := 'DUMMY_JUST_TO_SET_UP_OWA_UTIL';
7 vl(1) := 'WHATEVER';
8 owa.init_cgi_env( nm.count, nm, vl );
......htp.p('<DATA_STARTS_HERE>');
9 for i in 1 .. l_lines loop
10 htp.prn( 'This is a webdb procedure, trust me' );
11 end loop;
12 htp.prn( 'The end' );
13 end;
14 /

What change do you do here to ignore everything before the tag <DATA_STARTS_HERE>


PROCEDURE dump_clob(i_repname VARCHAR2)
AS
l_thePage htp.htbuf_arr;
l_lines NUMBER DEFAULT 99999999;
g_clob CLOB;


BEGIN
owa.get_page( l_thePage, l_lines );
FOR i IN 1 .. l_lines
LOOP

IF (i=1) THEN
INSERT INTO HTML_REPORTS VALUES (rep_seq.NEXTVAL, i_repname,SYSDATE,EMPTY_CLOB()) returning
html_page INTO g_clob;
dbms_lob.WRITE( g_clob,LENGTH(l_thePage(i)), 1,l_thePage(i));
ELSE
dbms_lob.writeappend( g_clob, LENGTH(l_thePage(i)), l_thePage(i));
END IF;

END LOOP;
END dump_clob;


Tom Kyte
March 11, 2011 - 7:18 am UTC

i am getting brain lock. Do you use INSTR() and SUBSTR() function on the first
line stored in the array to capture everything after the fake tag
<DATA_STARTS_HERE>.


Sam - I gave you the code. I mean - I actually already gave you working, functioning code. I don't know what more I can give you.


It would show the fake tag then.

ok, now, put on your programmer cap and think. what might you put as a tag into an HTML document that would not show up in the rendering of HTML.... think about. what do we as programmers put into our code that doesn't show up in the code when it is compiled?

Suggestion for Sam

Tubby, March 10, 2011 - 10:02 pm UTC

Sam, have you considered taking your questions to the Oracle forums?

http://forums.oracle.com/forums/category.jspa?categoryID=18

They are comprised of a plethora of volunteers who i'm sure would love to try and help you out.

Seems to me a much more appropriate venue than a site with a single resource who's time and patience are a limited commodity.

dum page

sam, March 10, 2011 - 11:40 pm UTC

Tom:

I got it working. I added 2 pipes after the owa intitialization htp.p('||'); and added a line in the dump page that drops anything before the ||. I think this what you meant.


owa.get_page(l_thePage, l_lines );

FOR i IN 1 .. l_lines
LOOP
IF (i=1) THEN

l_thePage(i) := substr(l_thePage(i),instr(l_thePage(i),'||',-1,1)+2);

INSERT.....

dump page

sam, March 11, 2011 - 9:44 am UTC

Tom:

sorry, i did not see the piece of code you posted using the l_found boolean variable.

I tested your suggestion and it has an issue. Part of the HTML page did not show up.


I think you are assuming that each HTP.P('something') statement = l_page(i).

That does not seem to be correct.

I printed (l_page(1)) and it has many the HTML tags even though my report uses one HTP.P per tag.


I am using 9iR2 DB too so it might be diff in 11gR2 but I doubt it.



The thing I do not understand (unless there is bug with 9iR2) is why the SUBSTR sometimes work and sometimes does not in the code when i check the first page

If (i=1) THEN

  l_thePage(i) := SUBSTR(l_thePage(i), INSTR(l_thePage(i), '||', -1, 1) +2);

  INSERT..
else
  APPEND
end if;Tom:



I am using 2 pipes here to indicate the start of the page. 


1  select SUBSTR('Content-type: text/html Content-type: text/html Content-length: 15814  || my data starts here',
  2* INSTR('Content-type: text/html Content-type: text/html Content-length: 15814 || my data starts here','||',-1,1)+3) name from dual
SQL> /

NAME
----------------------
 my data starts here

1 row selected.


Maybe you can explain this puzzle.

Tom Kyte
March 11, 2011 - 10:00 am UTC

Sam - work it out. You are a programmer, this is what we actually do for a living. Work it out.

dump page

A reader, October 11, 2011 - 7:42 pm UTC

Tom:

Do you know why I see this message in the first file getting dumped?
Do you see any issue in the procedure below that uses dump_page to dump many webpages and then ZIP them and then download them?

Originally I was getting BUFFER limitation error when there were many files (about 10 K each), but then after i move the initialiation inside the LOOP, it got fixed.

X-ORACLE-IGNORE: IGNORE X-ORACLE-IGNORE: IGNORE X-ORACLE-IGNORE: IGNORE Content-type: text/html; charset=WINDOWS-1252 Content-length: 12371 1


procedure dump_orders (p_seqno in varchar2)

is

nm owa.vc_arr;
vl owa.vc_arr;
c number;
d number;
l_zip_filename varchar2(30);
l_path varchar2(30) default '/app/tst/';
l_files_list varchar2(1000);

begin

dbms_output.enable(1000000);

for x in (select seqno, file_no, ordno, vendorcd, filename
from orders_download
where seqno = p_seqno
order by file_no)
loop
nm(1) := 'WEB_AUTHENT_PREFIX';
vl(1) := 'WEB$';
owa.init_cgi_env( nm.count, nm, vl );

orders_pkg.view_order(x.ordno, x.vendorcd);

orders_pkg.dump_page( '/app/tst', x.ordno||'.html' );
l_files_list := l_files_list||' '||x.filename||' ';


end loop;

-- ZIP the file
l_zip_filename := l_path||'orders-'||p_seqno||'.zip';

c := orders_pkg.RUN_CMD('/usr/bin/zip -r '||l_zip_filename||' '|| l_files_list||' ');


--URL redirection to download file to user
owa_util.redirect_url(' https://wwws.abc.com/orders-'||p_seqno||'.zip' );

htp.p('done! ');
end;

Tom Kyte
October 12, 2011 - 6:52 am UTC

Hi Sam,

that is the http header - since you are using a set of web packages, it should have been expected by you...

file size limitation

A reader, October 15, 2011 - 11:20 pm UTC

Tom:

One of your replies above about this error was to intialize the mod_plsql
when the column is a CLOB (over 32000 bytes).

ORA-06502: PL/SQL: numeric or value error


I did that and it does not seem to solve the issue. My file size was 67 KB and it was failing to download the file. When I did a LOOP and DBMS_LOB.SUBSTR at 32000 it did work.

Am i correct and is this how you would print a CLOB?



PROCEDURE DUMP_ORDNO
( p_report_id IN VARCHAR2 DEFAULT NULL )

IS
l_html_page clob;
l_mime_type nls_documents.mime_type%TYPE default 'text/html';
l_ordno varchar2(15);
nm owa.vc_arr;
v1 owa.vc_arr;
l_amt number := 32000;
l_off number := 1;
l_cnt integer;


BEGIN

SELECT vno, html_page
INTO l_vno, l_html_page FROM orders_html_reports
WHERE report_id = p_report_id;

nm(1) := 'DUMMY_JUST_TO_SET_UP_OWA_UTIL';
v1(1) := 'WHATEVER';
owa.init_cgi_env(nm.count, nm, v1);

l_cnt := DBMS_LOB.getlength(l_html_page);
l_cnt := l_cnt + 1;

--Create the header and download the file as attachement
OWA_UTIL.mime_header(l_mime_type, FALSE);
HTP.p('Content-Length: ' || l_cnt );
HTP.p('Content-Disposition: attachment; filename='||l_ordno||'.html');
OWA_UTIL.http_header_close;
begin
while (l_off < l_cnt)
loop
HTP.P(dbms_lob.substr(l_html_page,l_amt, l_off));
l_off := l_off + l_amt;
end loop;
exception
when no_data_found then
null;
end;
END;



Tom Kyte
October 16, 2011 - 10:24 pm UTC

Sam,

you are a programmer, when are you going to start debugging your own code?


One of your replies above about this error was to intialize the mod_plsql
when the column is a CLOB (over 32000 bytes).

ORA-06502: PL/SQL: numeric or value error


this is a big page, I have no clue to what you are referring. The only place I see the number 32,000 or 32000 on this page - is you writing it. I do not see the word mod_plsql is any of my replies. I'm not going to read the entire page - so no clue what you mean pops into my head.


It does appear that you are hitting the 32k limit on varchar2's in plsql yes. There isn't an HTP.P call that takes a clob - so the limit to htp.p is 32k.

I didn't verify the perfection or non-perfection of your code - you can do that, I've answered the 32k issue.


dump page

A reader, October 17, 2011 - 8:01 am UTC

Here is the question on the page

<< dump_page has a 32k limit but dump_csv doesn't? August 2, 2005 - 3pm Central time zone Bookmark | Bottom | Top>>


I found i am hitting 32K limitation using 2 approaches:

1) when dumping a CLOB from table to browser using HTP.P
2) when writing an HTML file from memory buffer to filesystem using UTL_FILE.

The solution for #1 was to include HTP.P in a loop that fetches 32K at a time and prints it.

The solution for the UTL_FILE was to to run
UTL_FILE.fflush(l_output) every 40-50 rows.

This applies to 9iR2. I do not know if oracle fixed or changed anything in 11g for those packages that removes this limitation.

Tom Kyte
October 17, 2011 - 8:43 am UTC

Sam

32k <> 32000 (neither in print, nor numerically)

so - what is your point here?

did you point me to anything in your original posting right above - or did you make it really impossible to figure out what you were talking about?

that is what is known as a rhetorical question by the way, I'm not expecting you to followup, that would not be necessary.



Plsql has a known and documented varchar2 length of 32k, there is nothing to "fix" and it hasn't changed in 11g.


Have your cake and share it!

Gabriel, January 28, 2016 - 11:56 pm UTC

Follow-up to "Have your cake and eat it too!"
There's an undocumented feature: htp.reset_get_page
Enhancement#5610575 per the pl/sql comment in the htp package.

This allows you to run:
htp.get_page(v_page,irows);
htp.reset_get_page;

And now you can do whatever you like with the html page without having to recreate it for the recipient!
Connor McDonald
January 29, 2016 - 1:16 am UTC

True...but "undocumented" is our way of saying "you're on your own if it goes wrong" :-)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library