• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

How to write output to multiple html tables?

I have a ksh script that writes data from one view to a table in html file. I want to createan html file with multiple tables with data from different views in my database. how can I modify my script to accomplish that?

#!/usr/bin/ksh

export ORACLE_SID=DBName
ORAENV_ASK=NO
. oraenv
sqlplus -s /nolog <<EOF
CONNECT usr1/pwd
SET LINES 4000 TRIMS ON FEED OFF PAGES 49999
SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
<STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO OFF
SPOOL myfile.html
SELECT * FROM my_view;
SPOOL OFF
EOF

Open in new window

0
YZlat
Asked:
YZlat
  • 7
  • 6
  • 3
3 Solutions
 
sdstuberCommented:
for each view spool to a new file


spool one.html
select * from view1;
spool off

spool two.html
select * from view2
spool off

etc
0
 
slightwv (䄆 Netminder) Commented:
Not sure about your exact requirements but here goes my initial thoughts:

multiple selects in the same spool file:
SPOOL myfile.html
SELECT * FROM my_view;
SELECT * FROM my_view1;
SPOOL OFF


Or a union but the columns form all the views need to be the same in number and data types:
SPOOL myfile.html
SELECT * FROM my_view
union all
SELECT * FROM my_view1;
SPOOL OFF

Of if you want multiple html files, multiple spools
SPOOL myfile.html
SELECT * FROM my_view;

SPOOL myfile1.html
SELECT * FROM my_view1;
SPOOL OFF
0
 
YZlatAuthor Commented:
Is it possible to also right a heading for each table that would go right before each table?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
slightwv (䄆 Netminder) Commented:
>>Is it possible to also right a heading for each table that would go right before each table?

Using which method?  I'm still not sure exactly what your desired results are.

Headings are based on the column names.  For the methods above except for the union, column names should be what they are in the tables.
0
 
YZlatAuthor Commented:
I want something like that:

                Title here
---------------------------------------
|  field1  | field2    |firld3       |
---------------------------------------
|             |              |             |
____________________________


          Title for the second table
---------------------------------------
|  field1  | field2    |firld3       |
---------------------------------------
|             |              |             |
____________________________
0
 
slightwv (䄆 Netminder) Commented:
>>I want something like that:

Still not answering all the questions:
One file for all tables, each table in a separate file?
0
 
sdstuberCommented:
spool one.html
select 'title 1' from dual;
select * from view1;
spool off

spool two.html
select 'title 2' from dual;
select * from view2
spool off

etc


you could, instead of using select 'title' from dual, use sql*plus formatting commands like TTITLE or BTITLE
0
 
YZlatAuthor Commented:
let me try that
0
 
slightwv (䄆 Netminder) Commented:
You realize that if you answered the questions asked, we could provide a pretty close to 100% accurate answer.
0
 
YZlatAuthor Commented:
one file for all tables and there could be more than 2 tables
0
 
slightwv (䄆 Netminder) Commented:
As sdstuber posted in http:#a39174838, use TTITLE.

#!/usr/bin/ksh

export ORACLE_SID=DBName
ORAENV_ASK=NO
. oraenv
sqlplus -s /nolog <<EOF
CONNECT usr1/pwd
SET LINES 4000 TRIMS ON FEED OFF PAGES 49999
SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
<STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO OFF
SPOOL myfile.html
TTITLE CENTER 'Title here'
SELECT * FROM my_view;

TTITLE CENTER 'Title for the second table'
SELECT * FROM second_table;


SPOOL OFF
EOF

Open in new window

0
 
YZlatAuthor Commented:
slightwv this is perfect! Thank you!
0
 
YZlatAuthor Commented:
slightwv this is perfect! Thank you!
0
 
sdstuberCommented:
wouldn't a split be in order?
0
 
slightwv (䄆 Netminder) Commented:
I agree that a split should be awarded since sdstuber posted TTITLE.

I just showed you how to implement it once I got the complete requirements.
0
 
YZlatAuthor Commented:
sdstuber, I am sorry, I didn't notice you were the one who posted TTITLE first!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now