Same as Select in html format, but with XMLTABLE instead of XMLTRANSFORM
select
xmlroot(
xmlelement(
"table",
xmlconcat(
xmltype(
'<tr><th>DEPTNO</th><th>DNAME</th><th>LOC</th></tr>'),
xmlagg(column_value)
)
),version '1.0'
)
from
xmltable('
for $f in ora:view("LSC_DEPT")
return
<tr>
<td>{$f/ROW/DEPTNO/text()}</td>
<td>{$f/ROW/DNAME/text()}</td>
<td>{$f/ROW/LOC/text()}</td>
</tr>');
XMLROOT(XMLELEMENT("TABLE",XMLCONCAT(XMLTYPE('<TR>
————————————————–
<?xml version="1.0"?>
<table>
<tr>
<th>DEPTNO</th>
<th>DNAME</th>
<th>LOC</th>
</tr>
<tr>
<td>10</td>
<td>ACCOUNTING</td>
<td>NEW YORK</td>
</tr>
<tr>
<td>20</td>
<td>RESEARCH</td>
<td>DALLAS</td>
</tr>
<tr>
<td>30</td>
<td>SALES</td>
<td>CHICAGO</td>
</tr>
<tr>
<td>40</td>
<td>OPERATIONS</td>
<td>BOSTON</td>
</tr>
</table>
addthis_url = ‘http%3A%2F%2Flaurentschneider.com%2Fwordpress%2F2008%2F11%2Fselect-in-html-format-xquery-variant.html’;
addthis_title = ’select+in+HTML+format%2C+XQUERY+variant’;
[...]
Last Wednesday I selected data from an HTML table : select from xml
Today, let’s try the opposite, generate an HTML table from a SQL query
select
XMLSERIALIZE(
DOCUMENT
XMLROOT(
XMLTRANSFORM(
XMLTYPE(
CURSOR(
SELECT * FROM DEPT
)
),
XMLTYPE.CREATEXML(
'<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<xsl:template match="/">
<html>
<head>
<title>Table DEPT</title>
</head>
<body>
<p>Table DEPT in HTML format</p>
<table border="1">
<tr>
<th align="right">DEPTNO</th>
<th align="left">DNAME</th>
<th align="left">LOC</th>
</tr>
<xsl:for-each select="/ROWSET/ROW">
<tr>
<td align="right"><xsl:value-of select="DEPTNO"/></td>
<td align="left"><xsl:value-of select="DNAME"/></td>
<td align="left"><xsl:value-of select="LOC"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>'
)
)
, VERSION '1.0')
)
from DUAL;
Table DEPT [...]
My wife is so happy that an African president is ruling the world for the next four years !
Ok, here is a simple exercice :
How do you select from an HTML file ?
Take an example
The table of departments
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
create table T of XMLTYPE;
insert into T values(
XMLTYPE(
'<?xml version="1.0" encoding="ISO-8859-1"?>
<html>
<head>
<title>Employee table</title>
</head>
<body>
<p>The table of departments</p>
<table border="1">
<tr>
<td>10</td>
<td>ACCOUNTING</td>
<td>NEW YORK</td>
</tr>
<tr>
<td>20</td>
<td>RESEARCH</td>
<td>DALLAS</td>
</tr>
<tr>
<td>30</td>
<td>SALES</td>
<td>CHICAGO</td>
</tr>
<tr>
<td>40</td>
<td>OPERATIONS</td>
<td>BOSTON</td>
</tr>
</table>
</body>
</html>'));
select deptno, [...]
duplicate of echo does not accept end of arguments operator
addthis_url = ‘http%3A%2F%2Flaurentschneider.com%2Fwordpress%2F2008%2F10%2Fecho-does-not-accept-end-of-arguments-signal.html’;
addthis_title = ‘echo+does+not+accept+end+of+arguments+signal’;
addthis_pub = ”;
Let’s start with an example :
$ cat AI
#!/usr/bin/bash
while :
do
echo "What's your name ?"
read a
if [ ! $a ]
then
break
fi
echo "Your name is :"
echo $a
echo
done
echo "Bye"
$ ./AI
What's your name ?
Jo
Your name is :
Jo
What's your name ?
Jack
Your name is :
Jack
What's your name ?
-e
Your name is :
What's your name ?
Bye
This artificial intelligence is not very intelligent, it cannot recognize [...]
Have fun with this caps-lock user question :
asktom:Logic behind the DENSE_RANK
This is one more statement on how to not use order by
like in
select ename, deptno, row_number() over (order by 1)
from emp order by 2;
ENAME DEPTNO ROW_NUMBER()OVER(ORDERBY1)
———- ———- ————————–
CLARK 10 1
KING 10 2
MILLER 10 3
JONES 20 4
FORD 20 5
ADAMS 20 6
SMITH 20 7
SCOTT 20 8
WARD 30 9
TURNER 30 10
ALLEN 30 11
JAMES 30 12
BLAKE 30 13
MARTIN 30 14
According to the doc, order by [...]
Whether you need to write in Chinese אני אוהב אותך
Reverse your characters ʎuunɟ
Play some chess ♔ or do some maths ≲∀∃∄∑
Unicode can helps you. It can be stored in the database, in email, in plain-text files.
Read more :
- Unicode Charmap
- Unicode Home Page
addthis_url = ‘http%3A%2F%2Flaurentschneider.com%2Fwordpress%2F2008%2F10%2F%25e2%2593%2595%25e2%2593%25a4%25e2%2593%259d-%25e2%2593%25a6%25e2%2593%2598%25e2%2593%25a3%25e2%2593%2597-%25e2%2593%25a4%25e2%2593%259d%25e2%2593%2598%25e2%2593%2592%25e2%2593%259e%25e2%2593%2593%25e2%2593%2594.html’;
addthis_title = [...]
Note:
Performance Tuning Guide
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer [...]
I just downloaded and installed osdm
Have a look at my first screen of the ERD generated from my Scott schema
addthis_url = ‘http%3A%2F%2Flaurentschneider.com%2Fwordpress%2F2008%2F10%2Foracle-sql-developer-data-modeling.html’;
addthis_title = ‘Oracle+SQL+Developer+Data+Modeling’;
addthis_pub = ”;
$ sqlplus scott/tiger@DEVL
SQL> select * from all_db_links;
no rows selected
SQL> select * from dual@PROD;
D
-
X
Hey, why does this work???
Ok, after some research I found out that this seems to be an implicit loopback database link. The fact that the DEVL database has the global name set to PROD is just to annoy more
SQL> select * from [...]