Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to write output to multiple html tables?

Posted on 2013-05-16
17
Medium Priority
?
574 Views
Last Modified: 2013-05-21
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
Comment
Question by:YZlat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
17 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39172818
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 total points
ID: 39172828
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
 
LVL 35

Author Comment

by:YZlat
ID: 39172833
Is it possible to also right a heading for each table that would go right before each table?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39173024
>>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
 
LVL 35

Author Comment

by:YZlat
ID: 39174618
I want something like that:

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


          Title for the second table
---------------------------------------
|  field1  | field2    |firld3       |
---------------------------------------
|             |              |             |
____________________________
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39174629
>>I want something like that:

Still not answering all the questions:
One file for all tables, each table in a separate file?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 39174838
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
 
LVL 35

Author Comment

by:YZlat
ID: 39182569
let me try that
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39182687
You realize that if you answered the questions asked, we could provide a pretty close to 100% accurate answer.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39184640
one file for all tables and there could be more than 2 tables
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1200 total points
ID: 39184750
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
 
LVL 35

Author Comment

by:YZlat
ID: 39184781
slightwv this is perfect! Thank you!
0
 
LVL 35

Author Comment

by:YZlat
ID: 39184785
slightwv this is perfect! Thank you!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39184799
wouldn't a split be in order?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39184812
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
 
LVL 35

Author Comment

by:YZlat
ID: 39185516
sdstuber, I am sorry, I didn't notice you were the one who posted TTITLE first!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question