4 Replies Latest reply: Mar 31, 2013 10:32 AM by gaverill RSS

    How to access the Type-object variable declared inside another Type-object

    936328
      Hi Guys,

      I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
      CREATE OR REPLACE TYPE typeObj1
      AS OBJECT 
         ( 
            SYSTEM_IDENTIFER                    VARCHAR2(50),
            PROCESS_TYPE                          VARCHAR2(50),
            abc                                            typeObj2
            
         )
      /
      CREATE OR REPLACE TYPE typeObj2
      AS OBJECT 
         (
            ADDTN_INFO_KEY_TYP_NM     VARCHAR2(50),
            def                                        typeObj3
         )
      /
      CREATE OR REPLACE TYPE typeObj3
      AS OBJECT 
         (
            ADDTN_RQST_VALUE          VARCHAR2(50),     
            ADDTN_RQST_VAL_DT         TIMESTAMP(6),
            ADDTN_RQST_VAL_NUM        NUMBER(19, 2)
         )
      /
      I have tried to access the type-object in where clause in following way
      FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
      ......
      Where
      .......
      AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
      
      AND (ADTINF.ADDTN_RQST_TYP_VAL_DT  IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE(     ITTPRC.def)) OR ITTPRC.def IS NULL )
      AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM  IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
      AND (ADTINF.ADDTN_RQST_TYP_VALUE  IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL ) 
      In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.

      I passed the values like this in proc execution.
      T_T_A_I_V  :=  typeObj3('asdasd',NULL,NULL),
                             typeObj3('String654',NULL,NULL),
                             typeObj3('abcdef',NULL,NULL));                                     
          
          
      T_T_A_I_I  :=  typeObj2('CampusCode',T_T_A_I_V),
                                typeObj2('PlanNumber',T_T_A_I_V); 
      What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct.Can any one suggest me on this. is it right way of doing.
        • 1. Re: How to access the Type-object variable declared inside another Type-object
          Most Wanted!!!!
          the only way that stikes in my mind
          DECLARE
             lv_t_typeobj3   t_typeobj3      := NEW t_typeobj3 ();
             lv_o_typeobj3   typeobj3        := NEW typeobj3 (NULL, NULL, NULL);
             lv_t_typeobj2   t_typeobj2      := NEW t_typeobj2 ();
             lv_o_typeobj2   typeobj2        := NEW typeobj2 (NULL, NULL);
             lv_t_typeobj1   t_typeobj1      := NEW t_typeobj1 ();
             lv_o_typeobj1   typeobj1        := NEW typeobj1 (NULL, NULL, NULL);
             lv_v_sys        VARCHAR2 (4000);
             lv_v_pro        VARCHAR2 (4000);
             lv_v_abc        VARCHAR2 (4000);
             SYS             VARCHAR2 (4000);
             pro             VARCHAR2 (4000);
             KEY             VARCHAR2 (4000);
             val             VARCHAR2 (4000);
             dt              DATE;
             num             NUMBER;
          BEGIN
             lv_t_typeobj3.EXTEND (1);
             lv_o_typeobj3.addtn_rqst_value := 'A';
             lv_o_typeobj3.addtn_rqst_val_dt := SYSTIMESTAMP;
             lv_o_typeobj3.addtn_rqst_val_num := 1;
             lv_t_typeobj3 (1) := lv_o_typeobj3;
          --
             lv_t_typeobj2.EXTEND (1);
             lv_o_typeobj2.addtn_info_key_typ_nm := 'A';
             lv_t_typeobj2 (1) := lv_o_typeobj2;
          --
             lv_t_typeobj1.EXTEND (1);
             lv_o_typeobj1.system_identifer := 'A';
             lv_o_typeobj1.process_type := 'B';
             lv_t_typeobj1 (1) := lv_o_typeobj1;
          
             SELECT (SELECT system_identifer
                       FROM TABLE (lv_t_typeobj1)) system_identifier,
                    (SELECT process_type
                       FROM TABLE (lv_t_typeobj1)) process_type,
                    (SELECT addtn_info_key_typ_nm
                       FROM TABLE (lv_t_typeobj2)) addtn_info_key_typ_nm,
                    (SELECT addtn_rqst_value
                       FROM TABLE (lv_t_typeobj3)) addtn_rqst_value,
                    (SELECT addtn_rqst_val_dt
                       FROM TABLE (lv_t_typeobj3)) addtn_rqst_val_dt,
                    (SELECT addtn_rqst_val_num
                       FROM TABLE (lv_t_typeobj3)) addtn_rqst_val_num
               INTO SYS,
                    pro,
                    KEY,
                    val,
                    dt,
                    num
               FROM DUAL;
          
             DBMS_OUTPUT.put_line (   SYS
                                   || '_'
                                   || pro
                                   || '_'
                                   || KEY
                                   || '_'
                                   || val
                                   || '_'
                                   || dt
                                   || '_'
                                   || num
                                  );
          END;
          we will wait the gurus might give a better solution

          eagerly,
          friend
          • 2. Re: How to access the Type-object variable declared inside another Type-object
            Peter vd Zwan
            Hi,

            Is this what you are looking for?
            with a as
            (
            select
              typeObj1('sys1'
                      ,'proc1'
                      ,typeObj2('CampusCode1'
                                ,typeobj3('peter'
                                          ,NULL
                                          ,NULL)
                              )
                        ) xx
            
            from
              dual
            union all
            select
              typeObj1('sys2'
                      ,'proc2'
                      ,typeObj2('CampusCode2'
                                ,typeobj3('john'
                                          ,NULL
                                          ,NULL)
                              )
                        ) xx
            
            from
              dual
            
            )
            
            select
              alias_a.xx.SYSTEM_IDENTIFER
              ,alias_a.xx.PROCESS_TYPE
              ,alias_a.xx.abc.ADDTN_INFO_KEY_TYP_NM
              ,alias_a.xx.abc.def.ADDTN_RQST_VALUE
            
            from
              a alias_a
            ;
            
            XX.SYSTEM_IDENTIFER                                XX.PROCESS_TYPE                                    XX.ABC.ADDTN_INFO_KEY_TYP_NM                       XX.ABC.DEF.ADDTN_RQST_VALUE                      
            -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
            sys1                                               proc1                                              CampusCode1                                        peter                                              
            sys2                                               proc2                                              CampusCode2                                        john                                               
            Regards,

            Peter
            • 3. Re: How to access the Type-object variable declared inside another Type-object
              936328
              Thank you,

              yes we will wait. I haven't got good responses for ObjectType related queries here.

              once again thank you for Ur valuable time.
              • 4. Re: How to access the Type-object variable declared inside another Type-object
                gaverill
                Perhaps you should try the "Objects" forum.

                In any case, an object instance is not a table of object instances. Where is "I_typeObj1" coming from? Is it a variable? An object table or view? You need a table type to use the TABLE operator; where is your table type?

                There are plenty here who use objects extensively. However, the quality of answers often reflect the quality of the question.

                Gerard