list targets

$p=new XML::Parser;$p->setHandlers(Start=>&b);$p->parsefile("-");sub b{($i,$e,%a)=@_;if($e eq"Target"){print$a{"NAME"}.":".$a{"TYPE"}}} — laurentsch (@laurentsch) September 19, 2017 As correctly pointed out by dhoogfr , the proper way to list targets on an agent is to use list target $ emctl config agent listtargets Oracle Enterprise Manager Cloud Control 13c Release 2 Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved. [, host] [, […]

xml to csv in powershell

Powershell is very strong with XML, to convert an XML document to something flat like a CSV file, it is incredibly powerfull. Let’s take a file called emp.xml <EMPTABLE>   <DEPT>     <DEPTNO>10</DEPTNO>                 <EMPLIST>       <ENAME>CLARK</ENAME>       <ENAME>MILLER</ENAME>       <ENAME>KING</ENAME>     </EMPLIST>   </DEPT>   <DEPT>     <DEPTNO>20</DEPTNO>     <EMPLIST>        <ENAME>SMITH</ENAME>        <ENAME>FORD</ENAME>        <ENAME>ADAMS</ENAME>        <ENAME>SCOTT</ENAME>        <ENAME>JONES</ENAME>     </EMPLIST>   </DEPT>   <DEPT>     <DEPTNO>30</DEPTNO>         <EMPLIST> […]

xml and powershell : using XPATH

I wrote about powershell [xml] yesterady : xml and powershell Let’s see how to use XPATH expressions in Powershell <emplist>   <emp no="1">     <ename>John</ename>   </emp>   <emp no="2">     <ename>Jack</ename>   </emp> </emplist> With the [xml] datatype, we create a navigator : (([xml](GC foo.xml)).psbase.createnavigator().evaluate( '//emplist/emp[@no="1"]/ename/text()' ))|%{$_.Value} John I have not been seduced by a Microsoft product since ages, but […]

xml and powershell

I wrote about the unix command-line utility xmllint there : extract xml from the command line Let’s do the same exercice in Powershell PS> gc foo.xml <emplist>   <emp no="1">     <ename>John</ename>   </emp>   <emp no="2">     <ename>Jack</ename>   </emp> </emplist> Simply use [xml] datatype ! (([xml](GC foo.xml)).emplist.emp|Where{$"1"}).ename John Powershell rules!

extract xml from the command line

I just discovered this morning this cool utility in my /bin directory : xmllint You can use it to extract values from your xml files within your shell scripts $ cat foo.xml <emplist>   <emp no="1">     <ename>John</ename>   </emp>   <emp no="2">     <ename>Jack</ename>   </emp> </emplist> $ echo 'cat //emplist/emp[@no="1"]/ename/text()'|   xmllint –shell foo.xml |   sed -n 3p John I […]

select from comma-separated list

This is asked over and over in the forums, but why not proposing an 11g solution here 😉 create table t(description varchar2(12) primary key,   numbers varchar2(4000)); insert into t(description, numbers) values ('PRIME','2,3,5,7'); insert into t(description, numbers) values ('ODD','1,3,5,7,9'); commit; DESCRIPTION NUMBERS PRIME 2,3,5,7 ODD 1,3,5,7,9 Now I want to unpivot numbers in rows select description,(column_value).getnumberval()   […]

high cost

What’s wrong with this query ? select (t6.column_value).getstringval() t6 from table(xmlsequence(extract(xmltype(‘<x/>’),’/x’))) t1, table(xmlsequence(t1.column_value))t2, table(xmlsequence(t2.column_value))t3, table(xmlsequence(t3.column_value))t4, table(xmlsequence(t4.column_value))t5, table(xmlsequence(t5.column_value))t6; T6 ———————— <x/> Elapsed: 00:00:00.01 Well, let’s check the plan : ——————————————————————– | Id  | Operation          | Rows  | Bytes | Cost (%CPU)| Time     | ——————————————————————– |   0 | SELECT STATEMENT   |    18E|    15E|    18E  (0)|999:59:59 | |   1 |  NESTED LOOPS      |    18E|    15E|    18E  (0)|999:59:59 | |   2 |   […]

select in HTML format, XQUERY variant

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>                             […]

select in HTML format

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="" > <xsl:template match="/">   <html>     <head>       <title>Table DEPT</title>     </head>     <body> […]

isNumber in sql

I tried this in 11g TABLE T X 123 -1.2e-3 abc select x,   to_number(     xmlquery('number($X)'       passing x as x       returning content)) n from t; X                N ——- ———- 123            123 -1.2e-3     -.0012 abc it is quite a common task to extract numbers from varchar2 and to dig out poor quality data. select x, to_number(x) from t; ERROR: […]

get Nth column of a table

I answered this question twice, once on otn forums and once on Here is the latest to get the third column of emp select column_name as name, extractvalue(column_value,’/ROW/’||column_name) as value from table(xmlsequence(cursor(select * from emp))), user_tab_columns where COLUMN_ID=3 and table_name=’EMP’ ; NAME VALUE     —- ———- JOB  CLERK     JOB  SALESMAN   JOB  SALESMAN   JOB  MANAGER   JOB  SALESMAN   JOB  MANAGER   JOB  MANAGER   JOB  ANALYST   JOB  PRESIDENT […]

select from a comma-separated string

This is one question I solved today with XML on the I have a table T Un,Trois,Cinq,Six Un,Deux,Quatre Trois Sept,Huit Un,Six I want to select un,deux,trois,quatre,cinq,six,sept,huit. I could well have written a plsql function like create or replace type c as object (value varchar2(40)); / create or replace type t_c as table of c; […]

csv format with select *

One more trick with xml. I want to get a semi-column separated format without having to specify the columns alter session set nls_date_format=’YYYY-MM-DD’; Session altered. select regexp_replace(column_value,’ *<[^>]*>[^>]*>’,’;’) from table(xmlsequence(cursor(select * from emp))); ;7369;SMITH;CLERK;7902;1980-12-17;800;20; ;7499;ALLEN;SALESMAN;7698;1981-02-20;1600;300;30; ;7521;WARD;SALESMAN;7698;1981-02-22;1250;500;30; ;7566;JONES;MANAGER;7839;1981-04-02;2975;20; ;7654;MARTIN;SALESMAN;7698;1981-09-28;1250;1400;30; ;7698;BLAKE;MANAGER;7839;1981-05-01;2850;30; ;7782;CLARK;MANAGER;7839;1981-06-09;2450;10; ;7788;SCOTT;ANALYST;7566;1987-04-19;3000;20; ;7839;KING;PRESIDENT;1981-11-17;5000;10; ;7844;TURNER;SALESMAN;7698;1981-09-08;1500;0;30; ;7876;ADAMS;CLERK;7788;1987-05-23;1100;20; ;7900;JAMES;CLERK;7698;1981-12-03;950;30; ;7902;FORD;ANALYST;7566;1981-12-03;3000;20; ;7934;MILLER;CLERK;7782;1982-01-23;1300;10;

How do i store the counts of all tables …

How do i store the counts of all tables … My answer to the question above using dbms_xmlgen SQL&gt; select   2    table_name,   3    to_number(   4      extractvalue(   5        xmltype(   6 dbms_xmlgen.getxml('select count(*) c from '||table_name))   7        ,'/ROWSET/ROW/C')) count   8  from user_tables; TABLE_NAME                      COUNT —————————— —— DEPT                                4 EMP                                14 BONUS                               0 SALGRADE                            5

search for a string in all tables of a schema

this is often asked on the forums. I also needed this a while ago while reverse engineering a database model. Here is my today solution: 1) select * and extract the first column found per table with regexp (10g) SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||’”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0; Enter […]