in SQL-Window with select of function returning object type using 64-bit version

[Bernhard S]

I create two object types:

create or replace type OT_1 as object
( -- Attributes
    a_n01   number(10)
  , a_n02   number(10)
  , a_n03   number(10)
  , a_n04   number(10)
  , a_n05   number(10)
  , a_n06   number(10)
  , a_n07   number(10)
  --
  , a_c01   char(1)
  , a_c02   char(1)
  , a_c03   char(1)
  , a_c04   char(1)
  , a_c05   char(1)
)

create or replace type OT_2 as object
( -- Attributes
    a_n01   number(10)
  , a_n02   number(10)
  , a_n03   number(10)
  , a_n04   number(10)
  , a_n05   number(10)
  --
  , a_c01   char(1)
  , a_c02   char(1)
  , a_c03   char(1)
  , a_c04   char(1)
  , a_c05   char(1)
)

I also create two functions returning those object types:

create or replace function get_ot_1 return ot_1
is
  v_ot_1   ot_1;
begin
  v_ot_1 := ot_1( a_n01 => 10
                , a_n02 => 20
                , a_n03 => 30
                , a_n04 => 40
                , a_n05 => 50
                , a_n06 => 60
                , a_n07 => 70
                --
                ,a_c01  => 'A'
                ,a_c02  => 'B'
                ,a_c03  => 'C'
                ,a_c04  => 'D'
                ,a_c05  => 'E'
                );

  return v_ot_1;
end get_ot_1;

create or replace function get_ot_2 return ot_2
is
  v_ot_2   ot_2;
begin
  v_ot_2 := ot_2( a_n01 => 10
                , a_n02 => 20
                , a_n03 => 30
                , a_n04 => 40
                , a_n05 => 50
                --
                ,a_c01  => 'A'
                ,a_c02  => 'B'
                ,a_c03  => 'C'
                ,a_c04  => 'D'
                ,a_c05  => 'E'
                );

  return v_ot_2;
end get_ot_2;

As you can see the whole difference between OT_1 and OT_2 types and functions is two number attributes more in the first ones.

In a SQL Window I then select using both functions:

select get_ot_1 from dual;
select get_ot_2 from dual;

Using PL/SQL Developer 64-bit Version 11.0.5.1790 I get for the first query for all the CHAR(1) attributes GET_OT_1.A_C01 .. GET_OT_1.A_C02
For the second query I get the correct results 'A'..'E' instead.
Using PL/SQL Developer 32-bit Version 11.0.5.1775 Iget the correct results for both queries. So it's clearly a problem with 64-bit version.

SQL*Plus shows the correct results for both queries as well:

SQL> select get_ot_1 from dual;

GET_OT_1(A_N01, A_N02, A_N03, A_N04, A_N05, A_N06, A_N07, A_C01, A_C02, A_C03, A_C04, A_C05)
---------------------------------------------------------------------------------------------
OT_1(10, 20, 30, 40, 50, 60, 70, 'A', 'B', 'C', 'D', 'E')

SQL> select get_ot_2 from dual;

GET_OT_2(A_N01, A_N02, A_N03, A_N04, A_N05, A_C01, A_C02, A_C03, A_C04, A_C05)
---------------------------------------------------------------------------------------------
OT_2(10, 20, 30, 40, 50, 'A', 'B', 'C', 'D', 'E')

SQL>

 

[Bernhard S]

I get the same behaviour in SQL Window as well with queries using the object types directly:

select ot_1( a_n01 => 10 , a_n02 => 20 , a_n03 => 30 , a_n04 => 40 , a_n05 => 50, a_n06 => 60, a_n07 => 70
           , a_c01 => 'A', a_c02 => 'B', a_c03 => 'C', a_c04 => 'D', a_c05 => 'E'
           )
  from dual;

select ot_2( a_n01 => 10 , a_n02 => 20 , a_n03 => 30 , a_n04 => 40 , a_n05 => 50--, a_n06 => 60, a_n07 => 70
           , a_c01 => 'A', a_c02 => 'B', a_c03 => 'C', a_c04 => 'D', a_c05 => 'E'
           )
  from dual;

 

[Marco Kalter]

We'll fix this for 11.0.6 later this month.