4 Replies Latest reply: Oct 20, 2010 3:24 AM by Zlatko Sirotic RSS

    Finding the type of a sub-type

    507471
      We have a table, Inventory, which has a column, item. item can be an item_ty or any sub-type of item_ty (ex: vehicle_ty, part_ty)
      My problem is that I do not know how to tell which sub-type an item is. when i select item from inventory how do i know what 'type' of item i have returned?
      I know how to use the IS OF predicate and that is not what i am trying to do. In essance i want to do the opposite of IS OF.
        • 1. Re: Finding the type of a sub-type
          247823
          Hi,

          If USER_DEPENDENCIES helps you then it will be great.

          Regards,
          Sailaja
          • 2. Re: Finding the type of a sub-type
            Zlatko Sirotic
            Maybe this helps

            "How to Get the Text Name of an Object's Type"
            http://www.revealnet.com/pipelines/plsql/tips06.htm


            Regards,
            Zlatko Sirotic
            • 3. Re: Finding the type of a sub-type
              Billy~Verreynne
              > Maybe this helps
              "How to Get the Text Name of an Object's Type

              Nice. Thanks.
              • 4. Re: Finding the type of a sub-type
                Zlatko Sirotic
                It seems that web page "How to Get the Text Name of an Object's Type" is removed.
                The original text (July 2006):


                               How to get the text name of an object's type
                               --------------------------------------------


                SYS_TYPEID function (in PL/SQL 9i/10g) can be used in a query to return the typeid of the most specific type of the object instance passed to the function as an argument.

                But, is there any way to get the text name of an object's type (or I must create my own get_type_name function and implement it in every type)?

                One (not perfect) solution is to use ANYDATA:
                CREATE TYPE Person_typ AS OBJECT (
                  ssn NUMBER,
                  name VARCHAR2(30),
                  address VARCHAR2(100)
                ) NOT FINAL
                /
                
                CREATE TYPE Student_typ UNDER Person_typ (
                  deptid NUMBER,
                  major VARCHAR2(30)
                ) NOT FINAL
                
                /
                CREATE TYPE PartTimeStudent_typ UNDER Student_typ (
                  numhours NUMBER
                )
                /
                
                CREATE TABLE persons OF Person_typ
                /
                
                INSERT INTO persons 
                  VALUES (Person_typ (1243, 'Bob', '121 Front St'))
                /
                
                INSERT INTO persons 
                  VALUES (Student_typ (3456, 'Joe', '34 View', 12, 'HISTORY'))
                /  
                
                INSERT INTO persons 
                  VALUES (PartTimeStudent_typ (5678, 'Tim', 'xx', 13, 'PHYSICS', 20))
                /
                
                SELECT name, SYS_TYPEID (VALUE(p)) typeid
                  FROM persons p
                /
                NAME   TYPEID
                ----   ------
                Bob    01
                Joe    02
                Tim    03
                First variant - parameter is of type Person_type:
                CREATE OR REPLACE FUNCTION person_typ_name (p_person Person_typ) RETURN VARCHAR2 IS
                  l_person ANYDATA;
                BEGIN
                  l_person := ANYDATA.ConvertObject (p_person);
                  RETURN l_person.GetTypeName;
                END;
                /
                or without local variable l_person:
                CREATE OR REPLACE FUNCTION person_typ_name (p_person Person_typ) RETURN VARCHAR2 IS
                BEGIN
                  RETURN ANYDATA.ConvertObject (p_person).GetTypeName;
                END;
                /
                
                SELECT name, person_typ_name (VALUE(p)) person_type_name
                  FROM persons p
                /
                NAME   PERSON_TYPE_NAME
                ----   ---------------------------------
                Bob    SCOTT.PERSON_TYP
                Joe    SCOTT.STUDENT_TYP
                Tim    SCOTT.PARTTIMESTUDENT_TYP
                Second variant - parameter is of (generic) type ANYDATA:
                CREATE OR REPLACE FUNCTION any_typ_name (p_object ANYDATA) RETURN VARCHAR2 IS
                BEGIN
                  RETURN p_object.GetTypeName;
                END;
                /
                and SELECT statement must be:
                SELECT name, any_typ_name (ANYDATA.ConvertObject (VALUE (p))) person_type_name
                  FROM persons p
                /
                NAME   PERSON_TYPE_NAME
                ----   ---------------------------------
                Bob    SCOTT.PERSON_TYP
                Joe    SCOTT.STUDENT_TYP
                Tim    SCOTT.PARTTIMESTUDENT_TYP