LENGTH function can not be used in this scope.
Date: 1 Dec 2002 15:10:16 -0800
Message-ID: <d8ed39f5.0212011510.37a52e8a_at_posting.google.com>
I am having difficulties running this block of pl/sql. The hangup
revolves around the LENGTH function. I can snip the select statement
using the LENGTH function and it works fine. I can not use it when
opening my cursor.
This is the most efficient way I can think of to evaluate if there is
only one division expression in the string.
Oracle gives me PLS-00222: no function with name 'LENGTH' exists in
this scope
DECLARE
CURSOR cur1 IS
SELECT dataset, dslevel, measure, seq_no,
substr(formula,instr(formula,'/')+1) as denominator
FROM mdetail
WHERE length(formula)-length(replace(formula,'/',null)) = 1
AND upper(ltrim(rtrim(substr(formula,1,2)))) <> 'DE';
BEGIN
FOR cur1_val IN cur1
LOOP
UPDATE mdetail
SET formula =
'DECODE'||cur1_val.denominator||',0,0,'||cur1_val.formula||')'
WHERE mdetail.dataset = cur1_val.dataset
AND mdetail.dslevel = cur1_val.dslevel
AND mdetail.measure = cur1_val.measure
AND mdetail.seq_no = cur1_val.seq_no;
END LOOP;
END;
Any suggestions to get around this???
Received on Mon Dec 02 2002 - 00:10:16 CET