CODEWITHSUNDEEP
htmloracle-databasereportsqlplus
dovka
dovka

Reputation: 1061

Oracle sqlplus HTML report - alternating rows color

I use Oracle sqlplus "set markup html on" to quickly convert query output into HTML report -

It's very simlpe way to publish database report online.

I'm missing however one thing - alternating colors for every other row, This is especially helpful while viewing wide reports.

is there any way to embed HTML color for every row, like make it dependent on mod(rownum/2) - even/odd row number ?

thank you !

Upvotes: 2

Views: 12899

Answers (4)

Yordan Georgiev
Yordan Georgiev

Reputation: 5430

/* File: sqlplus-runner-csv.sql v1.0.0 docs at the end */
/* do not print echo commands */
set echo off
/* do not require feedback from the user */
set feedback off
/* set a nice prompt */
set sqlprompt ''
/* -- separate columns with a comma */
set colsep ';'
/* -- only one header row */
set pagesize 0   
/*-- remove trailing blanks */
set trimspool on 
/* REMOVE THE WHITE SPACE */
set trimout on
/* -- this may or may not be useful...depends on your headings. */
set headsep ON
/* how- long the line should be */
set linesize 3000   
/* print the headers as well */
set heading on
/* request full html for output */
/* set markup html on spool off */

/* define the file where the output of the results should be stored */
spool "&1..html"

-- START SQL
select '<html> <head> <title>tables</title>   <style type="text/css">     table tbody tr td {       background-color: #eee;     }     table tr.odd  td {       background-color: #fff;     }   </style> </head><body><table>
' from dual ; 
-- specify grey ( #CCC ) and white colors 
-- open the row 
select '<tr class="' 
|| CASE mod(rownum,2) WHEN 0 THEN 'odd' ELSE 'even' END
|| '">' 
-- print a single table cell 
|| ' <td>' || OWNER || '</td>' 
-- print another single table cell
|| ' <td>' || TABLE_NAME || '</td>'        
-- close the row 
|| '</tr>'        FROM 
-- IF YOU NEED ORDER BY CLAUSE AS WELL, 
( 
SELECT OWNER , TABLE_NAME FROM ALL_TABLES ORDER BY OWNER , TABLE_NAME
) 
; 



select '</table></body></html>' from dual ; 
-- STOP SQL 

spool off ; 

/* exit the sqlplus tool */   
EXIT 0; 

/* 
Purpose: 
- to provide a single sql statement to run agains an oracle database 
- to output a single file passed as the first argument to the script 

VersionHistory: 
1.1.0 --- 2012.04.08 - 23:04:19  ---  ysg --- Added order by clause
1.0.0 --- 2012.04.08 - 22:07:19  ---  ysg --- Initial creation 
*/

Upvotes: 0

Yordan Georgiev
Yordan Georgiev

Reputation: 5430

:: File: sqlplus-runner-html
@ECHO off

:: go the run dir
cd %~dp0
:: this is the dir containing the batch file
set MyDir=%CD%

for %%A in (%0) do set MyDriveLetter=%%~dA
for %%A in (%0) do set MyPath=%%~pA
for %%A in (%0) do set MyName=%%~nA
for %%A in (%0) do set MyExtension=%%~xA

:: ECHO MYNAME IS %0
:: ECHO MyDriveLetter is %MyDriveLetter%
:: ECHO MyPath is %MyPath%
:: ECHO MyName is %MyName%
:: ECHO MyExtension is %MyExtension%

:: the ora script is the same as the file name + the sql extension
set OraSqlScript=%MyDir%\%MyName%.sql
:: define the oracle user name to use to connect to ora
set OracleUserName=MyOracleUserName
:: get user input
:: SET /P Pass=[Please, provide the password for the %OracleUserName%:]
:: the pass to use to connect to the service 
SET Pass=MySecretPass
:: define the name of the service 
set TnsServiceName=ORASERVICENAME
:: define the log file 
set LogFile=%MyDriveLetter%\%MyPath%\%MyName%.log
:: define the error log file 
set ErrorLogFile=%MyDriveLetter%\%MyPath%\%MyName%.error.log

:: clear the result of the previous runs 
del /q %MyDir%\*.lst
del /q %MyDir%\*.log


:: Action !!!
:: call with the listener service name 
:: sqlplus %OracleUserName%/%Pass%@%TnsServiceName @%OraSqlScript% %MyName% > %LogFile% 2>%ErrorLogFile%

:: call without the listener service name 
sqlplus %OracleUserName%/%Pass% @%OraSqlScript% %MyName% > %LogFile% 2>%ErrorLogFile%


:: Purpose: 
:: create a generic wrapper for executing oracle sql commands trough the sqplplus
:: client

:: Requirements:
:: Windows XP or newer
:: sqlplus client on local windows
:: access to oracle database 
:: %MyName%.sql with the proper sqlplus syntax to execute the sql statememtns


:: Usage: 
:: call from dos or double-click in Explorer

:: VersionHistory
:: 1.1.0 --- ysg --- Removed listener name 
:: 1.0.0 --- ysg --- Initial version

Upvotes: 0

dovka
dovka

Reputation: 1061

There is a great solution with CSS 3 (by expert web developer Leon Zinger). Before setting markup html on, inside of the head tag, print this:

prompt <style>tr:nth-child(2n) { background-color: #CCE6FF ;} tr:nth-child(2n+1) { background-color: LightGray;}</style>

(obviously the colors can be customized to meet your needs)

Here's another syntax to do alternation:

.row:nth-child(even) {
  background: #dde;
}
.row:nth-child(odd) {
  background: white;
}

Upvotes: 4

Tony Andrews
Tony Andrews

Reputation: 132590

I don't think there is, using SET MARKUP HTML ON. You would have to write your own markup something like:

select '<tr style="color:' 
       || CASE mod(rownum,2) WHEN 0 THEN 'red' ELSE 'green' END
       || '"><td>' || ename || '</td></tr>' data
from 
( select ename from emp order by ename );

Then add the surrounding table tags using PROMPTs or whatever.

Upvotes: 6

Related Questions