Get Oracle License usage/related data from OEM management View

Oracle Options/Components/Features usage data:

select pack.name, pack.total, sum(cpu.physical_cpu_count) as physical_cpu_count, sum(cpu.cpu_count) as core_count

from

    (select Pack_count.name, count(Pack_count.name) as total

       from  (select MDF.host, MDF.name, mdf.instance_name, count(MDF.host) as total

                     from MGMT$DB_FEATUREUSAGE MDF

                 where MDF.currently_used = 'TRUE'

                group by MDF.HOST, MDF.name, mdf.instance_name) Pack_count

     group by Pack_count.name) pack

, (select distinct MDF.host, MDF.name, cpu.physical_cpu_count, cpu.cpu_count

    from MGMT$DB_FEATUREUSAGE MDF, MGMT$OS_HW_SUMMARY cpu

  where MDF.currently_used = 'TRUE'

      and mdf.host = cpu.HOST_NAME ) cpu

where pack.name = cpu.name

group by pack.name, pack.total

order  by 1

OEM pack usage data:

select cpu_count.pack, sum(cpu_count.physical_cpu_count) as physical_cpu_count, sum(cpu_count.cpu_count) as core_count

from (select distinct mld.pack_label as PackLabel, mld.PACK_DISPLAY_LABEL as pack, mt.HOST_NAME, cpu.physical_cpu_count, cpu.cpu_count /*, mld.PACK_DESCRIPTION as DESCRIPTION*/

      from sysman.MGMT_LICENSE_VIEW mlv, sysman.MGMT_LICENSE_DEFINITIONS mld, MGMT$TARGET_TYPE_DEF mttd, mgmt$target mt, MGMT$OS_HW_SUMMARY cpu

      where mlv.PACK_NAME = mld.pack_label

      and mlv.target_type = mld.target_type

      and mlv.target_type = mttd.target_type

      and mlv.target_name = mt.target_name

      and mlv.target_type = mt.target_type

      and cpu.host_name = mt.host_name

      group by mld.pack_label, mttd.TYPE_DISPLAY_NAME, mld.PACK_DISPLAY_LABEL, mt.HOST_NAME, cpu.physical_cpu_count, cpu.cpu_count /*, mld.PACK_DESCRIPTION*/) cpu_count

group by cpu_count.pack

Oracle ORDS rest service receiving xml data

I have a project where I will need to provide a web service which can accept xml data, in ORDS, it's very easy to create a web service to accept Json data but somehow it's a different story when it come to xml data. Basically ORDS can parse the json data very well but it will not parse the xml itself. I struggled a little bit before I was able to make it work(thanks to Mike Kutz's help -- see the detail here https://community.oracle.com/thread/4020782).

It turns out that ORDS will treat the xml data only as BLOB, So we will need to parse the data inside our plsql.

Here is how it works:

1. create a web rest service(PUT operation in my case, application/xml in content type..) the same way you create a json based rest web service.
2. in the plsql, use :body to refer the incoming xml data from the client.
3. parse the xml data and use whatever needed in your plsql.

Here is the protocol of the plsql code:

declare

     bb varchar2(50);

     cc varchar2(40);

     aa varchar2(4000);

begin

    aa:=UTL_RAW.CAST_TO_VARCHAR2(:body); ---use :body to refer the xml data. and convert it to varchar2, :body will be BLOB type

SELECT xt.* into bb,cc

     FROM

           XMLTABLE('/Data'

              PASSING aa

              COLUMNS

                "RG_GROUP"   VARCHAR2(40)  PATH 'rs_group',

                "Status"    VARCHAR2(10) PATH 'status'

              ) xt;

-- parsing the xml data to get the data you wanted based on the xml format

     insert into mytrace values(bb||'/'||cc); --- do whatever you want here.

end;

in my case, the xml data is something like this:

<Data>
<rs_group>xxxx-prd</rs_group>
<status>Success</status>

</Data>

So the key here is that ORDS will accept the xml data as BLOB data and then we can refer it as :body in our plsql.

Call outside NTLM authorized Web Service from PL/SQL

This summary is not available. Please click here to view the post.