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]… Continue reading list targets

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 10 CLARK MILLER KING 20 SMITH FORD ADAMS SCOTT JONES 30 ALLEN WARD MARTIN BLAKE TURNER JAMES To get all employees, it is awfully easy ([xml](gc emp.xml)).EMPTABLE.DEPT.EMPLIST.ENAME… Continue reading xml to csv in powershell

xml and powershell : using XPATH

I wrote about powershell [xml] yesterady : xml and powershell Let’s see how to use XPATH expressions in Powershell John Jack 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 I must say I felt with love in… Continue reading xml and powershell : using XPATH

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 John Jack 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 John Jack $ echo ‘cat //emplist/emp[@no=”1″]/ename/text()’| xmllint –shell foo.xml | sed -n 3p John I like this !

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()… Continue reading select from comma-separated list

Categorized as 11g, sql, xml Tagged

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 | |… Continue reading high cost

old-fashion listagg

Yesterday I had my first session about XML, today I have one about SQL Model Ok, it was the first time I spoke about XML so I did not really now where to focus. XML is so big, you have XQUERY, XPATH, dozens of XML functions in the database. One of the XML function is… Continue reading old-fashion listagg

Categorized as sql, xml Tagged

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>’); DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Categorized as sql, xml

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( ‘ Table DEPT Table DEPT in HTML format DEPTNO DNAME LOC ‘ ) ) ,… Continue reading select in HTML format

Categorized as sql, xml

select from xml

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… Continue reading select from xml

Categorized as sql, xml

My book is available on

One year ago I started writing a book on SQL. Writing a book is an amazing amount of work and I am glad I got helped from my five reviewers Chen, Andrew, Tom, Marco and Lutz. It will ship in December 2008.

alert log in xml format

The alert log is in xml format in Oracle 11g. If you want to parse the content of the file and use the XML functions to retrieve the data, you can use this way : SQL> create or replace directory alert as 2 ‘/app/oracle/diag/rdbms/lsc01/lsc01/alert’; Directory created. SQL> var c clob SQL> declare 2 b bfile… Continue reading alert log in xml format

Obfuscation contest

I have been challenged to participated (my script : lsc2.sql) and now challenged to explain my query by Chen Ok, I give a try. To make the query unreadable and unformatable I used no space, no new line, and I started by q’

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… Continue reading isNumber in sql

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… Continue reading get Nth column of a table

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;… Continue reading select from a comma-separated string

csv with XML revisited…

Special thanks to Tom for pointing and Michaels for fixing the missing manager of King in my previous post : csv format with select Ok, here is a my PL/SQL table function. create or replace type tv as table of varchar2(4000); / create or replace function CSV(sqlQuery varchar2) return tv pipelined is ctx dbms_xmlgen.ctxhandle; begin… Continue reading csv with XML revisited…

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> 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… Continue reading search for a string in all tables of a schema