How to caught Invalid Month Exceptiion

Declare
               l_invalid_month exception;
               pragma exception_init( l_invalid_month, -1843 );
               l_date_chr Varchar2(30);
               l_trx_date  Date;
             Begin
               l_trx_date := to_date(l_date_chr,'MM/DD/RRRR');
             Exception
               When l_Invalid_month Then
                    l_trx_date := to_date(l_date_chr,'DD/MM/RRRR');
             End;

Create an exception variable and use exception_init pragma to associate the oracle error code.

Generate Table and Indexes DDL using with dbms_metadata

Generate DDL with dbms_metadata

conn user/password

set pagesize 0
set long 90000
set feedback off
set echo off 

Spool ddl.sql

SELECT DBMS_METADATA.GET_DDL('TABLE',ut.table_name)
     FROM USER_TABLES ut;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;

Spool off

or you can use dual table for generating DDL

set heading off;

set echo off;

set long 90000;

 

spool ddl.sql

 

select dbms_metadata.get_ddl('TABLE','YOUR TABLE NAME','YOUR TABLE OWNER NAME') from dual;

 

select dbms_metadata.get_ddl('INDEX','YOUR INDEX NAME','YOUR TABLE OWNER NAME') from dual;

 

spool off;

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded (bsu.sh)

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded message appears while applying patch 11781879_103604 on R12.2 

$bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=VKXF -prod_dir=$FMW_Home/wlserver_10.3

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded

            at java.util.HashMap.createEntry(HashMap.java:869)

            at java.util.HashMap.addEntry(HashMap.java:856)

            at java.util.HashMap.put(HashMap.java:484)

            at com.bea.cie.common.dao.xbean.XBeanDataHandler.loadPropertyMap(XBeanDataHandler.java:778)

            at com.bea.cie.common.dao.xbean.XBeanDataHandler.(XBeanDataHandler.java:99)

            at com.bea.cie.common.dao.xbean.XBeanDataHandler.createDataHandler(XBeanDataHandler.java:559)

            at com.bea.cie.common.dao.xbean.XBeanDataHandler.getComplexValue(XBeanDataHandler.java:455)

            at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getPatchDependencies(PatchCatalogHelper.java:442)

            at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getPatchDependencies(PatchCatalogHelper.java:464)

            at com.bea.plateng.patch.dao.cat.PatchCatalog.getPatchDependencies(PatchCatalog.java:56)

            at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getInvalidatedPatchMap(PatchCatalogHelper.java:1621)

            at com.bea.plateng.patch.PatchSystem.updatePatchCatalog(PatchSystem.java:436)

            at com.bea.plateng.patch.PatchSystem.refresh(PatchSystem.java:130)

            at com.bea.plateng.patch.PatchSystem.setCacheDir(PatchSystem.java:201)

            at com.bea.plateng.patch.Patch.main(Patch.java:281)

Solution for Linux:

1. Source EBS R12.2 environment
2. Change directory to $FMW_HOME/utils/bsu
3. Backup the existing copy of bsu.sh to bsu.sh_backup
4. Open the bsu.sh using vi editor 
    Change the line MEM_ARGS="-Xms256m -Xmx512m"
    to
    MEM_ARGS="-Xms512m -Xmx1024m -XX:+UseParallelGC"
5. Save the file
6. Run the patch installtion again.
    

Gather Schema Statistics fails with Ora-20001 errors after Database upgrade (11g to 12C)

"Gather Schema Statistics" program reported following errors in request log files:

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Above problem started after migrating database to 11g to 12c

There are 2 reason for this error message

1. There are duplicate rows on FND_HISTOGRAM_COLS table

2. Column doesn't exist in table but it is still listed in FND_HISTOGRAM_COLS table 

Solution

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them logged in as the apps user.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

Identify duplicate rows using the following query:

select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1;

-- Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS

where table_name = '&TABLE_NAME'

and  column_name = '&COLUMN_NAME'

and rownum=1;

-- Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS

where (table_name, column_name) in 

  (

   select hc.table_name, hc.column_name

   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

   where hc.table_name  ='&TABLE_NAME'

   and hc.table_name= tc.table_name (+)

   and hc.column_name = tc.column_name (+)

   and tc.column_name is null

  );

commit;