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>

Oracle Apex report cell background color based on value

1. Create a report and set the report static ID: DD
2. Create a after refresh DA, put following javascript:

$("#DD tbody tr td[headers='STATUS']").each(function(){
     if ($(this).text() == 'Completed')
        $(this).css("background-color","#31f904").css('color','white');
     else if ($(this).text() == 'WIP')
        $(this).css("background-color","#22e5be").css('color','white');
     else if ($(this).text() == 'Pending')
        $(this).css("background-color","#ff0800").css('color','white');
});

Here I am using the STATUS col of the report, you can change that to your col and adjust the value/color based on your requirement

Apex plugin - international phone item

Apex international phone plugin

I have an app where it will need customer to type in their cell phone numbers, Since the customer can be anywhere in the planet, it's a little harder to validate the phone # folks typed in.

Luckily I found this wonderful jquery plugin(intltelinput, you can find the plugin here https://github.com/jackocnr/intl-tel-input). 

I played with it and decided to write a Apex Plugin so that I can reuse it in my app:

1. it will decide your default country based on your laptop's IP address.

2. You can choose a country from the list

3. The plugin will return both the country code and the cell phone # so the item itself will have the format  like +country_code+cell phone number, although you just need to type in your cell phone #

4. It will validate the number you typed in based on the country you are in.

Here is the plugin:

https://drive.google.com/file/d/0B0SccnKDTnu5LXVzd3lQb3BIU3c/view?usp=sharing

Setup Oracle Database Active Data Guard and Broker automatically

Setup Oracle Database Active Data Guard and Broker automatically

Environment: Database: Oracle 12c(12.1.0). 

                         Server: Primary Database Server--P_server/Standby Database Server-S_server

                                       Center server: CC_server: this can auto ssh P_server/S_server without 

                                       login;

                         OS account:  oracle-- DB owner/oralist -- Listener owner

                         Primary Database SID/DB name: m12c1

                         OS: OEL 5

                         Filesystem Layout:  /u01 --> OH

                                                         /u02 ../u04: DB datafile/controlfile/online logfile

                                                         /orabackup:  backup fir

                                                        /oraarchive: arch file

                         

Goal:

   1. Setup DB m12c1 Active Data Guard between P_server and S_server

   2. Setup Data Guard Broker.

Setup:

    1. In CC server, create a dir called dataguard with following subdir:

              template ---> all templates

              tnsnames ---> all listener/tns related templates

              scripts     ---> all scripts:

   2. put followings into template subdir:

       a. primary.tmp

#!/bin/bash

. ~/.bash_profile

export ORACLE_SID=p_sid

export ORAENV_ASK=NO

. oraenv

export TNS_ADMIN=/u01/app/oracle/product/12clistener/network/admin

sqlplus -S / as sysdba<<EOF

spool /tmp/pp.archive

set heading off feedback off

select log_mode FROM v\$database;

spool off

EOF

archive=`cat /tmp/pp.archive | xargs echo`

sqlplus -S / as sysdba<<EOF

spool /tmp/pp.log

set heading off feedback off

select force_logging from v\$database;

spool off

EOF

LOG=`cat /tmp/pp.log | xargs echo`

sqlplus -S / as sysdba <<EOF

set heading off feedback off

spool /tmp/pp.redosize

select max(bytes/(1024*1024)) bytes

from v\$log;

spool off

EOF

test -f /tmp/initp_sid.ora

if [ $? == 0 ]; then

   rm /tmp/initp_sid.ora

fi

test -f /tmp/p_sid.ctl

if [ $? == 0 ]; then

 rm /tmp/p_sid.ctl

fi

TT=`cat /tmp/tnsnames.s_server|xargs echo`

grep "$TT" $TNS_ADMIN/tnsnames.ora

if [ $? != 0 ]; then

   grep s_short_p_sid $TNS_ADMIN/tnsnames.ora

   if [ $? == 0 ]; then

     cat $TNS_ADMIN/tnsnames.ora|sed -e "s/s_short_p_sid/xxxxx/g" >/tmp/tnsnames

     echo "$TT" >>/tmp/tnsnames

     mv /tmp/tnsnames  $TNS_ADMIN/tnsnames.ora

   fi

   echo "$TT" >>  $TNS_ADMIN/tnsnames.ora

fi

TT=`cat /tmp/tnsnames.p_server|xargs echo`

grep "$TT" $TNS_ADMIN/tnsnames.ora

if [ $? != 0 ]; then

   grep p_short_p_sid $TNS_ADMIN/tnsnames.ora

   if [ $? == 0 ]; then

     cat $TNS_ADMIN/tnsnames.ora|sed -e "s/p_short_p_sid/xxxxx/g" >/tmp/tnsnames

     echo "$TT" >>/tmp/tnsnames

     mv /tmp/tnsnames  $TNS_ADMIN/tnsnames.ora

   else

     echo "$TT" >>  $TNS_ADMIN/tnsnames.ora

   fi

fi

grep "listener12c =" $TNS_ADMIN/tnsnames.ora

if [ $? != 0 ]; then

   cat "/tmp/tt.p_server" >>$TNS_ADMIN/tnsnames.ora

fi

redo_size=`cat /tmp/pp.redosize | xargs echo`

a_size=`df /oraarchive|grep /oraarchive|awk '{print $(NF-2)}'`

#a_size=`df -k|grep oraarchive|awk '{print $2 }'`

arch_size1=$((${a_size}*7/1024))

arch_size=`printf "%.0f\n" $arch_size1`

if [ $archive != 'ARCHIVELOG' ];then

sqlplus / as sysdba <<EOF

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

EOF

fi

if [ $LOG == 'NO' ];then

sqlplus / as sysdba <<EOF

ALTER DATABASE FORCE LOGGING;

EOF

fi

cd $ORACLE_HOME/dbs

test -f orapwp_sid

if [ $? == 0 ]; then

   rm orapwp_sid

fi

orapwd file=orapwp_sid password=sbbbb# entries=20

sqlplus / as sysdba <<EOF

alter system set local_listener='listener12c' scope=both;

alter system register;

alter user sys identified by sbbbb#;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(p_sid,p_sid_stdy)' scope=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=s_short_p_sid NOAFFIRM ASYNC COMPRESSION=ENABLE VALID_FOR=(ONLINE_

LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=p_sid_stdy' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

ALTER SYSTEM SET FAL_SERVER=s_short_p_sid scope=both;

alter system set fal_client=p_short_p_sid scope=both;

alter system set archive_lag_target=1800 scope=both;

ALTER DATABASE FLASHBACK ON;

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=20 scope=both;

create user dga identified by saaaa2#;

grant connect,sysdg to dga;
shutdown abort;

startup;

ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/p_sid/standby_redo01.log') SIZE ${redo_size}M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/p_sid/standby_redo02.log') SIZE ${redo_size}M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u04/oradata/p_sid/standby_redo03.log') SIZE ${redo_size}M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u04/oradata/p_sid/standby_redo04.log') SIZE ${redo_size}M;

alter system set db_recovery_file_dest_size=${arch_size}M scope=both;

ALTER SYSTEM SET dg_broker_start=true scope=both;

CREATE PFILE='/tmp/initp_sid.ora' from spfile;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/p_sid.ctl';

execute dbms_service.delete_service('p_sidp');

execute dbms_service.create_service(service_name => 'p_sidp',network_name => 'p_sidp',failover_method => 'BASIC', failover_type => 'SELECT',failover_retries => 180,failover_delay => 1);

create or replace trigger manage_service after startup on database

declare  role varchar2(30);

begin

  select database_role into role from v$database;

     if role = 'PRIMARY' THEN

          DBMS_SERVICE.START_SERVICE('p_sidp');

     else

          DBMS_SERVICE.STOP_SERVICE('p_sidp');

     end if;

end;

exec dbms_service.start_service('p_sidp');

EOF

grep ^p_sid: /etc/oratab >/tmp/oratabp_sid.tmp

### Modify listener.ora to prepare for dataguard broker config

cp /u01/app/oracle/product/12clistener/network/admin/listener.ora /tmp/listener.tmp

grep "(SID_NAME = p_sid)" /tmp/listener.tmp >/dev/null

if [ $? != 0 ]; then

start_line=`grep -n "(SID_LIST =" /u01/app/oracle/product/12clistener/network/admin/listener.ora|awk -F: '{prin

t $1}'`

let start_line=$start_line+1

let start_line_2=$start_line+1

let start_line_3=$start_line_2+1

let start_line_4=$start_line_3+1

sed -i "${start_line} i\    (SID_DESC =" /tmp/listener.tmp>/dev/null

sed -i "${start_line_2} i\       (SID_NAME = p_sid)" /tmp/listener.tmp>/dev/null

sed -i "${start_line_3} i\       (ORACLE_HOME = $ORACLE_HOME)" /tmp/listener.tmp>/dev/null

sed -i "${start_line_4} i\     )" /tmp/listener.tmp>/dev/null

fi

grep "(GLOBAL_DBNAME = p_sid_DG)" /tmp/listener.tmp>/dev/null >/dev/null

if [ $? != 0 ]; then

start_line=`grep -n "(SID_LIST =" /tmp/listener.tmp|awk -F: '{print $1}'`

let start_line=$start_line+1

let start_line_2=$start_line+1

let start_line_3=$start_line_2+1

let start_line_4=$start_line_3+1

let start_line_5=$start_line_4+1

sed -i "${start_line} i\    (SID_DESC =" /tmp/listener.tmp>/dev/null

sed -i "${start_line_2} i\       (GLOBAL_DBNAME = p_sid_DG)" /tmp/listener.tmp>/dev/null

sed -i "${start_line_3} i\       (SID_NAME = p_sid)" /tmp/listener.tmp>/dev/null

sed -i "${start_line_4} i\       (ORACLE_HOME = $ORACLE_HOME)" /tmp/listener.tmp>/dev/null

sed -i "${start_line_5} i\     )" /tmp/listener.tmp>/dev/null

fi

cp /tmp/listener.tmp $TNS_ADMIN/listener.ora

ssh -o StrictHostKeyChecking=no -q -l oralist p_server ". ~/.bash_profile;lsnrctl reload listener12c"

b. primary1.tmp:

#!/bin/bash

. ~/.bash_profile

export ORACLE_SID=p_sid

export ORAENV_ASK=NO

. oraenv

sqlplus / as sysdba <<EOF

alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_u

nique_name=p_sid' scope=both;

alter system set log_archive_dest_2='' scope=both;

alter system set log_archive_min_succeed_dest=1 scope=both;

EOF

c. standby.tmp:

 standby.tmp

#!/bin/bash

. ~/.bash_profile

export TNS_ADMIN=/u01/app/oracle/product/12clistener/network/admin

grep ^p_sid: /etc/oratab

if [ $? != 0 ]; then

   cat /tmp/oratabp_sid.tmp >>/etc/oratab

fi

export ORACLE_SID=p_sid

export ORAENV_ASK=NO

. oraenv

mkdir -p /u02/oradata/p_sid /u03/oradata/p_sid /u04/oradata/p_sid

mkdir -p /orabackup/flash_recovery_area/p_sid

mkdir -p /u01/app/oracle/admin/p_sid

mkdir -p /u01/app/oracle/admin/p_sid/adump /u01/app/oracle/admin/p_sid/bdump /u01/app/oracle/admin/p_sid/dpdump

 /u01/app/oracle/admin/p_sid/cdump /u01/app/oracle/admin/p_sid/udump /u01/app/oracle/admin/p_sid/pfile /u01/app

/oracle/admin/p_sid/scripts

mkdir -p $ORACLE_BASE/diag/rdbms/p_sid/trace

mkdir -p $ORACLE_BASE/diag/rdbms/p_sid/cdump

cp $TNS_ADMIN/listener.ora /tmp/listener.tmp

grep "(SID_NAME = p_sid)" /u01/app/oracle/product/12clistener/network/admin/listener.ora >/dev/null

if [ $? != 0 ]; then

start_line=`grep -n "(SID_LIST =" /u01/app/oracle/product/12clistener/network/admin/listener.ora|awk -F: '{prin

t $1}'`

let start_line=$start_line+1

let start_line_2=$start_line+1

let start_line_3=$start_line_2+1

let start_line_4=$start_line_3+1

sed -i "${start_line} i\    (SID_DESC =" /tmp/listener.tmp>/dev/null

sed -i "${start_line_2} i\       (SID_NAME = p_sid)" /tmp/listener.tmp>/dev/null

sed -i "${start_line_3} i\       (ORACLE_HOME = $ORACLE_HOME)" /tmp/listener.tmp>/dev/null

sed -i "${start_line_4} i\     )" /tmp/listener.tmp>/dev/null

fi

grep "(GLOBAL_DBNAME = p_sid_sty_DG)" /tmp/listener.tmp>/dev/null >/dev/null

if [ $? != 0 ]; then

start_line=`grep -n "(SID_LIST =" /tmp/listener.tmp|awk -F: '{print $1}'`

let start_line=$start_line+1

let start_line_2=$start_line+1

let start_line_3=$start_line_2+1

let start_line_4=$start_line_3+1

let start_line_5=$start_line_4+1

sed -i "${start_line} i\    (SID_DESC =" /tmp/listener.tmp>/dev/null

sed -i "${start_line_2} i\       (GLOBAL_DBNAME = p_sid_sty_DG)" /tmp/listener.tmp>/dev/null

sed -i "${start_line_3} i\       (SID_NAME = p_sid)" /tmp/listener.tmp>/dev/null

sed -i "${start_line_4} i\       (ORACLE_HOME = $ORACLE_HOME)" /tmp/listener.tmp>/dev/null

sed -i "${start_line_5} i\     )" /tmp/listener.tmp>/dev/null

fi

cp /tmp/listener.tmp $TNS_ADMIN/listener.ora

grep "listener12c =" $TNS_ADMIN/tnsnames.ora

if [ $? != 0 ]; then

   cat "/tmp/tt.s_server" >>$TNS_ADMIN/tnsnames.ora

fi

TT=`cat /tmp/tnsnames.s_server`

grep "$TT" $TNS_ADMIN/tnsnames.ora

if [ $? != 0 ]; then

   grep s_short_p_sid $TNS_ADMIN/tnsnames.ora

   if [ $? == 0 ]; then

     cat $TNS_ADMIN/tnsnames.ora|sed -e "s/s_short_p_sid/xxxxx/g" >/tmp/tnsnames

     echo "$TT" >>/tmp/tnsnames

     mv /tmp/tnsnames  $TNS_ADMIN/tnsnames.ora

   else

     echo "$TT" >>  $TNS_ADMIN/tnsnames.ora

   fi

fi

TT=`cat /tmp/tnsnames.p_server`

grep "$TT" $TNS_ADMIN/tnsnames.ora

if [ $? != 0 ]; then

   grep p_short_p_sid $TNS_ADMIN/tnsnames.ora

   if [ $? == 0 ]; then

     cat $TNS_ADMIN/tnsnames.ora|sed -e "s/p_short_p_sid/xxxxx/g" >/tmp/tnsnames

     echo "$TT" >>/tmp/tnsnames

     mv /tmp/tnsnames  $TNS_ADMIN/tnsnames.ora

   fi

   echo "$TT" >>  $TNS_ADMIN/tnsnames.ora

fi

ctl1=`grep control_files= /tmp/initp_sid.ora|awk -F= '{print $2}'|sed "s/'//g"|awk -F, '{print $1}'|xargs echo`

cp  /tmp/p_sid.ctl $ctl1

ctl2=`grep control_files= /tmp/initp_sid.ora|awk -F= '{print $2}'|sed "s/'//g"|awk -F, '{print $2}'|xargs echo`

cp  /tmp/p_sid.ctl $ctl2

ctl3=`grep control_files= /tmp/initp_sid.ora|awk -F= '{print $2}'|sed "s/'//g"|awk -F, '{print $3}'|xargs echo`

cp  /tmp/p_sid.ctl $ctl3

d. standby.tmp2:

#!/bin/bash

. ~/.bash_profile

export TNS_ADMIN=/u01/app/oracle/product/12clistener/network/admin

export ORACLE_SID=p_sid

export ORAENV_ASK=NO

. oraenv

sqlplus / as sysdba <<EOF

shutdown abort;

STARTUP NOMOUNT PFILE='/tmp/initp_sid.ora';

EOF

rman TARGET sys/sbbbb#@p_short_p_sid AUXILIARY sys/sbbbb#@s_short_p_sid<<EOF

DUPLICATE TARGET DATABASE

  FOR STANDBY

  FROM ACTIVE DATABASE

  DORECOVER

  SPFILE

    SET db_unique_name='p_sid_stdy' COMMENT 'Is standby'

    SET LOG_ARCHIVE_DEST_2='SERVICE=p_shortr_p_sid ASYNC COMPRESSION=ENABLE VALID_FOR=(ONLINE_LOGFILES,PRIMARY_

ROLE) DB_UNIQUE_NAME=p_sid'

    SET FAL_SERVER='p_short_p_sid' COMMENT 'Is primary'

  NOFILENAMECHECK;

exit;

EOF

sqlplus / as sysdba<<EOF

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_u

nique_name=p_sid_stdy' scope=both;

alter system set log_archive_dest_2='' scope=both;

alter system set log_archive_min_succeed_dest=1 scope=both;

ALTER DATABASE FLASHBACK ON;

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240 scope=both;

ALTER DATABASE OPEN READ ONLY;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

EOF

e. listener.tmp

#!/bin/bash

. ~/.bash_profile

export ORACLE_SID=p_sid

export ORAENV_ASK=NO

. oraenv

export TNS_ADMIN=/u01/app/oracle/product/12clistener/network/admin

grep  'TCP.NODELAY=YES' $TNS_ADMIN/sqlnet.ora

if [ $? != 0 ]; then

 echo 'TCP.NODELAY=YES'>>$TNS_ADMIN/sqlnet.ora

fi

grep "DEFAULT_SDU_SIZ=32767" $TNS_ADMIN/sqlnet.ora

if [ $? != 0 ]; then

 echo 'DEFAULT_SDU_SIZ=32767'>>$TNS_ADMIN/sqlnet.ora

fi

f.  dg_check_status.tmp

#!/bin/bash

. ~/.bash_profile

export ORACLE_SID=p_sid

export ORAENV_ASK=NO

. oraenv

dgmgrl <<EOF | grep "Ready for Switchover:  Yes"

connect /

validate database p_sid

EOF

if [ $? == 0 ]; then

dgmgrl <<EOF | grep "Ready for Switchover:  Yes"

connect /

validate database p_sid_stdy

EOF

if [ $? != 0 ]; then

   exit 1

fi

else

  exit 1

fi

exit 0

3. Put followings file into tnsnames subdif:

a. tnsnames.tmp:

TNS=(DESCRIPTION=(SDU = 32767)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=DBSERVER)(PORT=1528)(SEND_BUF_SIZE=SSI

ZE)(RECV_BUF_SIZE=RSIZE)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=p_sid)))

b. tt.tmp:

listener12c =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = V_HOST )(PORT = 1525))

      (ADDRESS = (PROTOCOL = TCP)(HOST = V_HOST )(PORT = 1526))

      (ADDRESS = (PROTOCOL = TCP)(HOST = V_HOST )(PORT = 1527))

      (ADDRESS = (PROTOCOL = TCP)(HOST = V_HOST )(PORT = 1528))

      (ADDRESS = (PROTOCOL = TCP)(HOST = V_HOST )(PORT = 1529))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1530))

    )

  )

4. Put following  into scripts sub dir:

a. dg_setup.sh

#!/local/bash

beep()

{

  echo '\a'

}

red ()

{

   echo "$(tput setaf 1)$1$(tput sgr 0)"

}

green ()

{

   echo "$(tput setaf 2)$1$(tput sgr 0)"

}

warning()

{

   if [ $# != 1 ]; then

      beep;

   fi

   echo "\033[41m\033[37m" $1 "\033[33;00m"

}

if [ $# != 3 ]; then

   warning "Usage: setup_dg.sh primary_server standby_server DB_SID"

   exit;

fi

p_server=$1

s_server=$2

p_sid=$3

green "1. Checking environment ..."

echo $1 |grep aaaa.com>/dev/null

if [ $? != 0 ];then

   warning "$1 need to be full name"

   exit

fi

echo $2|grep aaaa.com>/dev/null

if [ $? != 0 ];then

   warning "$2 need to be full name"

   exit

fi

ssh -o StrictHostKeyChecking=no -q -l oracle $p_server 'hostname' >/dev/null

if [ $? != 0 ]; then

   warning "Can't auto ssh(passwordless) to oracle of $p_server from ccadmin of cc, please set up!"

   exit

fi

ssh -o StrictHostKeyChecking=no -q -l oracle $s_server 'hostname' >/dev/null

if [ $? != 0 ]; then

   warning "Can't auto ssh(passwordless) to oracle of $s_server from ccadmin of cc, please set up!"

   exit

fi

ssh -o StrictHostKeyChecking=no -q -l oralist $p_server 'hostname' >/dev/null

if [ $? != 0 ]; then

   warning "Can't auto ssh(passwordless) to oralist of $p_server from ccadmin of cc, please set up!"

   exit

fi

ssh -o StrictHostKeyChecking=no -q -l oralist $s_server 'hostname' >/dev/null

if [ $? != 0 ]; then

   warning "Can't auto ssh(passwordless) to oralist of $s_server from ccadmin of cc, please set up!"

   exit

fi

p_short=`echo $1|awk -F. '{print $1}'`

s_short=`echo $2|awk -F. '{print $1}'`

ping $p_server >/dev/null

if [ $p_server == $s_server ]; then

   warning "Primary Server can't be the same as Standy Server!"

   exit;

fi

if [ $? != 0 ]; then

   warning "Can't ping server $p_server, please check!"

   exit;

fi

ping $s_server >/dev/null

if [ $? != 0 ]; then

   warning "Can't ping server $p_server, please check!"

   exit;

fi

ssh -o StrictHostKeyChecking=no -q -l oracle $p_server "ps -ef|grep pmon|grep $p_sid$|egrep -v grep" >/dev/nul

l

if [ $? != 0 ]; then

   warning "Can't find primary database $p_sid in server $p_server, please check!"

   exit;

fi

ssh -o StrictHostKeyChecking=no -q -l oracle $s_server "ps -ef|grep pmon|grep $p_sid$|egrep -v grep" >/dev/nul

l

if [ $? == 0 ]; then

   warning "Find standby Database $p_sid in server $s_server up and running, please check!"

   exit;

fi

green " .... Passed!"

green "2. Prepare Active Data Guard Setup Between Primary DB($p_sid on $p_server) and Standby DB($p_sid on $s_s

erver ..."

ssh -o StrictHostKeyChecking=no -q -l oracle $p_server "ping -c 5 -s 64000 $s_server|grep 64008 >/tmp/ss" >/de

v/null

TNS_ADMIN='/u01/app/oracle/product/12clistener/network/admin'

LATENCY=`ssh -o StrictHostKeyChecking=no -q oracle@${p_server}  "cat /tmp/ss" |awk '{print $8}' |awk

-F= '{print $2}'|awk '{ total += $1; count++ } END { print total/count }'`

BDU=`echo "2500 * $LATENCY * 3 * 1000 / 8" | bc`

cat ../template/primary.tmp|sed -e "s/p_server/$p_server/g" -e "s/p_sid/$p_sid/g" -e "s/s_server/$s_server/g" -

e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g" > ../template/$p_server.sh

cat ../template/primary1.tmp|sed -e "s/p_server/$p_server/g" -e "s/p_sid/$p_sid/g" -e "s/s_server/$s_server/g"

-e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g" > ../template/${p_server}1.sh

cat ../template/standby.tmp|sed -e "s/p_server/$p_server/g" -e "s/p_sid/$p_sid/g" -e "s/s_server/$s_server/g" -

e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g"  > ../template/$s_server.sh

cat ../template/standby.tmp2|sed -e "s/p_server/$p_server/g" -e "s/p_sid/$p_sid/g" -e "s/s_server/$s_server/g"

-e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g"  > ../template/${s_server}2.sh

cat ../tnsnames/tnsnames.tmp|sed -e "s/SSIZE/$BDU/g" -e "s/RSIZE/$BDU/g" -e "s/p_sid/$p_sid/g" -e "s/TNS/${p_sh

ort}_${p_sid}/g" -e "s/DBSERVER/$p_server/g" >../tnsnames/tnsnames.$p_server

cat ../tnsnames/tnsnames.tmp|sed -e "s/SSIZE/$BDU/g" -e "s/RSIZE/$BDU/g" -e "s/p_sid/$p_sid/g" -e "s/TNS/${s_sh

ort}_${p_sid}/g" -e "s/DBSERVER/$s_server/g" >../tnsnames/tnsnames.$s_server

cat ../tnsnames/tt.tmp| sed -e "s/V_HOST/$p_server/g" >../tnsnames/tt.$p_server

cat ../tnsnames/tt.tmp| sed -e "s/V_HOST/$s_server/g" >../tnsnames/tt.$s_server

cat ../template/dgmgrl.tmp |sed -e "s/p_sid/$p_sid/g" -e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g" >../t

emplate/dgmgrl.$p_short

cat ../template/listener.tmp |sed -e "s/p_sid/$p_sid/g" -e "s/p_short/$p_short/g" -e "s/s_short/$s_short/g" >..

/template/listener.$p_short

cat ../template/dg_check_status.tmp |sed -e "s/p_sid/$p_sid/g" -e "s/p_short/$p_short/g" -e "s/s_short/$s_short

/g" >dg_check_status.sh_$p_sid

scp -o StrictHostKeyChecking=no -q ../tnsnames/tt.$p_server oracle@$p_server:/tmp

scp -o StrictHostKeyChecking=no -q ../tnsnames/tt.$s_server oracle@$s_server:/tmp

###scp -o StrictHostKeyChecking=no -q ../template/$p_server.sh oracle@$p_server:/tmp

###scp -o StrictHostKeyChecking=no -q ../template/$s_server.sh oracle@$s_server:/tmp

scp -o StrictHostKeyChecking=no -q ../tnsnames/tnsnames.$p_server oracle@$p_server:/tmp

scp -o StrictHostKeyChecking=no -q ../tnsnames/tnsnames.$s_server oracle@$p_server:/tmp

scp -o StrictHostKeyChecking=no -q ../tnsnames/tnsnames.$p_server oracle@$s_server:/tmp

scp -o StrictHostKeyChecking=no -q ../tnsnames/tnsnames.$s_server oracle@$s_server:/tmp

#ssh -o StrictHostKeyChecking=no -q -l oracle $p_server ". ~/.bash_profile;ksh /tmp/$p_server.sh"

ssh -o StrictHostKeyChecking=no -q -l oralist $p_server 'bash -s'</template/listener.$p_short >/dev/null

ssh -o StrictHostKeyChecking=no -q -l oralist $s_server 'bash -s'</template/listener.$p_short >/dev/null

green "3. Prepare Primary Database for Data Guard...."

ssh -o StrictHostKeyChecking=no -q -l oracle $p_server 'bash -s' <../template/$p_server.sh >/dev/null

scp -o StrictHostKeyChecking=no -q oracle@$p_server:/tmp/init${p_sid}.ora ../template

scp -o StrictHostKeyChecking=no -q oracle@$p_server:/tmp/oratab${p_sid}.tmp ../template

scp -o StrictHostKeyChecking=no -q ../template/oratab${p_sid}.tmp oracle@$s_server:/tmp

PP=`echo $p_short|tr '[:lower:]' '[:upper:]'`

SS=`echo $s_short|tr '[:lower:]' '[:upper:]'`

SSID=`echo $p_sid|tr '[:lower:]' '[:upper:]'`

cat ../template/init$p_sid.ora | sed -e "s/${s_short}_${p_sid}/${p_short}_${p_sid}/g" -e "s/${p_sid}_stdy/$p_si

d/g" -e "s/$SS/$PP/g" >../template/init${p_sid}.tmp

cat ../template/init${p_sid}.tmp|grep -v fal_client>../template/init${p_sid}.tmp1

echo "*.fal_client='${SS}_${SSID}'">>../template/init${p_sid}.tmp1

cp ../template/init${p_sid}.tmp1 ../template/init${p_sid}.tmp

rm ../template/init${p_sid}.tmp1

scp -o StrictHostKeyChecking=no -q ../template/init${p_sid}.tmp oracle@$s_server:/tmp/init${p_sid}.ora

scp -o StrictHostKeyChecking=no -q oracle@$p_server:/tmp/$p_sid.ctl /tmp

scp -o StrictHostKeyChecking=no -q /tmp/$p_sid.ctl oracle@$s_server:/tmp

scp -o StrictHostKeyChecking=no -q oracle@$p_server:/u01/app/oracle/product/12.1.0/dbs/orapw$p_sid /tmp

scp -o StrictHostKeyChecking=no -q /tmp/orapw$p_sid oracle@$s_server:/u01/app/oracle/product/12.1.0/dbs

###scp -o StrictHostKeyChecking=no -q ../template/dgmgrl.$p_short oracle@$p_server:/tmp

#ssh -o StrictHostKeyChecking=no -q -l oracle $s_server ". ~/.bash_profile;ksh /tmp/$s_server.sh"

green "4. Create Standby Database of $p_sid on $s_server through RMAN Duplicate ...."

ssh -o StrictHostKeyChecking=no -q -l oracle $s_server 'bash -s' <../template/$s_server.sh >/tmp/${s_short}.lo

g

grep "Finished Duplicate Db" /tmp/${s_short}.log

if [ $? != 0 ]; then

   red "Issues when setup Standby DB using RMAN Duplicate command, please check detail at /tmp/${s_short}.log"

   exit;

fi

ssh -o StrictHostKeyChecking=no -q -l oralist $s_server '. /usr/users/oralist/.bash_profile;lsnrctl status list

ener12c|grep TNS-12541'

if [ $? == 0 ]; then

  ssh -o StrictHostKeyChecking=no -l oralist $s_server '. /usr/users/oralist/.bash_profile;lsnrctl start listen

er12c' >/dev/null

else

  ssh -o StrictHostKeyChecking=no -l oralist $s_server '. /usr/users/oralist/.bash_profile;lsnrctl reload liste

ner12c' >/dev/null

fi

green "5. Setup Data Guard Broker ..."

ssh -o StrictHostKeyChecking=no -q -l oracle $s_server 'bash -s' <../template/${s_server}2.sh >/tmp/${p_short}

.dg.log

ssh -o StrictHostKeyChecking=no -q -l oracle $p_server 'bash -s' <../template/${p_server}1.sh >>/tmp/${p_short

}.dg.log

ssh -o StrictHostKeyChecking=no -q -l oracle $p_server 'bash -s' <../template/dgmgrl.$p_short >>/tmp/${p_short

}.dg.log

ssh -q oracle@${p_server} 'bash -s'< dg_check_status.sh_${p_server} >>/tmp/${p_short}.dg.log

ff=`cat /tmp/${p_short}.dg.log |grep "Switchover:  Yes"|wc -l`

if [ $ff -lt 2 ]; then

   red "There are some issues while setup the DataGuard Broker, please check for details at /tmp/${p_short}.dg.

log"

   exit

fi

green "Congratulations!! You have successfully setup the Active Dataguard between $p_server($p_sid) and $s_serv

er($p_sid)!"

Usage:

From CC center:

cd dataguard/scripts

ksh P_server S_server m12c1

tnsnames.ora in the client side:

m12c1p=
(DESCRIPTION_LIST=
 (LOAD_BALANCE=off)
 (FAILOVER=on)
 (DESCRIPTION=
   (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
     (ADDRESS_LIST=
       (LOAD_BALANCE=on)
       (ADDRESS=(PROTOCOL=TCP)(HOST=P_server)(PORT=1526)))
       (CONNECT_DATA=(SERVICE_NAME=m12c1p)))
 (DESCRIPTION=
   (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
     (ADDRESS_LIST=
       (LOAD_BALANCE=on)
       (ADDRESS=(PROTOCOL=TCP)(HOST=S_server)(PORT=1526)))
       (CONNECT_DATA=(SERVICE_NAME=m12c1p))))

Note:

1. You can use your own setting to replace the $ORACLE_HOME/$TNS_ADMIN..
2. The sys and dga password in the scrips is dummy ones, please replace them with your own password.

Apex report: Conditionally showing the link url

Apex report: Conditionally showing the link url

Sometime in the report/form combination, we will need to show/hide the linked url based on value of another col of the report, for example, in following report:

select empno,ename,job from emp;

There is a link on the empno col, (the link will point to the EMP detail form). What I would like to do is to only enable the link if the person's job title is 'Manager', otherwise hide the link.

Here is how to do it:

1. Modify the report sql to:

     select empno,ename,job,decode(job,'Manager','Y','N') flag from emp;

2.  in the report page(inline css section), put this in:

     .N { display: none; }

3. in the linked attribute of the report linked col, put this in

    class="#flag#"

That't IT!

In case you are not happen with the default edit icon, you can replace with a dynamic action on the report page:

create a dynamic action(after refresh), run javascripts:

$(".Y").removeClass("Y").addClass("fa fa-user-plus fa-2x")

In this case, I am replacing the default edit link icon with an font awesome icon

Apex Tabular Form -- disable one column based on value of another column

Apex Tabular Form -- disable one column based on value of another column

I have a Tabular Form, one col is an LOV(Phone MMS provider), another col is a text field, I would like to disable that text column if user doesn't choose "Other" in the LOV, otherwise make it enterable. 

Here is how to do it:

1. create a dynamic action:

    Event: Change on JQuery selector:  select[name='f08'] --- here f08 is the LOV field

    execute javascripts:

      var lov = this.triggeringElement.id;

      var row = lov.split("_")[1];

      if ($lov(el)  == "Other") {

      // disable the field 

       //       $x_disableItem("f09_" + row, false);  --- change this to below line, because I got no data found issue with this line after I added more than 16 rows in the tabular table, not sure the reason but changed it to below fixed the issue
              $( "#f09_" + row ).prop( 'readOnly', false);

      }

      else {

     // enable the field

     //        $x_disableItem("f09_" + row, true); ---- Change this to below line
             $( "#f09_" + row ).prop( 'readOnly', true);

      };

 --- f09 is the text field in the tabular form

2. copy the dynamic action, change the event to "Page Load"

Oracle Apex 5: Show fontawesome in Report

Oracle Apex 5: Show fontawesome in Report

Say I have a report with a column "NOTIFICATION", which will have Yes or No value, if I want to show  fontawesome instead of Yes/No in the report, do these:

1. Put following css into page inline css(or global css file):

.check-Yes:before {

 content: "\f00c";

 color: green;

}

.check-No:before {

 content: "\f00d";

 color: red;

} 

--- for other fonts, you can check it and choose the one you want here http://fontawesome.io/cheatsheet/

2. Modify the notification column in the report, put following into the  HTML expression sections

<span class="fa check-#NOTIFICATION# fa-2x"></span>

That's IT.

Apex 5: Change Badge List hover default color

Apex 5: Change Badge List hover default color

I am doing an project where I am using the Badge List Plugin come with the Apex packaged application, customer want to change the hover color from default to Teal, I tried to find the jquery class think it shall be easy to do that but it took me a while to figure it out:

Here is how to get it done:

In the page inline css section, put this part(Here I am changing the hover color from default to Teal color):

.t-BadgeList--circular a.t-BadgeList-wrap:hover .t-BadgeList-value {

    box-shadow: 0 0 0 4px Teal inset;

}

.t-BadgeList--circular a.t-BadgeList-wrap .t-BadgeList-value {

    border-color: Teal;

    transition: box-shadow 0.1s, color 0.1s, background-color 0.3s;

}

I somehow couldn't get the class through inspector which I originally thought I could, I end up looking  at https://apex.oracle.com/ut, which has all the Universal Theme component, where I can get this easily..

ORDS Query Based REST Service

ORDS Query Based REST Service

Today I found one wire thing:

I am using Oracle ORDS (3.0) amd Sql*Developer(4.0) to create a get service based on an simply Sql query(cursor expression included), I following the instruction of Sql*developer but somehow the service just didn't work, keep giving "Service Denied" error.

After some digging, it turns out that there is one parameter "Pagination Size" which is having default size 25, which need to be set to 0 in order to make it work. I haven't find any reason for that yet but that's the way it is

Oracle Apex Tabular Form delete new rows without submitting

A very good way from:

http://techxploration.blogspot.com/2013/08/oracle-apex-tabular-form-delete-unsaved.html

1. Put this in the Global Function:

function delete_rows(){
    var need_submit = 0;
    $("[name='f01']").each(function(){
    if($(this).attr("checked")) {
       if(this.value == 0) // new rows {
           $(this).closest("tr").remove();
       }
       else  {  //existing rows, need to be removed via page submission.
           need_submit = need_submit + 1;  
       }
     });   
     if(need_submit > 0)    // require submission?
        apex.confirm('Are you sure you want to delete it?','DELETE');
        apex.submit('APPLY_CHANGES_MRD'); 
    };

2. in the Button, put "redirect URL" and put this as target:

    javascript:delete_rows();

Sending email from sqlplus(pl/sql) using Apex_mail

declare
  wspace_id number;
begin

  select max(workspace_id) into wspace_id from apex_applications ;
  wwv_flow_api.set_security_group_id(wspace_id);

  apex_mail.send (
    p_from => 'me@test.com'
    , p_to => 'me@test.com'
    , p_subj => 'Send email from Pl/Sql using Apex_mail'
    , p_body => 'test test'
   );

  apex_mail.push_queue('mail.url.com', 25);
end;

Apex 5 Universal Theme Navigation menu color and background color

In Apex 5, we can use the List based Navigation Menu and submenu, sometime you want to change the color or background color of the menu/submenu you choose, I know some is using the Theme Roller to get this done but for me, I always have some difficulties to choose the color I wanted through the Roller,

Here is another way I am doing:

In the Custom css part, put this in:


.a-MenuBar-label:hover {
  background-color: red}

.a-Menu-inner:hover {
  background-color: red}


That's it!!, you can change it to any color you want!

Convert oracle column value into rows(and the reverse, rows to col)

Sometime I need to convert a col value(separated with some specific character(like :)) into rows:

Image that you have a column feeded by an Apex LOV, when user choose multiple values from the LOV, the return string will be a colon separated string. Later on you may need to convert this string into multiple rows so that you can put them back into some of your sql queries.

Here is the sql I used  for this:

  select server_name||'' aa from
  (
    with t as (select 'AA:NN:CC:DD:WW' server_list from dual)
         SELECT EXTRACT(column_value,'/e/text()') server_name from t x,
         TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<ROW><e>'||REPLACE(server_list,':','</e><e>')||'</e></ROW>'),'//e')))
  )

Replace the sql in red with your own table and columns.
If your separator is something else other than colon, you can simply replace the colon in the sql with your separator.

Here  is the reverse operation(convert rows to columns):

select 
   rtrim (xmlagg (xmlelement (e, wwid||'@xyz.com'|| ',')).extract ('//text()'), ',') email
from 
   majorevent_admin
where role='Admin'

where majorevent_admin is the table, one of the Col is people's WWID. another col is Role.

Exadata ASM usage

Here is the sql I used to get the ExaData ASM usage:

  select 'Exadata', round(TOTAL_MB/decode(type,''NORMAL'',2,''HIGH'',3,1)/1024) total_GB,
      round(usable_FILE_mb/1024) usable_free_GB,
      round((HOT_USED_MB+COLD_USED_MB)/decode(type,''NORMAL'',2,''HIGH'',3,1)/1024)
      data_use_GB,    
      round(REQUIRED_MIRROR_FREE_MB/decode(type,''NORMAL'',2,''HIGH'',3,1)/1024)
      Req_free_redun_GB,
      ROUND(((HOT_USED_MB+COLD_USED_MB)/total_MB)*100) perc,sysdate
      from v$asm_diskgroup where total_mb != 0'

Since we have multiple databases in several of our ExaData machines, I will need to write a script to run this against all of our databases.

ExaData/Exalogic resource usage(CPU/MEM/Storage) from OEM

ExaLogic/ExaData  CPU/Mem/Storage Usage (By Node) data:

Here is the sql I used to get the Exa Node CPU/Mem/Storage usage data from our OEM repo. We have several ExaData/ExaLogic machines in house and I was asked to get these data automatically, took me a while to get here.

select decode(Instr(Entity_Name,'exad'),0,'ExaLogic','ExaData') Catelog,substr(Entity_Name,1,9) Machine,Entity_Name node_name,
case Metric_Column_Label
       When 'CPU Utilization (%)' Then 'CPU'
       When 'Physical Memory Utilization (%)' Then 'Memory'
       When 'Memory Utilization (%)' then 'Memory'
       --when 'Total Disk Utilized (%) (across all local filesystems)' then 'Local FS'
        else 'Storage'
end case,
COLLECTION_TIME,round(Avg_Value,2) Avg_Usage
From sysman.GC$metric_Values_Daily
Where Entity_Type In( 'oracle_vm_server','host')
         And ((Substr(Entity_Name,4,4) ='exal'
         And Instr(Entity_Name,'-elc')=0) or Substr(Entity_Name,4,4) ='exad')
         And Metric_Column_Label in('CPU Utilization (%)',
                'Physical Memory Utilization (%)','Memory Utilization (%)',
                 'Used Space (%)')
--,'Total Disk Utilized (%) (across all local filesystems)')
and collection_time>=sysdate-15  and round(Avg_Value,2)>0;

Several notes:

  1. The above sql doesn't return Exadata ASM usages, I couldn't find those data anywhere in OEM and I now believe OEM doesn't have them. I ended up go to our Database instance directly to get them

  2. The ExaLogic Node Storage data is a little difference with the data showing in our Ops Center,  The difference is minor, don't know why but someone from Oracle told me that OEM data is just fine.

Exalogic capacity Planning

Exalogic capacity Planning: how to get the # of cpu/memory of your vServers in Exalogic:

We have several Oracle Exalogic/Exadata machines in house, one of my recent project is to get the cpu/memory number allocated to each vServer we created in all our Exalogic machines. I know these data are somewhere available in our OEM repo but I couldn't find any official document. Took me a while to figure it out but in the end, it's pretty cool considering I can pull all these data into one place(I create an Apex report for this) so they can see how our Exalogic Machines are using.

Below is the sql I was using, keep in mind that our Exalogic Node/vServer has a name convention(we name it exalxxxx and that's part of the logic of my sql, your may be difference.

CREATE OR REPLACE FORCE VIEW "EXA_CPU_MEM" ("HOST", "SERVER_NAME", "CPU", "MEM") AS
  select substr(a.host_name,1,instr(a.host_name,'-')-1) host,a.target_name
  server_name,to_char(b.cpu_count) CPU,to_char(b.alloc_mem_mb/1024) MEM
from  MGMT$VT_VM_CONFIG b,
(
    select host_name,target_name,target_guid from mgmt$target where host_name
    in
    (select target_name from mgmt$target where instr(target_name,'exal')!=0 and target_type='host')
and target_type='oracle_vm_guest'
and instr(target_name,'zsl')!=0
) a
where
a.target_guid=b.target_guid

Change Apex 5 Interactive Report Column Width

Here is how to change the column width on the Apex 5 Interactive Report:

1. Give a static id for the report: for example, in the Static ID field, say DD
2. Give a static id for the column you want to change width: for example, give it RR
3. Repeat 2 if you have multiple columns you want to change the size
3. in the page inline section, put this in

    #DD td[headers=RR]{min-width:300px}
    #DD td[headers=TT]{min-width:200px}
    #DD td[headers=YY]{min-width:200px}

 in this case, I wanted to change 3 columns

Thant's it!