2 Replies Latest reply on May 17, 2010 11:51 PM by 15078

    initialize objects wow

    15078
      Hi,
      recently and by accident I discovered another way to initialize object types.
      As you know sometime we have a lot of varchar2 members in type and there is always risk that we might pass a wrong value to a wrong type member. For instance somebody will recreate object type slightly changing order and last name will become first etc.
      I usually will initialize types with nulls, and then assign values to members directly just to be safe. I was thinking it would be nice to have position independent way of passing values
      similar to what we have in procedure parameters, here it is:

      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
      CREATE TYPE MY_TEST_TYP AS OBJECT
      2 (
      3 my_id number
      4 ,my_first varchar2(100)
      5 ,my_last varchar2(100)
      6 ,my_date date
      7 );
      8 /
      Type created.
      set serveroutput on size 1000000;
      declare
      2 l_obj1 MY_TEST_TYP;
      3 l_obj2 MY_TEST_TYP;
      4 begin
      5 l_obj1:=MY_TEST_TYP(my_id=>1,my_first=>'Andrei',my_last=>'Zverev',my_date=>sysdate);
      6 l_obj2:=MY_TEST_TYP(my_last=>'Zverev',my_first=>'Andrei',my_date=>sysdate,my_id=>1);
      7 dbms_output.put_line(l_obj1.my_id||','||l_obj1.my_first||','||l_obj1.my_last||','||l_obj1.my_date);
      8 dbms_output.put_line(l_obj2.my_id||','||l_obj2.my_first||','||l_obj2.my_last||','||l_obj2.my_date);
      9 end;
      10 /
      1,Andrei,Zverev,05/07/2010
      1,Andrei,Zverev,05/07/2010


      If works also in declaration of course:

      declare
      l_obj1 MY_TEST_TYP:=MY_TEST_TYP(my_id=>1,my_first=>'Andrei',my_last=>'Zverev',my_date=>sysdate);
      l_obj2 MY_TEST_TYP:=MY_TEST_TYP(my_last=>'Zverev',my_first=>'Andrei',my_date=>sysdate,my_id=>1);


      I did not find that it is documented anywhere. Does anyone know if that is referenced anywhere?
      Regards.
        • 1. Re: initialize objects wow
          Zlatko Sirotic
          Oracle PL/SQL User's Guide and Reference 10g Release 2 (10.2)
          - 12 Using PL/SQL With Object Types
          -- Manipulating Objects in PL/SQL
          --- Calling Object Constructors and Methods

          "... When you pass parameters to a constructor, the call assigns initial values to the attributes of the object being instantiated. When you call the default constructor to fill in all attribute values, you must supply a parameter for every attribute; unlike constants and variables, attributes cannot have default values. *You can call a constructor using named notation instead of positional notation.*"


          Before Release 11.1, a SQL statement that invoked a PL/SQL subprogram had to specify the actual parameters in positional notation.
          As of Release 11.1, named and mixed notation are also allowed:

          SQL> select MY_TEST_TYP(my_id=>1,my_first=>'Andrei',my_last=>'Zverev',my_date=>sysdate) from dual;

          MY_TEST_TYP(MY_ID=>1,MY_FIRST=>'ANDREI',MY_LAST=>'ZVEREV',MY_DATE=>SYSDATE)(MY_I
          --------------------------------------------------------------------------------
          MY_TEST_TYP(1, 'Andrei', 'Zverev', '08.05.10')


          Regards
          • 2. Re: initialize objects wow
            15078
            Sure, thanks Zlatko.