Delete Row Button to all records of Interactive Report in Oracle Apex

 1. add 'Delete' Del column to Report Query
2. Set the Following Properties for the DEL Column

Type: Link
Heading: Delete
Target > Type: URL
URL: javascript:void(null);
Link Text: <span class="t-Icon fa fa-trash delete-irrow" aria-hidden="true"></span>
Link Attributes: data-id=#EXAM_ID#

Note: here EXAM_ID is primary key ( We can use ROWID also if table does not have primary key)

3. Create a Page Item (hidden) to Hold the Primary Key Column Value

Name: P152_EXAM_ID (set the name according to your page)

Type: Hidden

Value Protected: No

4. Create a Dynamic Action for the Interactive Report Delete Row Button

Name: DA_DELETE_RECORD

Event: Click
Selection Type: jQuery Selector
jQuery Selector: .delete-irrow
Event Scope: Dynamic

create below 4 true actions for above Dynamic Action.

Action: Confirm
Text: Are you sure to delete this Exam?

Action: Set Value
Set Type: JavaScript Expression
JavaScript Expression: $(this.triggeringElement).parent().data('id')
Selection Type: Item(s)
Item(s): P152_EXAM_ID

Action: Execute PL/SQL Code
PL/SQL Code: Delete from exam_details where customer_id = :P152_EXAM_ID;
Items to Submit: P152_EXAM_ID

Action: Refresh
Selection Type: Region

Region: Exams (this is the interactive report region on my page)

How to disable to manual editing of Date Picker Item in Oracle Apex?

We can set the Custom Attributes of the Date Picker Item (under Advanced) to:

readonly="readonly"

Oracle APEX hide the x on the modal dialog

Below code to be added in Inline CSS of Page.

 .ui-dialog-titlebar .ui-button

{

    display: none;

}

Output:

SQL query to get Index Details on a Table

 SELECT

    ind.table_owner,
    ind.table_name,
    ind.index_name,
    LISTAGG(ind_col.column_name, ',') WITHIN GROUP(
    ORDER BY
        ind_col.column_position
    ) AS columns,
    col.data_type,
    col.data_length,
    ind.index_type,
    ind.uniqueness,
    ind_col.descend,
    ind.status
FROM
         all_indexes ind
    JOIN all_ind_columns ind_col ON ind.owner = ind_col.index_owner
                                    AND ind.index_name = ind_col.index_name
    JOIN all_tab_columns col ON col.owner = ind_col.index_owner
                                AND col.table_name = ind_col.table_name
                                AND col.column_name = ind_col.column_name
WHERE
    ind.table_name = 'EMP'
GROUP BY
    ind.table_owner,
    ind.table_name,
    ind.index_name,
    ind.index_type,
    ind.uniqueness,
    col.data_type,
    col.data_length,
    ind_col.descend,
    ind.status
ORDER BY
    ind.table_owner,
    ind.table_name;

REGEXP_SUBSTR

 SELECT REGEXP_SUBSTR ( VALUE,

                                                     '[^,]+',

                                                     1,

                                                     ROWNUM)

                                         AS result

                                 FROM TABLE WHERE ATTRIBUTE='EXAM'

                           CONNECT BY LEVEL <=

                                           LENGTH (

                                              REGEXP_REPLACE (

                                                 VALUE,

                                                 '[^,]+'))

                                         + 1

RANK and DENSE_RANK in Oracle

 

dynamic interactive report in oracle apex

 1. Create 3 items -->  P1_SQL, P1_COLCOUNT and P1_HEADER

2. Create a region --> PL/SQL Dyanamic Content

declare

    l_query varchar2(32767) := nvl(:P1_SQL,'select empno,ename from emp');

    l_theCursor     integer default dbms_sql.open_cursor;

    l_columnValue   varchar2(4000);

    l_descTbl       dbms_sql.desc_tab;

    l_colCnt        number;

    l_sql varchar2(4000) := 'select /*smartcheck*/ ';

    l_sep     varchar2(1);

 v_sql     VARCHAR2 (32000) := NULL;

   v_dates   VARCHAR2 (10000) := NULL;

BEGIN

 

  IF APEX_COLLECTION.COLLECTION_EXISTS ( p_collection_name => 'EMP_SQL') THEN

     APEX_COLLECTION.DELETE_COLLECTION( p_collection_name => 'EMP_SQL');

  end if;

  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (

      p_collection_name => 'EMP_SQL',

      p_query => l_query);

   dbms_sql.close_cursor(l_theCursor);

   l_theCursor     := dbms_sql.open_cursor;

   dbms_sql.parse(  l_theCursor, l_query, dbms_sql.native );

   dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

 

   :P1_COLCOUNT := l_colcnt;

   for i in 1 .. least(l_colCnt,2)

   loop

     apex_util.set_session_state( p_name  => 'P1_HEADER'||lpad(i,2,0)

                                , p_value =>  l_descTbl(i).col_name

                                );

   end loop;

end;

3. Create Interactive Report

select 

 c001 ,c002 

from apex_collections 

where collection_name = 'EMP_SQL';

JSON Parsing

 DECLARE
v_response      CLOB := NULL;
 v_status              VARCHAR2 (2000);
   v_message             VARCHAR2 (2000);
BEGIN
v_response :='{"status":"NOK","message":"ZOOM: Meeting 99711037631 is not found or has expired."}';
apex_json.parse (p_source => v_response);
 v_status              := apex_json.get_varchar2('status');
   v_message             := apex_json.get_varchar2('message');
dbms_output.put_line('v_status='||v_status);
dbms_output.put_line('v_message='||v_message);
END;

/

v_status=NOK
v_message=ZOOM: Meeting 99711037631 is not found or has expired.

Statement processed.

0.01 seconds

Connect to OCI via GIT Bash Tunneling

1. mkdir .ssh

2. cd .ssh

3. vi config

 

Host *
   ServerAliveInterval 240
   ServerAliveCountMax 20
   StrictHostKeyChecking=no
   ForwardAgent yes
Host P1
   Hostname 123.123.123.123
   LocalForward localhost:9021 abc000021801.a1-prodapp-ash1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host P2
   Hostname 123.123.123.123
   LocalForward localhost:9022 abc000021802.a1-prodapp-ash1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host S1
   Hostname 123.123.123.123
   LocalForward localhost:9023 abc000021841.a1-prodapp-phx1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host S2
   Hostname 123.123.123.123
   LocalForward localhost:9024 abc000021842.a1-prodapp-phx1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host D1
   Hostname 123.123.123.123
   LocalForward localhost:9025 abc00003324.a1-prodapp-phx1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1  

4. ssh P1

5. SQL Developer: 

    HostName: localhost

    Port: 9021

    Service Name: ORCL

Pivot

 CREATE TABLE TESTING (  ID VARCHAR2(100),SES VARCHAR2(100),AMOUNT NUMBER,CNT NUMBER);

INSERT INTO TESTING VALUES('abc123','njn-jn',10,9);
INSERT INTO TESTING VALUES('abc123','kjsdb-jj',20,3);
INSERT INTO TESTING VALUES('abc123','jskj-5g',30,6);
COMMIT;
INSERT INTO TESTING VALUES('abc345','njn-jn1',10,9);
INSERT INTO TESTING VALUES('abc345','kjsdb-jj1',20,3);
INSERT INTO TESTING VALUES('abc345','jskj-5g1',30,6);
COMMIT;

SELECT * FROM TESTING;

WITH SET_RN AS
(SELECT ID,SES,AMOUNT,CNT,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWID ASC) RN FROM TESTING)
SELECT S.ID,
(SELECT S1.SES FROM SET_RN S1 WHERE S1.ID=S.ID AND S1.RN=1) AS SES1 ,
(SELECT S1.AMOUNT FROM SET_RN S1 WHERE S1.ID=S.ID AND S1.RN=1) AS AMOUNT1, 
(SELECT S1.CNT FROM SET_RN S1 WHERE S1.ID=S.ID AND S1.RN=1) AS CNT1,
(SELECT S2.SES FROM SET_RN S2 WHERE S2.ID=S.ID AND S2.RN=2) AS SES2 ,
(SELECT S2.AMOUNT FROM SET_RN S2 WHERE S2.ID=S.ID AND S2.RN=2) AS AMOUNT2, 
(SELECT S2.CNT FROM SET_RN S2 WHERE S2.ID=S.ID AND S2.RN=2) AS CNT3,
(SELECT S3.SES FROM SET_RN S3 WHERE S3.ID=S.ID AND S3.RN=3) AS SES3 ,
(SELECT S3.AMOUNT FROM SET_RN S3 WHERE S3.ID=S.ID AND S3.RN=3) AS AMOUNT3, 
(SELECT S3.CNT FROM SET_RN S3 WHERE S3.ID=S.ID AND S3.RN=3) AS CNT3
FROM SET_RN S GROUP BY ID;

SQL query to view structure of the table in Oracle

  SELECT A.OWNER,
         A.TABLE_NAME,
         C.COLUMN_NAME,
         C.DATA_TYPE,
         C.DATA_LENGTH,
         C.DATA_PRECISION,
         C.DATA_SCALE,
         C.COLUMN_ID
    FROM ALL_TABLES A, ALL_TAB_COLUMNS C
   WHERE     A.TABLE_NAME = C.TABLE_NAME
         AND A.OWNER = 'SCOTT'
         AND C.OWNER = 'SCOTT'
         AND A.TABLE_NAME = 'EMP'
ORDER BY A.TABLE_NAME, C.COLUMN_ID;

Bar and Pie chart in Oracle APEX

CREATE TABLE PRODUCT (ID NUMBER, NAME VARCHAR2(100));
ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY(ID);
INSERT INTO PRODUCT VALUES(1, 'Iphone');
INSERT INTO PRODUCT VALUES(2, 'Samsung');
INSERT INTO PRODUCT VALUES(3, 'Xiami');
INSERT INTO PRODUCT VALUES(4, 'Lenovo');
INSERT INTO PRODUCT VALUES(5, 'Oppo');
INSERT INTO PRODUCT VALUES(6, 'OnePlus');
INSERT INTO PRODUCT VALUES(7, 'Nokia');
COMMIT; 

 

CREATE TABLE ORDERS (ID NUMBER, PRODUCT_ID NUMBER, QUANTITY NUMBER, ORDER_DATE DATE);
ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_PK PRIMARY KEY(ID);
ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_FK FOREIGN KEY(PRODUCT_ID) REFERENCES PRODUCT(ID);
INSERT INTO ORDERS VALUES (1,1,20,SYSDATE);
INSERT INTO ORDERS VALUES (2,3,10,'02/13/1992');
INSERT INTO ORDERS VALUES (3,1,40,'03/19/2010');
INSERT INTO ORDERS VALUES (4,1,10,'12/10/1998');
INSERT INTO ORDERS VALUES (5,4,10,'09/18/1992');
INSERT INTO ORDERS VALUES (6,3,50,'02/13/2020');
INSERT INTO ORDERS VALUES (7,1,10,'03/15/1992');
INSERT INTO ORDERS VALUES (8,2,30,'07/19/1992');
INSERT INTO ORDERS VALUES (9,7,10,'02/22/1992');
INSERT INTO ORDERS VALUES (10,7,80,'09/28/2017');
INSERT INTO ORDERS VALUES (11,2,10,'21/28/2019');
COMMIT;

 

PIE Chart SQL:
Application --> Create Page --> Chart --> Pie --> Page Name as Charts (anything) --> SQL Query --> Select LABEL and VALUE --> Create
SELECT P.NAME AS LABEL, COUNT(*) AS VALUE FROM PRODUCT P, ORDERS O WHERE P.ID = O.PRODUCT_ID GROUP BY P.NAME; 

 

BAR Chart SQL:
Application --> Create Page --> Chart --> Bar --> Page Name as Charts (anything) --> Soucre: Localdatabase ,Type: SQL Query --> Select LABEL and VALUE --> Create
SELECT P.NAME AS LABEL, COUNT(*) AS VALUE FROM PRODUCT P, ORDERS O WHERE P.ID = O.PRODUCT_ID GROUP BY P.NAME; 

 

Example: https://apex.oracle.com/pls/apex/royal/r/testing40/charts?session=112166733385562

Variables in UNIX

 [scott]$ cat sample.sh
#!/bin/bash
#This is basic bash script exmaple.
a=Hello
b='Hello'
c="Hello"
d=123
echo $a
echo $b
echo $c
echo $d
echo "Hello, value of d is $d"
[scott]$ sh sample.sh
Hello
Hello
Hello
123
Hello, value of d is 123
[scott]$
Variables Declaration
[scott]$ cat sample.sh
#!/bin/bash
#This is basic bash script exmaple.
a=Hello
b='Hello'
c="Hello"
d=123
echo $a
echo $b
echo $c
echo $d
echo "Hello, value of d is $d"
echo
echo
declare -i e=123 #e is an integer
declare -r f=999 #f is read-only
declare -l g="king kong" #g is in lower case
declare -u h="Hello World" #h is in upper case
echo $e
echo $f
echo $g
echo $h
[scott]$ sh sample.sh
Hello
Hello
Hello
123
Hello, value of d is 123

123
999
king kong
HELLO WORLD
[scott]$
Built-in Variables
[scott]$ echo $HOME
/u02/test/abc
[scott]$ echo $PWD
/u04/ftp/scott
[scott]$ echo $MACHTYPE
x86_64-redhat-linux-gnu
[scott]$ echo $HOSTNAME
abc12345667890
[scott]$ echo $BASH_VERSION
4.1.2(2)-release
[scott]$ echo $SECONDS
8010
[scott]$ echo $0
-bash
[scott]$

echo in UNIX

 [scott]$ vi sample.sh
[scott]$ cat sample.sh
#!/bin/bash
#This is basic bash script exmaple.
name="Raj"
echo Hello $name, country\(ind\)!
echo 'Hello $name, country(IND)!'
echo "Hello $name,country(IND)!"
[scott]$ sh sample.sh
Hello Raj, country(ind)!
Hello $name, country(IND)!
Hello Raj,country(IND)!
[scott]$

Tilde and Brace usage in UNIX

 [test scott]$ pwd
/u04/ftp/orcl/scott
[test scott]$ cd ..
[test orcl]$ echo ~-
/u04/ftp/orcl/scott
[test orcl]$ pwd
/u04/ftp/orcl
[test orcl]$ touch {apple,grape,banana}
[test orcl]$ ls
apple  banana  grape  scott/
[test orcl]$ ls -l
total 2
-rw-rw-r--  1 apps apps  0 Nov  4 06:21 apple
-rw-rw-r--  1 apps apps  0 Nov  4 06:21 banana
-rw-rw-r--  1 apps apps  0 Nov  4 06:21 grape
drwxrwxrwx 13 apps root  14 Nov  3 10:56 scott/
[test orcl]$ touch file_{1..6}
[test orcl]$ ls -l
total 2
-rw-rw-r--  1 apps apps  0 Nov  4 06:21 apple
-rw-rw-r--  1 apps apps  0 Nov  4 06:21 banana
-rw-rw-r--  1 apps apps  0 Nov  4 06:22 file_1
-rw-rw-r--  1 apps apps  0 Nov  4 06:22 file_2
-rw-rw-r--  1 apps apps  0 Nov  4 06:22 file_3
-rw-rw-r--  1 apps apps  0 Nov  4 06:22 file_4
-rw-rw-r--  1 apps apps  0 Nov  4 06:22 file_5
-rw-rw-r--  1 apps apps  0 Nov  4 06:22 file_6
-rw-rw-r--  1 apps apps  0 Nov  4 06:21 grape
drwxrwxrwx 13 apps root  14 Nov  3 10:56 scott/
[test orcl]$ ls -l
total 2
drwxrwxrwx 13 apps root 14 Nov  3 10:56 scott/
[test orcl]$ ls -l
total 2
drwxrwxrwx 13 apps root 14 Nov  3 10:56 scott/
[test orcl]$ ls
scott/
[test orcl]$ pwd
/u04/ftp/orcl
You have mail in /var/spool/mail/apps
[test orcl]$ cd scott
[test scott]$
[test scott]$
[test scott]$ clear
[test scott]$ pwd
/u04/ftp/orcl/scott
[test scott]$ cd ..
[test orcl]$ echo ~-
/u04/ftp/orcl/scott
[test orcl]$ pwd
/u04/ftp/orcl
[test orcl]$ ls
scott/
[test orcl]$ ls -l
total 2
drwxrwxrwx 13 apps root 14 Nov  3 10:56 scott/
[test orcl]$ touch {apple banana cherry}
[test orcl]$ ls -l
total 2
-rw-rw-r--  1 apps apps  0 Nov  4 06:31 {apple
-rw-rw-r--  1 apps apps  0 Nov  4 06:31 banana
-rw-rw-r--  1 apps apps  0 Nov  4 06:31 cherry}
drwxrwxrwx 13 apps root  14 Nov  3 10:56 scott/
[test orcl]$ toych file_{1..5}
-bash: toych: command not found
[test orcl]$ ls -l
total 2
-rw-rw-r--  1 apps apps  0 Nov  4 06:31 {apple
-rw-rw-r--  1 apps apps  0 Nov  4 06:31 banana
-rw-rw-r--  1 apps apps  0 Nov  4 06:31 cherry}
drwxrwxrwx 13 apps root  14 Nov  3 10:56 scott/
[test orcl]$ echo {1..10..2}
1 3 5 7 9
[test orcl]$ echo {1..10..3}
1 4 7 10
[test orcl]$ echo {A..Z}
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
[test orcl]$ echo {a..z}
a b c d e f g h i j k l m n o p q r s t u v w x y z
[test orcl]$ echo {0..9}
0 1 2 3 4 5 6 7 8 9
[test orcl]$ echo {0..21}
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
[test orcl]$ echo {a..Z}
a ` _ ^ ]  [ Z
[test orcl]$ echo {m..a..2}
m k i g e c a
[test orcl]$ touch {apple,banana}_{0..2}{a..b}
You have mail in /var/spool/mail/apps
[test orcl]$ ls
apple_0a  apple_0b  apple_1a  apple_1b  apple_2a  apple_2b  banana_0a  banana_0b  banana_1a  banana_1b  banana_2a  banana_2b  scott/
[test orcl]$

copy file names from UNIX server to Oracle Database

1. connect to scott user and create below table

 CREATE TABLE SFTP_FILE_NAMES (FILE_NAME VARCHAR2(100),PROCESSED_ON DATE);

2. assume /u04/ftp/abcdev/abc/scripts/file_list.20201102 contains below files

sftp> cd /test
sftp> ls -l 20201102*
-rwx------    0 0        0          112255 Nov  2 01:04 20201102_orcl_result1.xml
-rwx------    0 0        0          112255 Nov  2 05:09 20201102_orcl_result2.xml
-rwx------    0 0        0          181669 Nov  2 09:03 20201102_orcl_result3.xml
-rwx------    0 0        0          166850 Nov  2 13:05 20201102_orcl_result4.xml
-rwx------    0 0        0          117372 Nov  2 17:01 20201102_orcl_result5.xml
-rwx------    0 0        0           35570 Nov  2 22:01 20201102_orcl_result50.xml
-rwx------    0 0        0           77009 Nov  2 21:03 20201102_orcl_result6.xml
sftp> 

3. create load_files2db.sh to copy the files from unix to oracle database

#!/bin/bash
export ORACLE_HOME=/u02/test/abc/instantclient/instantclient_11_2
export TNS_ADMIN=/u02/test/abc/instantclient/instantclient_11_2/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin
DATETIME=`date +%d-%^h-%y`
CTLDIR=/u04/ftp/abcdev/ac/inactive_ctls
LOGDIR=/u04/ftp/abcdev/abc/inactive_logs
BADDIR=/u04/ftp/abcdev/abc/inactive_bads
ctlfile=$CTLDIR/$DATETIME".ctl"
logfile=$LOGDIR/$DATETIME".log"
badfile=$BADDIR/$DATETIME".bad"
FILENAMES=/u04/ftp/abcdev/abc/scripts/file_list.`date -d "1 days ago" -u +%Y%m%d`
echo "LOAD DATA" > $ctlfile
echo "INFILE *" >> $ctlfile
echo "BADFILE '$badfile'" >> $ctlfile
echo "APPEND" >> $ctlfile
echo "INTO TABLE SCOTT.SFTP_FILE_NAMES" >> $ctlfile
echo "FIELDS TERMINATED BY ','" >> $ctlfile
echo "(" >> $ctlfile
echo "FILE_NAME CHAR(100)," >> $ctlfile
echo "PROCESSED_ON DATE" >> $ctlfile
echo ")" >> $ctlfile
echo "BEGINDATA" >> $ctlfile
if [ $(grep -m 1 '\.xml' $FILENAMES|awk '{print $NF}') ];
then
for line in $(grep '\.xml' $FILENAMES|awk '{print $NF}')
do
echo `echo $line|tr -d '\r'`","$DATETIME >> $ctlfile
done
else
echo ""","$DATETIME >> $ctlfile
fi
$ORACLE_HOME/bin/sqlldr control=$ctlfile userid=SCOTT/tiger@ORCL log=$logfile
exit

4. run the script using the command -  ./ load_files2db.sh or sh load_files2db.sh

5. connect to scott user and verify the results 

SELECT * FROM SCOTT.SFTP_FILE_NAMES;

FILE_NAME        PROCESSED_ON
----------------------------------------------------
20201102_orcl_result1.xml 03-NOV-20
20201102_orcl_result2.xml 03-NOV-20
20201102_orcl_result3.xml 03-NOV-20
20201102_orcl_result4.xml 03-NOV-20
20201102_orcl_result5.xml 03-NOV-20
20201102_orcl_result50.xml 03-NOV-20
20201102_orcl_result6.xml 03-NOV-20

echo and read commands in UNIX

 [oracle]$ echo "Hello World"
Hello World
[oracle]$ read
Hi
[oracle]$ echo "Hello";echo "World"
Hello
World
[oracle]$ echo "what is your name?";read name;echo "Hello $name"
what is your name?
Raj
Hello Raj
[oracle]$ echo "what is your name?";read name;echo "Hello $name";echo "Press Enter to continue";read
what is your name?
Raj
Hello Raj
Press Enter to continue
[oracle]$

$ echo "One"
One
$ echo "one" > output.txt
$ cat output.txt
one
$ echo "one" > output.txt
$ cat output.txt
one
$ echo "one" >> output.txt
$ cat output.txt
one
one
$ echo "one" >> output.txt
$ cat output.txt
one
one
one
$

sed command in unix

$ vi sample.txt
$ cat sample.txt
one two three
this is example for sed substitution
$ cat sample.txt|sed 's/t/T/'
one Two three
This is example for sed substitution
$ cat sample.txt|sed 's/t/T/g'
one Two Three
This is example for sed subsTiTuTion

$ sed 's/t/T/g' sample.txt
one Two Three
This is example for sed subsTiTuTion
$ cat sample.txt
one two three
this is example for sed substitution
$ sed -i 's/t/T/g' sample.txt
$ cat sample.txt
one Two Three
This is example for sed subsTiTuTion
$
**************************************************************************

[oracle]$ cat sample.txt
one two three
10 aug 1991
5 may 2020
sed command is used to replace string or characters
[oracle]$ sed 's/[0-9]/*/g' sample.txt
one two three
** aug ****
* may ****
sed command is used to replace string or characters
[oracle]$ sed 's/[0-9][0-9]/*/g' sample.txt
one two three
* aug **
5 may **
sed command is used to replace string or characters
[oracle]$ sed 's/[0-9]/(&)/g' sample.txt
one two three
(1)(0) aug (1)(9)(9)(1)
(5) may (2)(0)(2)(0)
sed command is used to replace string or characters
[oracle]$ sed 's/[a-z]/(&)/g' sample.txt
(o)(n)(e) (t)(w)(o) (t)(h)(r)(e)(e)
10 (a)(u)(g) 1991
5 (m)(a)(y) 2020
(s)(e)(d) (c)(o)(m)(m)(a)(n)(d) (i)(s) (u)(s)(e)(d) (t)(o) (r)(e)(p)(l)(a)(c)(e) (s)(t)(r)(i)(n)(g) (o)(r) (c)(h)(a)(r)(a)(c)(t)(e)(r)(s)
[oracle]$ sed -i  's/t/T/g' sample.txt
[oracle]$ cat sample.txt
one Two Three
10 aug 1991
5 may 2020
sed command is used To replace sTring or characTers
[oracle]$ sed 's/[a-z]/*/g' sample.txt
*** T** T****
10 *** 1991
5 *** 2020
*** ******* ** **** T* ******* *T**** ** ******T***
[oracle]$ sed 's/[A-Z]/*/g' sample.txt
one *wo *hree
10 aug 1991
5 may 2020
sed command is used *o replace s*ring or charac*ers
[oracle]$ sed 's/[A-Za-z]/*/g' sample.txt
*** *** *****
10 *** 1991
5 *** 2020
*** ******* ** **** ** ******* ****** ** **********
[oracle]$ sed 's/[a-Z]/*/g' sample.txt
*** *** *****
10 *** 1991
5 *** 2020
*** ******* ** **** ** ******* ****** ** **********
[oracle]$ sed 's/[0-Z]/*/g' sample.txt
*** *** *****
** *** ****
* *** ****
*** ******* ** **** ** ******* ****** ** **********
You have mail in /var/spool/mail/ougbs
[oracle]$ sed 's/[0-5]/*/g' sample.txt
one Two Three
** aug *99*
* may ****
sed command is used To replace sTring or characTers
[oracle]$ sed 's/[d-m]/*/g' sample.txt
on* Two T*r**
10 au* 1991
5 *ay 2020
s** co**an* *s us** To r*p*ac* sTr*n* or c*aracT*rs
[oracle]$

*********************************************************************

Convert date to UTC TimeZone in Oracle

SELECT SYSDATE,
       SYSTIMESTAMP,
       CURRENT_DATE,
       CURRENT_TIMESTAMP,
       TO_CHAR (SYSTIMESTAMP AT TIME ZONE 'UTC', 'YYYY/MM/DD HH24:MI:SS TZD')
          DT_AS_UTC,
       TO_CHAR (SYSTIMESTAMP AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS')
          DT_AS_UTC,
       TO_CHAR (SYS_EXTRACT_UTC (SYSTIMESTAMP), 'yyyy-mm-dd"T"hh24:mi:ss"Z"')
          DT_AS_UTC
  FROM DUAL;

 

Data Validation in SQL Server

DROP TABLE if exists #temp1,#temp2;

SELECT empno, HASHBYTES ('SHA2_256', CONCAT ([ename]

      ,[hiredate]

      ,[job]

      ,[deptno]

      ,CONVERT(DECIMAL(32,6),[sal])

      )) HashByte

      INTO #temp1

  FROM [prod].[scott].[EMP];

SELECT empno, HASHBYTES ('SHA2_256', CONCAT ([ename]

      ,[hiredate]

      ,[job]

      ,[deptno]

      ,CONVERT(DECIMAL(32,6),[sal])

      )) HashByte

      INTO #temp2

  FROM [stage].[scott].[EMP];

CREATE clustered INDEX IX ON #temp2  (empno);

CREATE clustered INDEX IX1 ON #temp1  (empno);

-- Data Validation

SELECT top 10  *  FROM #temp1 t1

  WHERE  not EXISTS (SELECT 1 FROM #temp2 t2 where t1.empno=t2.empno and t1.HashByte=t2.HashByte )

-- Sample Record

SELECT 'prod'

   [empno] 

      ,[ename]

      ,[hiredate]

      ,[job]

      ,[deptno]

      ,CONVERT(DECIMAL(32,6),[sal])

FROM [prod].[scott].[EMP]

WHERE [empno] = 7863

UNION ALL

SELECT 'stage'

   [empno] 

      ,[ename]

      ,[hiredate]

      ,[job]

      ,[deptno]

      ,CONVERT(DECIMAL(32,6),[sal])

FROM [stage].[scott].[EMP]

WHERE [empno] = 7863;