DA, Page Items to Submit and Page Items to Return

If you have a pl/sql type DA, the "Page Items to Submit" and "Page Items to Return" will be important, you must put the right Item into different bucket otherwise your DA may not work.

Basically, Items in "Page Items to Submit" will be those Items that you want to use the values inherit from your database inside your plsql block; while Items in  "Page Items to Return" will be those that you have changed their value and want to submit back to the databases by the page process.

Number and E-notation

Today I found out that the AWS usage report, some usages are showing up as E-notation(for example, 1.6E-5 which shall be  1.6×10⁻5. That broken my code as I am trying to load them into my Oracle table as numbers. 

SO I will need to find a way to to a transfer, after awhile, here is the working soultions

round(decode(instr(UsageQuantity,'E'),0,UsageQuantity,to_number(replace(UsageQuantity,' ',''),'9.99999999999999999999EEEE')),2),

Thx.

remote ssh inside a while loop

Today I found an issue for one of my script, the script is get a list server from server A and remote ssh Server B for doing something, the issues is that even though I have multiple servers but the ssh would stop after the first one, my original code is like this:

cat server_list|while read Server
 ssh -l oraware B  "echo $server_name"
done

After some digging, the issues is the  read will treat standard input as it's input.

To make it works, just add the red part to the ssh


cat server_list|while read Server
 ssh -l oraware B  "echo $server_name" < /dev/null
done

shell-- get last records from group based on certain field

I have some common separated text files, with some having same value on the second field while all other fields having different values, What I want is to get the last records from each group(based on second field), here is how to do it

cat data.txt|awk 'BEGIN {FS = OFS = ","}
       last != $2 {
       if(last != "") print first,last, hi3
          first = $1
          last = $2
          hi3 = $3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10 FS $11 FS $12
       next
    }
   {hi3 =  $3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10 FS $11 FS $12}
END { if(last != "") print first,last, hi3}'

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"))