Migrating from roundhouse to grate for oracle database - Oracle package script failing

[shortgrass]

Describe the bug
We have scripts that run fine in roundhouse that are failing in grate.

To Reproduce
CREATE OR REPLACE PACKAGE ARGUS_DBA.AnalyticsCommonUtility IS
cMAX_CPP NUMBER(16,2) DEFAULT 10000000;

FUNCTION CalculateCPM(
Rate IN NUMBER,
Imps IN NUMBER
) RETURN NUMBER;

FUNCTION CalculateCPP(
Rate IN NUMBER,
Rating IN NUMBER
) RETURN NUMBER;

FUNCTION CalculateIndex(
Imps IN NUMBER,
Gtee IN NUMBER
) RETURN NUMBER;

END AnalyticsCommonUtility;
;

CREATE OR REPLACE PACKAGE BODY ARGUS_DBA.AnalyticsCommonUtility IS

FUNCTION CalculateCPM(
Rate IN NUMBER,
Imps IN NUMBER
)
RETURN NUMBER
IS
CPM NUMBER;
BEGIN

IF (Imps IS NULL OR Imps = 0) THEN
RETURN 0;
END IF;

CPM := ROUND((Rate * 1000) / Imps, 2);

IF CPM >= 10000 THEN
RETURN 10000;
ELSE
RETURN CPM;
END IF;
END CalculateCPM;

FUNCTION CalculateCPP(
Rate IN NUMBER,
Rating IN NUMBER
)
RETURN NUMBER
IS
CPP NUMBER;
BEGIN

IF (Rating IS NULL OR Rating = 0) THEN
RETURN 0;
END IF;

CPP := ROUND(Rate / Rating, 2);

IF CPP > cMAX_CPP THEN
RETURN cMAX_CPP;
ELSE
RETURN CPP;
END IF;
END CalculateCPP;

FUNCTION CalculateIndex(
Imps IN NUMBER,
Gtee IN NUMBER
)
RETURN NUMBER
IS
Idx NUMBER;
BEGIN

IF (Gtee = 0) THEN
RETURN 0;
END IF;

Idx := (Imps * 1.0 / Gtee * 1.0) * 100;

Idx := Round(Idx,2);

IF Idx >= 1000 THEN
Idx := 999.99;
END IF;

RETURN Idx;
END CalculateIndex;

END;
;

Expected behavior
The Oracle package should be created.

Screenshots
Stored Procedures ("sprocs"):

0014_ARGUS_ANALYTICS_COMMONUTILITY_PACKAGE.sql: ORA-00900: invalid SQL statement
(0,1):
^ ORA-00900: invalid SQL statement

Desktop (please complete the following information):
Windows

[shortgrass]

I did some more testing and it appears that the slash in the CalculateCPM function is the issue.

Here is the line
CPM := ROUND((Rate * 1000) / Imps, 2);

This code is doing division but for some reason the slash is causing an issue. I removed the spaces before and after the slash in the entire file and it ran fine.

[shortgrass]

I noticed in the source code for OracleSyntax that the separator is defined like so:

const string separator = @"(?<KEEP1>^|\s)(?<BATCHSPLITTER>/)(?<KEEP2>\s|;|$)";

Is the slash in that regex the issue?

[wokket]

G'day @shortgrass, thanks for the detailed report and preliminary investigation.

I can barely even spell Oracle 🤣 so I appreciate you digging this far. I did add a test alongside the existing batch splitter tests locally which confirms that in your case the function tries to get split mid-calculation, which is clearly a bug, and like you suspect it's to do with the slash in the regex.

I did a little bit of investigation, and like so much of this stuff realised I'm really not the guy to be trying to sort it out.

If you or anyone else that knows Oracle wants to take a punt here you're very welcome.

[shortgrass]

I think roundhouse was using a single semi-colon on a line by itself to split the batch. Example:
END AnalyticsCommonUtility;
;

and here:
END;
;

instead of splitting when it sees a forward slash.

[wokket]

Roundhouse looks to use a semicolon: https://github.com/chucknorris/roundhouse/blob/e2bb6cbd55879e293d33c4f29d6320d096c47de6/product/roundhouse.databases.oracle/OracleDatabase.cs#L17

I'll add the back compat label, but again, given the ambiguous behaviour called out in that link above and my lack of knowledge of conversations within the Oracle world, I probably won't be looking into this myself.