4 Replies Latest reply on Aug 30, 2012 2:24 PM by Billy~Verreynne

    Displaying value of Object type

    vivekan
      Hi All,

      I have created one type as Object and I am trying to display the values available in object type for debugging purpose. Please assist me to display the contents of object type.


      Thanks in Advance.


      Regards,
      Venkatesh S
        • 1. Re: Displaying value of Object type
          Gennady Sigalaev
          Hi Venkatesh,

          In SQL*plus
          set serveroutput on 
          In code:
          dbms_output.put_line ('your value');
          Regards,
          Gena
          • 2. Re: Displaying value of Object type
            vivekan
            Thanks Gena..

            In dbms_output.put_line ();, can I able to mention object name. like
            dbms_output.put_line (Object);
            • 3. Re: Displaying value of Object type
              Karthick2003
              vivekan wrote:
              Thanks Gena..

              In dbms_output.put_line ();, can I able to mention object name. like
              dbms_output.put_line (Object);
              NO. You need to specify Object.attribute
              • 4. Re: Displaying value of Object type
                Billy~Verreynne
                vivekan wrote:

                I have created one type as Object and I am trying to display the values available in object type for debugging purpose. Please assist me to display the contents of object type.
                You can do this statically or dynamically.

                The static approach would be to add a DebugDump() method to the object type class - and code this method to display a "debug dump" (via DBMS_OUTPUT for example) of the current instantiated object. Of course, should the class change (new attributes added for example), this DebugDump() method has to be updated.

                The dynamic method is more flexible - but requires a copy of the object to be created for the dynamic code to use and "dump".

                As the static method is pretty straightforward, I'm only providing a basic example of the dynamic method.
                // create a function that generates a PL/SQL anonymous code block that accepts the
                // object to dump as bind variable and the display the object's attributes
                SQL> create or replace function GenerateDumpCode( typeName varchar2 ) return varchar2 is
                  2          LF_INDENT       constant varchar2(10) default chr(10)||chr(9);
                  3          LF              constant varchar2(10) default chr(10);
                  4          type TAttrList is table of user_type_attrs.attr_name%Type;
                  5          type TAttrTypeList is table of user_type_attrs.attr_type_name%Type;
                  6          attrName        TAttrList;
                  7          attrType        TAttrTypeList;
                  8          plCode          varchar2(32767);
                  9  begin
                 10          select
                 11                  a.attr_name, a.attr_type_name
                 12                          bulk collect into
                 13                  attrName, attrType
                 14          from    user_type_attrs a
                 15          where   a.type_name = typeName
                 16          order by
                 17                  a.attr_no;
                 18  
                 19          plCode :=
                 20  'declare
                 21          obj '||typeName||';
                 22  
                 23          procedure W( line varchar2 ) is
                 24          begin
                 25                  DBMS_OUTPUT.put_line( line );
                 26          end;
                 27  begin
                 28          obj := :var1;
                 29  ';
                 30  
                 31          for i in 1..attrName.Count loop
                 32                  plCode := plCode ||LF_INDENT|| 'W( ''Attribute '||attrName(i)||'=''||';
                 33                  case attrType(i)
                 34                          when 'NUMBER' then plCode := plCode || 'to_char(obj.'||attrName(i)||') );';
                 35                          when 'INTEGER' then plCode := plCode || 'to_char(obj.'||attrName(i)||') );';
                 36                          when 'VARCHAR2' then plCode := plCode || 'obj.'||attrName(i)||' );';
                 37                          when 'DATE' then plCode := plCode || 'to_date(obj.'||attrName(i)||',''yyyy/mm/dd hh24:mi:ss'') );';
                 38                  else
                 39                          plCode := plCode || '''data type '||attrType(i)||': value not displayed.'');';
                 40  
                 41                  end case;
                 42          end loop;
                 43  
                 44          plCode := plCode ||LF|| 'end;';
                 45  
                 46          return( plCode );
                 47  end;
                 48  /
                
                Function created.
                // we need a sample object type/class for, so here's a basic one
                SQL> create or replace type TFunkyFoo is object(
                  2          day             date,
                  3          empID           integer,
                  4          empName         varchar2(20),
                  5          contract        clob
                  6  );
                  7  /
                
                Type created.
                // the dynamic code block that the function will generate looks as follows:
                SQL> select GenerateDumpCode( 'TFUNKYFOO' ) as PLSQL_CODE from dual;
                
                PLSQL_CODE
                --------------------------------------------------------------------------------
                declare
                        obj TFUNKYFOO;
                
                        procedure W( line varchar2 ) is
                        begin
                                DBMS_OUTPUT.put_line( line );
                        end;
                begin
                        obj := :var1;
                
                        W( 'Attribute DAY='||to_date(obj.DAY,'yyyy/mm/dd hh24:mi:ss') );
                        W( 'Attribute EMPID='||to_char(obj.EMPID) );
                        W( 'Attribute EMPNAME='||obj.EMPNAME );
                        W( 'Attribute CONTRACT='||'data type CLOB: value not displayed.');
                end;
                // using the function to dynamically display an object's attributes
                SQL> declare
                  2          foo TFunkyFoo;
                  3  begin
                  4          foo := new TFunkyFoo( sysdate, 100, 'John Smith', null );
                  5          execute immediate
                  6                  GenerateDumpCode( 'TFUNKYFOO' )
                  7          using   foo;
                  8  end;
                  9  /
                Attribute DAY=2012-08-30 14:48:35
                Attribute EMPID=100
                Attribute EMPNAME=John Smith
                Attribute CONTRACT=data type CLOB: value not displayed.
                
                PL/SQL procedure successfully completed.
                
                SQL>