[DB Cleaner on Steroids] Oracle issues when deleting applications
Question
Forge component by Miguel 'Kelter' Antunes

Hello,

When running on an Oracle backend, if you attempt to delete an Application you get this error:

[1] Error executing query.
   at ssDBCleanerOnSteroids.Actions.FuncActionCascadeDelete_eSpace.QueryDeleteSiteProperties(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, Int32 qpinEspaceId)
   at ssDBCleanerOnSteroids.Actions.ActionCascadeDelete_eSpace(HeContext heContext, Int32 inParameSpaceId, Boolean& outParamSuccess)
[2] Error in advanced query DeleteSiteProperties in CascadeDelete_eSpace in DBCleanerOnSteroids (DELETE {Site_Property}  FROM {Site_Property} JOIN       {Site_Property_Definition} ON {Site_Property}.[Site_Property_Definition_Id] = {Site_Property_Definition}.[Id]  WHERE {Site_Property_Definition}.[Espace_Id] = @EspaceId): ORA-00933: SQL command not properly ended

I adjusted DBCleanerOnSteroids :: Server Actions :: eSpaces :: CascadeDelete_eSpace :: DeleteSiteProperties from this query:

DELETE {Site_Property}
FROM {Site_Property} JOIN
{Site_Property_Definition} ON {Site_Property}.[Site_Property_Definition_Id] = {Site_Property_Definition}.[Id]
WHERE {Site_Property_Definition}.[Espace_Id] = @EspaceId

To this query:

DELETE
FROM {Site_Property} JOIN
{Site_Property_Definition} ON {Site_Property}.[Site_Property_Definition_Id] = {Site_Property_Definition}.[Id]
WHERE {Site_Property_Definition}.[Espace_Id] = @EspaceId

I also had to adjust the first line accordingly in DeleteCyclicJobs, DeleteAssemblyDependencies, DeleteAreaEntryPoints, DeleteSolutionVersionReferences, and DeleteApplicationVersionModuleVersions.

Next, I had to change ApplicationVersions :: PruneApplicationsVersions :: GetApplicationVersions to not use the TOP keyword:

SELECT ossys@APPLICATION_VERSION.[Id]
FROM ossys@APPLICATION_VERSION
WHERE
ossys@APPLICATION_VERSION.[Id] NOT IN (
SELECT ID FROM (
SELECT ossys@APPLICATION_VERSION.[Id] AS ID, ROWNUM AS RN
FROM ossys@APPLICATION_VERSION
WHERE ossys@APPLICATION_VERSION.[ApplicationId] = @ApplicationId
ORDER BY ossys@APPLICATION_VERSION.[CreatedOn] DESC
WHERE RN <= @OldVersions
AND
ossys@APPLICATION_VERSION.[ApplicationId] = @ApplicationId
ORDER BY ossys@APPLICATION_VERSION.[CreatedOn] DESC

Next I had to split the 2 queries in ApplicationVersions :: DeleteApplicationVersion :: DeleteApplicationVersion into 2 separate SQL blocks.

I haven't looked through other parts of the application to see if they have the same problem on Oracle or not.

Hope that helps anyone else having this problem.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.