APEX 5 Rich Text Item DA

I need to implement an DA on one Rich Text Editor type Item, If I use the ITEM itself, it will not work. I will need to use the Jquery selector #ID_DISPLAY to make it work

Oracle Apex5: Set select 2 values based on paraemt LOV fields

I have a form where I have two LOV, the first is a normal LOV and second LOV is a Select2 plugin field, I would like to set the value of the second LOV based on the first LOV value, here is how to do it:

1. create a change type DA on the first LOV, execute following javascript in the DA:

$('#P_SELECT2').select2('data', { R:$v("P_FIRST_LOV") , D: $v("P_FIRST_LOV")+'/Display value'}); 


2. Here is how to get it cleaned/initilized:

$('#P_SELECT2').val(null).trigger('change');

UPDATE:

New version need to do this way:


 $('#P_ITEM').select2("trigger", "select", { data: { id: $v('P_FIRST_LOV'), text: $v('P_FIRST_LOV')+'/Display value'  }});

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.