5 Replies Latest reply: Feb 20, 2012 6:04 AM by mouratos RSS

    CHAR (and VARCHAR2) attribute semantics in Catalog Views

    mouratos
      Hello,

      It may sound quite elementary but I am struggling to find information about this.

      I have created an object that contains an attribute declared in the type spec as CHAR(8 CHAR).
      But where in the catalog views will I find the character semantic ?

      For example, in the case of TABLEs, I can look at the column CHAR_USED in the DBA_TAB_COLUMNS view. Unfortunately though, none of the three views (DBA_TYPES, DBA_TYPE_VERSIONS and DBA_TYPE_ATTRS) related to type specifications have such an indicator.

      I could of course do a string analysis on the TEXT column of the DBA_TYPE_VERSIONS view, but that would seem rather unnatural if there is a direct wayl ...

      Any ideas?

      Best Regards

      Philip
        • 1. Re: CHAR (and VARCHAR2) attribute semantics in Catalog Views
          rp0428
          Not sure what you are looking for but you can query the USER_SOURCE to get the source code for objects.
          CREATE OR REPLACE TYPE SCOTT.emp_scalar_type as object
            (EMPNO NUMBER(4) ,
             ENAME VARCHAR2(10),
             JOB VARCHAR2(9),
             MGR NUMBER(4),
             HIREDATE DATE,
             SAL NUMBER(7, 2),
             COMM NUMBER(7, 2),
             DEPTNO NUMBER(2)
            )
          /
          
          select * from user_source
          where name = 'EMP_SCALAR_TYPE'
          
          NAME                    TYPE LINE  TEXT
          EMP_SCALAR_TYPE,TYPE,1,type emp_scalar_type as object
          EMP_SCALAR_TYPE,TYPE,2,  (EMPNO NUMBER(4) ,
          EMP_SCALAR_TYPE,TYPE,3,   ENAME VARCHAR2(10),
          EMP_SCALAR_TYPE,TYPE,4,   JOB VARCHAR2(9),
          EMP_SCALAR_TYPE,TYPE,5,   MGR NUMBER(4),
          EMP_SCALAR_TYPE,TYPE,6,   HIREDATE DATE,
          EMP_SCALAR_TYPE,TYPE,7,   SAL NUMBER(7, 2),
          EMP_SCALAR_TYPE,TYPE,8,   COMM NUMBER(7, 2),
          EMP_SCALAR_TYPE,TYPE,9,   DEPTNO NUMBER(2)
          EMP_SCALAR_TYPE,TYPE,10,  ) 
          Is this what you are looking for?
          • 2. Re: CHAR (and VARCHAR2) attribute semantics in Catalog Views
            Solomon Yakobson
            mouratos wrote:

            But where in the catalog views will I find the character semantic?
            Good question. Oracle obviously keeps it in data dictionary tables but not in views. If you check DBA_TYPE_ATTRS view, you'll see it is based on sys.attribute$ table. It has column PROPERTIES and it looks like it holds attribute character semantic:
            SQL> create or replace
              2    type mouratos_chars
              3    as object(n varchar2(10 char))
              4  /
            
            Type created.
            
            SQL> create or replace
              2    type mouratos_bytes
              3    as object(n varchar2(10 byte))
              4  /
            
            Type created.
            
            SQL> select  ta.type_name,
              2          ta.attr_type_name,
              3          properties
              4    from  user_types t,
              5          user_type_attrs ta,
              6          sys.attribute$
              7    where toid = t.type_oid
              8      and ta.type_name = t.type_name
              9      and t.type_name like 'MOURATOS%'
             10  /
            
            TYPE_NAME                      ATTR_TYPE_NAME                 PROPERTIES
            ------------------------------ ------------------------------ ----------
            MOURATOS_BYTES                 VARCHAR2                                2
            MOURATOS_CHARS                 VARCHAR2                             4098
            
            SQL> 
            Now based on the above we can deduce it is 13th bit (from the right):
            select  ta.type_name,
                    ta.attr_type_name,
                    case
                      when ta.attr_type_name in ('CHAR','VARCHAR2') then decode(bitand(properties,4096),0,'BYTE','CHAR')
                    end char_semantic
              from  user_types t,
                    user_type_attrs ta,
                    sys.attribute$
              where toid = t.type_oid
                and ta.type_name = t.type_name
                and t.type_name like 'MOURATOS%'
            /
            
            TYPE_NAME                      ATTR_TYPE_NAME                 CHAR
            ------------------------------ ------------------------------ ----
            MOURATOS_BYTES                 VARCHAR2                       BYTE
            MOURATOS_CHARS                 VARCHAR2                       CHAR
            
            SQL>  
            SY.
            • 3. Re: CHAR (and VARCHAR2) attribute semantics in Catalog Views
              Barbara Boehmer
              Solomon,

              Very nice! I looked at this one previously and couldn't figure it out. I applied your solution to a test case that I had previously set up back then and found I needed to make a slight modification (additional join) for multiple columns. I have provided it below.
              SCOTT@orcl_11gR2> create or replace type test_object as object
                2    (char_attribute     char(8 char),
                3       byte_attribute     char(8 byte));
                4  /
              
              Type created.
              
              SCOTT@orcl_11gR2> column type_name     format a11
              SCOTT@orcl_11gR2> column attr_name     format a14
              SCOTT@orcl_11gR2> column attr_type_name format a14
              SCOTT@orcl_11gR2> column char_semantic     format a13
              SCOTT@orcl_11gR2> select t.type_name,
                2           ta.attr_name,
                3           ta.attr_type_name,
                4           case
                5              when ta.attr_type_name in ('CHAR','VARCHAR2')
                6              then decode(bitand(a.properties,4096),0,'BYTE','CHAR')
                7           end char_semantic
                8  from   user_types t,
                9           user_type_attrs ta,
               10           sys.attribute$ a
               11  where  t.type_name = 'TEST_OBJECT'
               12  and    t.type_name = ta.type_name
               13  and    t.type_oid = a.toid
               14  and    ta.attr_no = a.attribute#
               15  /
              
              TYPE_NAME   ATTR_NAME      ATTR_TYPE_NAME CHAR_SEMANTIC
              ----------- -------------- -------------- -------------
              TEST_OBJECT CHAR_ATTRIBUTE CHAR           CHAR
              TEST_OBJECT BYTE_ATTRIBUTE CHAR           BYTE
              
              2 rows selected.
              • 4. Re: CHAR (and VARCHAR2) attribute semantics in Catalog Views
                Solomon Yakobson
                Hi Barbara,

                yeap, i missed:
                ta.attr_no = a.attribute#
                SY.
                • 5. Re: CHAR (and VARCHAR2) attribute semantics in Catalog Views
                  mouratos
                  Solomon and Barbara,

                  many many thanks for this !!! Exactly what I was looking for :)


                  Best regards to you both

                  Philip