Oracle PL/SQL: How to print a table type

Question

Hi,

I have the following Problem in a PL/SQL Script:

I am trying to print an TABLE TYPE for debugging purposes, but don't know how. I tried the following:
dbms_output.put_line (V_TEMP_TABTYPE(1));
and
dbms_output.put_line (V_TEMP_TABTYPE);
which doesn't work (PLS-00306: wrong number or types of arguments in call to).

So, how can I print the contents of a TABLE TYPE? Or is there a different way to display the contents?

MY_TABTYPE is:
create or replace TYPE MY_TABTYPE AS TABLE OF MY_TYPE;
MY_TYPE is:
create or replace TYPE MY_TYPE IS OBJECT( MyString Varchar(20), counter Number(9) );

Answer 1

dbms_output.put_line(v_temp_tabtype(i).myString);

Answer 2

Oracle has objects but it's ... different. Not exactly sure with your question if you want to see the values of the properties or if you want to actually see the type.

CREATE OR REPLACE TYPE MY_TYPE IS OBJECT ( 
  MyString Varchar(20)
  , counter Number(9) 
);

Now run some code for it.

DECLARE
    myType  MY_TYPE;
BEGIN
  myType := MY_TYPE('ABC123',0);
  -- To see the values reference the properties
  DBMS_OUTPUT.PUT_LINE(myType.mystring);
  -- To see they TYPE of the OBJECT
  DBMS_OUTPUT.PUT_LINE(SYS.ANYDATA.CONVERTOBJECT(myType).getTypeName());
END;

Of course you can create methods on the object to return information for you a bit easier.

CREATE OR REPLACE TYPE MY_TYPE IS OBJECT ( 
  MyString Varchar(20)
  , counter Number(9)
 , MEMBER FUNCTION getType RETURN VARCHAR2
 , MEMBER FUNCTION toString RETURN VARCHAR2
)
/

CREATE OR REPLACE TYPE BODY MY_TYPE 
AS
  MEMBER FUNCTION getTYPE RETURN VARCHAR2 IS
    BEGIN
      RETURN SYS.ANYDATA.CONVERTOBJECT(SELF).getTypeName();
    END;
  MEMBER FUNCTION toString RETURN VARCHAR2 IS
    BEGIN
      RETURN 'MY_TYPE('||self.mystring||','||self.counter||')';
    END;
END;
/

You can call the functions on the object now makes it easier to read imo.

DECLARE
  mytype    MY_TYPE;
BEGIN
  mytype := MY_TYPE('AGAIN','0');
  DBMS_OUTPUT.PUT_LINE(mytype.toString);
  DBMS_OUTPUT.PUT_LINE(mytype.getType);
END;