Forum Stats

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

Discussions

Categories

Generate SQL queries in HTML File with PL/SQL

Moonlight
Moonlight Member Posts: 176 Blue Ribbon
in SQL & PL/SQL

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.

· Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • User_H3J7U
    User_H3J7U Member Posts: 651 Silver Trophy

    escape reserved special markup characters

    htf.escape_sc()

    · Share on TwitterShare on Facebook
  • 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 ?

    · Share on TwitterShare on Facebook