When you need to develop and output HTML reports in the database, you can directly use the htp package during the process to implement HTML code and output after data processing. and the output is in the desired format (controlled by the HTML Markup Language ). the following is a test example for generating HTML reports directly from a database. You need to modify or add HTML reports as needed.
Before use, you must configure the Apache connection. During the call process, the system directly outputs the generated HTML to the Web;
-- Configure the Apache connection:
For example, if the file is installed in D:/system/https, D:/system/https/Apache/modplsql/CONF/dads. conf is set:
<Location/SS>
Sethandler pls_handler
Order deny, allow
Allow from all
AllowOverride none
Plsqldatabaseusername dinya
Plsqldatabasepassword dinya
Plsqldatabaseconnectstring dinya: 1521: ora10g
Plsqlauthenticationmode basic
</Location>
Of course, the connection to the database must be valid. For more information, see the relevant configuration documents. Restart the apache service.
-- Create a test package:
Create or replace package cux_html_report
Function T (p_size in number, p_content in varchar2) return varchar2;
Type c_ SQL is ref cursor;
Procedure main;
Procedure html_header (p_title in varchar2 );
Procedure html_body (p_ SQL in varchar2, p_column_count in number, p_subtitle in varchar2, p_align in varchar2, p_black in varchar2 );
Procedure html_tail;
Procedure table_h (p_width in number, p_border in number, p_align in varchar2 );
Procedure table_t;
Procedure tr _ (p_col_count in number, p_column in varchar2, p_align in varchar2, p_black in varchar2 );
Procedure tr_h;
Procedure tr_t;
Procedure TD _ (p_col_count in number, p_content in varchar2, p_align in varchar2, p_black in varchar2 );
Procedure report_header (p_title in varchar2 );
Procedure report_subtitle (p_col_count in number, p_content in varchar2, p_align in varchar2, p_black in varchar2 );
Procedure report_tail (p_content in varchar2 );
End;
/
Create or replace package body cux_html_report
Function T (p_size in number, p_content in varchar2) return varchar2
I number: = 0;
V_out varchar2 (300): = '';
P_cont varchar2 (1000): = p_content;
Begin
For II in 1 .. p_size Loop
Select substr (p_cont, 0, decode (instr (p_cont, ';'), 0, length (p_cont), instr (p_cont, ';')-1 ))
Into v_out from dual;
P_cont: = substr (p_cont, instr (p_cont, ';') + 1 );
I: = I + 1;
If I = p_size then
Return v_out;
End if;
End loop;
End;
/*************************************** ****************************************
Main Program
Paging issues to be handled
**************************************** ****************************************/
Procedure main
Begin
Html_header ('My test Report ');
Report_header ('My title ');
Html_body ('select T. owner | '';'' | T. object_name | '';'' | T. object_id | '';'' | T. object_type | '';'' | T. owner
From all_objects t
Where T. Owner = ''dinya''
And rownum <= 100 ', 5,' sequence number; Object Name; ID; Type ', 'center',' <B> </B> ');
Report_tail ('here is the end of the report ');
Html_tail;
End;
-- Page header, fixed format
Procedure html_header (p_title in varchar2)
Begin
HTP. P ('
<! Doctype HTML public "-// W3C // dtd html 4.01 transitional // en">
<HTML>
<Head>
<Meta http-equiv = "Content-Type" content = "text/html; charset = gb2312">
<Title> '| nvl (TRIM (p_title), 'Untitled document') |' </title>
</Head>
<Body> ');
End;
-- Page body: displays data based on the SQL program and field count
Procedure html_body (p_ SQL in varchar2, p_column_count in number, p_subtitle in varchar2, p_align in varchar2, p_black in varchar2)
I number: = 0;
C c_ SQL;
V_column varchar2 (4000 );
Begin
Table_h (90,1, 'center ');
Report_subtitle (p_column_count, p_subtitle, p_align, p_black );
Open C for p_ SQL;
Loop
Fetch C into v_column;
Exit when C % notfound;
I: = I + 1;
Tr _ (p_column_count, v_column, 'left ','');
End loop;
Table_t;
End;
-- End of the page, fixed content
Procedure html_tail
Begin
HTP. P ('</body> End;
--------------------------------------------------------------------------------
-- Table header, table attributes
Procedure table_h (p_width in number, p_border in number, p_align in varchar2)
Begin
HTP. P ('<Table width = "' | p_width | '%" border = "' | p_border | '" align = "' | p_align | '"> ');
End;
-- End of table
Procedure table_t
Begin
HTP. P ('</table> ');
End;
--------------------------------------------------------------------------------
-- Table row
--------------------------------------------------------------------------------
Procedure tr _ (p_col_count in number, p_column in varchar2, p_align in varchar2, p_black in varchar2)
Begin
HTP. P ('<tr> ');
TD _ (p_col_count, p_column, p_align, p_black );
HTP. P ('</tr> ');
End;
Procedure tr_h
Begin
HTP. P ('<tr> ');
End;
Procedure tr_t
Begin
HTP. P ('</tr> ');
End;
-------------------------------------------------------------------------------
-- Table cell
-------------------------------------------------------------------------------
Procedure TD _ (p_col_count in number, p_content in varchar2, p_align in varchar2, p_black in varchar2)
V_cont varchar2 (300): = p_content;
V_out varchar2 (300): = '';
Begin
For I in 1 .. p_col_count Loop
Select substr (v_cont, 0, decode (instr (v_cont, ';'), 0, length (v_cont), instr (v_cont, ';')-1 ))
Into v_out from dual;
HTP. P ('<TD align = "' | p_align | '">' | substr (p_black, 1, 3) | v_out | substr (p_black, 4) | '</TD> ');
V_cont: = substr (v_cont, instr (v_cont, ';') + 1 );
End loop;
End;
------------------------------------------------------------------------------
-- Report Content
-----------------------------------------------------------------------------
Procedure report_header (p_title in varchar2)
Begin
Table_h (90,0, 'center ');
Tr _ (1, p_title, 'center', '<B> </B> ');
Table_t;
End;
Procedure report_subtitle (p_col_count in number, p_content in varchar2, p_align in varchar2, p_black in varchar2)
Begin
Tr_h;
TD _ (p_col_count, p_content, p_align, p_black );
Tr_t;
End;
Procedure report_tail (p_content in varchar2)
Begin
Table_h (90,0, 'center ');
Tr _ (1, p_content, 'left ','');
Table_t;
End;
----------------------------------------------------------------------------------
End;
/
SQL> @ D:/cux_html_report.pck
Package created.
Package body created.
SQL>
After compilation is successful, Use http: // dinya: 7777/SS/cux_html_report.main to view the report.
If you use owa_util.mime_header (ccontent_type => 'application/vnd. MS-Excel ') during the main process, you can directly generate the data to excel.
For example:
Procedure main
Begin
Owa_util.mime_header (ccontent_type => 'application/vnd. MS-Excel ');
Html_header ('My test Report ');
You can modify the above program as needed.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
