Question

Update scripts for Oracle

08:36 Dec 28, 2020

Hi Community,

In the update guide (https://academy.creatio.com/docs/release/update-guide/update-guide#titl…), there are sets of scripts needs to be executed before proceeding to the upgrade. But in the guide they only specified MSSQL and PostgreSQL scripts they have not given a script for Oracle. Any idea what is the counterpart of the below scripts in Oracle. Thank you so much.

Before updating to version 7.15.3

{

declare @val as varchar(max) = ''

select @val = concat(@val, SchemaName, ', ')

from

(select SysSchema.Name as SchemaName

from SysSchemaContent with (nolock)

inner join SysSchema with(nolock) on SysSchemaContent.SysSchemaId = SysSchema.Id

inner join SysPackage with(nolock) on SysSchema.SysPackageId = SysPackage.Id

where

SysPackage.Maintainer != 'Terrasoft' and

SysSchema.ManagerName in ('EntitySchemaManager', 'PageSchemaManager',

'ProcessSchemaManager', 'ProcessUserTaskSchemaManager', 'SourceCodeSchemaManager') and

(

cast(SysSchemaContent.Content as varchar(max)) like '%Terrasoft.Reports%' or

cast(SysSchemaContent.Content as varchar(max)) like '%ReportSchemaManager%'

)

union

select SysSchema.Name as SchemaName

from SysSchema with(nolock)

inner join SysPackage with(nolock) on SysSchema.SysPackageId = SysPackage.Id

where

SysPackage.Maintainer != 'Terrasoft' and

SysSchema.ManagerName in ('EntitySchemaManager', 'PageSchemaManager',

'ProcessSchemaManager', 'ProcessUserTaskSchemaManager', 'SourceCodeSchemaManager') and

(

cast(SysSchema.MetaData as varchar(max)) like '%Terrasoft.Reports%' or

cast(SysSchema.MetaData as varchar(max)) like '%ReportSchemaManager%'

)

) as contentList

select DB_NAME(), @val as SchemaContent

}

Updating to 7.16.1

SELECT SysSchema.Name AS SchemaName FROM SysSchema (NOLOCK)

WHERE SysSchema.Id IN (

SELECT SysSchemaId FROM SysSchemaSource

WHERE SysSchemaId IN (

SELECT

ss.Id

FROM SysSchema ss WITH (NOLOCK)

INNER JOIN SysPackage sp WITH (NOLOCK) ON ss.SysPackageId = sp.Id

WHERE sp.Name NOT IN ('Base', 'ProcessDesigner', 'NUI', 'SSP')

AND sp.Maintainer != 'Terrasoft'

AND ss.ManagerName NOT IN ('ClientUnitSchemaManager', 'DcmSchemaManager', 'PageSchemaManager')

)

AND (Source LIKE '%MailBe%' OR (Source like '%SmtpClient%' AND Source like '%Terrasoft.Mail%'))

)

1 comments

Olga Avis

13:23 Jan 15, 2021

Hello Fulgen,

Our apologies for the delay in responding!

The required scripts for Oracle are ready.

The Before updating to version 7.15.3 script is available below. It will be added to the update guide shortly.

begin
	declare
		v_val VARCHAR2(4000) := '';
	begin
		EXECUTE IMMEDIATE 'CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
		RETURN CLOB
		AS
			 v_clob CLOB;
			 v_varchar VARCHAR2(32767);
			 v_start PLS_INTEGER := 1;
			 v_buffer PLS_INTEGER := 32767;
		BEGIN
			IF blob_in IS NULL THEN
				RETURN NULL;
			END IF;
			 DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
 
			 FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
			 LOOP
 
				v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
				DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
				v_start := v_start + v_buffer;
			 END LOOP;
			RETURN v_clob;
		END blob_to_clob;';
 
		select v_val = concat(concat(v_val, ','), "SchemaName") into v_val
		from(
			select "SysSchema"."Name" as "SchemaName"
			from "SysSchemaContent"
			inner join "SysSchema" on "SysSchemaContent"."SysSchemaId" = "SysSchema"."Id"
			inner join "SysPackage" on "SysSchema"."SysPackageId" = "SysPackage"."Id"
			where
				"SysPackage"."Maintainer" != 'Terrasoft' and
				"SysSchema"."ManagerName" in (
					'EntitySchemaManager',
					'PageSchemaManager',
					'ProcessSchemaManager',
					'ProcessUserTaskSchemaManager',
					'SourceCodeSchemaManager') and
				(blob_to_clob("SysSchemaContent"."Content") LIKE '%Terrasoft.Reports%' or
				 blob_to_clob("SysSchemaContent"."Content") LIKE '%ReportSchemaManager%')
		union
		select "SysSchema"."Name" as "SchemaName"
		from "SysSchema"
		inner join "SysPackage" on "SysSchema"."SysPackageId" = "SysPackage"."Id"
		where
			"SysPackage"."Maintainer" != 'Terrasoft' and
			"SysSchema"."ManagerName" in (
				'EntitySchemaManager',
				'PageSchemaManager',
				'ProcessSchemaManager',
				'ProcessUserTaskSchemaManager',
				'SourceCodeSchemaManager') and
			(blob_to_clob("SysSchema"."MetaData") LIKE '%Terrasoft.Reports%' or
			 blob_to_clob("SysSchema"."MetaData") LIKE '%ReportSchemaManager%')
		);
	end;
end;

The Updating to 7.16.1 and 7.16.2 script is available in the Academy article.

Best regards,

Olga.

Show all comments