Home » SQL & PL/SQL » SQL & PL/SQL » procedure error ger end of file
| procedure error ger end of file [message #639853] |
Sat, 18 July 2015 00:55  |
 |
naga_raju
Messages: 1
Registered: July 2015
|
Junior Member |
|
|
CREATE OR replace PROCEDURE Validation_proc(mtrno VARCHAR2,
rdate VARCHAR2)
AS
cnt INT DEFAULT 2;
vcnt INT;
ival INT;
noofrows INT;
rid INT;
--TYPE INT;
stat INT;
sumc INT;
spikec INT;
kvarhc INT;
highcc INT;
lowcc INT;
highdc INT;
lowdc INT;
meterr0c INT;
uniqueid INT;
pname VARCHAR(30);
rname VARCHAR(30);
sumcstat VARCHAR(10);
spikecstat VARCHAR(10);
kvarhstat VARCHAR(10);
highccstat VARCHAR(10);
lowccstat VARCHAR(10);
highdcstat VARCHAR(10);
lowdcstat VARCHAR(10);
meterrostat VARCHAR(10);
estat VARCHAR(10);
tname VARCHAR(30);
dt VARCHAR(30);
bdt DATE;
pbdt DATE;
configparam VARCHAR(30);
formula VARCHAR(50);
colname VARCHAR(30);
amu FLOAT;
mu FLOAT;
c1 FLOAT;
c2 FLOAT;
c3 FLOAT;
d1 FLOAT;
d2 FLOAT;
kvarhlag FLOAT;
wh FLOAT;
pwh FLOAT;
dem FLOAT;
lcons FLOAT;
BEGIN
SELECT Count(*)
INTO cnt
FROM xml_d1
WHERE Trim(meterno) = mtrno;
--METER IDENTIFICATION CHECK
IF cnt <> 0 THEN
SELECT Max(CASE T1.ruleid
WHEN 3 THEN config
END)
INTO sumc
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Nvl(Max(CASE T2.ruleid
WHEN 3 THEN val_flag
END), 'ENABLE')
INTO sumcstat
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Max(CASE T1.ruleid
WHEN 4 THEN config
END)
INTO spikec
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Nvl(Max(CASE T2.ruleid
WHEN 4 THEN val_flag
END), 'ENABLE')
INTO spikecstat
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Max(CASE T1.ruleid
WHEN 5 THEN config
END)
INTO kvarhc
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Nvl(Max(CASE T2.ruleid
WHEN 5 THEN val_flag
END), 'ENABLE')
INTO kvarhstat
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Max(CASE T1.ruleid
WHEN 6 THEN config
END)
INTO highcc
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Nvl(Max(CASE T2.ruleid
WHEN 6 THEN val_flag
END), 'ENABLE')
INTO highccstat
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Max(CASE T1.ruleid
WHEN 7 THEN config
END)
INTO lowcc
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Nvl(Max(CASE T2.ruleid
WHEN 7 THEN val_flag
END), 'ENABLE')
INTO lowccstat
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Max(CASE T1.ruleid
WHEN 8 THEN config
END)
INTO highdc
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Nvl(Max(CASE T2.ruleid
WHEN 8 THEN val_flag
END), 'ENABLE')
INTO highdcstat
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Max(CASE T1.ruleid
WHEN 9 THEN config
END)
INTO lowdc
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Nvl(Max(CASE T2.ruleid
WHEN 9 THEN val_flag
END), 'ENABLE')
INTO lowdcstat
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Max(CASE T1.ruleid
WHEN 10 THEN config
END)
INTO meterr0c
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Nvl(Max(CASE T2.ruleid
WHEN 10 THEN val_flag
END), 'ENABLE')
INTO meterrostat
FROM mas_validation_rule T1
left outer join mas_validation_mapping T2
ON T1.ruleid = T2.ruleid
AND Trim(meterno) = mtrno;
SELECT Count(*)
INTO vcnt
FROM mas_validation_rule
WHERE TYPE = 2;
INSERT INTO ruletab
SELECT ruleid,
parameter,
rulename
FROM mas_validation_rule
WHERE TYPE = 2;
--TYPE 2 VALIDATION START
ival := 1;
WHILE ival <= vcnt LOOP
SELECT ruleformula
INTO formula
FROM mas_validation_rule
WHERE TYPE = 2
AND ruleid = (SELECT ruleid
FROM ruletab
WHERE ROWNUM = ival);
SELECT Substr(MODULE, 1, Instr(',', MODULE, 1, 1) - 1)
INTO tname
FROM mas_validation_rule
WHERE TYPE = 2
AND ruleid = (SELECT ruleid
FROM ruletab
WHERE ROWNUM = ival);
SELECT Substr(MODULE, Instr(',', MODULE, 1, 1) + 1, Length(MODULE) -
Instr(',', MODULE,
1, 1) + 1)
INTO dt
FROM mas_validation_rule
WHERE TYPE = 2
AND ruleid = (SELECT ruleid
FROM ruletab
WHERE ROWNUM = ival);
EXECUTE IMMEDIATE
'INSERT INTO STR (SELECT COUNT(*) FROM(SELECT *,RANK() OVER(PARTITION BY TRIM(METERNO) ORDER BY '
||dt||' DESC) RN FROM '||tname||' WHERE TRIM(METERNO)='''||mtrno||''') T WHERE '||
formula||'AND RN=1)';
SELECT Max(CASE
WHEN col > 0 THEN 1
END)
INTO stat
FROM str;
IF stat = 1 THEN
SELECT ruleid
INTO rid
FROM ruletab
WHERE ROWNUM = ival;
SELECT paramname
INTO pname
FROM ruletab
WHERE ROWNUM = ival;
SELECT rulename
INTO rname
FROM ruletab
WHERE ROWNUM = ival;
INSERT INTO mas_vee_log
(val_ruleno,
meterno,
val_type,
description,
parameter_name,
generateddate,
meterreaddate,
status,
tag_id,
val_flag)
VALUES (rid,
mtrno,
2,
rname,
pname,
SYSDATE,
rdate,
'VARIFIED'
||' '
||rname,
tname,
'YES');
SELECT Max(vee_id)
INTO uniqueid
FROM mas_vee_log;
EXECUTE IMMEDIATE ('UPDATE '||tname||' SET VAL_FLAG=VAL_FLAG||'',''||'''||
rname||''',VEE_FLAG=VEE_FLAG||'',''||CAST('||uniqueid||
' AS VARCHAR(10)) WHERE TRIM(METERNO)='''||mtrno||''' AND '||formula||'');
END IF;
ival := ival + 1;
END LOOP;
--TYPE 2 VALIDATION END;
EXECUTE IMMEDIATE
'INSERT INTO EDTABLE (WITH CTE AS (SELECT ROW_NUMBER() OVER(ORDER BY MDRESETDT DESC) RN,MDRESETDT,METERNO, KWH,KVARHL,KVA FROM SUM_MONTHLY_BILLING WHERE TRIM(METERNO)='''||mtrno||
''') SELECT CUR.MDRESETDT,LED1.MDRESETDT PREVDATE,CUR.KWH KWH,LED1.KWH PREVKWH,CUR.KVA KVA,CUR.KVARHL KVARHL,CUR.KVARHL-LED1.KVARHL KVARHL1, CUR.KWH-LED1.KWH CON1,LED1.KWH-LED2.KWH CON2,LED2.KWH-LED3.KWH CON3,CUR.KVA D1,LED1.KVA D2 FROM CTE CUR LEFT OUTER JOIN CTE LED1 ON LED1.RN=CUR.RN+1 LEFT OUTER JOIN CTE LED2 ON LED2.RN=CUR.RN+2 LEFT OUTER JOIN CTE LED3 ON LED3.RN=CUR.RN+3 WHERE CUR.RN=1;)'
;
IF Upper(highccstat) = 'ENABLE' --START HIGH CONSUMPTION CHECK
THEN
SELECT mdresetdt
INTO bdt
FROM edtable;
SELECT con1
INTO c1
FROM edtable;
SELECT con2
INTO c2
FROM edtable;
SELECT kwh
INTO wh
FROM edtable;
IF c1 > ( highcc * c2 ) / 100 THEN
EXECUTE IMMEDIATE ('INSERT INTO MAS_VEE_LOG(VAL_RULENO,METERNO,VAL_TYPE,DESCRIPTION,PARAMETER_NAME,PARAMETER_VALUE,GENERATEDDATE,METERREADDATE,TAG_ID,VAL_FLAG,STATUS ) VALUES (6,'''||mtrno||
''',1,''HIGH USAGE'', ''KWH'', CAST('||wh||' AS DECIMAL(10,2)),SYSDATE,'''
||
bdt||''', ''SUM_MONTHLY_BILLING'', ''YES'', ''VERIFIED_HIGH_CON'')');
SELECT Max(vee_id)
INTO uniqueid
FROM mas_vee_log;
EXECUTE IMMEDIATE(
'UPDATE SUM_MONTHLY_BILLING SET VAL_FLAG=VAL_FLAG||'',''||''INVALID_HIGH_CON'',VEE_FLAG=VEE_FLAG||'',''||CAST('
||uniqueid||' AS VARCHAR(10)) WHERE TRIM(METERNO)='''||mtrno||
''' AND MDRESETDT='''||bdt||'''');
END IF;
END IF; --END HIGH CONSUMPTION CHECK
END IF;
END;
*BlackSwan added {code} tags. Please do so yourself in the future. http://www.orafaq.com/forum/t/174502/
[Updated on: Sat, 18 July 2015 08:53] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: procedure error ger end of file [message #639859 is a reply to message #639853] |
Sat, 18 July 2015 05:09  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
In addition to John: it is impossible to spot anything when you do not post exact and full error (although it is just my guess as the title contains the word error folowed by some kind of mess). Oracle provides very descriptive reports about any error, so it is leading to the failing part. It is impossible to say what you did - did the error happen during procedure creation or run? if the second one, what was the command you used?
As the post body contains only procedure creation statement, I will make some comments on it (based by just a short look to the code):
- why are you using those multiple SELECT INTO with the same FROM/WHERE clause? It is possible to do it in one statement:
select max(...), max(...), ...
into val1, val2, ...
from ... -- these parts are the same
where ...;
- why are you using dynamic SQL? except the first two ones, everything is static, so e.g. the last one is simple
UPDATE SUM_MONTHLY_BILLING
SET VAL_FLAG=VAL_FLAG||','||'INVALID_HIGH_CON',
VEE_FLAG=VEE_FLAG||','||CAST( uniqueid AS VARCHAR(10))
WHERE TRIM(METERNO)= mtrno
AND MDRESETDT=bdt;
- even in the dynamic code with table names in variables, you may benefit from binding - for a start, study e.g. this post about it: http://www.akadia.com/services/ora_bind_variables.html
As there is a semicolon inside one of your EXECUTE IMMEDIATE statement, it will probably fail during procedure run. But it is really impossible to state it because of your post content (again, it is even impossible to say if you have any problem; anyway I suppose so as this forum is supposed to help with them).
[Edit: just prefaced my comments about code]
[Updated on: Sat, 18 July 2015 05:37] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Wed Jul 29 02:15:51 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
