Ich versuche, einen ROWTYPE-, RECORD- oder OBJECT-Typ zu verwenden und ihn dynamisch in eine einzelne Zeichenfolgendarstellung zu konvertieren.
Ich möchte dies dynamisch tun.

Aktualisieren Dies für einen OBJECT-Typ zu tun, funktioniert jetzt dank des Feedbacks von Justin Cave.

Beispieldaten:

ID | VAL
---------
1  | BOB

Gewünschte Ausgabe:

ID=1, VAL=BOB

Error Received:

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

Was ich bisher habe (Durchläuft noch keine Spalten):

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            => 1, --First attribute
            prec           => lv_prec,
            scale          => lv_scale,
            len            => lv_len,
            csid           => lv_csid,
            csfrm          => lv_csfrm,
            attr_elt_type  => lv_attr_elt_type,
            aname          => 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 => 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;
/

Szenario 1 - Wählen Sie INTO mit ROWTYPE:

DECLARE
   lv_cv dual%ROWTYPE;
   lv_str VARCHAR2(32767);
BEGIN
   DBMS_OUTPUT.PUT_LINE('-----------------------------');
   SELECT * INTO lv_cv FROM dual WHERE ROWNUM <= 1;
   DBMS_OUTPUT.PUT_LINE(to_string(lv_cv));
END;
/

Szenario 2 - FETCH IN mit Cursor ROWTYPE:

DECLARE
   CURSOR cv_cur IS SELECT * FROM dual WHERE ROWNUM <= 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;
/

Szenario 3 - Regelmäßiges OBJEKT: (Aktualisiert

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;
/