list targets

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.
[srv01.example.com, host]
[srv01.example.com:3872, oracle_emd]
[agent13c2_2_srv01.example.com_3830, oracle_home]
[DB01.example.com, oracle_database]
[OraDB12Home1_14_srv01.example.com_743, oracle_home]
[DB01_srv01.example.com_CDBROOT, oracle_pdb]
[DB01_srv01.example.com_PDB01, oracle_pdb]
[LISTENER001_srv01.example.com, oracle_listener]

Not really easy to parse, and it does not contain all information. Let’s imagine I want to get the TARGET_NAME out of my SID? hard…

What is actually emctl doing ? It is parsing the targets.xml with perl. Oracle wrote a module, called ias::simpleXPath, that helps parsing the file.


$AGENT_HOME/perl/bin/perl -l -I$AGENT_HOME/sysman/admin/scripts -Mias::simpleXPath -e '
  foreach $t(
    simpleXPathQueryForNodes(
      "targets.xml","Targets/Target")){
    print 
      "[".($t->{"attributes"}->{"NAME"}).
      ", ".($t->{"attributes"}->{"TYPE"}).
      "]"}'

ias::simpleXPath is a wrapper for XML::Parser. XML::Parser is a supported perl that is included in the agent home. So no need to install your own perl modules for this purpose!

back to by example, if I want to get the target name for my SID DB01


$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "SID" ) {
      $sid=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $sid eq lc "DB01"
      ) {
        print $tn . ":" . $tt;
      }
      $sid="";
    }
  }
'
DB01.example.com:oracle_database

This could be useful, for instance if you want to start a blackout


emctl start blackout db01_black DB01.example.com:oracle_database

For listener, you could retrieve the LsnrName for your listener LISTENER001


$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "LsnrName" ) {
      $lsn=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $lsn eq lc "LISTENER001"
      ) {
        print $tn . ":" . $tt;
      }
      $lsn="";
    }
  }
'
LISTENER001_srv01.example.com:oracle_listener

Which you could also blackout before rebooting.

The parser is not limited to Entreprise Manager targets, you could use it for oraInventory/ContentsXML/inventory.xml or whatever files.

There are plenty of other mean to read xml, from the database, xmllint, 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


<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 I must say I felt with love in this goody much more than in perl, cygwin, or whatever python, dos, java, vb…

It is simply great to use on the command line and can do my work.

1:0 for Microsoft

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{$_.no-eq"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 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()  
from t,xmltable(numbers)

DESCRIPTION (COLUMN_VALUE).GETNUMBERVAL()
PRIME 2
PRIME 3
PRIME 5
PRIME 7
ODD 1
ODD 3
ODD 5
ODD 7
ODD 9

It is that simple 🙂

Works also with strings :


select (column_value).getstringval() 
from xmltable('"a","b","c"');

(COLUMN_VALUE).GETSTRINGVAL()
a
b
c

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 called XMLTRANSFORM and transforms XML according to XSLT

I had a fun demo about XSLT to create a semi-column separated list :


select
   deptno,
   xmltransform
   (
      sys_xmlagg
      (
         sys_xmlgen(ename)
      ),
     xmltype
     (
       '<?xml version="1.0"?><xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
          <xsl:template match="/">
            <xsl:for-each select="/ROWSET/ENAME">
              <xsl:value-of select="text()"/>;</xsl:for-each>
          </xsl:template>
        </xsl:stylesheet>'
     )
  ).getstringval() listagg
from emp
group by deptno;


 DEPTNO LISTAGG
------- --------------------------------------
     10 CLARK;KING;MILLER;
     20 SMITH;FORD;ADAMS;SCOTT;JONES;
     30 ALLEN;BLAKE;MARTIN;TURNER;JAMES;WARD;