Get multiple substring starting with special characters in col

Today my project is trying to load the Window AD Service Account data into Oracle Database, the Service Account data was captured by one script which has Account Name/description/Membership info, the Membership col is having data like this:

CN=xxxx,OU=....,DC=... CN=yyyy.

I will need to get all the substring after the CN= and character ',', which can be one or any number values,

Here is what I did:

1. Create a function to get all the member into one string like xxxx:yyyy

create or replace function get_ad_member(member_string in varchar2)
return varchar2 
is
out_member varchar2(4000);
begin
select listagg(member,':') within group(order by member) mm into out_member from 
(
select replace(REGEXP_SUBSTR(q,'[^,]+',1,1),'CN=','') member from ( 
select * from (
select trim(regexp_substr(member_string,'[^,]+[^ ]+', 1,level) ) as q 
from dual
connect by regexp_substr(member_string,'[^,]+|[^ ]+', 1, level) is not null
order by level
)
where q like '%CN=%'
)
);
return out_member;
end;


Note: the reason I am using '[^,]+[^ ]+' is because before the CN=, sometime it has ',', sometime it's s ' '. 
Based on your actual situation, you can adjust the format of your own

2, in the sql, use the function before.

null, '', char and varchar2

I always thought that null is not equal to '' in Oracle Database world till today I am trying to use them in the procedure, One of my procedure will accept few in parameters(varchar2) since I am going to call an outside xml based webservice so I will need to manager the situation when the parameter is null or '': I will pass the value when it's not null AND it's not '' so I write it like this:

  where in_bu is not null and in_bu!='' then..

everything works fine if I pass some thing till I pass '', I thought since I have in_bu!='' it will go to other branch but I was very surprised to see that it is not: When I pass '', it always go to a wrong process and it always treat it as NULL. 

I write some simple test code and did some research and find out that in PL/SQL, if the variable is varchar type, then '' is same as NULL; when the variable is char type, '' is different with NULL.

I know NULL is very 'special' in Oracle term but still thought I understood the whole concept but obviously not really :-)

APEX Model Dialog Title

APEX 5.

I have a report with one col that is linking to an open Model Dialog, What I want is to show one of the report Column as part of the model dialog title. Here is what I did:

1. Create an hidden item in the dialog page: Pxx_TITLE
2. Build the link between the report and the dialog page and transfer the column name to this hidden item
3. In the dialog page, create an after refresh DA, putting following javascript as Action:

apex.util.getTopApex().jQuery(".ui-dialog-content").dialog("option", "title", $v("Pxx_TITLE")) 

Split Checkbox values into 'different Group'

I know this is kind of wired but I have a project where I will need to kind of putting different(multiple) checkbox values into multiple 'group' while it's actually just one CheckBox Item. this is most for the customer to kind of think of those values into multiple categories.

Here is how I did it:

$('table tr td').find("input[value='Home Street Address']").parent().addClass('break1');
boundary1 = $("td.break1");
$('table tr td').find("input[value='Religious Beleifs']").parent().addClass('break2');
boundary2 = $("td.break2");
$('table tr td').find("input[value='Physical Health']").parent().addClass('break3');
boundary3 = $("td.break3");
$('table tr td').find("input[value='Credit or Debit Card Number (PAN)']").parent().addClass('break4');
boundary4 = $("td.break4");
$("<tr>").insertAfter(boundary1.parent()).append(boundary1.nextAll().andSelf());
$("<tr>").insertAfter(boundary2.parent()).append(boundary2.nextAll().andSelf());
$("<tr>").insertAfter(boundary3.parent()).append(boundary3.nextAll().andSelf());
$("<tr>").insertAfter(boundary4.parent()).append(boundary4.nextAll().andSelf());
$('#P369_EMPLOYEE_DATA').find("input[value='Home Street Address']").parents('tr').before('<tr bgcolor="#f9940e"><td><font color=white>Contact Information</font></td></tr>');
$('#P369_EMPLOYEE_DATA').find("input[value='Religious Beleifs']").parents('tr').before('<tr bgcolor="#f9940e"><td><font color=white>Employee Beliefs</font></td></tr>');
$('#P369_EMPLOYEE_DATA').find("input[value='Physical Health']").parents('tr').before('<tr bgcolor="#f9940e"><td><font color=white>Health Information - Sensitive information</font></td></tr>');
$('#P369_EMPLOYEE_DATA').find("input[value='Credit or Debit Card Number (PAN)']").parents('tr').before('<tr bgcolor="#f9940e"><td><font color=white>Financial Information</font></td></tr>');

Basically I will need to 'split' the values into multiple 'groups'. then add 'one' virtual 'group title'(the red part) to each group. 

Set APEX select2 plugin Item Read Only

I have a form with some of the Select2 plugin items, I would like to set it to read only on the Edit page, here is how I do it:

 $('#s2id_P_ITEM').select2("readonly", true);

Replace P_ITEM with your own form Item

New version will be like this:

 $('#P_ITEM').select2("disabled", true);

Load AWS usage/billing data to Oracle

1. install aws cli and config using your ID and secret access key
2. setup cronjob in your database server to run following script:

#!/usr/bi/bash
billing_file_prex='XXXXX-aws-billing-detailed-line-items-with-resources-and-tags-'
mon=`date '+%Y-%m'`
billing_file="s3://cummins_aws_billing/$billing_file_prex$mon.csv.zip"
aws s3 cp $billing_file ./aws.csv.zip
unzip -o ./aws.csv.zip
mv "$billing_file_prex$mon.csv" aws.csv

Please replace XXXXX with your own account. You can also change the dir where you want to save your file.

3. In Oracle DB, create a dir EXT_TAB_DATA pointing to the file dir you use for the dowload

4. create an Oracle external table like this:

CREATE TABLE  "AWS_BILLING_EXTERNAL" 
   ( "INVOICEID" VARCHAR2(1000), 
 "PAYERACCOUNTID" VARCHAR2(1000), 
 "LINKEDACCOUNTID" VARCHAR2(2000), 
 "RECORDTYPE" VARCHAR2(1000), 
 "RECORDID" VARCHAR2(1000), 
 "PRODUCTNAME" VARCHAR2(2000), 
 "RATEID" VARCHAR2(1000), 
 "SUBSCRIPTIONID" VARCHAR2(1000), 
 "PRICINGPLANID" VARCHAR2(1000), 
 "USAGETYPE" VARCHAR2(200), 
 "OPERATION" VARCHAR2(1000), 
 "AVAILABILITYZONE" VARCHAR2(1000), 
 "RESERVEDINSTANCE" VARCHAR2(3000), 
 "ITEMDESCRIPTION" VARCHAR2(2000), 
 "USAGESTARTDATE" VARCHAR2(1000), 
 "USAGEENDDATE" VARCHAR2(1000), 
 "USAGEQUANTITY" VARCHAR2(1000), 
 "BLENDEDRATE" VARCHAR2(2000), 
 "BLENDEDCOST" VARCHAR2(3000), 
 "UNBLENDEDRATE" VARCHAR2(3000), 
 "UNBLENDEDCOST" VARCHAR2(1000), 
 "RESOURCEID" VARCHAR2(1000), 
 "AWS_CREATEDBY" VARCHAR2(3000), 
 "USER_APPLICATION" VARCHAR2(1000), 
 "USER_BILL_GROUP" VARCHAR2(1000), 
 "USER_ENVIRONMENT" VARCHAR2(1000), 
 "USER_NAME" VARCHAR2(1000)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "EXT_TAB_DATA"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
    BADFILE ext_tab_data:'aws.bad'
    LOGFILE ext_tab_data:'aws.log'
    DISCARDFILE ext_tab_data:'aws-discarded.log'
    skip 1
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
        InvoiceID, 
 PayerAccountId, 
 LinkedAccountId, 
 RecordType, 
 RecordId, 
 ProductName, 
 RateId, 
 SubscriptionId, 
    PricingPlanId, 
 UsageType, 
 Operation, 
 AvailabilityZone, 
 ReservedInstance, 
 ItemDescription, 
 UsageStartDate, 
 UsageEndDate, 
 UsageQuantity, 
 BlendedRate, 
 BlendedCost, 
 UnBlendedRate, 
 UnBlendedCost, 
 ResourceId, 
 aws_createdBy, 
 user_Application, 
 user_Bill_Group, 
 user_Environment,
 user_Name
    )
                 )
      LOCATION
       ( 'aws.csv'
       )
    )
   REJECT LIMIT UNLIMITED
/

Load Azure Usage/Billing Data into Oracle

I need to download Azure usage/billing detail data into Oracle Table and build an APEX dashboard on top of it, here is what I did:

1. schedule a cronjob to run this script to get the Azure usage data file everyday, normally this will be on your database server:

import requests
import urllib2
import urllib
import csv
import os
import datetime

download_date=datetime.datetime.today().strftime('%Y%m')

ur='https://consumption.azure.com/v2/enrollments/your_enrollment_ID/usagedetails/download?'

mydict = {'billingPeriod': download_date}
url = ur + urllib.urlencode(mydict)

headers= {"Authorization":"Bearer XXXXXXXXX"}

billing_file='az.csv'
x = requests.get(url,headers=headers)
with open(os.path.join("/usr/users/your_dir", billing_file), 'wb') as f:
    f.write(x.content)

Replace XXXXX with your authorization key, which you can get from Azure.
Replace your_dir with your defined dir where you are going to download the csv file
Replace your_enrollment_ID with your own enrollment ID

2.  In Oracle DB, create a dir EXT_TAB_DATA pointing to the file dir you use for the dowload

3. create an Oracle External table similar to this:

CREATE TABLE  "AZURE_BILLING_EXTERNAL" 
   ( "ACCOUNTID" VARCHAR2(1000), 
 "ACCOUNTNAME" VARCHAR2(1000), 
 "ACCOUNTOWNEREMAIL" VARCHAR2(2000), 
 "ADDITIONALINFO" VARCHAR2(1000), 
 "CONSUMEDQUANTITY" VARCHAR2(1000), 
 "CONSUMEDSERVICE" VARCHAR2(2000), 
 "CONSUMEDSERVICEID" VARCHAR2(1000), 
 "COST" VARCHAR2(1000), 
 "COSTCENTER" VARCHAR2(1000), 
 "USE_DATE" VARCHAR2(200), 
 "DEPARTMENTID" VARCHAR2(1000), 
 "DEPARTMENTNAME" VARCHAR2(1000), 
 "INSTANCEID" VARCHAR2(3000), 
 "METERCATEGORY" VARCHAR2(2000), 
 "METERID" VARCHAR2(1000), 
 "METERNAME" VARCHAR2(1000), 
 "METERREGION" VARCHAR2(1000), 
 "METERSUBCATEGORY" VARCHAR2(2000), 
 "PRODUCT" VARCHAR2(3000), 
 "PRODUCTID" VARCHAR2(3000), 
 "RESOURCEGROUP" VARCHAR2(1000), 
 "RESOURCELOCATION" VARCHAR2(1000), 
 "RESOURCELOCATIONID" VARCHAR2(3000), 
 "RESOURCERATE" VARCHAR2(1000), 
 "SERVICEADMINISTRATORID" VARCHAR2(1000), 
 "SERVICEINFO1" VARCHAR2(1000), 
 "SERVICEINFO2" VARCHAR2(1000), 
 "STORESERVICEIDENTIFIER" VARCHAR2(1000), 
 "SUBSCRIPTIONGUID" VARCHAR2(1000), 
 "SUBSCRIPTIONID" VARCHAR2(1000), 
 "SUBSCRIPTIONNAME" VARCHAR2(1000), 
 "TAGS" VARCHAR2(2000), 
 "UNITOFMEASURE" VARCHAR2(1000)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "EXT_TAB_DATA"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
    BADFILE ext_tab_data:'az.bad'
    LOGFILE ext_tab_data:'az.log'
    DISCARDFILE ext_tab_data:'az-discarded.log'
    skip 1
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
accountId,
accountName,
accountOwnerEmail,
additionalInfo,
consumedQuantity,
consumedService,
consumedServiceId,
cost,
costCenter,
use_date,
departmentId,
departmentName,
instanceId,
meterCategory,
meterId,
meterName,
meterRegion,
meterSubCategory,
product,
productId,
resourceGroup,
resourceLocation,
resourceLocationId,
resourceRate,
serviceAdministratorId,
serviceInfo1,
serviceInfo2,
storeServiceIdentifier,
subscriptionGuid,
subscriptionId,
subscriptionName,
Tags,
UnitOfMeasure
    )
             )
      LOCATION
       ( 'az.csv'
       )
    )
   REJECT LIMIT UNLIMITED
/




3. you now have the usage/billing data in Oracle table, so you can built your dashboard on top of it.



We are using Azure enterprise portal here, if you don't have enterprise portal for your billing, your url maybe different.

Preventing Apex Classic report csv download showing exponential

I have a Classic report with a Account_ID column where it has a big number, it shows ok but when download as csv and open it using  excel, it shows in exponential format instead of the original number, Here is how I fixed it like change the sql to something similar to this:

decode(INSTR(NVL(:REQUEST,'FOO'),'FLOW_EXCEL'),0,account_ID,'="'||account_ID||'"') account_id

So basically in the csv file, we will need to tell excel that to treat it as char by putting your number in quotes and proceeding with and equal sign, eg:

="001145",="55666",="02133"

Transfer user entered/LOV value to model dialog page and its title

I have a main report page(Page 1) where it has several LOV based parameter fields user use to apply to the underline report. It also has several button where each will pointing to a different model dialog page. What I what  is to transfer the use chosen value on the report page and apply their value to those model dialog page, the dialog page title also need to change dynamically based on the user chosen values. 
Here is how to do it:

1.  Create a hide item P1_DIALOG_TITLE, this will hold the value of the dialog title
2. On the report page(page 1),  create DA based on change of these parameter values, put three true actions:
 
    a. PL/SQL action, put 'null;' in the plsql.  This is to submit the user chosen values for those parameter
    b. put follow javascript into the second true action:
    3. Refresh the report action

var header_str
if ($v("P1_MONTH")!='') {
header_str='Billing Month:'+$v("P1_MONTH");
}
else {
header_str='Billing Month: Current Month';
}
if ($v("P1_BU")!='') {
header_str=header_str+" BU:"+$v("P1_BU");
}
if ($v("P1_BC")!='') {
header_str=header_str+' BC:'+$v("P1_BC");
}
if ($v("P1_RC")!='') {
header_str=header_str+' RC:'+$v("P1_RC");
}

$s('P1_DIALOG_TITLE',header_str)

In this case, I have 4 parameters: MONTH/BU/BC/RC

3. In the dialog page, apply the needed parameters to the sql section.
4. in the dialog page, put this in the Attribute of the page
   
   title: XXXX + $v('P1_DIALOG_TITLE')

Here XXX can be different title for different dialog page

Note. the dialog page will be able to share the same item from the Mail Page. I didn't realized this in the beginning, instead, I created separate hide items on the dialog page and tried to use the set value to carrier them over but that doesn't work

That's IT

embed a video into a help page

Sometime you want to create some user guide to introduce your application or show your customers how to use your application: normally you can write a world/pdf document and link it to your help page but most time it will be more helpful if you can just use some kind of screen recording tool to record a live video and then show it to your customers.

Here is what I did for one of my app:

1. use Snagit(or any other screen recording tool) to record what I want to show to my customers and save it as a MP4 video file
2. upload the video file to workspace
3. create a blank page then create a region, put following into the region:

<video id="video" width="700" height="550" controls>
  <source src="#WORKSPACE_IMAGES#xxx.mp4" type="video/mp4">
  Your browser does not support the video tag.

</video>
<p><strong>Note:</strong> The video tag is not supported in Internet Explorer 8 and earlier versions.</p>

replace xxx.mp4 with your video file.

Now link this page to your application as help page.

Note: this will not work for some old version IE/Browser where HTML5 is not supported but these days, most customer will have a browser that support HTML5.