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

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

select in HTML format, XQUERY variant

Same as Select in html format, but with XMLTABLE instead of XMLTRANSFORM select xmlroot( xmlelement( “table”, xmlconcat( xmltype( ‘ DEPTNO DNAME LOC ‘), xmlagg(column_value) ) ),version ‘1.0’ ) from xmltable(‘ for $f in ora:view(“LSC_DEPT”) return {$f/ROW/DEPTNO/text()} {$f/ROW/DNAME/text()} {$f/ROW/LOC/text()} ‘); XMLROOT(XMLELEMENT(“TABLE”,XMLCONCAT(XMLTYPE(‘ ————————————————– DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 […]

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

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