Re: 8.1.7 and 9.2 pl/sql handling

RR wrote:

> v. 8.1.7 & 9.2, linux
> Hi, I have some code here that returns the results that I expect in
> 9.2, but not in 8.1.7.
>
> I can't seem to find the reasoning behind it, other than the obvious
> fact that pl/sql handles the code differently in 8.1.7 than it does in
> 9.2.
>
> If I run the stand-alone sql, it returns the correct results for both
> versions, though not in pl/sql
>
> Is there a work-around to get the values that I want from the 8.1.7
> version, by using pl/sql code.
>
> test code:
>
> declare
> v_num1 number(6);
> v_num2 number(6);
> v_num3 number(6);
> v_num4 number(6);
> v_num5 number(6);
> v_type1 varchar2(50);
> v_type2 varchar2(50);
> v_type3 varchar2(50);
> v_type4 varchar2(50);
> v_type5 varchar2(50);
> begin
> for i in 1..10 loop
> v_num1 := null;
> v_num2 := null;
> v_num3 := null;
> v_num4 := null;
> v_num5 := null;
> v_type1 := null;
> v_type2 := null;
> v_type3 := null;
> v_type4 := null;
> v_type5 := null;
> select max(decode(rn,1,num,null)) num1,
> max(decode(rn,1,object_type,null)) type1,
> max(decode(rn,2,num,null)) num2,
> max(decode(rn,2,object_type,null)) type2,
> max(decode(rn,3,num,null)) num3,
> max(decode(rn,3,object_type,null)) type3,
> max(decode(rn,4,num,null)) num4,
> max(decode(rn,4,object_type,null)) type4,
> max(decode(rn,5,num,null)) num5,
> max(decode(rn,5,object_type,null)) type5
> into v_num1,v_type1,v_num2,v_type2,v_num3,v_type3,v_num4,v_type4,v_num5,v_type5
> from (select rownum rn,num,object_type
> from (select count(*) num,object_type
> from all_objects
> group by object_type));
> dbms_output.put_line('i. '||v_num1||', ii. '||v_num2||', iii.
> '||v_num3||', iv. '||v_num4||', v. '||v_num5);
> dbms_output.put_line('i. '||v_type1||', ii. '||v_type2||', iii.
> '||v_type3||', iv. '||v_type4||', v. '||v_type5);
> end loop;
> end;
> /
>
> thanks!

This would work a whole lot better if you gave us some sense of what the data looks like and samples from the two different result sets. Right now the only thing I can think of is: You haven't got a WHERE clause so what's the point of the loop.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)