1 Reply Latest reply: Sep 11, 2013 12:28 PM by PatrickMarshall RSS

    (How) Can I unit test a function that returns a multi-column object type?


      Client: SQL Developer on Windows 7

      Database: Oracle 11g R2 (


      In my project I have created an object type and a function that returns that type (much simplified but still valid code below). I would now like to unit test that code using the SQL Developer Unit Test tools.


      CREATE OR REPLACE TYPE four_column_obj IS OBJECT
          col_name      VARCHAR2(255),
          col_value     VARCHAR2(4000),
          col_text      VARCHAR2(4000),
          col_sql       VARCHAR2(4000)
      CREATE OR REPLACE FUNCTION get_column_dtl (p_name IN VARCHAR2)
        RETURN four_column_obj
          v_ret four_column_obj := four_column_obj('','','','');
          v_ret.col_name  := p_name;
          v_ret.col_value := 'A value';
          v_ret.col_text  := 'Some relevant text';
          v_ret.col_sql   := 'SELECT some_column FROM any_table WHERE condition';
          RETURN V_RET;
      END get_column_dtl;

      The problems come when I try to supply expected values to the output object's attributes in the testing tool. The values I key for the first three are accepted but the last is not. Thinking there was perhaps a character limit, I tried reducing the number of attributes to two, but the outcome was the same with the second attribute value stubbornly staying null.


      Is there a way to get the tool to behave "properly" or is this a known issue similar to https://apex.oracle.com/pls/apex/f?p=43135:7:4785700093560::NO:RP,7:P7_ID:22801? If the latter, is there any sign of support for user-defined types being included any time soon?