PostgreSQL function similar to Oracle validate_conversion function

 

This function accepts a text value, and a data type; returns 1 if the text value can be converted to the data type, otherwise 0.   This is tested on  server 15.4

CREATE OR REPLACE FUNCTION validate_conversion (p_input TEXT, p_type VARCHAR(128))
RETURNS integer
AS
$body$
    DECLARE
           valid_convert integer = 1;
           tmp_val TEXT;
               
     BEGIN 
         p_type = TRIM(LOWER(p_type));
               
         EXECUTE FORMAT('SELECT CAST (''%s'' AS %s)', p_input, p_type)
         INTO tmp_val;
           
               IF LENGTH(tmp_val) <> LENGTH(p_input)
               THEN
                       valid_convert = 0;
               END IF;
               RETURN valid_convert;
                          
               EXCEPTION
                       WHEN OTHERS THEN
                               valid_convert = 0;
                               RETURN valid_convert;
END;
$body$
LANGUAGE plpgsql;  

NFS not mounting on Linux

 NFS not mounting

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

Seen below messages in /var/log/messages

Sep  1 08:57:45 server-app1 systemd[1]: u01.mount: Unit is bound to inactive unit dev-oracleoci-oraclevdb1.device. Stopping, too.

Sep  1 08:57:45 server-app1 systemd[1]: Unmounting /u01...

Sep  1 08:57:45 server-app1 nfsrahead[8179]: setting /u01 readahead to 128

Sep  1 08:57:45 server-app1 systemd[1]: u01.mount: Succeeded.

Sep  1 08:57:45 server-app1 systemd[1]: Unmounted /u01.

 

Change:

Created a new compute instance from a custom image which already has the mountpoint reference in fstab which we are trying to use (u01)

 

Solution:

-----------

After altering fstab, systemd need to reparse /etc/fstab and pick up the changes or else the mount point will be unmounted automatically by systemd.

Correct the entry in the /etc/fstab . Make sure that required mount point entry is fstab and unwanted is reqmoved.

Also, make sure to unmount if same nfs export is mounted in other mount point on this server

 

Reload systemctl or reboot the server to avoid this issue.

 

# systemctl daemon-reload

 

We have executed systemctl daemon-reload command and ran mount -a

Issue solved.

How to Resolve Oracle TNS Error ORA -12514 and ORA-12521

 When you are trying to connect to your database using TNS alias you may get below errors like

ORA-12514 : TNS:listener does not currently know of service requested in connect descriptor

OR

ORA-12521: TNS:listener does not currently know of instance requested in connect descriptor

For example you are trying to connect to a DB using sqlplus as below

sqlplus user1@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 23 05:28:08 2023

Version 19.18.0.0.0

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

Enter password:

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When you try tnsping your TNS alias 

Used TNSNAMES adapter to resolve the alias

# tnsping ORCL

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=orclhost1)(PORT=1526)) (CONNECT_DATA= (SERVICE_NAME=ORCL) (INSTANCE_NAME=ORCLCDB1)))

OK (0 msec)

Solution for ORA-12514

It seems the SERVICE_NAME given in the TNS alias is not correct. Please check with your DBA and verify the listener has a service named ORCL. Find out the correct service_name and update your TNS alias in the local tnsnames.ora file.

Solution for ORA-12521

If you are getting ORA-12521: TNS:listener does not currently know of instance requested in

connect descriptor, similarly, try tnsping of the TNS alias. In this case INSTANCE_NAME in the TNS alias description is wrong. Find out the correct instance_name and update your TNS alias in the local tnsnames.ora file.

Oracle error-20001:APP-FND-01972: When Trying To End Date A Responsibility

 EBS Release 12.1.3

When trying to end_date a responsibility that is assigned to a user an error occurs.

ERROR
-----------------------
Oracle error-20001:APP-FND-01972:Error in FND_USER_RESP_GROUPS_API. Update_ Assignment. Cannot update this row because there is no direct assignment of the user XXX to the role FND_RESP\XXX\XXXXXXXXX\STANDARD in the workflow user/Role table. has been detected in FND_USER_RESP_GROUP_API.UPDATE_ASSIGNMENT.

This is caused by invalid data in the workflow user/Role table.

Solution

 please execute the following steps:

1. Go into the responsibility: System administrator.

2. Navigate to Requests > Run

3. Select request ''Workflow Directory Services User/Role Validation' and submit

4. Retry to update the responsibility

Error when application cloning- adcfgclone: EBS R12.2 /etc/inittab does not seem to contain default runlevel information. Linux 7.x

Environment Details:

OS: RHEL 7.8 
EBS Release: 12.2.4
AD/TXK delta 12
ATG: 7

Issue: 

While cloning (adcfgclone) the EBS R12.2 on Linux 7.8 getting below error.

Check Name:CertifiedVersions

Check Description:This is a prerequisite condition to test whether the Oracle software is certified on the current O/S or not. 

/etc/inittab does not seem to contain default runlevel information. 

Expected result: One of oracle-7,redhat-7,redhat-6,oracle-6,oracle-5,enterprise-5.4,enterprise-4,enterprise-5,redhat-5.4,redhat-4,redhat-5,SuSE-10,SuSE-11 

Actual Result: redhat-Red 

Check complete. The overall result of this check is: Failed <<<<

Solution:

Verify the RPM redhat-lsb-4.1-27.0.1.el7.x86_64 is Installed on the server. If missing, install it. This is a pre-requisite for EBS R12.2 on Linux 7.x

Oracle Apps - CDB / PDB how to source the environment and connect to CDB and PDB

 In Oracle E-Business suite with Multi tenant database, load the proper environment variables and connect to the database by performing the following steps:

• For the non-CDB database, source the  $ORACLE_HOME/<non-CDB SID>_<HOST>.env/cmd file. Then, run  sqlplus<user>/<password>@<non-CDB SID>

• For the CDB database, source the $ORACLE_HOME/<CDB SID>_<HOST>.env / cmd file. Then, run sqlplus<user>/<password> or connect as SYSDBA.

• For the PDB database on UNIX/Linux platforms, to connect as SYSDBA, source the $ORACLE_HOME/<CDBSID>_<HOST>.env file. Set the ORACLE_PDB_SID environment variable to <PDB SID>. Then, connect as SYSDBA.

• For the PDB database on Windows platforms, to connect as SYSDBA, source the $ORACLE_HOME/<PDBSID>_<HOST>.cmd file. Then, connect as SYSDBA.

• For the PDB database, to connect to other users, source the $ORACLE_HOME/<PDB_SID>_<HOST>.env / cmd file. Then,run sqlplus <user>/<password>@<PDB SID>

.

How to check if connected to Oracle Pluggable Database (PDB)

 To know if you are connected to a Pluggable Database ( PDB) which is part of Container Database(CDB)

Execute below SQL script.

SELECT DECODE (SYS_CONTEXT ('Userenv', 'CDB_NAME')

                                         ,null,'NO','YES'),

                                  Sys_Context('Userenv', 'CON_ID'),

                                  Sys_Context('Userenv', 'CON_NAME')

                          From dual;

If connected to PDB you will get first column value as YES , a non zero number for second column and PDB Name in third column

If connected to a normal database (non CDB/PDB) you will get first column value as NO, zero for second column and Database Name in third column.

How to Delete and Clear some URLs from Edge Browser Address Bar History

Microsoft Edge is the modern browser available with Windows 10. When you browse a website by typing in the address(URL) directly in the address bar of Edge browser, the URL will be saved, and can be viewed in the address bar. Due to privacy concern, you may want to  clear the history of link(s)/URL from Edge Browser address bar. As of now there is no direct interface to delete url from address bar in Edge.

Follow below steps to delete particular URL(s) without deleting your entire browsing history

Note: You must have privilege to do edit the registry. Take backup of registry as safeguard by exporting.

Warning: Be informed and careful while editing registry. This could cause system instability.

Close all Edge Browser

1. Press Windows+R button to Open Run Window.

2. Run Registry Edit by typing RegEdit in the text box and click OK

3. In Regedit, navigate the entry similar to:

Computer\HKEY_CLASSES_ROOT\Local Settings\Software\Microsoft\Windows\CurrentVersion\AppContainer\Storage\microsoft.microsoftedge_8wekyb3d8bbwe\MicrosoftEdge\TypedURLs

4. Delete the registry value name for the url on right side pane for the one you wish to delete.

5. Close the registry editor.

How to move files from one directory to another using find and xargs command

find - search for files in a directory hierarchy
xargs - build and execute command lines from standard input

Using find and xargs we can search any file in the directory hiearchy and xargs execute the command accepting the output of find command as input.

Use xargs with -I option to replace occurrences of replace-str in the initial-arguments with names read from standard input(In our case from find command). The placeholder following the -I is a string that can appear multiple times in the command.

Example: Below command will find all files with pattern *.txt in current directory and move to /NewDir/.

find . -name "*.txt" | xargs -I '{}' mv '{}' /NewDir/'{}'


Here '{}' is placeholder

How to download and setup Oracle Cloud Control agent software for provisioning

You want to add/update your target server to the Oracle Enterprise Manager Cloud Control and you notice that the Agent Software for your platform is shown as unavailable. Here I will describe the method how to download the agent software manually and setup in the Oracle Enterprise Manager Cloud Control

This step is tested on Oracle Enterprise Manager Cloud Control 12C 12.1.0.5.0

1) Ensure Cloud Control is set to offline mode. From the Setup menu, select Provisioning and Patching, then select Offline Patching.

2) Change the setting for Connection to Offline and click Apply.

3) From the Setup menu, select Extensibility, then select Self Update.

4) Click on Agent Software Type and open Agent Software updates page.

5) Select the Agent Software you want to download for example: Oracle Solaris on x86-64 (64-bit) and click Download button on the top of table

  A message is displayed that contains the URL to be accessed to download

6) From an Internet-enabled computer, download the agent zip file
Once the update is downloaded, it can be imported to Enterprise Manager in one of the following ways:

7) Transfer the downloaded file to the OMS host, and run following command to import it to Enterprise Manager

8) Navigate to the 12.1 MOS home/bin directory and log in to emcli, running the command as the install user, for example:

 ./emcli login -username=sysman
 ./emcli import_update -omslocal -file=<absolute path to zipfile>

At this stage, the update will show up in a downloaded state in the Self Update home page.
Once the download is complete, select the Management Agent, then click Apply. This step will stage the Management Agent software in the Software Library and make it available to the Add Host Targets wizard, which you will use to install the Management Agent on host machines.
Click Agent Software to launch the Add Host Targets wizard.

java.sql.SQLException: ORA-28040: No matching authentication protocol

When connecting to 10g ,11g or 12c databases using a  JDBC thin driver , fails with following errors:

The Network Adapter could not establish the connection

ORA-28040: No matching authentication protocol

This happens because you are using a non compatible version of JDBC driver.

To resolve this issue, make sure that you are using the latest version of Oracle JDBC driver, if not use JDBC 12c or higher versions.


Oracle Client/Server Compatibility Matrix: 

Oracle Client Version Database Version 12.2.0 12.1.0 11.2.0 11.1.0 10.2.0 10.1.0 9.2.0 12.2.0 Yes Yes Yes No No No No 12.1.0 Yes Yes Yes Was MDS No No 11.2.0 Yes Yes Yes Was MDS No Was 11.1.0 No Was Was Was Was Was Was 10.2.0 No MDS MDS  Was MDS Was Was 10.1.0 No No Was  Was Was Was Was 9.2.0 No No Was  Was Was Was Was Key: Yes Supported ES Supported but fixes only possible for customers with Extended Support . LES or MDS Supported but fixes only possible for customer with a valid Limited Extended Support or Market-Driven Support contract respectively. Was Was a supported combination but one of the releases is no longer covered by any of Premier Support, Primary Error Correct support, Extended Support, nor Extended Maintenance Support. Fixes are no longer possible. No Has never been Supported Courtesy: My Oracle Support

Oracle Messaging Gateway - MGW - Install and Setup

Install and setup Oracle Messaging Gateway - MGW

Messaging Gateway(MGW) is a feature in Oracle Database Enterprise Edition.  Messaging Gateway enables communication between applications based on non-Oracle messaging systems such as Java Message Service (Oracle JMS) ,WebSphere MQ Java Message Service (WebSphere MQ JMS),TIB/Rendezvous messages and Oracle Streams Advanced Queuing.

Here I will provide the steps I used to load and setup MGW in a existing Oracle Database.

Enviroment

OS: Oracle Enterprise Linux 7.2

DB: Oracle 12c Enterprise Edn.

Pre-Requisites

Check value of the JOB_QUEUE_PROCESSES database initialization parameter. It must be 1 or high.

Loading Database Objects into the Database

Using SQL*Plus, run ORACLE_HOME/mgw/admin/catmgw.sql as user SYS as SYSDBA

This script loads the database objects required for Messaging Gateway. It also creates two roles, MGW_ADMINISTRATOR_ROLE and MGW_AGENT_ROLE, with certain privileges granted.

Modifying listener.ora for the External Procedure

LISTENER=
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome_1)
      (SID_NAME = DB1)
    )
    (SID_DESC =
     (SID_NAME= mgwextproc)
    (ENVS="LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/jre/lib/amd64:/u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/jre/lib/amd64/server:/u01/app/oracle/product/12.1.0.2/dbhome_1/lib")
     (ORACLE_HOM= /u01/app/oracle/product/12.1.0.2/dbhome_1)
     (PROGRAM= extproc)
    )
  )

Note: Set up the LD_LIBRARY_PATH environment needed for the external procedure to run. The LD_LIBRARY_PATH must contain the following paths:
JRE_HOME/lib/PLATFORM_TYPE
JRE_HOME/lib/PLATFORM_TYPE/server
ORACLE_HOME/lib

Modifying tnsnames.ora for the External Procedure

The net service name must be MGW_AGENT (this value is fixed). The KEY value must match the KEY value specified for the IPC protocol in listener.ora. The SID value must match the value specified for SID_NAME of the SID_DESC entry in listener.ora.

MGW_AGENT=
(DESCRIPTION=
   (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))
   )
   (CONNECT_DATA=(SID=mgwextproc))
)

In sqlnet.ora set SQLNET.INBOUND_CONNECT_TIMEOUT =0 and restart the listener

Setting Up an mgw.ora Initialization File

The Messaging Gateway external procedure uses it to get initialization parameters to start the Messaging Gateway agent. Copy ORACLE_HOME/mgw/admin/sample_mgw.ora to mgw.ora and modify it according to your environment.

Creating an Oracle Messaging Gateway Administrator User

CREATE USER mgwadmin IDENTIFIED BY admin123;
GRANT CREATE SESSION to mgwadmin ;
GRANT MGW_ADMINISTRATOR_ROLE to mgwadmin ;

Creating an Oracle Messaging Gateway Agent User

CREATE USER mgwagent IDENTIFIED BY agent123;
GRANT CREATE SESSION to mgwagent ;
GRANT MGW_AGENT_ROLE to mgwagent ;

Configuring Oracle Messaging Gateway Connection Information

sqlplus /nolog

SQL> set echo off
SQL> set verify off
SQL> connect mgwadmin
Enter password:
Connected.

SQL> EXEC DBMS_MGWADM.ALTER_AGENT(agent_name => 'default_agent',username => 'mgwagent',password => 'agent123',database => 'DB1')

PL/SQL procedure successfully completed.

SQL>

Verifying the Oracle Messaging Gateway Setup

The following procedure verifies the setup and includes a simple startup and
shutdown of the Messaging Gateway agent:
1. Start the database listeners.
Start the listener for the external procedure and other listeners for the regular database connection.
2. Test the database connect string for the Messaging Gateway agent user.

Run sqlplus mgwagent/agent123@DB1.

If it is successful, then the Messaging Gateway agent can connect to the database.
3. Linux Operating System Only: Test the net service entry used to call the external
procedure.

Run sqlplus mgwagent/agent123@MGW_AGENT.

This should fail with "ORA-28547: connection to server failed, probable Oracle Net
admin error". Any other error indicates that the tnsnames.ora, listener.ora,
or both are not correct.

4. Connect as admin_user and call DBMS_MGWADM.STARTUP to start the Messaging
Gateway agent.

sqlplus mgwadmin/admin123@DB1

SQL> exec DBMS_MGWADM.STARTUP

PL/SQL procedure successfully completed.

wait for AGENT_STATUS to change to RUNNING and AGENT_PING to change to REACHABLE
SQL> select agent_status , agent_ping from MGW_GATEWAY;

Command Equivalent to "grep" in Windows

grep in Linux/Unix, find in Windows

Many times you might have wondering what is the command available in Windows which is  equivalent for "grep" in Linux/Unix. You can use findstr command in Windows

grep
The grep command in Linux/Unix is one of the most used command.  This is used for searching patterns in text.  grep process text line by line and prints any line that matching the specified pattern. Regular Expressions can be used searching.

grep can be used in many scenarios.

Examples: Search for a file Starting with "XX" in a directory

$ ls | grep XX

List all files except file Starting with "XX" in a directory

$ ls  | grep -v XX

Search for pattern in a file and list file names and lines

$ grep 187 *

Windows - findstr

findstr command in Windows can be used for similar purpose. Now let us see how above scenarios can  be achieved using findstr command.

Examples: Search for a file Starting with "XX" in a directory

c:\ dir | findstr "XX"

List all files except file Starting with "XX" in a directory

c:\ dir | findstr /v "XX"

Search for pattern in a file and list file names and lines

c:\ findstr "Internal" *

There are more parameters that can be used in combination to get the output.

Import Favorites from Internet Explorer to Edge Browser from Old PC to New PC

If you have newly installed Windows 10 on New PC or Dual Boot with earlier  version of Windows, you may wonder how to transfer the favorites to Edge browser. It is easy to import favorites from Internet Explorer to Edge on Windows 10 if you are upgraded. But if you did a clean install, importing favorites from Earlier version is bit tricky.

Here is how I did it:

Environment: I have a PC with Dual Boot enabled for Windows 8.1 and Windows 10

Note: If you are doing a clean install Windows 10 on a PC, before installing windows 10 you must export the favorites to file and copy it to a secure location for later use.

Step 1: Export the Favorites from Old version

On Windows 8.1 Open Internet Explorer , Press Alt + F, Move down and select Import Export. Select Export to a file option in the Dialog window and click next. Select Favorites and Click Next. Select the folders you want to export and click next. Select the file location you want to save.

Step 2: Import the favorites in Internet Explorer in Windows 10

Note: Since the Edge browser does not support importing from html file directly, you have to import it to Internet Explorer and then import to Edge.

Copy the exported favorites to Windows 10. Open the Internet Explorer and Import the favorites from file.

If you can not see the menu/icon for internet explorer in Windows 10, you open it by typing it in Cortana Search Box.

On Windows 10 Internet Explorer , Press Alt + F, Move down and select Import Export. Select Import from a file option in the Dialog window and click next. Select Favorites and Click Next. Select the html file path you want to import and click next. Now favorites imported to the Internet Explorer in Windows

Step 3: Import favorites to Edge from Internet Explorer on Windows 10

Open the Edge Browser, and click on the Hub link in the top right corner (3 Lines)

Click on the import favorites, and on the pane which opened will show the browsers you have installed. Select the Internet Explorer and Click Import. When import completed, you will see All Done message.

Windows 10 : How to setup proxy

Windows 10 : How to setup proxy

On Windows 10 Proxy setting is done in different method on comparison to previous versions.

To Setup proxy on Windows 10:

• Click on the Start Menu Button
• Click on Settings Menu
• On the Settings Window, Click on Network & Internet Icon
• Choose Proxy from Left Hand Side, On right Hand side you can setup proxy server, Scroll down to setup proxy manually.
• Click on Save Button.

 

OBIEE 11g Graphs Do not display after applying patch on 11.1.1.6.x

I have applied patch bundle 11.1.1.6.5 on my OBIEE 11.1.1.6.0. After applying the patch, I have noticed that none of my graphs/charts are displayed on the browser. I have deleted the browser cache and cookies, but still not displaying.

After referring the oracle support, I have did the following to resolve the issue.

We have customization for custom skins and styles in custom directory, so we did the below section first.

Section 1 Copy BIChart.swf  file to custom directory

1. Copied the BIChart.swf file from ORACLE_BI_HOME'bifoundation\web\app\res\sk_blafp\dvt directory to our custom directory

Custom directory located at <MW_HOME>/Oracle_BI/bifoundation/OracleBIPresentationServiceComponent/coreapplication_obips1/analyticsRes/<custom_dir>/dvt

2. Stop and Restart the BI Presentation Service components

cd <INSTANCE_HOME>/bin
opmnctl stopproc ias-component=coreapplication_obips1

opmnctl startproc ias-component=coreapplication_obips1

Section 2 Clear the managed server temporary files

1. Stop the Services
2. backup $DOMAIN_HOME/servers/bi_server1/tmp
3. cd $DOMAIN_HOME/servers/bi_server1/tmp
4. rm -rf * (including the hidden .internal directory)
5. Start the services

Now, open the browser and clear the cache and cookies.
Login to the analytics dashboard. If graphs/charts not visible, refresh the page by pressing ctrl+F5 (On Internet Explorer)

This should resolve the issue.

Oracle Apps: How to copy responsibilities assgined to a user to another user on same instance.

In the PROD instance, there is a user X who has 10 responsibilities assigned. Now you want to assign these 10 responsibilities to another user Y.  User Y already has 2 responsibility and want to copy remaining 8 responsibilities to user Y. You do not want to assign these responsibilities manually on the User Define form in System Administrator responsibility.

You can use a PL/SQL block similar to below to get it done.

 DECLARE

  resp_count NUMBER := 0;

  CURSOR src_user_resps
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg,
      fnd_responsibility_tl frt
      WHERE 1=1
      AND fu.user_name                        = '&From_Username'
      AND fu.user_id                          = furga.user_id
      AND fa.application_id                   = fr.application_id
      AND furga.responsibility_id             = fr.responsibility_id
      and furga.responsibility_id = frt.responsibility_id
      AND furga.responsibility_application_id = fa.application_id
      AND fsg.security_group_id               = furga.security_group_id
      AND furga.end_date IS NULL
minus
SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg,
      fnd_responsibility_tl frt
      WHERE 1=1
      AND fu.user_name                        = '&To_Username'
      AND fu.user_id                          = furga.user_id
      AND fa.application_id                   = fr.application_id
      AND furga.responsibility_id             = fr.responsibility_id
      and furga.responsibility_id = frt.responsibility_id
      AND furga.responsibility_application_id = fa.application_id
      AND fsg.security_group_id               = furga.security_group_id
      AND furga.end_date IS NULL;


BEGIN
  FOR user_resp_rec IN src_user_resps
  LOOP
    BEGIN
      --
      fnd_user_pkg.addresp
                 (username            => '&To_Username',
                  resp_app            => user_resp_rec.application_short_name,
                  resp_key            => user_resp_rec.responsibility_key,
                  security_group      => user_resp_rec.security_group_key,
                  description         => 'Demo',
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );
    
      resp_count := resp_count + 1;
    
 EXCEPTION
    WHEN OTHERS THEN
    
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_rec.responsibility_key );
    
    END;
  END LOOP;

  DBMS_OUTPUT.put_line (resp_count || '  Responsibilities copied Successfully!!' );

  COMMIT;

END;

How to convert a PDF to Word (2013)

Even though this is not related to DBA topic, this might be useful to you. In this article I will describe how to open a pdf document in Microsoft Word 2013 without using any third party component.

1. Open the word and create a new blank document
2. Select Insert Menu,  in the Text group > click the arrow next to Object > click Text from File.
 Keyboard shortcut: Press Alt Key then NJF

3. Select the pdf file you want to open in the Insert File window and Click Insert Button.
4. Click OK.

Wait for the conversion to complete. It will take some time to complete depends on the size of the pdf file and graphics content.

 

R12 Error: Application Diagnostics is not a valid responsibility for the current user. Please contact your System Administrator.

User Must have one of the below roles to be assigned to run Oracle Diagnostics.

• Diagnostics Super user
• Application Super user
• Application End User

To Assign role:

1. Login to E-Business Suite as System Administrator
2. Select the "User Management" responsibility
3. Click 'Users' in the right hand side menu
4. Enter 'User Name' and click 'Go'
5. Click 'Update' on the correct user
6. Click 'Assign Roles' button
7. Enter role name (or partial with %) you want to assign. You can enter any of the seeded roles (Diagnostics Super User Role, Application Super User Role, Application End User Role), or your own one
8. Check the box next to the appropriate role and click 'Select'
9. Enter 'Justification' and click 'Save'

Now ask the user to logoff and login again to  E-Business Suite and try again to access Oracle Diagnostics.

ORA-07445: exception encountered: core dump [__intel_new_memcpy()+382] [SIGILL] [ADDR:0x46F1D9E] [PC:0x46F1D9E] [Illegal operand]

Recently one of my database shows ORA-07445: exception encountered: core dump [__intel_new_memcpy()+382] [SIGILL] [ADDR:0x46F1D9E] [PC:0x46F1D9E] [Illegal operand]  while running one report.

This is how i resolved the issue.

I raised the SR and Oracle Support told me that the failing function is associated with ORA-07445 is "evadcd" , which shows that the error comes from decode.

Identify the failing  statement: Trace file generated at the time of ORA-07445 will contain the SQL caused the error. Identify all the tables used in the SQL statement and analyze the tables to ensure that there is no corruption on the tables using analyze table <owner>.<table_name> validate structure cascade;

None of the tables reported any corruption, all tables analyzed successfully, So the error must be related to invalid data in some of the colums.

So, i have to identify the record(s) which causing the error. For that, to narrow down, i have added additional condition to the query the records for a prticular time period in my case. 

For Example:

In my query on of the condition used was

where (txn_date) BETWEEN TO_DATE ('17-APR-2013  07:00:00',
                                                   'DD-MON-YYYY HH24:MI:SS')
                                      AND  TO_DATE ('18-APR-2013 08:00:00',
                                                    'DD-MON-YYYY HH24:MI:SS')

In order to narrow down to the erroring record,  i have tested the query by narrowing down to smaller date period to locate the exact row which throws the error. Once i have identified the row which gives the error, look for the colums used in the query and look for invalid value and correct it.