ORA-01422: exact fetch returns more than requested number of rows

Error Message:

 *******FATAL ERROR*******

AutoConfig could not successfully execute the following scripts:

Directory: <EBS_BASE>/fs#/FMW_Home/webtier/perl/bin/perl -I <EBS_BASE>/fs#/FMW_Home/webtier/perl/lib/5.10.0 -I <EBS_BASE>/fs#/FMW_Home/webtier/perl/lib/site_perl/5.10.0 -I <EBS_BASE>/fs#/EBSapps/appl/au/12.0.0/perl -I <EBS_BASE>/fs#/FMW_Home/webtier/ohs/mod_perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /<EBS_BASE>/fs#/inst/apps/<SID_hostname>/admin/install

txkGenADOPWrapper.pl INSTE8_APPLY 1

SQL> SQL> Connected.
SQL> SELECT ad_zd_adop.get_node_type('<hostname>') 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 3049

Resolution:

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);

 

 

 

 

 

 

 

 1. A full backup should be taken before any testing begins.

 

 2. Stop Application services

 

 3. Backup the fnd_oam_context_files, fnd_nodes, and adop_valid_nodes tables, and if on AD/TXK 8 or higher; ad_nodes_config_status in the EBS env nodes:

 

 Linux#> sqlplus applsys/<pwd>

 

      SQLPlus#> create table fnd_oam_context_files_bkp as select * from fnd_oam_context_files;

 

      SQLPlus#> create table fnd_nodes_bk as select * from fnd_nodes;

 

        SQLPlus#> create table adop_valid_nodes_bk as select * from adop_valid_nodes;

 

      If on AD/TXK 8 or higher:

 

      SQLPlus#> create table ad_nodes_config_status_bk as select * from ad_nodes_config_status;

               

               

 4. Truncate the following tables (Continue from step 3):

 

 SQLPlus#> truncate table fnd_oam_context_files;

 

      SQLPlus#> truncate table fnd_nodes;

 

      SQLPlus#> truncate table adop_valid_nodes;

 

      If on AD/TXK 8 or higher:

 

      SQLPlus#> truncate table ad_nodes_config_status;

               

               

5.  Run AutoConfig on the DB tier:

 

Source the <RDBMS_ORACLE_HOME> home.

 

Linux#> cd <RDBMS_ORACLE_HOME>/appsutil/scripts/<SID>_<HOSTNAME>/

 

Linux#> ./adautocfg.sh

 

              ... Confirm Autoconfig completes successfully.

 

              ... If RAC, Repeat step 3 on all RAC nodes.

 

 

 

6.  Run Autoconfig on the run file system.

 

Linux#> source <EBS_BASE>/EBSapps.env run

 

Linux#> cd $ADMIN_SCRIPTS_HOME

 

Linux#> ./adautocfg.sh

 

      ... Confirm Autoconfig completes successfully.

 

      ... If more than one EBS node, repeat step 4 on all EBS nodes.

 

 

 

7. Run Autoconfig on patch file system

 

NOTE:

 

Before running Autoconfig on the patch file system the ebs_login trigger MUST be disabled

... After the successful completion of Autoconfig the ebs_login trigger MUST be re-enabled.

 

 

 

a. Disable the ebs_login trigger using the following SQL.

 

 

sqlplus system/xxxxxx

 

 

SQL> alter trigger ebs_logon disable;

 

 

b. At this time Run autoconfig with the patch env sourced .      

 

Linux#> source <EBS_BASE>/EBSapps.env patch

 

Linux#> cd $ADMIN_SCRIPTS_HOME

 

Linux#> ./adautocfg.sh

 

... Confirm Autoconfig completes successfully

 

... If more than one EBS node, repeat step 5.b on all EBS nodes.

 

 

c. Enable the ebs_login trigger using the following SQL.

 

Linux#> sqlplus system/<pwd>

 

SQLPlus#> alter trigger ebs_logon enable;

 

 

After Autoconfig has been run successfully on all nodes, run the following two (2) queries in order to verify the tables have been correctly populated:

 

 

 

 

 

        set pagesize 200

 

        set linesize 200

 

        col node_name format a15

 

        col server_id format a8

 

        col server_address format a15

 

        col platform_code format a4

 

        col webhost format a25

 

        col domain format a20

 

        col virtual_ip format a18

 

 

 

        select node_id, platform_code, support_db D, support_cp C, support_admin A, support_forms F, support_web W, node_name, server_id,

 

               server_address, domain, webhost, virtual_ip, status

 

          from fnd_nodes order by node_name;

 

 

                              

 

              set pagesize 200

 

        set linesize 200

 

        col NAME format A20

 

        col VERSION format A22

 

        col PATH format A86

 

        col STATUS format A10

 

 

 

        select NAME,VERSION,PATH, STATUS from FND_OAM_CONTEXT_FILES;