Skip to content

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

@shortgrass

Description

@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

Metadata

Metadata

Assignees

No one assigned

    Labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions