Re: 8.1.7 and 9.2 pl/sql handling

RR schrieb:
> 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!

really interesting code. You loop 10 times over the same sql statement? You expect to see ten times the same output? I guess you should check the logic once again.

As I understand, you run this code on two different databases. The statement does not contain some ORDER BY clause, so the order of the returned values is very mucht to be different on different databases, even on databases of the same version. Have you tried to run the code on two 9i databases? Or on two 8i databases?

Regards!
Rainer

-- 
------------------------------------------------
Rainer Herbst         Linux - Registered
ZEIK                  User #319157
Universität Potsdam   Usual disclaimers applies!
------------------------------------------------