Hi,
I'm currently developing a backend automation and things aren't really going smooth. The problem is that I never developed in Oracle before. The situation:
I have 26 tables in my system. The goal is to automatically correct results from measurements using standards. Someone else did the system analysis long time ago and noone around here knows the details anymore. Anyhow.
How I want to implement it:
A trigger responds to an update of the measurement header. If the value is updated to 'true' the automatical correction has to kick off.
I tought to put the whole correction block in a package and started reading (ask tom, several university sites etc)
The following is what I came up with, and the result is: no compilation. Can you please give me some tips on how to structure my code?
-- Oracle Background processing for ARC (collection of triggers, functions and stored procedures
-- View of the SampleMeasurement Details ordered by concentration (speeds up processing)
/*
CREATE OR REPLACE VIEW V_SAT01_OrderByConcentration
AS
SELECT * FROM ARC_SAT01_SMPMDET ORDER BY SAT01_Concentration;
*/
-- Package containing all rule data of a certain method version
-- Package header
CREATE OR REPLACE PACKAGE AutomaticResultCorrectionPckg
AS
-- Own cursortype to enable functions returning a cursor
TYPE ARCCursorType IS REF CURSOR;
-- Record representing a sample header
TYPE SampleHeader_RecType IS RECORD(
SampleCode VARCHAR2(17),
Run NUMBER(2),
RealWeight FLOAT,
UAT09_ID NUMBER(6),
UAT07_ID NUMBER(5),
DateMeasured DATE NOT NULL := SYSDATE,
R2_SumTopConc FLOAT,
R4_SumTopConc FLOAT,
LIMS_Smp_Name VARCHAR2(80),
LIMS_ST_Code VARCHAR2(10),
LIMS_Smp_Type VARCHAR2(2),
CustomWeight FLOAT,
UserValidated CHAR(7),
DateValidated DATE
);
-- Record representing a methodversion
TYPE MethodVersion_RecType IS RECORD(
UAT03_ID NUMBER(3),
UAT04_ID1 NUMBER(4),
UAT04_ID2 NUMBER(4),
UAT04_ID3 NUMBER(4),
MinStd NUMBER(1),
MaxStd NUMBER(2),
DaysKept NUMBER(2),
SelectedRules NUMBER(4),
R1_CntConcTop NUMBER(2),
R2_NbrConcTop NUMBER(2),
R2_ConcPerc FLOAT,
R3_TopConc FLOAT,
R4_NbrChemConcStd NUMBER(2),
R4_NbrChemConcSmp NUMBER(2),
R4_RelConcDev FLOAT,
R5_AbsConcDev FLOAT,
R6_SlpMin FLOAT,
R7_BckMax FLOAT,
R8_SlpMax FLOAT,
R9_TotMax FLOAT,
AutoSendLIMS NUMBER(1) NOT NULL := 0
);
-- Public accessors of the automated correction. One for complete automated correction and one for correction fired by user (will probable change while building)
FUNCTION gf_DoAutomaticCorrection(p_sampleId IN NUMBER) RETURN NUMBER;
FUNCTION gf_DoCorrection(p_sampleId IN NUMBER) RETURN NUMBER;
END AutomaticResultCorrectionPckg;
/
-- Package Body: Implementation.
CREATE OR REPLACE PACKAGE BODY AutomaticResultCorrectionPckg
AS
R1 BOOLEAN := FALSE;
R2 BOOLEAN := FALSE;
R3 BOOLEAN := FALSE;
R4 BOOLEAN := FALSE;
R5 BOOLEAN := FALSE;
R6 BOOLEAN := FALSE;
R7 BOOLEAN := FALSE;
R8 BOOLEAN := FALSE;
R9 BOOLEAN := FALSE;
StandardSubSetToUse NUMBER := 0;
l_SampleHeader SampleHeader_RecType;
l_MethodVersion MethodVersion_RecType;
l_SampleDetailCursor ARCCursorType;
FUNCTION gf_DoAutomaticCorrection(p_sampleId IN NUMBER) RETURN NUMBER IS
correctionCode NUMBER := 0;
BEGIN
correctionCode := gf_DoCorrection(p_sampleID);
RETRUN correctionCode;
END gf_DoAutomaticCorrection;
FUNCTION gf_DoCorrection(p_sampleId IN NUMBER) RETURN NUMBER IS
correctionCode NUMBER := 0;
BEGIN
l_SampleHeader := GetSampleHeader(p_sampleId);
l_MethodVersion := GetMethodVersion(l_SampleHeader.UAT09_ID);
CheckRules(l_MethodVersion.SelectedRules);
l_SampleDetailCursor := GetSampleDetails(p_sampleId);
IF (R2 = TRUE) AND (CheckRule2(l_SampleDetailCursor) = TRUE) THEN
StandardSubsetToUse := l_MethodVersion.UAT04_ID2;
ELSIF (R3 = TRUE) AND (CheckRule3(l_SampleDetailCursor) = TRUE) THEN
StandardSubsetToUse := l_MethodVersion.UAT04_ID3;
ELSE
StandardSubsetToUse := l_MethodVersion.UAT04_ID1;
END;
RETURN correctionCode;
END gf_DoCorrection;
-- Checks wheter the result of Rule2 is true or false
FUNCTION CheckRule2(p_cursor IN ARCCursorType) RETURN BOOLEAN IS
l_retVal BOOLEAN := FALSE;
l_cursor ARCCursorType := p_cursor;
l_record l_cursor%ROWTYPE;
l_cnt NUMBER := 0;
BEGIN
IF (l_SampleHeader.R2_SumTopConc = 0) THEN
FOR l_cnt IN 1 .. l_MethodVersion.R2_NbrConcTop
LOOP
FETCH l_cursor INTO l_record;
l_SampleHeader.R2_SumTopConc = l_SampleHeader.R2_SumTopConc + l_record.SAT01_Concentration;
END LOOP;
END;
IF (l_SampleHeader.R2_SumTopConc <= l_MethodVersion.R2_ConcPerc) THEN
l_retVal := TRUE;
END;
RETURN l_retVal;
END CheckRule2;
-- Checks wheter the result of Rule3 is true or false
FUNCTION CheckRule3(p_cursor IN ARCCursorType) RETURN BOOLEAN IS
l_retVal BOOLEAN := FALSE;
l_cursor ARCCursorType := p_cursor;
l_record l_cursor%ROWTYPE;
BEGIN
FETCH l_cursor INTO l_record;
IF (l_record.SAT01_Concentration <= l_MethodVersion.R3_TopConc) THEN
l_retVal := TRUE;
END;
RETURN l_retVal;
END CheckRule3;
-- Gets the current sample header
FUNCTION GetSampleHeader(p_sampleId IN NUMBER) RETURN SampleHeader_RecType IS
l_record SampleHeader_RecType;
BEGIN
SELECT
SAT00_SampleCode,
SAT00_Run,
SAT00_RealWeight,
SAT00_UAT09_ID,
SAT00_UAT07_ID,
SAT00_DateMeasured,
SAT00_R2_SumTopConc,
SAT00_R4_SumTopConc,
SAT00_LIMS_Smp_Name,
SAT00_LIMS_ST_Code,
SAT00_LIMS_Smp_Type,
SAT00_CustomWeight,
SAT00_UserValidated,
SAT00_DateValidated
INTO l_record
FROM ARC_SAT00_SMPM
WHERE SAT00_ID = p_sampleId;
RETURN l_record;
END GetSampleHeader;
-- Gets the methodversion of the current sample
FUNCTION GetMethodVersion(p_methodVersionId IN NUMBER) RETURN MethodVersion_RecType IS
l_record MethodVersion_RecType;
BEGIN
SELECT
UAT09_UAT03_ID,
UAT09_UAT04_ID1,
UAT09_UAT04_ID2,
UAT09_UAT04_ID3,
UAT09_MinStd,
UAT09_MaxStd,
UAT09_DaysKept,
UAT09_SelectedRules,
UAT09_R1_CntConcTop,
UAT09_R2_NbrConcTop,
UAT09_R2_ConcPerc,
UAT09_R3_TopConc,
UAT09_R4_NbrChemConcStd,
UAT09_R4_NbrChemConcSmp,
UAT09_R4_RelConcDev,
UAT09_R5_AbsConcDev,
UAT09_R6_SlpMin,
UAT09_R7_BckMax,
UAT09_R8_SlpMax,
UAT09_R9_TotMax,
UAT09_AutoSendLIMS
INTO l_record
FROM ARC_UAT09_MTDVER
WHERE UAT09_ID = p_methodVersionId;
RETURN l_record;
END GetMethodVersion;
-- Checks which rules must be applied on the sample
PROCEDURE CheckRules(p_SelectedRules IN NUMBER) IS
l_temp NUMBER := p_SelectedRules;
BEGIN
if (l_temp >= 256) then
begin
l_temp := l_temp - 256;
R9 := true;
end;
if (l_temp >= 128) then
begin
l_temp := l_temp - 128;
R8 := true;
end;
if (l_temp >= 64) then
begin
l_temp := l_temp - 64;
R7 := true;
end;
if (l_temp >= 32) then
begin
l_temp := l_temp - 32;
R6 := true;
end;
if (l_temp >= 16) then
begin
l_temp := l_temp - 16;
R5 := true;
end;
if (l_temp >= 8) then
begin
l_temp := l_temp - 8;
R4 := true;
end;
if (l_temp >= 4) then
begin
l_temp := l_temp - 4;
R3 := true;
end;
if (l_temp >= 2) then
begin
l_temp := l_temp - 2;
R2 := true;
end;
if (l_temp >= 1) then
begin
l_temp := l_temp - 1;
R1 := true;
end;
END CheckRules;
-- Retrievese the current sample details
FUNCTION GetSampleDetails(p_sampleId IN NUMBER) RETURN ARCCursorType IS
l_cursor ARCCursorType;
BEGIN
OPEN l_cursor FOR SELECT * FROM V_SAT01_OrderByConcentration WHERE SAT01_SAT00_ID = p_SampleId;
RETURN l_cursor;
END GetSampleDetails;
BEGIN
-- Initialisation Section
END AutomaticResultCorrectionPckg;
/
-- End Of Package ----------------------------------------------------------------------------------------------------------------------------------
/*
CREATE TRIGGER SAT00_TRG_AfterUpdate01
AFTER UPDATE OF SAT00_AutomaticCorrection ON ARC_SAT00_SMPM
FOR EACH ROW
BEGIN
IF (:new.SAT00_CanSendLims > 0) THEN
VARIABLE c1 REFCURSOR
EXEC :c1 := F_SAT01_SelectSample(:new.SAT00_ID)
END IF;
END;
/*/
The trigger and the view are remnants (thats why they are commented out).
Last Edit I promise :)
The oracle version that i'm using:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Thanks in advance.
Message was edited by:
SL33PY