Log Errors when insert select all

When you use insert into ., select ... statement, if few of your data will have issues, your statement will fail due to violation of some existing constraints. To avoid this happening, which means you want to make sure all the 'good' data are still being inserted while keep your eye on those 'bad' boys, you can use log errors feature:

1. set up the base error log table:

BEGIN

  DBMS_ERRLOG.create_error_log (dml_table_name => 'XXXXX');

END;

XXX is your table name that you are going to insert to

This will create a table err$_XXX which will store all error data/log

2. Add log errors at the end of your insert statement

insert into .... 

LOG ERRORS INTO err$_XXX ('INSERT') REJECT LIMIT UNLIMITED;

Oracle APEX: url inside email contains ROWID

If your email contains a url which has ROWID as part of it, be careful because sometime the ROWID has some 'special' character which the browser will 'convert' it to 'space' and when that happens, ur url will broke(today I have one record which has the '+' in the ROWID).

Here is how to get it address:

inside your email procedure, replace the rowid with APEX_UTIL.URL_ENCODE(IN_ROWID), so your procedure will looks like this:

Before change:

create or replace procedure my_email(in_rowid varchar2) is

begin

 ...

 ...

 url:='https://xxx/apex/f?p=207:442:::NO::P442_ROWID:'||in_rowid;

...

end;

After change:

create or replace procedure my_email(in_rowid varchar2) is

begin

 ...

 ...

 url:='https://xxx/apex/f?p=207:442:::NO::P442_ROWID:'||APEX_UTIL.URL_ENCODE(in_rowid);

...

end;

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}'