Oracle GG Golden Gate Replicat Process is Abended in UPDATE. What to check.

==========================
General
==========================
The issue: One of the Oracle GG (Golden Gate) REPLICAT processes is in ABENDED status

>%cd /software/ogg/1212
/software/ogg/1212/>% ./ggsci
info all

EXTRACT     RUNNING     EXT_S_01    00:00:07      00:00:06
EXTRACT     RUNNING     EXT_S_03    00:00:06      00:00:06
EXTRACT     RUNNING     EXT_S_04    00:00:05      00:00:09
EXTRACT     RUNNING     EXT_S_05    00:00:06      00:00:01
REPLICAT    RUNNING     REP_I_01    00:00:00      00:00:08
REPLICAT    RUNNING     REP_I_03    00:00:00      00:00:04
REPLICAT    RUNNING     REP_I_04    00:00:00      00:00:09
REPLICAT    RUNNING     REP_I_05    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_01    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_03    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_04    00:00:02      00:00:03
REPLICAT    ABENDED     REP_P_05    6208:22:23    00:44:39
REPLICAT    RUNNING     REP_P_06    00:00:00      00:00:08
REPLICAT    RUNNING     REP_S_01    00:00:00      00:00:03
REPLICAT    RUNNING     REP_S_03    00:00:00      00:00:01
REPLICAT    RUNNING     REP_S_04    00:00:00      00:00:00


==========================
Error
==========================

When checking error log for the Replicat process, there are these errors

2018-07-30 14:23:27  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rep_p_05.prm:  
Aborted grouped transaction on 'BEL_BELGA_MOCOQ.GA_PRODUCT_TREE_CONF', Database error 1403 (OCI Error ORA-01403: no data found, SQL <UPDATE "BEL_BELGA_MOCOQ"."GA_PRODUCT_TREE_CONF" x SET x."ITEM_ID" = :a1 WHERE x."ITEM_ID" = :b0>).

2018-07-30 14:23:27  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rep_p_05.prm: 
SQL error 1403 mapping BEL_BELGA_MOCOQ.GA_PRODUCT_TREE_CONF 
to BEL_BELGA_MOCOQ.GA_PRODUCT_TREE_CONF OCI Error ORA-01403: no data found, SQL <UPDATE "BEL_BELGA_MOCOQ"."GA_PRODUCT_TREE_CONF" x SET x."ITEM_ID" = :a1 WHERE x."ITEM_ID" = :b0>.

2018-07-30 14:23:27  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rep_p_05.prm:  Error mapping from BEL_BELGA_MOCOQ.GA_PRODUCT_TREE_CONF to BEL_BELGA_MOCOQ.GA_PRODUCT_TREE_CONF.
2018-07-30 14:23:27  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rep_p_05.prm:  PROCESS ABENDING.



==========================
SKIPTRANSACTION
==========================
The syntax:

START REPLICAT <group_name> SKIPTRANSACTION

Maybe need to repeat this step several times, since several transactions should be skipped.

SKIPTRANSACTION causes Replicat to skip the first transaction after its expected startup position in the trail. 

All operations from that first transaction are excluded.

If the MAXTRANSOPS parameter is also being used for this Replicat, it is possible that the process will start to read the trail file from somewhere in the middle of a transaction. 

In that case, the remainder of the partial transaction is skipped, and Replicat resumes normal processing from the next begin-transaction record in the file. 

The skipped records are written to the discard file if the DISCARDFILE parameter is being used; 
Otherwise, a message is written to the report file that is similar to:

User requested START SKIPTRANSACTION. 
The current transaction will be skipped. 


Transaction ID txid, position Seqno seqno, RBA rba


==========================
Fix for error
==========================

The solution is to reset the Replicat Process on BOTH nodes.

ALTER REPLICAT REP_P_05, BEGIN NOW
REPLICAT altered

START REPLICAT REP_P_05
Sending START request to MANAGER ...
REPLICAT REP_P_05 starting

INFO ALL

EXTRACT     RUNNING     EXT_S_01    00:00:07      00:00:06
EXTRACT     RUNNING     EXT_S_03    00:00:06      00:00:06
EXTRACT     RUNNING     EXT_S_04    00:00:05      00:00:09
EXTRACT     RUNNING     EXT_S_05    00:00:06      00:00:01
REPLICAT    RUNNING     REP_I_01    00:00:00      00:00:08
REPLICAT    RUNNING     REP_I_03    00:00:00      00:00:04
REPLICAT    RUNNING     REP_I_04    00:00:00      00:00:09
REPLICAT    RUNNING     REP_I_05    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_01    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_03    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_04    00:00:09      00:00:04
REPLICAT    RUNNING     REP_P_05    00:00:00      00:00:08
REPLICAT    RUNNING     REP_S_01    00:00:00      00:00:03
REPLICAT    RUNNING     REP_S_03    00:00:00      00:00:01
REPLICAT    RUNNING     REP_S_04    00:00:00      00:00:00
REPLICAT    RUNNING     REP_S_05    00:00:00      00:00:04

==========================
Deep Fix for error
==========================
Need to add SCHEMATRANDATA or TRANDATA on the mapping schemas.

ADD SCHEMATRANDATA <schema_name>

ADD TRANDATA <schema_name>.*

If possible, use the ADD SCHEMATRANDATA command rather than the ADD TRANDATA command. 
The ADD SCHEMATRANDATA command ensures replication continuity should DML occur on an object for which DDL has just been performed.


ADD SCHEMATRANDATA

Example:
DBLOGIN...
ADD SCHEMATRANDATA scott

Use ADD SCHEMATRANDATA to enable schema-level supplemental logging for a table. 
ADD SCHEMATRANDATA acts on all of the current and future tables in a given schema to automatically log a superset of available keys that Oracle GoldenGate needs for row identification.


ADD SCHEMATRANDATA is valid for both integrated and classic capture and does the following:

- Enables Oracle supplemental logging for new tables created with a CREATE TABLE.

- Updates supplemental logging for tables affected by an ALTER TABLE to add or drop columns.

- Updates supplemental logging for tables that are renamed.


- Updates supplemental logging for tables for which unique or primary keys are added or dropped.

ADD SCHEMATRANDATA is not straight forward:
1. Need to apply Oracle Patch 10423000 to the source database if the Oracle version is earlier than 11.2.0.2.

2. Oracle strongly encourages putting the source database into forced logging mode and enabling minimal supplemental logging at the database level when using Oracle GoldenGate. 

ADD TRANDATA

Example:
DBLOGIN...
ADD TRANDATA scott.my_table

Use ADD TRANDATA to enable Oracle GoldenGate to acquire the transaction information that it needs from the transaction records.

By default, ADD TRANDATA for Oracle enables the unconditional logging of the primary key and the conditional supplemental logging of all unique key(s) and foreign key(s) of the specified table.

If possible, use the ADD SCHEMATRANDATA command rather than the ADD TRANDATA command. 

The ADD SCHEMATRANDATA command ensures replication continuity should DML occur on an object for which DDL has just been performed.







==========================
Alternative Fix for error

==========================

Another solution, which I did not test myself, would be to follow Oracle TechNote:

"Replicat Abends with "OGG-01154 SQL error 1403 mapping" (Doc ID 1329189.1)"

Symptoms
Replicat abending with the below error:
2011-05-31 22:42:22  WARNING OGG-01004  Aborted grouped transaction on 'STG_SAP.HRP1000', Database error 100 (retrieving bind info for query).
2011-05-31 22:42:22  WARNING OGG-01003  Repositioning to rba 21070779 in seqno 15.
2011-05-31 22:42:22  WARNING OGG-01154  SQL error 1403 mapping SAPSR3.HRP1000 to STG_SAP.HRP1000.
2011-05-31 22:42:22  WARNING OGG-01003  Repositioning to rba 21070779 in seqno 15.

Cause
The cause is not entirely identified 

Solution
Deleted trandata for all the tables and add them again
GGSCI (Sapzax04) 1> DBLOGIN USERID GGSUSER PASSWORD ****
GGSCI (Sapzax04) 2> DELETE TRANDATA XXXX.YYYY

GGSCI (Sapzax04) 2> ADD TRANDATA XXXX.YYYY

==========================

Reference

==========================

Approach to Troubleshoot an Abended OGG Process

http://www.oracle-scn.com/approach-to-troubleshoot-an-abended-ogg-process/

Code Example. PL/SQL Kill and re-create Stuck Jobs

=====================================
General
=====================================
For a job connecting to a remote DB via a db_link, sometimes due to network issues, the job is in "stuck" mode
as a workaround, there is a job running every 2 hours, and killing jobs that run over 1 hour.


=====================================
PL/SQL CODE

=====================================

CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL IS

-----------------------------------------------
-- Known Exceptions
-----------------------------------------------
  EXP_ORA_SESS_MARK_FOR_KILL            EXCEPTION;
  PRAGMA EXCEPTION_INIT(EXP_ORA_SESS_MARK_FOR_KILL,-31);
  
  -------------------------------------------------
  PROCEDURE write_sga_w_log(p_procedure_name IN VARCHAR2,
                            p_data           IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO SGA_W_LOG (module_name, msg_text, msg_date)
    VALUES (p_procedure_name, p_data, SYSDATE);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
       NULL;
  END write_sga_w_log;
  
  
  -------------------------------------------------
  PROCEDURE KILL_LONG_RUNNING_JOB  IS
    v_module_name      SGA_W_LOG.module_name%TYPE;
    v_msg_text         SGA_W_LOG.msg_text%TYPE;
    v_sql_cmd          VARCHAR2(1000);
    
    CURSOR long_running_jobs_cur IS
    SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||V$SESSION.sid||','||V$SESSION.serial#||'''', 1,15)||' IMMEDIATE' AS kill_cmd,
           V$SESSION.sid, 
           V$SESSION.serial#,
           DBA_JOBS.what
      FROM DBA_JOBS ,
           DBA_JOBS_RUNNING,
           V$SESSION
     WHERE DBA_JOBS.this_date < SYSDATE - 1/24
       AND DBA_JOBS_RUNNING.job = DBA_JOBS.job
       AND DBA_JOBS_RUNNING.sid = V$SESSION.sid
       AND V$SESSION.status <> 'KILLED';       
           
  BEGIN  
    v_module_name := 'KILL_LONG_RUNNING_JOB';
    
    FOR long_running_jobs_rec IN long_running_jobs_cur LOOP
      BEGIN
        v_sql_cmd := long_running_jobs_rec.kill_cmd;
        v_msg_text := 'Killing Stuck Job :'||long_running_jobs_rec.what;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);        
        
        v_msg_text := 'Runing: '||v_sql_cmd;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);
        
        EXECUTE IMMEDIATE v_sql_cmd;
    
        
      EXCEPTION
        WHEN EXP_ORA_SESS_MARK_FOR_KILL THEN
          v_msg_text := 'Done! Session Was Marked For Kill';
          WRITE_SGA_W_LOG(v_module_name,v_msg_text);          
        
        WHEN OTHERS THEN
          v_msg_text := 'Unexpected Error: '||SQLERRM;
          WRITE_SGA_W_LOG(v_module_name,v_msg_text);    
      END;
    END LOOP;    
  EXCEPTION
    WHEN OTHERS THEN
      v_msg_text := 'Unexpected Error: '||SQLERRM;
      WRITE_SGA_W_LOG(v_module_name,v_msg_text);    
  END KILL_LONG_RUNNING_JOB;    

  -------------------------------------------------
  PROCEDURE RECREATE_JOB  IS
    v_module_name      SGA_W_LOG.module_name%TYPE;
    v_msg_text         SGA_W_LOG.msg_text%TYPE;
    v_sparx_what       VARCHAR2(1000);
    CURSOR get_job_list_cur(cp_job_what IN VARCHAR2) IS
    SELECT *
      FROM DBA_JOBS 
     WHERE UPPER(WHAT) = cp_job_what;
           
  BEGIN  
    v_module_name := 'RECREATE_JOB';
    v_sparx_what := UPPER('EXT_SPARX.schedule;');
        
    
    FOR get_job_list_rec IN get_job_list_cur(v_sparx_what) LOOP

        v_msg_text := 'Deleting Job :'||get_job_list_rec.job;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);        
        
        BEGIN
          DBMS_JOB.remove(get_job_list_rec.job);
          COMMIT;
        END;
        
        v_msg_text := 'Creating Job :'||get_job_list_rec.job;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);        
        
        BEGIN
          DBMS_JOB.ISUBMIT
          ( job       => get_job_list_rec.job 
           ,what      => get_job_list_rec.what
           ,next_date => get_job_list_rec.next_date
           ,interval  => get_job_list_rec.interval
          );
          COMMIT;
        END;
    END LOOP;    
  EXCEPTION
    WHEN OTHERS THEN
      v_msg_text := 'Unexpected Error: '||SQLERRM;
      WRITE_SGA_W_LOG(v_module_name,v_msg_text);    
  END RECREATE_JOB;    
  -------------------------------------------------
END;

=====================================
Job

=====================================

DECLARE

   v_job_number NUMBER(10);
BEGIN
 DBMS_JOB.SUBMIT (JOB => v_job_number, 
                  WHAT => 'ADMIN_UTIL.KILL_LONG_RUNNING_JOB;', 
                  NEXT_DATE => TRUNC(SYSDATE,'HH24')+((FLOOR(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/1)+121)*1)/(1440), 
                  INTERVAL => 'TRUNC(SYSDATE,''HH24'')+((FLOOR(TO_NUMBER(TO_CHAR(SYSDATE,''MI''))/1)+121)*1)/(1440)'
 );
 COMMIT;
END;
/

ORA-21561: OID generation failed

=================================
General
=================================
sqlplus is giving an error:
my_user@ip:>% sqlplus system/sys_pass@orainst

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 19 19:02:16 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-21561: OID generation failed

=================================
Investigation
=================================

Findings:

A.  Linux prompt has changed 

from: my_user@srv-020-app-009

to:      my_user@ip:

B. tnsping is working fine:

my_user@ip: tnsping orainst

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 19-JUL-2018 19:02:10

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/software/oracle/112/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 100.200.220.230)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = otainst)))
OK (10 msec)

But sqlplus is giving an error:
my_user@ip:>% sqlplus system/sys_path@orainst

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 19 19:02:16 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-21561: OID generation failed

/etc/hosts
Checking /etc/hosts - that file was not changed for a very long time
What might be the issue?


~root/.bash_history
Checking for suspicious commands inside ~root/.bash_history.
There is this command:

hostname ip

revert that setup by:

hostname srv-020-app-009

Now Linux hostname of the client, is matching the hostname inside /etc/hosts/

# Do not remove the following line, or various programs
# that require network functionality will fail.
::1             localhost6.localdomain6 localhost6
127.0.0.1       localhost.localdomain localhost
100.200.220.230 srv-020-app-009 

Now the prompt in Linux is correct.

And sqlplus is working fine.

Migrate Tables Data from schema to schema

====================================
General
====================================
Example of how to move data from schema to schema.
This includes creating tables using 'CREATE TABLE AS SELECT' and DBMS_METADATA.get_ddl.


====================================
Code
====================================

Step A. - Create DDL and INSERT SQLs

--Generate DDL for Partitioned Tables script
SET LONG 5000
SET PAGESIZE 0
SET LINESIZE 400
SET FEEDBACK OFF
COL ddl_cmd for A400 WORD_WRAP

spool cre_FACT_ROAMER_SCENARIO.sql
SELECT 
DBMS_METADATA.get_ddl('TABLE','FACT_ROAMER_SCENARIO') as ddl_cmd
FROM DUAL;
spool off

spool cre_FACT_ROAMER_CAMPAIGNS.sql
SELECT 
DBMS_METADATA.get_ddl('TABLE','FACT_ROAMER_CAMPAIGNS') as ddl_cmd
FROM DUAL;
spool off

--Generate Populate Partitioned Tables script
SET LONG 10000
SET PAGESIZE 0
SET LINESIZE 400
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
COL sql_str FOR A300

spool populate_FACT_ROAMER_SCENARIO.sql
SELECT 'INSERT /*+ APPEND */ INTO SCHEMA_B.FACT_ROAMER_SCENARIO SELECT * FROM SCHEMA_A.FACT_ROAMER_SCENARIO PARTITION('||partition_name||') WHERE ROWNUM < 1000000;'||CHR(10)||'commit;' AS sql_str 
FROM USER_TAB_PARTITIONS 
WHERE table_name = 'FACT_ROAMER_SCENARIO';
spool off

spool populate_FACT_ROAMER_CAMPAIGNS.sql
SELECT 'INSERT /*+ APPEND */ INTO SCHEMA_B.FACT_ROAMER_CAMPAIGNS SELECT * FROM SCHEMA_A.FACT_ROAMER_CAMPAIGNS PARTITION('||partition_name||') WHERE ROWNUM < 1000000;'||CHR(10)||'commit;' AS sql_str 
FROM USER_TAB_PARTITIONS 
WHERE table_name = 'FACT_ROAMER_CAMPAIGNS';
spool off

--Generate Big Non Partitioned Tables using CREATE AS SELECT script
SET LONG 10000
SET PAGESIZE 0
SET LINESIZE 400
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
COL sql_str FOR A300

spool create_big_tables.sql
SELECT 'CREATE TABLE SCHEMA_B.'||SEGMENT_NAME||' AS SELECT * FROM SCHEMA_A.'||SEGMENT_NAME||' WHERE 1=2' as sql_str
from dba_segments 
WHERE OWNER = 'SCHEMA_A' 
  AND bytes > 100000 
  AND SEGMENT_TYPE = 'TABLE';
spool off

--Generate Populate Non Partitioned Tables script
spool populate_big_tables.sql
SELECT 'INSERT /*+ APPEND */ INTO SCHEMA_B.'||SEGMENT_NAME||' SELECT * FROM SCHEMA_A.'||SEGMENT_NAME||' WHERE ROWNUM < 1000000;'||CHR(10)||'commit;' 
from dba_segments 
WHERE OWNER = 'SCHEMA_A' 
  AND bytes > 100000 
  AND SEGMENT_TYPE = 'TABLE';
spool off


--Generate TRUNCATE SQLs - for Partitioned Tables
SET LONG 10000
SET PAGESIZE 0
SET LINESIZE 400
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
COL sql_str FOR A300

spool truncate_partitiones.sql
SELECT 'ALTER TABLE '||table_name||' TRUNCATE PARTITION '||partition_name||';' as sql_str  
FROM USER_TAB_PARTITIONS 
WHERE table_in ('.FACT_ROAMER_CAMPAIGNS','FACT_ROAMER_SCENARIO');

--Generate TRUNCATE SQLs - for Non Partitioned Tables
spool truncate_tables.sql
SELECT 'TRUNCATE TABLE '||table_name||';' as sql_str 
FROM USER_TABLES;

Step B. - Execute Generated SQLs
sqlplus  SCHEMA_B/SCHEMA_B@orainst

--For Non Partitioned Tables
@create_big_tables.sql
@populate_big_tables.sql

--For Partitioned Tables
@cre_FACT_ROAMER_SCENARIO.sql

@cre_FACT_ROAMER_SCENARIO.sql

@populate_FACT_ROAMER_SCENARIO.sql

@populate_FACT_ROAMER_CAMPAIGNS.sql

if condition in sqlplus by Example

=================================
General
=================================
sqlplus is a non procedural language.
Thus implementing "if" condition in sql script is not straight forward.
This example, is using logic to determine weather the installation is running on EE or SE Oracle Edition, and per returned result, installing tables in Partitioned, or non Partitioned mode, or, in case table was already installed, skip the installation all together.

This is done via spooling a name of the file to be executed into generated gen_install_rep_online.sql file

Then executing the gen_install_rep_online.sql file.
=================================
Code Example, "if in sqlplus"
=================================
@../set_connection.sql

connect &&user/&&pass@&&connectstr

set serverout on

exec dbms_output.enable(100000); 

set heading off linesize 132 pagesize 1000  heading off feedback off

spool gen_install_rep_online.sql

DECLARE

  v_install_type     NUMBER;

  v_install_type_str VARCHAR2(100);

BEGIN  

  SELECT TO_CHAR(is_installed||is_ee) AS install_type 

   INTO v_install_type 

   FROM

  (

  SELECT (SELECT COUNT(*) FROM USER_TABLES WHERE table_name = 'REP_DAILY_DATA') AS is_installed,

         (SELECT COUNT(*) FROM V$VERSION WHERE UPPER(banner) LIKE '%ENTERPRISE EDITION%') as is_ee

  FROM DUAL

  );

  IF v_install_type = '01' THEN

    dbms_output.put_line('@./install_partitioned_rep_online.sql');

  ELSIF v_install_type = '00' THEN    dbms_output.put_line('@./install_non_partitioned_rep_online.sql');  

  ELSE

    dbms_output.put_line('@./do_nothing.sql');  

  END IF;

END;

/

spool off  

set heading on serverout off verify on define on

@../set_connection.sql

@./gen_install_rep_online.sql

Oracle Dates Example

=====================================
General
=====================================
Code example of creating a table DATES_CALENDER with dates in different format

=====================================
Code Example
=====================================

PROCEDURE populate_dates_calender IS

  
  v_module_name        REP_W_LOG.procedure_name%TYPE;
  v_msg_text           REP_W_LOG.data%TYPE;    
  v_max_day_str        DATES_CALENDER.day%TYPE;
  v_max_day            DATE;  
  v_target_day         DATES_CALENDER.day%TYPE;    
  v_dates_calender_row DATES_CALENDER%ROWTYPE;
  
BEGIN
  v_module_name := 'populate_dates_calender';
  
  SELECT MAX(day) into v_max_day_str FROM DATES_CALENDER;
  IF v_max_day_str IS NULL THEN
    v_max_day_str := '20180101';
  END IF;  
  SELECT TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))+1)||'1231' INTO v_target_day FROM DUAL;
  
  WHILE v_max_day_str < v_target_day LOOP
    v_max_day := TO_DATE(v_max_day_str,'YYYYMMDD')+1;
    v_max_day_str := TO_CHAR(v_max_day,'YYYYMMDD');
    v_dates_calender_row.day := TO_CHAR(v_max_day,'YYYYMMDD');
    v_dates_calender_row.week := TO_CHAR(v_max_day,'YYYY')||'/'||TO_CHAR(v_max_day,'WW');
    v_dates_calender_row.month := TO_CHAR(v_max_day,'YYYY')||'/'||TO_CHAR(v_max_day,'MM');
    v_dates_calender_row.quarter := 'Q'||TO_CHAR(FLOOR(TO_NUMBER(TO_CHAR(TRUNC(v_max_day,'Q'),'MM'))/3)+1)||'/'||TO_CHAR(v_max_day,'YYYY');
    v_dates_calender_row.year := TO_NUMBER(TO_CHAR(v_max_day,'YYYY'));
    
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA'' ';
    v_dates_calender_row.start_date_week := TO_CHAR(TRUNC(v_max_day, 'IW'),'YYYYMMDD');    
    v_dates_calender_row.end_date_week := TO_CHAR(TRUNC(v_max_day, 'IW')+6,'YYYYMMDD');    
    
    INSERT INTO DATES_CALENDER VALUES v_dates_calender_row;
    COMMIT;
  END LOOP;  
  
EXCEPTION
  WHEN OTHERS 
    THEN
      
      v_msg_text :='Unexpected Error .'||SQLERRM;
      write_to_log(v_module_name,v_msg_text);
      RAISE;

END populate_dates_calender;



SELECT day, week, month, quarter, year,
       start_date_week,
       end_date_week
  FROM DATES_CALENDER 
WHERE day LIKE '201805%';

DAY      WEEK       MONTH      QUARTER     YEAR START_DATE_WEEK    END_DATE_WEEK
-------- ---------- ---------- ---------- ----- ------------------ ----------------
20180501 2018/18    2018/05    Q2/2018     2018 20180430           20180506
20180502 2018/18    2018/05    Q2/2018     2018 20180430           20180506
20180503 2018/18    2018/05    Q2/2018     2018 20180430           20180506
20180504 2018/18    2018/05    Q2/2018     2018 20180430           20180506
20180505 2018/18    2018/05    Q2/2018     2018 20180430           20180506
20180506 2018/18    2018/05    Q2/2018     2018 20180430           20180506
20180507 2018/19    2018/05    Q2/2018     2018 20180507           20180513
20180508 2018/19    2018/05    Q2/2018     2018 20180507           20180513
20180509 2018/19    2018/05    Q2/2018     2018 20180507           20180513
20180510 2018/19    2018/05    Q2/2018     2018 20180507           20180513
20180511 2018/19    2018/05    Q2/2018     2018 20180507           20180513
20180512 2018/19    2018/05    Q2/2018     2018 20180507           20180513
20180513 2018/19    2018/05    Q2/2018     2018 20180507           20180513
20180514 2018/20    2018/05    Q2/2018     2018 20180514           20180520
20180515 2018/20    2018/05    Q2/2018     2018 20180514           20180520
20180516 2018/20    2018/05    Q2/2018     2018 20180514           20180520
20180517 2018/20    2018/05    Q2/2018     2018 20180514           20180520
20180518 2018/20    2018/05    Q2/2018     2018 20180514           20180520
20180519 2018/20    2018/05    Q2/2018     2018 20180514           20180520
20180520 2018/20    2018/05    Q2/2018     2018 20180514           20180520