How to get the values of an OBJECT or ROWTYPE dynamically (Reflection) in Oracle PL/SQL?
<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>