Set Value DA for Cascade LOV field

One of my app is having some LOV fields, some of them are Cascade, something like this:

First Field, LOV, upon chosen, it shall set the value of the second LOV using Set Value Dynamic Action(and set the value of the third LOV field in the same DA, DA is based on the first table A)

Second LOV is the parent of Third LOV which is based on second Table B.

So basically when customer choose the first value, it will check table A, if it has the relative value for the second Field/third field, I would like the DA to populate them over; if it's relative value is null for those fields, customer will choose the LOV based on Table B.

Well, the DA works on the Second LOV field, setting the value correctly but not the Third field, seems like the DA just not able to set the value correctly(If I set the third field as Text field, the DA works correctly)

After some testing, it seems like that since the DA is a change DA on first field, so it did set the value for both the second LOV field and the third field; but since the second LOV field changes its value by the DA, it also resets the value of the third LOV filed.

Here is what I came out for the remedy:

1. create a clone item for the third field(hide field)

2. in the first DA, set the value of the Cloned Field

3. Create second field DA (triggered when the second(parent) field changes values): set value to set the third field to its clone.

That seems solved the problem!

get certain length of substring from right of string

Use this to get substring of any length (x in this case) form right side of a string.

select regexp_substr( in_string , '.{1,x}$' ) from dual

Oracle Apex Classic Report Refresh and keeping pagination

I have a project where I have a classic report(with multiple pages with 15 records in each page), I will need to refresh it each 3 seconds and also want to keep the pagination after each refresh.(mine is Apex 5)

Here is how I did it:

1. Create a page Item P_REGION_ID(hidden, default value set by following plsql:

DECLARE

   v_region_id VARCHAR2(4000);

BEGIN

    SELECT    region_id

         INTO v_region_id

         FROM apex_application_page_regions

        WHERE application_id = :APP_ID

          AND page_id         = :APP_PAGE_ID

          AND region_name     = 'Checking Database Request Status'; <-- you will need to replace the names with yours

   RETURN v_region_id;

END; 

2. Give a status ID to your region(in my example, I named mine DD)

3. put following in the page header (Function and Global Variables Section)

    var current_set;

    var first_record :=0;

    var last_record;

    var maxrec;

4. Create a dynamic action( After refresh and run following javascripts:)

current_set = $("#DD .t-Report-paginationText").html();

first_record = current_set.split(' ').filter(e => e !== "row(s)").filter(e => e !== "of").filter(e => e !== "-")[0];

last_record  = current_set.split(' ').filter(e => e !== "row(s)").filter(e => e !== "of").filter(e => e !== "-")[1];

var maxrec=last_record-first_record+1;

5. Put following in the page header(Execute when Page Loads):

if (first_record!=0) {

apex.widget.report.paginate('&P_REGION_ID.'{min:first_record,maxrec:max,fetched:0});

}

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.

Apex Tabular Form tooltips/Classic Report tooltips based on other column

I have a Tabular Form and I would like to add an Tooltips when user hover some fields there, here is how I did it:

1. Go to report column Attribute Page, under the col you would like to have tooltip, put following into the Element Attribute field:
   title="your hints here"
2. Go to page, put following into Global Function Section, you can change the color/background color as your own choice:


$( function() {
    $("#DD").tooltip({
      position: {
        my: "left+5 center", at: "right center",
    using: function( position, feedback ) {
          $( this ).css( position ).css('background-color','blue').css('color','white');
          $( "<div>" )
            .addClass( feedback.vertical )
            .addClass( feedback.horizontal )
            .appendTo( this );
        }
      }
    });
  } );

3. Put following into the inline css section(you can add the arrow class in above script to let the hint having an arrow..)

 .ui-tooltip, .arrow:after {
    background: black;
    border: 2px solid white;
  }
  .ui-tooltip {
    padding: 10px 20px;
    color: white;
    border-radius: 20px;
    font: bold 14px "Helvetica Neue", Sans-Serif;
    box-shadow: 0 0 7px black;
  }
  .arrow {
    width: 70px;
    height: 12px;
    overflow: hidden;
    position: absolute;
    left: 10%;
    margin-left: -15px;
    bottom: -16px;
  }
  .arrow.top {
    top: -16px;
    bottom: auto;
  }
  .arrow.left {
    left: 20%;
  }
  .arrow:after {
    content: "";
    position: absolute;
    left: 20px;
    top: -20px;
    width: 25px;
    height: 25px;
    box-shadow: 6px 5px 9px -9px black;
    -webkit-transform: rotate(45deg);
    -ms-transform: rotate(45deg);
    transform: rotate(45deg);
  }
  .arrow.top:after {
    bottom: -20px;
    top: auto;
  }

That's IT!

If you have a Classic Report which you want to show another Column(B)'s value as a Tooltips when you hover a column(A), you can do this:

1. Go to report Column Attribute part, in A's HTML expression Section, put followings

<span  title="#B#"></span>

2. Put the same javascript/css into the Page's Global Function/Inline css section as described about.

That's IT!

Update:  if you want to show multiple col values on the tooltips, just put them all in the title section(for example, you have B C D column and would like to show them in A col's tooltip, just put followings in A's html expression Section, you can also put some explanation between the columns.

<span  title="#B# 
#C#
#D#"></span>