Recently used SQL*Plus Command to generate html file , Come across some interesting knowledge , By the way , It's convenient to forget these knowledge points when you need them later . A good memory is not as good as a bad pen !

Why use SQL*Plus Generate html file ?

 

Some people will definitely ask , I use SQL*Plus Why generate html What about the documents ? SQL*Plus It's a command tool in itself , Generate html Is the document necessary ? Here are some personal views , If there is any wrong , Please correct me :

There's a lot of it ORACLE DBA Are used to using SQL*Plus management 、 Maintain database , And I'm quite familiar with the command 、 Quite smooth , SQL*Plus Less occupied resources , Fast , Even today UI Tools (Toad、PL/SQL Developer..) It's a time of great popularity , There are still a lot of DBA or SQL*Plus It's widely used by fans SQL*Plus, however SQL*Plus Indeed, there are many deficiencies , For example, the format setting is complicated , If you don't format it , The output is quite unfriendly , in addition , The command interface needs to swipe the screen , Sometimes you need to roll forward , Look at the previous output ; occasionally , You need to save that output , It's convenient for you to analyze afterwards 、 Look up ; Sometimes you need to save your queries SQL. Now SQL*Plus It's exposed , Of course , You'll say I can use spool Save as text , Of course , But there are many ways that text files don't html The documents are convenient ......

How to use SQL*Plus Generate html file ?

Actually SQL*Plus Generate html The document is simple , Mainly used set markup html on( Specify whether to generate HTML Format ,ON Generate ,OFF No generation , The default is OFF) and spool command , As shown below

set markup html on;

spool salgrade.html

select * from scott.salgrade;  -- Need to check SQL Script 

spool off

set markup html off

exit

 

How to use SQL*Plus Make beautiful html file ?

In fact, the above cases are generated html Files are ugly , If you want to generate beautiful html file , Then the output of html Set the format .

MARK[UP] HTML [OFF|ON]

[HEAD text] [BODY text] [TABLE text]

[ENTMAP {ON|OFF}]

[SPOOL {OFF|ON}]

[PRE[FORMAT] {OFF|ON}]

As shown above , You can set HEAD、BODY 、TABLE The format of ,

[HEAD text]

Set up html Of documents head Format

[BODY text]

Set up html Of documents body Format

[TABLE text]

Set up html Of documents table Format

[ENTMAP {ON|OFF}]

Specified in the SQL * Plus Whether to use HTML Character entities such as &lt;, &gt;, &quot; and &amp; And so on <, >, " and & . The default setting is ON

In fact, to generate beautiful html file , Still lie in html Of css Set up 、 adjustment , This is a complex 、 Meticulous and flexible . Of course, there are some good-looking ones on the Internet css Set template , You can refer to 、 reference 、 Even take it directly to use .

SET MARKUP HTML ON ENTMAP OFF;

SET MARKUP HTML ON SPOOL ON -

HEAD '<title>My Html Report Test</title> -

<style type="text/css"> -

   table {border:1px solid #9ec9ec; width:60%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px} -

   td {border:solid #9ec9ec;  border-width:0px 1px 1px 0px; padding:4px 0px;}-

</style>'

 

SPOOL htmltest.html

SELECT * FROM SCOTT.SALGRADE;

 

SPOOL OFF;

SET MARKUP HTML OFF;

 

Some of them are beautiful html Template of the file .

SET MARKUP HTML ON SPOOL ON -

HEAD '<title>My Report</title> -

<style type="text/css"> -

  table {-

        font-family: arial, sans-serif;-

        font-size: 11px;-

        color: #333333;-

        border-width: 1px;-

        border-color: #3A3A3A;-

        border-collapse: collapse;-

    }-

     th {-

        border-width: 1px;-

        padding: 8px;-

        border-style: solid;-

        border-color: #517994;-

        background-color: #B2CFD8;-

    }-

    tr:hover td {-

        background-color: #DFEBF1;-

    }-

   td {-

        border-width: 1px;-

        padding: 8px;-

        border-style: solid;-

        border-color: #517994;-

        background-color: #ffffff;-

    }-

</style>'

 

spool htmltest.html

select * from scott.salgrade;

 

spool off;

set markup html off;

SET MARKUP HTML ON SPOOL ON -

HEAD '<title>My Report</title> -

<style type="text/css"> -

table{-

        font-family: verdana, arial, sans-serif;-

        font-size: 11px;-

        color: #333333;-

        border-width: 1px;-

        border-color: #3A3A3A;-

        border-collapse: collapse;-

    }-

 th {-

        border-width: 1px;-

        padding: 8px;-

        border-style: solid;-

        border-color: #FFA6A6;-

        background-color: #D56A6A;-

        color: #ffffff;-

    }-

 tr:hover td {-

        cursor: pointer;-

        background-color: #F7CFCF;-

    }-

 td {-

        border-width: 1px;-

        padding: 8px;-

        border-style: solid;-

        border-color: #FFA6A6;-

        background-color: #ffffff;-

    }-

</style>'

 

 

spool htmltest.html

select * from scott.salgrade;

 

spool off;

set markup html off;

Reference material :

https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch7.htm

https://blog.mafr.de/2006/12/27/creating-html-reports-with-sqlplus-2/

SQL*Plus Generate html More articles about the document

  1. PowerDesigner adopt SQL Statements generated PDM Document and will name and comment Make mutual conversion

    This article mainly introduces PowerDesigner adopt SQL Statements generated PDM Document and will name and comment Make mutual conversion Super detailed process ( Image & Text ), It has certain reference value , Interested friends can refer to 1. Software preparation soft ...

  2. PowerDesigner adopt SQL Statements generated PDM file

    Premise : I use it PowerDesigner15 The database for Mysql5.5 Steps are as follows : First step :File->New Model Click on OK Just create a template remarks : When creating a physical model DBMS The drop-down box is empty , ...

  3. Use SQL Reverse generation PDM file

    First, export the table structure , have access to Navicat perhaps DataGrip Generate SQL Use after the document PowerDesigner Specify the database type , choice SQL File can

  4. PHP adopt sql Generate CSV File and download ,PHP Realize file download

    /** * PHP adopt sql Generate CSV File and download * @param string $sql Inquire about sql, The result is a two-dimensional array * @param array $title data ,CSV Document title * @para ...

  5. SQL Server ---------- Detach database Generate .mdf file

    1. First look at the location of the database storage you want to detach Select the data database to be separated, right-click the mouse and click properties If you can't remember the advice     Copy it 2. Detach database       Generate   .mdf file Right click -----> Mission -- ...

  6. SQL Server How to restore a database in the absence of a filegroup

    SQL Server How to restore a database in the absence of a filegroup One . background I have one A library , because a,b The two tables have a large amount of data , So partition the table : In the A Move the library to a new cluster , I just backed up A The primary partition of the library was restored to ...

  7. WPF according to Oracle Table of database , Generate CS File widget

    The reason for developing gadgets : 1. Our company's development is for clients C#, The service side with Java, When the front and back platforms communicate with each other , use Oracle Database fields , Because the server side has a basis developed by the general manager of the company Oracle Database table generated cla ...

  8. FluentData- New lightweight ORM utilize T4 Templates Batch generation of multiple files Entity and business logic Code

    FluentData, It's a lightweight framework , Focus on performance and ease of use . Download address :FlunenData.Model utilize T4 Templates ,[MultipleOutputHelper.ttinclude] Batch generation of multiple files ...

  9. Java Generate XML file

    Our data in the database can be extracted to generate XML file , Convenient transmission . For example, there are Admin This table : Let's write a java Class represents admin data : package xmlDom.vo; import java. ...

Random recommendation

  1. Flowers (2012Noip Popularization Group No 3 topic )

    Flowers (flower.cpp/c/pas) [ Problem description ] Xiaoming's florist is newly opened , To attract customers , He wants to put a row of flowers in front of the flower shop , common m basin . By investigating customer preferences , Xiao Ming listed the customers' favorite n grow flowers , from 1 To n ...

  2. Delphi The character array is stored in a file

    TDMSRequestBuffer=object    Head:TDMSHead;    Data:array[0..2047] of char;    DataSize:Integer;    p ...

  3. JS Judge the mobile access page , According to mobile phone access or PC Visit jump

    When users visit your website , If it is PC End access , No treatment , If it's mobile or tablet access , Just jump to your own mobile page , This is doing webapp It's often used when I'm in the hospital , Share the code with you , I hope that's helpful . First , You need to quote in the page ...

  4. mysql Data type introduction

    One .int.bigint.smallint and tinyint The difference between bigint  from -2^63 (-9223372036854775808) To 2^63-1 (9223372036854 ...

  5. java Concurrent programming --Executor frame ( One )

    Abstract :        Eexecutor As a flexible and powerful asynchronous execution framework , It supports many different types of task execution strategies , It provides a standard method to decouple the task submission process from the task execution process , Based on producer - Consumer model , The thread that submits the task ...

  6. #WEB Safety Foundation : HTML/CSS | 0x11 Talking about GET and POST

    HTTP Medium GET and POST Request method I mentioned last time GET and POST, Now let's meet these new friends See figure POST and GET All of them send the data input by the user to the browser to the server , But in two different ways ,POST ...

  7. Border regression (bounding-Box regression)

    from :https://blog.csdn.net/zijin0802034/article/details/77685438 Why return to ? What is border regression ? How to do it ? Why is the border wide ...

  8. The correct way to generate floating point type , solve sqlachemy Float Floating point pits , Generate float Type , The length and accuracy are 0, The result of the query cannot be found !

    Problem description In the use of flask_sqlachemy when , to price Field selected Float type , For database mysql, After generating the database table , Find out from sqlalchemy import Float,Colum ...

  9. NodeJS Traverse the file production file list

    This article gives an example of NodeJS Traversal file production file list function . Share with you for your reference , As follows : functional requirement : In our work, we may often need to know the static file list publishing in the project , Search and write one by one , That would be too hard . If you want to know the files inside ...

  10. take excel Table import to mysql in

    // Import excel surface Method 1 : ) open Excel Save as CSV file ) Convert file encoding to utf8, use NotePad++ open csv file , Select Format — To utf8 Coding format — preservation ) stay MySQL Build table , The order of the fields is ...