Forum Stats

  • 3,768,537 Users
  • 2,252,807 Discussions
  • 7,874,614 Comments

Discussions

Generate SQL queries in HTML File with PL/SQL

Moonlight
Moonlight Member Posts: 176 Blue Ribbon

Hello,

I would like to generate SQL queries results into HTML file with PL/SQL program... so I developped the script below .... But it's not working ☹️ ... Any one know how to do that?

declare

html_file utl_file.file_type;

re clob;

begin

html_file:=utl_file.fopen ('TEST_DIR', 'html_test.html', 'W');

SELECT '<HTML>

     <title> Database Parameter </title>

     <table cols="3" border=2>

     <tr><th>Param_Name</th><th>Param_Value</th></tr>

     <tr><td> '||name||' </td><td> ' ||value||' </td></tr>

     </table>

     </HTML>' into re

 FROM v$parameter

 where name in ('spfile','controle_files','instance_name';

utl_file.put_line(html_file,re );

end;


Thank you.

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,490 Blue Diamond
    edited Oct 26, 2021 1:16AM Accepted Answer

    I wouldn't recommend using Frank's approach as it won't escape reserved special markup characters correctly and you'll get errors.

    You could generate your HTML via XML DB, (your sample is in XHTML form anyway)

    select XMLElement("HTML"
           , XMLElement("title", 'Database Parameter')
           , XMLElement("table"
             , XMLAttributes(3 as "cols", 2 as "border")
             , XMLElement("tr"
               , XMLElement("th", 'Param_Name')
               , XMLElement("th", 'Param_Value')
               )
             , XMLAgg(
                 XMLElement("tr"
                 , XMLElement("td", name)
                 , XMLElement("th", value)
                 )
               )
             )
           ) RES
    from v$parameter
    where name in ('spfile','controle_files','instance_name');
    
    RES
    --------------------------------
    <HTML><title>Database Parameter</title><table cols="3" border="2"><tr><th>Param_Name</th><th>Param_Value</th></tr><tr><td>spfile</td><th>C:\ORACLE\PRODUCT\18.0.0\DBHOMEXE\DATABASE\SPFILEXE.ORA</th></tr><tr><td>instance_name</td><th>xe</th></tr></table></HTML>
    
    Moonlight

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,208 Red Diamond

    Hi, @Moonlight

    Formatting text (such as putting it into an HTML format) is best done by the front end, not the database. Many front-end tools have built-in features for getting HTML output. In SQL*Plus, for example, there's SET MARKUP.

    If you must do this in the back-end (e.g., using PL/SQL, like you tried) then SEELECT ... INTO won't help. SELECT ... INTO is only useful when a query produces exactly one row of output. You may want to do something like this instead:

    DECLARE
    	html_file utl_file.file_type;
    BEGIN
    	html_file := utl_file.fopen ('TEST_DIR', 'html_test.html', 'W');
    	utl_file.put_line ( html_file
    			  , '<HTML>
       			    <title> Database Parameter </title>
       			    <table cols="3" border=2>
       			    <tr><th>Param_Name</th><th>Param_Value</th></tr>'
    			  );
    	FOR p_row IN   ( SELECT   name, value
    	   	    	 FROM	  v$parameter
     			 WHERE 	  name in ( 'spfile'
    			 	  	  , 'controle_files'
    					  , 'instance_name'
    					  )
    			 ORDER BY name
    	   	    )
    	LOOP
    	    utl_file.put_line    ( html_file
    				 , '<tr><td> '    ||
    				    p_row.name	  ||
    				    ' </td><td> ' ||
    				    p_row.value   ||
    				    ' </td></tr>'
    				 );
    
    	END LOOP;
    	utl_file.put_line ( html_file
    			  , '</table>
       			    </HTML>'
    			  );
    	utl_file.fclose (html_file);
    END;
    /
    

    depending on exactly what you want to do.

  • Paulzip
    Paulzip Member Posts: 8,490 Blue Diamond
    edited Oct 26, 2021 1:16AM Accepted Answer

    I wouldn't recommend using Frank's approach as it won't escape reserved special markup characters correctly and you'll get errors.

    You could generate your HTML via XML DB, (your sample is in XHTML form anyway)

    select XMLElement("HTML"
           , XMLElement("title", 'Database Parameter')
           , XMLElement("table"
             , XMLAttributes(3 as "cols", 2 as "border")
             , XMLElement("tr"
               , XMLElement("th", 'Param_Name')
               , XMLElement("th", 'Param_Value')
               )
             , XMLAgg(
                 XMLElement("tr"
                 , XMLElement("td", name)
                 , XMLElement("th", value)
                 )
               )
             )
           ) RES
    from v$parameter
    where name in ('spfile','controle_files','instance_name');
    
    RES
    --------------------------------
    <HTML><title>Database Parameter</title><table cols="3" border="2"><tr><th>Param_Name</th><th>Param_Value</th></tr><tr><td>spfile</td><th>C:\ORACLE\PRODUCT\18.0.0\DBHOMEXE\DATABASE\SPFILEXE.ORA</th></tr><tr><td>instance_name</td><th>xe</th></tr></table></HTML>
    
    Moonlight
  • User_H3J7U
    User_H3J7U Member Posts: 651 Silver Trophy

    escape reserved special markup characters

    htf.escape_sc()

  • Moonlight
    Moonlight Member Posts: 176 Blue Ribbon

    Thank you so much all of you for your answer ...

    It's possible to make graph with pl/sql then generate this graph in html result file?

    For exemple, display the tablespaces' sizes in pie chart ?