title: How to get the values of an OBJECT or ROWTYPE dynamically (Reflection) in Oracle PL/SQL?
body: <p>I am trying to take a ROWTYPE, RECORD, or OBJECT type and dynamically convert it to a single string representation.<br>
  I want to do this dynamically.</p>
  
  <p><strong><em>Update:</em></strong> Doing this for an OBJECT type now working thanks to Justin Cave's feedback.</p>
  
  <p><strong>Example Data:</strong></p>
  
  <pre><code>ID | VAL
  ---------
  1  | BOB
  </code></pre>
  
  <p><strong>Desired Output:</strong></p>
  
  <pre><code>ID=1, VAL=BOB
  </code></pre>
  
  <p><strong>Error Received:</strong></p>
  
  <pre><code>ORA-06550: line 7, column 25:
  PLS-00306: wrong number or types of arguments in call to 'TO_STRING'
  ORA-06550: line 7, column 4:
  PL/SQL: Statement ignored
  </code></pre>
  
  <p><strong>What I have so Far (Doesn't loop through columns yet.):</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION to_string (
      ip_anydata in out anydata --note the "out" - this is required for the "piecewise"
  )
  RETURN VARCHAR2
  IS
     lv_typecode PLS_INTEGER;
     lv_anytype anytype;
  BEGIN
     DBMS_OUTPUT.PUT_LINE('[Expected='||dbms_types.typecode_object||', Actual='||ip_anydata.getType(lv_anytype)||']');
     --Get the typecode, and the ANYTYPE
     lv_typecode := ip_anydata.getType(lv_anytype);
  
     --Check that it's really an object
     IF lv_typecode = dbms_types.typecode_object
     THEN
        --If it is an object, find the first item
        DECLARE
           lv_first_attribute_typecode pls_integer;
           lv_aname          varchar2(32767);
           lv_result         pls_integer;
           lv_varchar        varchar2(32767);
           --Variables we don't really care about, but need for function output
           lv_prec           pls_integer; 
           lv_scale          pls_integer;
           lv_len            pls_integer;
           lv_csid           pls_integer;
           lv_csfrm          pls_integer;
           lv_attr_elt_type  anytype;
        BEGIN
           lv_first_attribute_typecode := lv_anytype.getAttrElemInfo(
              pos            =&gt; 1, --First attribute
              prec           =&gt; lv_prec,
              scale          =&gt; lv_scale,
              len            =&gt; lv_len,
              csid           =&gt; lv_csid,
              csfrm          =&gt; lv_csfrm,
              attr_elt_type  =&gt; lv_attr_elt_type,
              aname          =&gt; lv_aname
           );
  
           --Check typecode of attribute
           IF lv_first_attribute_typecode = dbms_types.typecode_varchar2
           THEN
              --Now that we've verified the type, get the actual value.
              ip_anydata.piecewise;
              lv_result := ip_anydata.getVarchar2(c =&gt; lv_varchar);
  
              --DEBUG: Print the attribute name, in case you're curious
              --dbms_output.put_line('lv_aname: '||lv_aname);
              RETURN lv_aname||'='||lv_varchar;
           ELSE
              raise_application_error(-20000, 'Unexpected 1st Attribute Typecode: '||lv_first_attribute_typecode);
           END IF;
        END;
     ELSE
         raise_application_error(-20000, 'Unexpected Typecode: '||lv_typecode);
     END IF;
  END;
  /
  </code></pre>
  
  <p><strong>Scenario#1 - Select INTO w/ ROWTYPE:</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE
     lv_cv dual%ROWTYPE;
     lv_str VARCHAR2(32767);
  BEGIN
     DBMS_OUTPUT.PUT_LINE('-----------------------------');
     SELECT * INTO lv_cv FROM dual WHERE ROWNUM &lt;= 1;
     DBMS_OUTPUT.PUT_LINE(to_string(lv_cv));
  END;
  /
  </code></pre>
  
  <p><strong>Scenario#2 - FETCH INTO w/ Cursor ROWTYPE:</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE
     CURSOR cv_cur IS SELECT * FROM dual WHERE ROWNUM &lt;= 1;
     lv_cv cv_cur%ROWTYPE;
     lv_str VARCHAR2(32767);
  BEGIN
     DBMS_OUTPUT.PUT_LINE('-----------------------------');
     OPEN cv_cur;
     FETCH cv_cur INTO lv_cv;
     CLOSE cv_cur;
     DBMS_OUTPUT.PUT_LINE(to_string(lv_cv));
  END;
  /
  </code></pre>
  
  <p><strong>Scenario#3 - Regular OBJECT:</strong> <em>(Updated)</em></p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE
     lv_cv T_CODE_VAL_REC := T_CODE_VAL_REC('BOB', 5);
     lv_str VARCHAR2(32767);
     lv_any ANYDATA;
  BEGIN
     DBMS_OUTPUT.PUT_LINE('-----------------------------');
     lv_any := sys.anydata.ConvertObject(lv_cv);
     DBMS_OUTPUT.PUT_LINE(to_string(lv_any));
  EXCEPTION WHEN OTHERS THEN
     pts2_test_valitation_util.fail(999, CHR(10)||CHR(10)||'Unexpected Error:'||CHR(10)||SQLERRM||CHR(10)||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  END;
  /
  </code></pre>