Home > Others

Test the HTML format report output by the database

Source: Internet
Author: User
Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

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.

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

Related Keywords:
wordpress the field php format html output php format output powershell format output emc test report template mysql format date output mysql select output format

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

What's Trending
Top 10 Tags
datastax versions naming convention zookeeper client class definition md5 microsoft sql server 2005 data structures exception handling error handling
Top 10 Keywords
microsoft download center down wordpress address url site address url wordpress address url windows installer 4 0 download 302 not found web address url definition site address url wordpress db2 integer mac os installation step by step pdf abbreviation for return

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More