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

You could see this if you have defined a function of your own called "length"...

ETA afilonov_at_yahoo.com (Alex Filonov) wrote in message news:<336da121.0212021217.1d88f731_at_posting.google.com>...
> 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 Wed Dec 04 2002 - 15:48:22 CET