Hello all,

I think for the Oracle Users using Discovery the version v.4.0.4 could have an error in two advance queries:

eSpace: DiscoveryProbe

Action: Modules_Reload

Query: UpdateModuleDefinitionsForEspacesOracle

UPDATE {ModuleDef} MD 
SET (MD.Description, MD.Name, MD.ApplicationDefId, MD.IsDeleted, MD.Kind, MD.SS_Key, MD.EspaceId) = 
    (
     Select EV.Description, E.Name, AD.Id, 0, 'oml', E.SS_Key, E.Id
     From {Espace_Version} EV
     Inner JOIN {Espace} E ON E.[VERSION_ID] = EV.[ID]
     Inner JOIN {Module} M ON M.[ESPACE_ID] = E.[ID]
     Inner JOIN {App_Definition_Module} ADM ON ADM.[MODULE_ID] = M.[ID]
     Inner JOIN {ApplicationDef} AD ON ADM.[APPLICATION_ID] = AD.[APPLICATIONID]
     Where MD.[ESPACEID] = E.[ID] and E.[IS_ACTIVE] = 1 AND AD.[ISDELETED] <> 1
     )
Where MD.Kind = 'oml'       

The Select comes with more than 1 row to update so the error:

ORA-01427 single-row subquery returns more than one row

https://www.dba-oracle.com/t_ora_01427_single_row_subquery_returns_more_than_one_row.htm

I try to use the operator > after the SET atributes but have other error

Then I added one more WHERE clause:

 rownum = 1

UPDATE {ModuleDef} MD 
SET (MD.Description, MD.Name, MD.ApplicationDefId, MD.IsDeleted, MD.Kind, MD.SS_Key, MD.EspaceId) = 
    (
     Select EV.Description, E.Name, AD.Id, 0, 'oml', E.SS_Key, E.Id
     From {Espace_Version} EV
     Inner JOIN {Espace} E ON E.[VERSION_ID] = EV.[ID]
     Inner JOIN {Module} M ON M.[ESPACE_ID] = E.[ID]
     Inner JOIN {App_Definition_Module} ADM ON ADM.[MODULE_ID] = M.[ID]
     Inner JOIN {ApplicationDef} AD ON ADM.[APPLICATION_ID] = AD.[APPLICATIONID]
     Where MD.[ESPACEID] = E.[ID] and E.[IS_ACTIVE] = 1 AND AD.[ISDELETED] <> 1 AND rownum = 1  
     )
Where MD.Kind = 'oml'

After fixed this query I realized that in the same action you have the same problem to other advance query:

Query: UpdateModuleDefinitionsForExtensionsOracle

UPDATE {ModuleDef} MD 
SET (MD.Description, MD.Name, MD.ApplicationDefId, MD.IsDeleted, MD.Kind, MD.SS_Key, MD.ExtensionId) = 
    (
     Select EV.Description, E.Name, AD.Id, 0, 'xif', E.SS_Key, E.Id
     From {Extension_Version} EV
     Inner JOIN {Extension} E ON E.[VERSION_ID] = EV.[ID]
     Inner JOIN {Module} M ON M.[EXTENSION_ID] = E.[ID]
     Inner JOIN {App_Definition_Module} ADM ON ADM.[MODULE_ID] = M.[ID]
     Inner JOIN {ApplicationDef} AD ON ADM.[APPLICATION_ID] = AD.[APPLICATIONID]
     Where MD.[ExtensionId] = E.[ID] and E.[IS_ACTIVE] = 1 AND AD.[ISDELETED] <> 1 AND rownum = 1
     )
Where MD.Kind = 'xif'     

But is not the good solution because duplicate the results:

Any Oracle User with the same issue?