Problem & Goal

Usually, like in java, any object will implement its to_string() function. So in Plsql, there is the same requirement.

 We want to implement to_string() in plsql.

Thinking

When one Object' type is basic type in plsql, then the function will return its value. Otherwise, the function will return its typename.

Solution  & Example

CREATE OR REPLACE FUNCTION to_string(obj IN ANYDATA) RETURN VARCHAR2 IScode PLS_INTEGER;v_type AnyType;BEGIN    code :=obj.getType(v_type);    CASE         code WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN                RETURN  TO_CHAR(obj.AccessVarchar2());            WHEN DBMS_TYPES.TYPECODE_BDOUBLE THEN                RETURN TO_CHAR(obj.AccessBDouble());            WHEN DBMS_TYPES.TYPECODE_BFLOAT THEN                RETURN TO_CHAR(obj.AccessBFloat());            WHEN DBMS_TYPES.TYPECODE_NUMBER THEN                RETURN TO_CHAR(obj.AccessNumber());        ELSE RETURN obj.getTypeName(); END CASE;END;/show errors;CREATE OR REPLACE TYPE TEST FORCE AS OBJECT(id Number, name VARCHAR2(20), CONSTRUCTOR FUNCTION TEST RETURN SELF AS RESULT);/show errors;CREATE OR REPLACE TYPE BODY TEST AS CONSTRUCTOR FUNCTION TEST RETURN SELF AS RESULT IS  BEGIN RETURN;END;END; /show errors;SET SERVEROUTPUT ON;DECLARE a Number;b VARCHAR2(20);c TEST;BEGIN    a :=1;    b:='julia zhang';    c :=TEST();    dbms_output.put_line('a:to_string()::::'||to_string(AnyData.convertNumber(a)));    dbms_output.put_line('b:to_string()::::'||to_string(AnyData.convertVarchar2(b)));    dbms_output.put_line('c:to_string()::::'||to_string(AnyData.convertObject(c)));END;/

Extend

 

CREATE OR REPLACE FUNCTION AnydataArray1ToString (id IN AnydataArray , for_flatten BOOLEAN ) RETURNVARCHAR2 ISi INTEGER ; result_str VARCHAR2 (32767); BEGIN    result_str  := '';    FOR i IN 1..id.count    LOOP        IF  for_flatten  THEN            result_str  := result_str  || ', ' || objectToString (id (i ));        ELSE result_str  := result_str  || objectToString (id (i )); END IF;    END LOOP;    IF  for_flatten  THEN RETURN substr (result_str , 3); ELSE        RETURN result_str ; END IF;END ;/

 

 

 

Refference

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/t_anydat.htm#BEHEICHI