4 Replies Latest reply: Sep 25, 2010 4:49 AM by 795356 RSS

    Is there any possibility to provide default value while creating a type??

    795356
      Create or replace type t_type_1 is object
      (
      n number ,
      v varchar2(20)
      );
      /

      What I mean is this

      Create or replace type t_type_1 is object
      (
      n number default null,
      v varchar2(20)
      );
      /

      How to provide a default value as we give for a table.Beacuase whenever I alter a type as there is no default value I'm forced to modify dependent packages ,procedures and functions.

      Any help??
        • 1. Re: Is there any possibility to provide default value while creating a type??
          Zlatko Sirotic
          How to provide a default value ...
          create or replace type t_type_1 as object (
            n number,
            v varchar2(20),
            constructor function t_type_1 (p_n number, p_v varchar2) return self as result
          )
          /
          create or replace type body t_type_1 as
            constructor function t_type_1 (p_n number, p_v varchar2) return self as result
            is
            begin
               if p_n is null then n := '1'; end if;
               if p_v is null then v := 'ABC'; end if;
               return;
            end;
          end;
          /
          
          set serveroutput on
          /
          declare
            x t_type_1;
          begin
            x := new t_type_1(p_n => null, p_v => null);
            dbms_output.put_line (x.n || ' ' || x.v);
          end;
          /
          1 ABC
          
          PL/SQL procedure successfully completed.
          Regards
          • 2. Re: Is there any possibility to provide default value while creating a type
            795356
            Hii Sirotic thanx for your response but I want to know is there any possiblity to provide a defualt value while creating a type. Your explicitly passing a null value in your example. But what I mean is

            SQL> Create or replace type t_type_1 is object
            2 (
            3 n number,
            4 v varchar2(20)
            5 );
            6 /

            Type created.

            Create or replace procedure p
            is
                 l_type_1 t_type_1 := t_type_1(null,null);

            begin
                      l_type_1.n := 5;
                      l_type_1.v := 'ttt';

            end;
            /


            SQL> Create or replace procedure p
            2 is
            3 l_type_1 t_type_1 := t_type_1(null,null);
            4
            5 begin
            6 l_type_1.n := 5;
            7 l_type_1.v := 'ttt';
            8
            9 end;
            10 /

            Procedure created.

            SQL> Select status from user_objects where object_type = 'PROCEDURE' and object_name = 'P';

            STATUS
            -------
            VALID

            SQL> Alter type t_type_1 add attribute d date cascade;

            Type altered.

            SQL> Select status from user_objects where object_type = 'PROCEDURE' and object_name = 'P';

            STATUS
            -------
            INVALID

            In real time I'm using the same type in almost 20 procedures and now a new enhancement has come to alter the type and to add a new attribute.

            So what I'm asking is if I provide a default value to the newly added attribute (as we provide for a procedure or function parameters)then it wiil not have any adverse affect on the dependent procedures.

            But its not alowing me to provide a default value.

            SQL> Create or replace type t_type_1 is object
            2 (
            3 n number,
            4 v varchar2(20)
            5 gender char(1) default 'M'
            6 );
            7 /
            Create or replace type t_type_1 is object
            *
            ERROR at line 1:
            ORA-22308: operation not allowed on evolved type
            • 3. Re: Is there any possibility to provide default value while creating a type
              Zlatko Sirotic
              create or replace type t_type_1 as object (
                n number,
                v varchar2(20),
                g varchar2(1),
                constructor function t_type_1 (p_n number, p_v varchar2) return self as result
              )
              / 
              create or replace type body t_type_1 as
                constructor function t_type_1 (p_n number, p_v varchar2) return self as result
                is
                begin
                   if p_n is null then n := '1'; end if;
                   if p_v is null then v := 'ABC'; end if;
                   g := 'M';
                   return;
                end;
              end;
              /
              
              
              set serveroutput on
              / 
              declare
                x t_type_1;
              begin
                x := new t_type_1(null, null);
                dbms_output.put_line (x.n || ' ' || x.v || ' ' || x.g);
              end;
              /
              1 ABC M
              
              declare
                x t_type_1;
              begin
                x := new t_type_1('2', 'XYZ', 'F');
                dbms_output.put_line (x.n || ' ' || x.v || ' ' || x.g);
              end;
              /
              2 XYZ F
              Regards
              • 4. Re: Is there any possibility to provide default value while creating a type
                795356
                Thats great..thanks alot Zlatko Sirotic.Wow,I learnt a new feature..really delighted...Once again thanks for your quick response.