grate-devs / grate-devs/grate

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

Open
#642 5 comments 0 reactions 0 assignees Claimed by @erikbra View on GitHub
back-compat bug help wanted
Dominant language
C#
Stars
297
Forks
55
Avg merge
18m
Merged PRs (30d)
1

Description

**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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.