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;

Change Display Resolution of Oracle Linux 6.5

Check the all possible resolution supported using the following command:
# xrandr

Change or set the resolution using the available resolution

#xrandr -s 1024x768

Dead Locks occured while applying patch# 21314548 R12.AD.B.DELTA.7 PATCH

Dead Locks occured while applying patch# 21314548 

sqlplus -s APPS/***** @/u02/app/oracle/PROD/apps/apps_st/appl/ad/12.0.0/patch/115/sql/ADFIXUSER.sql
Connected.
begin
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.AD_ZD_SYS", line 39
ORA-06512: at line 2


The error is a lock on object.

1. stop you patch application

2.  Stop and start your database

3. re-apply the patch .

Oracle BI Administration Tool: Connection has failed

Oracle BI Administration Tool: Connection has failed

Import metadata error message:

The connection has failed.
[nQSError: 17014] Could not connect to Oracle Database.
[nQSError: 17001] Oracle Error code: 12154, message: ORA-12154:
TNS:could not resolve the connect identifier specified at OCI call OCIServerAttach.

Solution:

Oracle Instant Client must be installed in your machine and set environment variable TNS_ADMIN pointing to network admin folder of oracle instant client.

Click ok to close window

Start the OBIEE Administration Tool and import metadata again.

Employee Payroll Costing Creation Using API

Employee Payroll Creation Using API

Declare
  Cursor c_cost_emp_cur Is
         Select paaf.assignment_id
         From per_all_assignments_f paaf
             ,per_all_people_f papf
         Where papf.person_id = paaf.person_id
         And sysdate between papf.effective_start_date And papf.effective_end_date
         And sysdate between paaf.effective_start_date and paaf.effective_end_date

         And Not exists (Select 'x'
                                    From pay_cost_Allocations_F pcaf
                                   Where pcaf.assignment_id =  paaf.assignment_id
                                   And sysdate Between pcaf.effective_Start_date and pcaf.effective_end_date);
Type l_emp_rec Is Table of c_cost_emp_cur%rowtype Index By Pls_Integer;
l_emp_table l_emp_rec;
l_flag Boolean;
l_combination_name          varchar2(150);
l_cost_allocation_id        number;
l_cost_effective_start_date date;
l_cost_effective_end_date   date;
l_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%type;
l_cost_obj_version_number   number;

Begin
l_emp_table.delete;
Open c_cost_emp_cur;
Fetch c_cost_emp_cur Bulk Collect Into l_emp_table;
Close c_cost_emp_cur;

For i In 1..l_emp_table.Count
Loop
 -- We enabled only cost center in cost allocation.
  pay_cost_allocation_api.create_cost_allocation (p_validate => false
                        ,p_effective_date       => '01-nov-2015'
                        ,p_assignment_id        => l_emp_table(i).assignment_id
                        ,p_proportion           => 1 -- the parameter value should be 1 , This is equal to 100% entered via applcation screen
                        ,p_business_group_id    => 101
                       -- ,p_segment1             => l_company
                        ,p_segment2             => '254'
                        --,p_segment3             => l_cost_code
                        --,p_segment4             => l_budget_code
                        --,p_segment5             => l_account
                        -- Out
                        ,p_combination_name     => l_combination_name
                        ,p_cost_allocation_id   => l_cost_allocation_id
                        ,p_effective_start_date => l_cost_effective_start_date
                        ,p_effective_end_date   => l_cost_effective_end_date
                        ,p_object_version_number => l_cost_obj_version_number
                        -- In / Out
                        ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
                        );

End Loop;

commit;

End;

Linux: Delete Files Older than x number of days

Delete files older than x number of days

$find . -mtime +5 -print 0 |xargs -0 rm -f

Adobe Acrobat: JavaScript Enabled warning Message while opening some documents

Enable JavaScript in Adobe Reader , follow the following steps


1. Launch Acrobat or Adobe Reader.
2. Select Edit>Preferences
3. Select the JavaScript Category
4. Uncheck the ‘Enable Acrobat JavaScript’ option
5. Click OK

Remove Special Characters from String

Remove Special Characters from a String

REGEXP_REPLACE('String for replace','[^[:alnum:]'' '']', NULL)

E.g

SELECT REGEXP_REPLACE('##787__!!','[^[:alnum:]'' '']', NULL) FROM dual;

787

How To Install LVMs With a GUI

The Logical Volume Manager or LVM

Install LVM GUI utility

On RHEL

You can install the package using the CLI, by issuing in a terminal:

#yum install system-config-lvm

Once installed, you can issue the following command to launch it:

#sudo system-config-lvm

Once installed  the program will appear under “System” -> “Administration” -> “Logical Volume Management”.

Update People National Identifier using API

Bulk Update Employee national identifier using oracle hrms api

Declare
 Cursor c_emp_cur (cp_employee_number In Varchar2) Is
        Select papf.employee_number
              ,papf.effective_start_date
              ,papf.object_Version_number
              ,papf.person_id
        From per_All_people_f papf
        Where papf.business_group_id = <<Business Group Id>>
        And papf.person_type_id = <<Active Person Type Id>>
        And papf.employee_number = cp_employee_number
        And sysdate Between papf.effective_start_date And papf.effective_end_date;
  l_emp_rec c_emp_cur%rowtype;
  l_emp_no Varchar2(30);
  l_effective_start_date Date;
  l_effective_end_date Date;
  l_full_name per_all_people_f.employee_number%type;
  l_commnet_id Number;
  l_name_combination_warning Boolean;
  l_assign_payroll_warning Boolean;
  l_orig_hire_warning Boolean;
Begin
 -- initialize app session
 fnd_global.apps_initialize(<<AOL user id>>,<<HRMS Responsibility Id>>,HRMS Application Id (Default Id 800));
 For i In (Select  emp_no,
                           nat_no
              from xxhr_emp_nat )
  Loop
    Open c_emp_cur(cp_employee_number => i.emp_no);
    Fetch c_emp_cur Into l_emp_rec;
    Close c_emp_cur;
  l_emp_no := l_emp_rec.employee_number;
  hr_person_api.update_person
  (p_effective_date               => l_emp_rec.effective_start_date
  ,p_datetrack_update_mode        => 'CORRECTION'
  ,p_person_id                    => l_emp_rec.person_id
  ,p_object_version_number        => l_emp_rec.object_Version_number
  ,p_employee_number              => l_emp_no
  ,p_national_identifier          => i.nat_no
  ,p_effective_start_date         => l_effective_start_date
  ,p_effective_end_date           => l_effective_end_date
  ,p_full_name                    => l_full_name
  ,p_comment_id                   => l_commnet_id
  ,p_name_combination_warning     => l_name_combination_warning
  ,p_assign_payroll_warning       => l_assign_payroll_warning
  ,p_orig_hire_warning            => l_orig_hire_warning
  );
  End Loop;
  commit;
End;

XML Publisher RTF template Page Break

Use any one the syntax inside the form field

Delete Duplicate Rows Based on a Column

There are so many ways available, but I used oracle analytic .

Use analytic to delete duplicate rows
You can  detect and delete duplicate rows using Oracle analytic functions:

delete from
   <<xxx_accounts>>

where rowid in
 (select rowid from
   (select
     rowid,
     row_number()
    over
     (partition by account_name order by account_name) dup
    from <<xxx_accounts>>
    )
  where dup > 1 )

How to Sign Into Two or More Skype Accounts at Once in Windows

To launch a second Skype application on Windows, press Windows Key + R to open the Run dialog, copy-paste the below command into it, and press Enter.

On a 64-bit version of Windows

"C:\Program Files (x86)\Skype\Phone\Skype.exe" /secondary

On a 32-bit version of Windows

"C:\Program Files\Skype\Phone\Skype.exe" /secondary

You can create shortcut also, so you don't need to repeat the same command again and again

Secure Admin must be enabled to access the DAS remotely

I installed Glassfish on my development server and  access the administration module locally by using “localhost:4048”. But when I try to access it remotely, by using “http:/my_host:port”. It shows me an error as cannot login with the message as

Secure Admin must be enabled to access the DAS remotely

Solution:
Activate the secure admin by using the following command line 

asadmin --host localhost --port 4848 enable-secure-admin

The host is the host name or IP address for the Glassfish Server which we need to enable the secure admin.The port is the target Glassfish Server port which wee need to enable the secure admin.

Rollback

 disable the secure admin, it can be done easily as the following: –
asadmin --host [host] --port [port] disable-secure-admin 

Glassfish Change Admin Password

Use change-admin-password sub command to change the password of glassfish admin user. Default administrator username of glassfish is "admin". Default password of "admin"" user is blank on first installation.


Changing the Administrator Password 

Ensure that the server is running

asadmin --user admin
asadmin> change-admin-password
Please enter the old admin password>
Please enter the new admin password>
Please enter the new admin password again>
Command change-admin-password executed successfully

How to Delete XML Publisher Definition

In the XML publisher Responsibility,  data definition there is no option to delete unwanted or wrong definitions.

BEGIN

XDO_DS_DEFINITIONS_PKG.DELETE_ROW('<<APPLICATION SHORT NAME>>', '<<XML DEFITION CODE>>')

END;

Removing Special Character File Names in Linux

Accidentally a file was created with a name of "-rw-r--r--.err". I can't remove this file using our normal shell commands. At last lot of searches in Google I found a solution.

Find the inode of the file using the following command

$ ls –il

 9078727 is inode number.

Now Use find command to delete file by inode:

$ find . -inum 9078727 -exec rm -f {} \;

It will find that file and will remove it with force i.e remove without prompt.