ORA-01422: Exact Fetch Returns More Than Requested Number Of Rows (Doc ID 2212698.1)

Applies to:

Oracle Applications Manager – Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.4 version, Rapidclone

While running autoconfig the following error occurs

ERROR
———————–
Script Name : txkGenADOPWrapper.pl
Script Version : 120.0.12020000.4
ERROR DESCRIPTION:
(*******FATAL ERROR*******
PROGRAM : (/u01/app/applmgr/iRec/fs2/inst/apps/UAT_u01vuire01/admin/install/txkGenADOPWrapper.pl)
TIME : Fri Nov 18 19:21:47 2016
FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]
MESSAGES:
SQLPLUS error: buffer=

SQL*Plus: Release 10.1.0.5.0 – Production on Fri Nov 18 19:21:46 2016

SQL> SQL> Connected.
SQL> SELECT ad_zd_adop.get_node_type(‘cloned-node-name’) FROM DUAL
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at “APPS.AD_ZD_ADOP”, line 3053

Changes

The cloned-node-name-001 node has been recently cloned from primary node.

Cause

The underlying query used by the ad_zd_adop.get_node_type() function is returning two rows (or more) when it should be returning ONLY ONE ROW.

SQL> SELECT ad_zd_adop.get_node_type(‘cloned-node-name-001‘) FROM DUAL
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at “APPS.AD_ZD_ADOP”, line 3053

The ad_zd_adop.get_node_type() function is expected to return only one row with information of node enabled for the service “s_web_admin_status”. The internal query used by the function is returning the following:

Node_name Node_Type
————————————————
cloned-node-name-001 1
cloned-node-name-001 4

The cloned node (cloned-node-name) is also enabled with this service, which should have been disabled as soon as the clone completed.

Note: the node name used cloned-node-name is provided just as an example name. It will be different every time this error appears.

Solution

To solve this issue do the following:

1. First, check the following query returns just one row. If it will return more than one row, then you are hitting the issue described in this note.

COL node_name FORMAT A20
COL note_type FORMAT A10

SELECT avn.node_name,
DECODE(EXTRACTVALUE(XMLType(focf.text),’//APPL_TOP_NAME’),
admin_node.appl_top, DECODE(avn.node_name,admin_node.node_name, 1,3),
DECODE(ROW_NUMBER() OVER (PARTITION BY EXTRACTVALUE(XMLType(focf.text),’//APPL_TOP_NAME’)
ORDER BY avn.node_name),
1,2,4)) node_type
FROM
fnd_oam_context_files focf,
adop_valid_nodes avn,
(SELECT node_name,
EXTRACTVALUE(XMLType(text),’//APPL_TOP_NAME’) appl_top
FROM fnd_oam_context_files
WHERE name NOT IN (‘TEMPLATE’,’METADATA’,’config.txt’)
AND CTX_TYPE=’A’
AND (status IS NULL or UPPER(status) IN (‘S’,’F’))
AND EXTRACTVALUE(XMLType(text),’//file_edition_type’) = ‘run’
AND EXTRACTVALUE(XMLType(text),’//oa_service_group_status[@oa_var=”s_web_admin_status”]’)=’enabled’
AND EXTRACTVALUE(XMLType(text),’//oa_service_list/oa_service[@type=”admin_server”]/oa_service_status’)=’enabled’) admin_node
WHERE focf.name not IN (‘TEMPLATE’,’METADATA’,’config.txt’)
AND focf.CTX_TYPE=’A’
AND (focf.status is null
or upper(focf.status) IN (‘S’,’F’))
AND EXTRACTVALUE(XMLType(focf.text),’//file_edition_type’) = ‘run’
AND upper(focf.node_name)=upper(avn.node_name);

The meaning of various return values are:
—————————————————–
1 – Admin Node
2 – Non-Shared master node
3 – Admin Shared slave node
4 – Shared slave node

2. If the above query returns more than one row, do the following:

2.1. Disable the “s_web_admin_status” parameter on the application context file of the recently cloned node and
2.2. Rerun autoconfig and confirm the issue is solved.

Mahendran Manickam