For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Course Of The Month
8 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
ORACLE XML From SELECT statement
Posted on 2011-03-08
Hello Experts,
I have a table; ErrTbl with 4 columns: Process, Num, Desc , StartTime.
What I like to do, is send out an email and have the table results in a nice format. In sql server, I'm able to pull the results with the following query:
SELECT
td =[Process], '',
td =[Num], '',
td =[Desc], '',
td =[StartTime], '',
td =[StartTime], ''
FROM ErrTbl
FOR XML PATH('tr'), TYPE
I would like to get similar results using Oracle 10g. I've played around with DBMS_XMLQuery, but I'm not getting the results that I'm looking for.
Any suggestions are greatly appreciated.
Thank you
I have a table; ErrTbl with 4 columns: Process, Num, Desc , StartTime.
What I like to do, is send out an email and have the table results in a nice format. In sql server, I'm able to pull the results with the following query:
SELECT
td =[Process], '',
td =[Num], '',
td =[Desc], '',
td =[StartTime], '',
td =[StartTime], ''
FROM ErrTbl
FOR XML PATH('tr'), TYPE
I would like to get similar results using Oracle 10g. I've played around with DBMS_XMLQuery, but I'm not getting the results that I'm looking for.
Any suggestions are greatly appreciated.
Thank you
[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
8-
8
7
24 Comments
I'm attaching a screen shot of what I would like to do.
EmailRslt.JPG
EmailRslt.JPG
by using SQL Plus:
set markup HTML on
spool index.html
select * from <tablename>;
spool off
set markup HTML off
set markup HTML on
spool index.html
select * from <tablename>;
spool off
set markup HTML off
Active 1 day ago
With the sqlserver code above and that screenshot it looks like a simple html table not really xml.
A picture of the expected results doesn't help much since it depends on the display tool.
Can you provide the specific xml/html you want from an Oracle query for those rows in a table?
A picture of the expected results doesn't help much since it depends on the display tool.
Can you provide the specific xml/html you want from an Oracle query for those rows in a table?
My plan is to send out an email, and here's the body for the email:
Let me know if this is what you're asking for.
'<H1><FONT FACE="Geneva, Arial" SIZE=3>An Error Has Occurred</FONT></H2><table
Let me know if this is what you're asking for.
'<H1><FONT FACE="Geneva, Arial" SIZE=3>An Error Has Occurred</FONT></H2><table
apart from my simple approach above, you can do advance formatting on the html codes based on this link:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch7.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch7.htm
you need to write Oracle procedure to send emails:
1- http://arunrathod.blogspot.com/2008/05/sending-email-using-plsql.html
2- http://www.techrepublic.com/blog/programming-and-development/sending-e-mail-from-an-oracle-database-with-utlsmtp/515
1- http://arunrathod.blogspot.com/2008/05/sending-email-using-plsql.html
2- http://www.techrepublic.com/blog/programming-and-development/sending-e-mail-from-an-oracle-database-with-utlsmtp/515
Active 1 day ago
Aside from sqlplus, Oracle has several very powerful methods of generating XML.
It can generate just about any variation you need. If the sqlplus/cmdline approach is what you need then the link above will probably give you what you need.
If you need to issue a select statement in some other tool/platform/??? and need a single select to generate the table, please let us know the tool/platform/??? you want to use.
For example: PL/SQL stored procedures can send an email directly from the database.
Again, not a sqlserver person are you saying the initially provided simple select statement will generate the html table you posted complete with headings?
It can generate just about any variation you need. If the sqlplus/cmdline approach is what you need then the link above will probably give you what you need.
If you need to issue a select statement in some other tool/platform/??? and need a single select to generate the table, please let us know the tool/platform/??? you want to use.
For example: PL/SQL stored procedures can send an email directly from the database.
Again, not a sqlserver person are you saying the initially provided simple select statement will generate the html table you posted complete with headings?
Active 1 day ago
>>you need to write Oracle procedure to send emails:
If you want them to use that approach then you need a solution that does not use sqlplus that you suggested in the previous posts.
If you want them to use that approach then you need a solution that does not use sqlplus that you suggested in the previous posts.
We currently have a package that uses UTL_MAIL.SEND to send out an email. Within that proc, we supply the message, which is the body of the email, and this is where I'd like to place XML.
I just ran the sqlplus/cmdline approach and the results are EXACTLY what I'm looking for: the results from SELECT * FROM Table look perfect. However, can I get that result into the message body to send via UTL_Mail?
I just ran the sqlplus/cmdline approach and the results are EXACTLY what I'm looking for: the results from SELECT * FROM Table look perfect. However, can I get that result into the message body to send via UTL_Mail?
yes - i agree with you.
i'm not clear with the question. he is asking for XML yet showing sample of HTML. well - i added the solution to generate an email via Oracle pl/sql :)
cheers.
OP
i'm not clear with the question. he is asking for XML yet showing sample of HTML. well - i added the solution to generate an email via Oracle pl/sql :)
cheers.
OP
DBL9SSG,
glad that the HMTL code in sql plus helps.
this is a great document of sending email using UTL_Mail
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_mail.htm
first you need to grant the user on UTL_MAIL : grant execute on utl_mail to <user>;
glad that the HMTL code in sql plus helps.
this is a great document of sending email using UTL_Mail
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_mail.htm
first you need to grant the user on UTL_MAIL : grant execute on utl_mail to <user>;
OP Zaharin,
I just realized that I'm typing XML, but I'm thinking HTML. My apologies, its been a long day.
You are correct, I'm giving you HTML earlier.
I just realized that I'm typing XML, but I'm thinking HTML. My apologies, its been a long day.
You are correct, I'm giving you HTML earlier.
Active 1 day ago
You realize that utl_mail.send is limited to a 32K message body. If you need more than that you need to use UTL_SMTP.
I've never tried to simulate the sqlplus HTML reporting in pl/sql and cannot find a quick link that says how.
If no other Expert can provide the solution, I'll see what I can come up with when I get back to work tomorrow.
I've never tried to simulate the sqlplus HTML reporting in pl/sql and cannot find a quick link that says how.
If no other Expert can provide the solution, I'll see what I can come up with when I get back to work tomorrow.
Thank you all for your inputs, but I have another quertion for OP Zaharin:
The link you sent me to Generate HTML Reports, can I generate the HTML from within PL/SQL and assign that HTML to a LONG variable ?
SlightWV, thanks for the heads up on 32K limit. I'll investigate on using UTL_SMTP. I'll certaintly wait to see if you can find anything on HTML reporting in PL/SQL.
The link you sent me to Generate HTML Reports, can I generate the HTML from within PL/SQL and assign that HTML to a LONG variable ?
SlightWV, thanks for the heads up on 32K limit. I'll investigate on using UTL_SMTP. I'll certaintly wait to see if you can find anything on HTML reporting in PL/SQL.
Active 1 day ago
Don't use LONG. Use CLOB. They are a lot easier to deal with.
Since you really have no need to generate the entire HTML in a single statement I just thought of something.
Just brute force the output.
Check the sample below (not tested... pseudo code but should be close). You just need to add the UTL_MAIL (or UTL_SMTP) code to send the message_body.
Once you get the UTL_SMTP code working you can change message_body to a CLOB and should be fine.
There is a lot of examples usint UTL_SMTP to send mail using Oracle.
Since you really have no need to generate the entire HTML in a single statement I just thought of something.
Just brute force the output.
Check the sample below (not tested... pseudo code but should be close). You just need to add the UTL_MAIL (or UTL_SMTP) code to send the message_body.
Once you get the UTL_SMTP code working you can change message_body to a CLOB and should be fine.
There is a lot of examples usint UTL_SMTP to send mail using Oracle.
drop table tab1 purge;
create table tab1 (col1 char(1), col2 char(1), col3 char(1));
insert into tab1 values('a','b','c');
create or replace procedure checkErrors
is
message_body varcahr2(32767);
begin
message_body := '<table>';
message_body := message_body || '<tr><th>Col1</th><th>Col2</th><th>Col3</th></tr>';
for i in (select col1,col2,col3 from tab1) loop
message_body := message_body ||
'<tr><td>' || col1 || '</td><td>' || col2 || '</td><td>' || col3 || '</td></tr>';
end loop;
message_body := '</table>';
end;
/
Slight-
Got your code, basically, we'll have to generate the HTML ourselves? Â Oracle doesn't have a way of doing it more simple?
I'm going to accept multiple solutions and close this out, but if you think of anything else, drop me a line at my personal email: <email removed by slightwv>.
Fellas, you guys are Awesome and thank you again for your input.
Got your code, basically, we'll have to generate the HTML ourselves? Â Oracle doesn't have a way of doing it more simple?
I'm going to accept multiple solutions and close this out, but if you think of anything else, drop me a line at my personal email: <email removed by slightwv>.
Fellas, you guys are Awesome and thank you again for your input.
Active 1 day ago
Oracle and sqlserver compete head to head and typically can do anything the other one can.
Are you saying that you can issues the original select statement posted as-is to generate an html table and all you need to do is issue a simple email call in a stored procedure to get the output you posted?
If so, I'm a little impressed with sqlserver.
Are you saying that you can issues the original select statement posted as-is to generate an html table and all you need to do is issue a simple email call in a stored procedure to get the output you posted?
If so, I'm a little impressed with sqlserver.
SLightwv-
Here's a piece that we use on sqlserver:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1><FONT FACE="Geneva, Arial" SIZE=3>An Error Has Occurred</FONT></H2>' +
N'<table border="1">' +
N'<th>COLUMNNAME1</th>' +
CAST (
(
SELECT
td =COLUMNAME1, ''
FROM SQLSERVERTABLE
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)
) + N'</table>' +
N'<br/>'
--Disable Wrapping In Table
SELECT @tableHTML = REPLACE(@tableHTML,'<td','
--Change font style and size
SELECT @tableHTML = REPLACE(@tableHTML,'<td nowrap>','<td nowrap><FONT FACE="Geneva, Arial" SIZE=1>')
SELECT @tableHTML = REPLACE(@tableHTML,'</TD',
Finally, we use @TableHTML to send on message body.
We can use something similiar from what you sent me last, so I greatly appreciate pointing me the right direction.
Here's a piece that we use on sqlserver:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1><FONT FACE="Geneva, Arial" SIZE=3>An Error Has Occurred</FONT></H2>' +
N'<table border="1">' +
N'<th>COLUMNNAME1</th>' +
CAST (
(
SELECT
td =COLUMNAME1, ''
FROM SQLSERVERTABLE
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)
) + N'</table>' +
N'<br/>'
--Disable Wrapping In Table
SELECT @tableHTML = REPLACE(@tableHTML,'<td','
--Change font style and size
SELECT @tableHTML = REPLACE(@tableHTML,'<td nowrap>','<td nowrap><FONT FACE="Geneva, Arial" SIZE=1>')
SELECT @tableHTML = REPLACE(@tableHTML,'</TD',
Finally, we use @TableHTML to send on message body.
We can use something similiar from what you sent me last, so I greatly appreciate pointing me the right direction.
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 9 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.