A type with declaration but without definition used throughout many packages

Question

I have just started my adventure with Oracle and PL/SQL by analysing some legacy code.

I came across the following declaration which does not seem to have any definition (BODY)

CREATE OR REPLACE TYPE "TY_STRING_T";

but is used in many places throughout many packages. For example like this.

otc ty_string_t := ty_string_t();

I understand that this is like creation of an instance of class ty_string_t.

Then it is used for example as a collection.

otc.EXTEND;
otc(1) := 'test';

How come that this type is used that way while there is no definition of it at all? What am I missing? In SQL Developer under types there is no body part when I extend the node.

EDIT:

Searching for the dependencies in all_dependencies table gives the following result.

SELECT * FROM all_dependencies WHERE name = `TY_STRING_T`;
------------------------------------------------------------------------------------------------------------------------------
| OWNER | NAME        | TYPE | REFERENCED_OWNER | REFERENCED_NAME | REFERENCED_TYPE | REFERENCED_LINK_NAME | DEPENDENCY_TYPE |
------------------------------------------------------------------------------------------------------------------------------
| AAA   | TY_STRING_T | TYPE | SYS              | STANDARD        | PACKAGE         | (null)               | HARD            |
------------------------------------------------------------------------------------------------------------------------------

Answer 1

The statement you show:

CREATE OR REPLACE TYPE "TY_STRING_T";

creates an incomplete object type:

Use the CREATE TYPE statement to create the specification of an object type, a SQLJ object type, a named varying array (varray), a nested table type, or an incomplete object type.

...

An incomplete type is a type created by a forward type definition. It is called "incomplete" because it has a name but no attributes or methods. It can be referenced by other types, and so can be used to define types that refer to each other. However, you must fully specify the type before you can use it to create a table or an object column or a column of a nested table type.

The object-relational developer's guide has more information about incomplete types and examples of using them.

That isn't what you have though. You are instantiating and populating a varray, not an object type. It would not compile the PL/SQL code with the incomplete type - you would get an PLS-00311: the declaration of "TY_STRING_T" is incomplete or malformed compilation error.

From comments you do not have an overriding PL/SQL type and (from chat) the all_dependencies view confirms that your packages are dependent on the SQL-level type. But it can't be defined the way you think it is.

You can check the current actual definition of the type with

select dbms_metadata.get_ddl('TYPE', 'TY_STRING_T') from dual;

With the statement you showed that returns:

DBMS_METADATA.GET_DDL('TYPE','TY_STRING_T')                                    
--------------------------------------------------------------------------------

  CREATE OR REPLACE TYPE "STACKOVERFLOW"."TY_STRING_T" ;

Again from chat, when you run that you really see:

CREATE OR REPLACE TYPE "AAA"."TY_STRING_T" as table of varchar2(32767);

which makes much more sense. Oracle won't have added the as table... part itself though - since it would have been happy leaving it as an incomplete object type; you just woudn't have been able to use it, certainly not as if it was a varray.

So some other part of your code base recreated the type with the new, full, definition; or someone manually recreated the type (before creating the packages that refer to it). If I had to speculate I'd think this might be a source code control problem - that incomplete code was checked in and shipped, and it had to be corrected on the fly in each environment as it was installed. Or possibly that the code had been changed after installation, to a version that is no longer correct.

The type name suggests it was always supposed to be a table of strings though, not an object type; and the truncated version of the create command that you found would never have actually created a varray.

You aren't really missing anything in your understanding, it's just that what you are looking at doesn't reflect reality.