We need to set up the concenpt that the type define in sql and in plsql is differenct. Type define in sql can be referenced by plsql but not visa versa, e.g. the statement
"create or replace type My_Type AS OBJECT of ...;"
is creating a type in the sql level, which means both the sql and plsql can use that type. of course you can use that type in your table creation.
On the contrast, Record is a plsql type only and you can only define it in plsql block, which as following:
"declare
type My_Type is RECORD ... ; //declare record type;
v_record My_Type ; //create a variable of that type;
type My_Type AS OBJECT of ...; //samples here shows object can also defined in plsql, but that is in plsql level only
begin
..........
end;
"
Since Record is plsql level only, the statement like "create or replace type My_Type is RECORD of ...;" is always fault!
plsql(or maybe i should say oracle) has three kind of collections: associat tables, nested table and varray. Among them only the associate table is avaialable in plsql only, that means the statement
"create or relace type My_Type is table of .... index by binary_integer"
will never compiled successfully. Also noted the oracle9i release1 only support binary_integer index while r2 can support varchar2, that make it closer to hast table in other programing language.