Re: LENGTH function can not be used in this scope.

kraig_mail_at_hotmail.com (Kraig) wrote in message news:<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???

It's strange you're getting this message. What version of Oracle are you using? When I tried PL/SQL version on 8.1.7 (no update, just print the denominator column), it worked OK.

You can easily get around using plain SQL:

update mdetail
set formula = 'DECODE'||substr(formula,instr(formula,'/')+1)||

        ',0,0,'||formula||')'
WHERE length(formula)-length(replace(formula,'/',null)) = 1   AND upper(ltrim(rtrim(substr(formula,1,2)))) <> 'DE';

Side notice. It's not a good idea to update a table inside of a cursor loop when cursor is defined on the same table. It's OK if you're using FOR UPDATE in the cursor definition and WHERE CURRENT OF, but not in your version. Received on Mon Dec 02 2002 - 21:17:21 CET