Luís Santos Monteiro wrote:

 Hi Luís,

Please remove the comment from the query.

like.  /*Retreives the number of violations that the application is generating at the Domain Level*/  

There are several problems with this new version of Discovery with an Oracle database:

- GetApplicationDefs: remove the AS in all statements like: <table> AS <new name>.

(oracle does not accept the AS keyword)

- GetModules, GetModulesOracle: 

... WITH statement has a unique WITH keyword in Oracle, remove the 2 WITH that should not be there.

... {ModuleLayer}.[Order] on the OrderBy, must be put on the DISTINCT SELECT fields;

... Several CLOB that need to be converted with "to_char()";

... Here is the complete query:

WITH UpperViolations(AppId, Violator, ViolatorList) AS
(
    SELECT
        {ApplicationDef}.[Id],
        to_char(SUBSTR({ApplicationDef}.[UpperViolations], 1 , INSTR({ApplicationDef}.[UpperViolations] || ',', ',') - 1)),
        to_char(LTRIM(SUBSTR({ApplicationDef}.[UpperViolations], INSTR({ApplicationDef}.[UpperViolations] || ',', ',') +1 , length({ApplicationDef}.[UpperViolations]))))
    FROM {ApplicationDef}
    WHERE length(trim({ApplicationDef}.[UpperViolations])) > 0
    UNION ALL
    SELECT
        AppId,
        to_char(SUBSTR(ViolatorList, 1 , INSTR(ViolatorList || ',', ',') - 1)),
        to_char(SUBSTR(ViolatorList, INSTR(ViolatorList || ',', ',') + 1 , LENGTH(ViolatorList)))
    FROM UpperViolations
    WHERE
        length(trim(ViolatorList)) > 0
),
/*WITH*/ SideViolations(AppId, Violator, ViolatorList) AS
(
    SELECT
        {ApplicationDef}.[Id],
        to_char(SUBSTR({ApplicationDef}.[SideViolations], 1 , INSTR({ApplicationDef}.[SideViolations] || ',', ',') - 1)),
        to_char(LTRIM(SUBSTR({ApplicationDef}.[SideViolations], INSTR({ApplicationDef}.[SideViolations] || ',', ',') +1 , length({ApplicationDef}.[SideViolations]))))
    FROM {ApplicationDef}
    WHERE length(trim({ApplicationDef}.[SideViolations])) > 0
    UNION ALL
    SELECT
        AppId,
        to_char(SUBSTR(ViolatorList, 1 , INSTR(ViolatorList || ',', ',') - 1)),
        to_char(SUBSTR(ViolatorList, INSTR(ViolatorList || ',', ',') + 1 , LENGTH(ViolatorList)))
    FROM SideViolations
    WHERE
        length(trim(ViolatorList)) > 0
),
/*WITH*/ CyclicViolations(AppId, Violator, ViolatorList) AS
(
    SELECT
        {ApplicationDef}.[Id],
        to_char(SUBSTR({ApplicationDef}.[CyclicViolations], 1 , INSTR({ApplicationDef}.[CyclicViolations] || ',', ',') - 1)),
        to_char(LTRIM(SUBSTR({ApplicationDef}.[CyclicViolations], INSTR({ApplicationDef}.[CyclicViolations] || ',', ',') +1 , length({ApplicationDef}.[CyclicViolations]))))
    FROM {ApplicationDef}
    WHERE length(trim({ApplicationDef}.[CyclicViolations])) > 0
    UNION ALL
    SELECT
        AppId,
        to_char(SUBSTR(ViolatorList, 1 , INSTR(ViolatorList || ',', ',') - 1)),
        to_char(SUBSTR(ViolatorList, INSTR(ViolatorList || ',', ',') + 1 , LENGTH(ViolatorList)))
    FROM CyclicViolations
    WHERE
        length(trim(ViolatorList)) > 0
),
ModuleApplicationViolations(ModuleDefId, UpperViolationsCounter, SideViolationsCounter, CyclicViolationsCounter) AS
(
    SELECT ModuleViolation.ModuleDefId,
           SUM(CASE WHEN ModuleViolation.srcAppUpperViolationsId IS NOT NULL and srcAppUpperViolations <> '' THEN 1 ELSE 0 END),
             SUM(CASE WHEN ModuleViolation.srcAppSideViolationsId IS NOT NULL and srcAppSideViolations <> '' THEN 1 ELSE 0 END),
            SUM(CASE WHEN ModuleViolation.srcAppCyclicViolationsId IS NOT NULL and srcAppCyclicViolations <> '' THEN 1 ELSE 0 END)
    FROM 
    (
    SELECT  srcModule.[Id] AS ModuleDefId,
        srcModule.[Name] AS ModuleDefName,
        UpperViolations.AppID AS srcAppUpperViolationsId,
          to_char(srcApp.[UpperViolations]) AS srcAppUpperViolations,
        SideViolations.AppID AS srcAppSideViolationsId,
          to_char(srcApp.[SideViolations]) AS srcAppSideViolations,
          CyclicViolations.AppID AS srcAppCyclicViolationsId,
          to_char(srcApp.[CyclicViolations]) AS srcAppCyclicViolations
    FROM {ApplicationDef} trgApp
    JOIN {ModuleDef} trgAppModule ON trgApp.[Id] = trgAppModule.[ApplicationDefId] and trgAppModule.[IsDeleted] = 0
    JOIN {ElementNode} ON {ElementNode}.[ModuleDefId] = trgAppModule.[Id]
    JOIN {Reference} ON {Reference}.[TargetElementNodeId] = {ElementNode}.[Id]
    JOIN {ModuleNode} ON {Reference}.[SourceModuleNodeId] = {ModuleNode}.[Id]
    JOIN {ModuleDef} srcModule ON srcModule.[Id] = {ModuleNode}.[ModuleDefId]
    JOIN {ApplicationDef} srcApp ON srcModule.[ApplicationDefId] = srcApp.[Id]
    LEFT JOIN UpperViolations ON trgApp.[Name] = UpperViolations.Violator
    LEFT JOIN SideViolations ON trgApp.[Name] = SideViolations.Violator
    LEFT JOIN CyclicViolations ON trgApp.[Name] = CyclicViolations.Violator
    WHERE     trgApp.[Id] <> srcApp.[Id] AND
            ((trgApp.[Name] = UpperViolations.Violator AND trgApp.[Id] <> UpperViolations.Appid) OR
            (trgApp.[Name] = SideViolations.Violator AND trgApp.[Id] <> SideViolations.Appid) OR
            (trgApp.[Name] = CyclicViolations.Violator AND trgApp.[Id] <> CyclicViolations.Appid))
    GROUP BY srcModule.[Id],
         srcModule.[Name],
           UpperViolations.AppID,
           to_char(srcApp.[UpperViolations]),
           SideViolations.AppID,
           to_char(srcApp.[SideViolations]),   
         CyclicViolations.AppID,
            to_char(srcApp.[CyclicViolations])
    ) ModuleViolation
    GROUP BY ModuleViolation.ModuleDefId
),
ModuleDomainViolations(ModuleDefId, UpperViolationsCounter, SideViolationsCounter, DownViolationsCounter) AS
(
    SELECT     
        {DomainReferenceModule}.[TargetModuleDefId],
        SUM(CASE WHEN srcDomain.[UpperViolations] LIKE '%' + trgDomain.[Label] + '%' THEN 1 ELSE 0 END) AS DomainUpperViolations,
        SUM(CASE WHEN srcDomain.[SideViolations] LIKE '%' + trgDomain.[Label] + '%' THEN 1 ELSE 0 END) AS DomainSideViolations,
        SUM(CASE WHEN srcDomain.[DownDependencies] LIKE '%' + trgDomain.[Label] + '%' THEN 1 ELSE 0 END) AS DownViolations
    FROM {DomainReferenceModule}
    LEFT JOIN {ModuleDef} trgModDef ON trgModDef.[Id] = {DomainReferenceModule}.[SourceModuleDefId] 
    LEFT JOIN {ApplicationDef} trgAppDef ON trgAppDef.[Id] = trgModDef.[ApplicationDefId]
    LEFT JOIN {Domain} trgDomain ON trgAppDef.[DomainId] = trgDomain.[Id]
    LEFT JOIN {DomainReferenceApplication} ON {DomainReferenceApplication}.[Id] = {DomainReferenceModule}.[DomainReferenceApplicationId]
    LEFT JOIN {DomainReference} ON {DomainReferenceApplication}.[DomainReferenceId] = {DomainReference}.[Id]
    LEFT JOIN {Domain} srcDomain ON srcDomain.[Id] = {DomainReference}.[TargetDomainId]
    GROUP BY {DomainReferenceModule}.[TargetModuleDefId]
    HAVING SUM(CASE WHEN srcDomain.[UpperViolations] like '%' + trgDomain.[Label] + '%' THEN 1 ELSE 0 END) > 0 OR
           SUM(CASE WHEN srcDomain.[SideViolations] like '%' + trgDomain.[Label] + '%' THEN 1 ELSE 0 END) > 0 OR
           SUM(CASE WHEN srcDomain.[DownDependencies] like '%' + trgDomain.[Label] + '%' THEN 1 ELSE 0 END) > 0
)

SELECT DISTINCT
    {ModuleDef}.[Id], 
    {ModuleDef}.[Name], 
    {ModuleDef}.[Kind], 
    {ModuleDef}.[ApplicationDefId], 
    {ModuleDef}.[Layer], 
    to_char({ModuleDef}.[UpperViolations]), 
    to_char({ModuleDef}.[SideViolations]), 
    to_char({ModuleDef}.[CyclicDependencies]), 
    {ModuleDef}.[FanIn], 
    {ModuleDef}.[FanOut], 
    {ModuleDef}.[IsSelected],
    {ModuleLayer}.[Label],
    {ApplicationDef}.[Name] ApplicationDefName,
    S.NrScreens,
    E.NrEntities,
    W.NrPublicWebBlocks,
    A.NrPublicActions,
    SA.NrServiceAPIMethod,
    {ModuleLayer}.[Color],
    to_char({ModuleDef}.[WeakUpperViolations]),
    ModuleApplicationViolations.UpperViolationsCounter,
    ModuleApplicationViolations.SideViolationsCounter,
    ModuleApplicationViolations.CyclicViolationsCounter,
    ModuleDomainViolations.UpperViolationsCounter,
    ModuleDomainViolations.SideViolationsCounter,
    ModuleDomainViolations.DownViolationsCounter,
    {ModuleLayer}.[Order]
FROM
    {ModuleDef}
    INNER JOIN {ApplicationDef} on {ModuleDef}.[ApplicationDefId] = {ApplicationDef}.[Id]
        AND {ModuleDef}.[IsDeleted] <> @TrueBooleanFlag
        AND {ApplicationDef}.[IsDeleted] <> @TrueBooleanFlag
        AND
        (
            @SearchKeywordFilter = @Nulltext
            OR
            (
                @SearchKeywordFilter <> @Nulltext
                 AND
                 (
                    {ModuleDef}.[Name] like @SearchKeywordFilter
                    OR {ModuleDef}.[Kind] LIKE @SearchKeywordFilter
                    OR {ModuleDef}.[UpperViolations] like @SearchKeywordFilter 
                    OR {ModuleDef}.[SideViolations] like @SearchKeywordFilter
                    OR {ModuleDef}.[CyclicDependencies] like @SearchKeywordFilter
                )
            )
        )
        AND
        (
            @SelectedOnlyFilter <> @TrueBooleanFlag 
            OR
            (
                @SelectedOnlyFilter = @TrueBooleanFlag 
                AND {ApplicationDef}.[IsSelected] = @SelectedOnlyFilter
                AND {ModuleDef}.[IsSelected] = @SelectedOnlyFilter
            )
        )
        AND
        (
            @HideExtensionsFilter <> @TrueBooleanFlag 
            OR
            (
                @HideExtensionsFilter = @TrueBooleanFlag 
                AND {ModuleDef}.[Kind] = 'oml'
            )
        )
        AND
        (
            @ConsumersFilter = -1
            OR
            (
                @ConsumersFilter <> -1
                AND
                (
                    (
                        @ConsumerOperation = '='
                        AND {ModuleDef}.[FanIn] = @ConsumersFilter
                    )
                    OR
                    (
                        @ConsumerOperation = '>'
                        AND {ModuleDef}.[FanIn] > @ConsumersFilter
                    )
                    OR
                    (
                        @ConsumerOperation = '<'
                        AND {ModuleDef}.[FanIn] < @ConsumersFilter
                    )
                )
            )
        )
        AND
        (
            @ProducersFilter = -1
            OR
            (
                @ProducersFilter <> -1
                AND
                (
                    (
                        @ProducerOperation = '='
                        AND {ModuleDef}.[FanOut] = @ProducersFilter
                    )
                    OR
                    (
                        @ProducerOperation = '>'
                        AND {ModuleDef}.[FanOut] > @ProducersFilter
                    )
                    OR
                    (
                        @ProducerOperation = '<'
                        AND {ModuleDef}.[FanOut] < @ProducersFilter
                    )
                )
            )
        )
        AND
        (
            @ViolationsOnlyFilter != @TrueBooleanFlag
            OR
            (
                @ViolationsOnlyFilter = @TrueBooleanFlag
                AND
                (
                    @TempModuleViolationsFilter 
                )
            )
        )
        AND
        (
            (
                @DomainId IS NULL
                OR @DomainId = 0
            )
            OR
            (
                (
                    @DomainId IS NOT NULL
                    OR @DomainId <> 0
                )
                AND {ApplicationDef}.[DomainId] = @DomainId
            )
        )
        AND
        (
            (
                @ApplicationDefId IS NULL
                OR @ApplicationDefId = 0
            )
            OR
            (
                (
                    @ApplicationDefId IS NOT NULL
                    OR @ApplicationDefId != 0
                )
                AND
                (
                    (
                        @IncludeExternalDependencies <> @TrueBooleanFlag
                        AND {ModuleDef}.[ApplicationDefId] = @ApplicationDefId
                    )
                    OR
                    (
                        @IncludeExternalDependencies = @TrueBooleanFlag
                        AND
                        (
                            {ModuleDef}.[ApplicationDefId] = @ApplicationDefId
                            OR {ModuleDef}.[Id] IN (@ModuleDefFromAppAndExternalDeps)
                        )
                    )
                )
            )
        )
    LEFT JOIN {ModuleLayer} on {ModuleDef}.[Layer] = {ModuleLayer}.[Id]
    
    LEFT JOIN ModuleDomainViolations ON ModuleDomainViolations.ModuleDefId = {ModuleDef}.[Id]
    
    LEFT JOIN ModuleApplicationViolations ON ModuleApplicationViolations.ModuleDefId = {ModuleDef}.[Id]
    
    LEFT JOIN ( SELECT count(1) NrScreens, {ElementNode}.[ModuleDefId] ModuleDefId from {ElementNode} inner join {ReferenceKind} on {ElementNode}.[Kind] = {ReferenceKind}.[Label] where {ReferenceKind}.[Id] = @WebScreen GROUP BY {ElementNode}.[ModuleDefId] ) S ON 
            S.ModuleDefId = {ModuleDef}.[Id]
    LEFT JOIN ( SELECT count(1) NrEntities, {ElementNode}.[ModuleDefId] ModuleDefId from {ElementNode} inner join {ReferenceKind} on {ElementNode}.[Kind] = {ReferenceKind}.[Label] where {ReferenceKind}.[Id] = @Entity GROUP BY {ElementNode}.[ModuleDefId] ) E ON 
            E.ModuleDefId = {ModuleDef}.[Id]
    LEFT JOIN ( SELECT count(1) NrPublicWebBlocks, {ElementNode}.[ModuleDefId] ModuleDefId from {ElementNode} inner join {ReferenceKind} on {ElementNode}.[Kind] = {ReferenceKind}.[Label] where {ReferenceKind}.[Id] = @WebBlock AND {ElementNode}.[IsPublic] = @TrueBooleanFlag GROUP BY {ElementNode}.[ModuleDefId] ) W ON 
            W.ModuleDefId = {ModuleDef}.[Id]
    LEFT JOIN ( SELECT count(1) NrPublicActions, {ElementNode}.[ModuleDefId] ModuleDefId from {ElementNode} inner join {ReferenceKind} on {ElementNode}.[Kind] = {ReferenceKind}.[Label] where {ReferenceKind}.[Id] = @Action AND {ElementNode}.[IsPublic] = @TrueBooleanFlag GROUP BY {ElementNode}.[ModuleDefId] ) A ON 
            A.ModuleDefId = {ModuleDef}.[Id]
    LEFT JOIN ( SELECT count(1) NrServiceAPIMethod, {ElementNode}.[ModuleDefId] ModuleDefId from {ElementNode} inner join {ReferenceKind} on {ElementNode}.[Kind] = {ReferenceKind}.[Label] where {ReferenceKind}.[Id] = @ServiceAPIMEthod AND {ElementNode}.[IsPublic] = @TrueBooleanFlag GROUP BY {ElementNode}.[ModuleDefId] ) SA ON 
            SA.ModuleDefId = {ModuleDef}.[Id]
WHERE
    (
        @UndefinedLayer <> @TrueBooleanFlag
        AND
        (
            (
                (
                    @ModuleTopLayerFilter IS NULL
                    OR @ModuleTopLayerFilter = 0
                )
                OR
                (
                    (
                        @ModuleTopLayerFilter IS NOT NULL
                        OR @ModuleTopLayerFilter <> 0
                    )
                    AND
                    (
                        {ModuleLayer}.[ParentId] = @ModuleTopLayerFilter
                        OR {ModuleDef}.[Layer] = @ModuleTopLayerFilter
                    )
                )
            )
            AND
            (
                (
                    @ModuleSubLayerFilter IS NULL
                    OR @ModuleSubLayerFilter = 0
                )
                OR
                (
                    (
                        @ModuleSubLayerFilter IS NOT NULL
                        OR @ModuleSubLayerFilter <> 0
                    )
                    AND {ModuleDef}.[Layer] = @ModuleSubLayerFilter
                )
            )
        )
    )
    OR
    (
        @UndefinedLayer = @TrueBooleanFlag
        AND
        (
            {ModuleDef}.[Layer] IS NULL
            OR {ModuleDef}.[Layer] = 0
        )
    )
@MySQLOrOracleServerMax 
ORDER BY
    {ModuleLayer}.[Order] desc
    ,@OrderBy

These are just workarounds, Outsystems Architecture Team should analyze to provide a new fixed version of Discovery.

Hi,

Thank you for feedback. For some reason that we were not aware of there seams to be a mismatch in the Oracle SQL statement tested vs what is in this release.
We are already working to replace this version.

Sorry for the inconvenience.

Cheers,

Davide Duarte

Any news about the new Discovery version for Oracle ?

About snapshots, found what was wrong. On DiscoveryProbe, module Module_CycleDependencies, advanced query GetCycles, remove the AS in all statements like: <table> AS <new name>.

This solves the snapshots problems apparently.

Hi all,

Version 5.1.1 (OutSystems 11) and version 4.1.1 (OutSystems 10) were released today with the expected fixes.

Thank you!

Hi Davide,

Thank you for the release 5.1.1

The latest version fixed all the errors that I faced with the Advanced SQL queries.

Cheers,

Luís