Generating Html from Pl/Sql
Create or Replace Procedure Hello_World2 Is Begin Htp. Htmlopen; Htp. Headopen; Htp. Title ('You Knew It Was Coming. ..'); Htp. Headclose; Htp. Comment ('This...
CREATE OR replace PROCEDURE Hello_world2
IS
BEGIN
HTP.htmlopen;
HTP.headopen;
HTP.Title ('You knew it was coming...');
HTP.headclose;
HTP.comment ('This phrase is in every computer book.');
HTP.Bodyopen (cattributes => 'body bgcolor=blue');
HTP.Print ('And here it is .... Hello, World!');
HTP.bodyclose;
HTP.htmlclose;
END;
How can I output this to an HTML file? I have tried as shown below:
spool report.htm
exec hello_world2;
spool off
exit
But I am getting error as shown below:
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 "SYS.HTP", line 72
ORA-06512: at "T416493.HELLO_WORLD2", line 4
ORA-06512: at line 1
Can anyone help me with this
5 Answers
Normally you would call owa_util.showpage to spool the contents out.
Dan has a nice overview of the usages Oracle OWA_UTIL
There is something funny about this, it should run from sqlplus but I think it expects to be called from mod_plsql, using a web browser.
If you really only want te generate static web pages, you can also use plain old dbms_output to do that. Otherwise, it might be smarter to take a look at Oracle Apex or maybe even a very nice and capable alternative for Oracle Apex, Formspider
ORA-06512: at "SYS.OWA_UTIL", line 356
Did you try to see what's there? :-)
That line has
for i in 1..owa.num_cgi_vars
so you're trying to run cgi-aware code in non-cgi environment.
The owa.num_cgi_vars variable is NULL.
begin for i in 1..null loop null; end loop; end;
will give you the same ORA-06512.
You need to init the CGI with owa.init_cgi_env
See
It worked for me where everything else failed.
I've had the same problem. I inserted another dad-profile and it worked like a charm :) Somehow the old dad-profile got rotten.
-- as xdbadmin or sys (mpl_user is of course schema where packages reside): exec dbms_epg.create_dad('new_dad', '/my_db/*'); exec dbms_epg.set_dad_attribute('new_dad', 'database-username', 'UUSSEERR'); -- username in uppercase grant execute on dbms_epg to uusseerr;
-- as user (uusseerr): exec dbms_epg.authorize_dad('new_dad');
Below code will print the html page formed using stored procedure.
set serveroutput on;
declare
vNames owa.vc_arr;
vValues owa.vc_arr;
begin
htp.init;
vNames(1) := 'REQUEST_PROTOCOL';
vValues(1) := 'HTTP';
owa.init_cgi_env(
num_params => 1,
param_name => vNames,
param_val => vValues );
test_package.proc_call; --procedure where html pages are formed using htp.p
owa_util.showpage;
end;
/
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
