This discussion is archived
4 Replies Latest reply: Aug 30, 2012 7:24 AM by BillyVerreynne RSS

Displaying value of Object type

949283 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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
    949283 Newbie
    Currently Being Moderated
    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
    Karthick_Arp Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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> 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points