--- description: Object-Oriented Programming is especially suited for building reusable components and complex applications. They are organized around "objects" rather than "actions" i.e. the programs are designed to title: Oracle PL/SQL Object Types: CREATE TYPE with Examples image: https://www.guru99.com/images/oracle-plsql-object-types-create-type.png --- [Skip to content](#main) **⚡ Smart Summary** Object types in PL/SQL bring object-oriented programming to Oracle, letting you model real-world entities with attributes and methods. They support constructors, inheritance, and equality comparison, so a single schema-level type can store and process structured data. * 🧱 **Object Type Components:** An object type combines attributes that store data and members, or methods, that define its processing logic. * 🏗️ **Create Object:** Object types are created at the schema level with CREATE TYPE, and their methods are defined in a separate CREATE TYPE BODY. * 🔧 **Constructors:** Every object type has an implicit constructor named after the type, and you can define an explicit constructor to set default values. * 🧬 **Inheritance:** A NOT FINAL parent type is extended with UNDER, so a sub-type inherits all parent attributes and members. * ⚖️ **Equality:** An ORDER member function compares two object instances and returns a negative, zero, or positive number. * 🤖 **AI Assistance:** AI assistants such as GitHub Copilot draft CREATE TYPE definitions, constructors, and ORDER methods from a comment. [ Read More ](javascript:void%280%29;) ![Oracle PL/SQL object types showing CREATE TYPE with attributes and methods](https://www.guru99.com/images/oracle-plsql-object-types-create-type.png) ## What is Object Type in PL/SQL? Object-Oriented Programming is especially suited to building reusable components and complex applications. Programs are organized around “objects” rather than “actions”; that is, they are designed to work with and interact with an entire object rather than a single action. This approach lets the programmer populate and manipulate details at the object-entity level. The picture below depicts an example of an object type in which a bank account is treated as an object entity. The object attributes hold values — for a bank account these are the account number, bank balance, and so on — while the object methods describe actions such as calculating the interest rate or generating a bank statement, each of which requires a process to be completed. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes1.png) In [PL/SQL](https://www.guru99.com/pl-sql-tutorials.html), object-oriented programming is based on object types. An object type can represent any real-world entity. The sections below explore object types, their components, and how to create and use them. ## Components of Object Types A PL/SQL object type contains mainly two components. 1. Attributes 2. Members/Methods ### Attributes Attributes are the columns or fields in which data are stored. Each attribute is mapped to the datatype that defines the processing and storage type for that attribute. An attribute can be of any valid [PL/SQL datatype](https://www.guru99.com/pl-sql-data-types.html), or it can be of another object type. ### Members/Methods Members, or methods, are [subprograms](https://www.guru99.com/subprograms-procedures-functions-pl-sql.html) defined inside the object type. They are not used to store data; instead, they define the processing performed inside the object type — for example, validating data before populating the object. They are declared in the object type specification and defined in the object type body. The body is optional: if no members are present, an object type has no body part. ## Create Object in Oracle An object type cannot be created at the subprogram level; it can be created only at the schema level. Once the object type is defined in the schema, it can be used in subprograms. The object type is created using the “CREATE TYPE” statement, and the type body can be created only after its object type exists. The screenshots below show the CREATE TYPE syntax for the object specification and the CREATE TYPE BODY syntax that defines its methods. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes2.png) [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes3.png) CREATE TYPE AS OBJECT ( , . . ); / CREATE TYPE BODY AS OBJECT ( MEMBER[PROCEDURE|FUNCTION] IS BEGIN END; . . ); / **Syntax Explanation:** * The syntax above shows the creation of an OBJECT with attributes and an OBJECT BODY with methods. * The methods can also be overloaded in the object body. ## Declaration Initialization of Object Type Like other components in PL/SQL, object types must be declared before they are used in a program. Once the object type is created, it can be used in a subprogram’s declarative section to declare a variable of that object type. Whenever a variable is declared as an object type, at run time a new instance of the object type is created, and this newly created instance is referred to by the variable name. In this way, a single object type can store multiple values under different instances. The screenshot below shows a variable being declared as an object type inside a [PL/SQL block](https://www.guru99.com/blocks-pl-sql.html). [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes4.png) DECLARE ; BEGIN . . END; / **Syntax Explanation:** * The syntax above shows the declaration of a [variable](https://www.guru99.com/pl-sql-identifiers.html) as an object type in the declarative section. Once the variable is declared as an object type in a subprogram, it is atomically null — the entire object itself is null. It must be initialized with values before it can be used in the program. Objects are initialized using constructors. Constructors are the implicit method of an object that can be referred to with the same name as the object type. The screenshot below shows the initialization of an object type instance. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes5.png) DECLARE ; BEGIN :=(); END; / **Syntax Explanation:** * The syntax above shows the initialization of the object type instance with a null value. * The object itself is no longer null once it has been initialized, but the attributes inside the object remain null until values are assigned to them. ### RELATED ARTICLES * [What is PL/SQL? Full Form & Architecture Explained ](https://www.guru99.com/introduction-pl-sql.html "What is PL/SQL? Full Form & Architecture Explained") * [Oracle PL/SQL Package: Type, Specification, Body \[Example\] ](https://www.guru99.com/packages-pl-sql.html "Oracle PL/SQL Package: Type, Specification, Body [Example]") * [Oracle PL/SQL Trigger: Instead of & Compound Types ](https://www.guru99.com/triggers-pl-sql.html "Oracle PL/SQL Trigger: Instead of & Compound Types") * [Oracle PL/SQL FOR LOOP with Example ](https://www.guru99.com/oracle-plsql-for-loop.html "Oracle PL/SQL FOR LOOP with Example") ## Constructors Constructors are the implicit method of an object that can be referred to with the same name as the object type. Whenever the object is referred to for the first time, this constructor is called implicitly. You can also initialize objects using these constructors. A constructor can be defined explicitly by defining a member in the object type body with the same name as the object type. **Example 1:** In the following example, we use the object type member to insert records into the emp table with the values (‘RRR’, 1005, 20000, 1000) and (‘PPP’, 1006, 20000, 1001). Once the data is inserted, we display it using the object type member. We also use an explicit constructor to populate the manager id with the default value 1001 for the second record. We execute it in the following steps. * Step 1: Create the Object type and Object type body. * Step 2: Create an anonymous block to call the object type through the implicit constructor for emp\_no 1005. * Step 3: Create an anonymous block to call the object type through the explicit constructor for emp\_no 1006. **Step 1) Create Object type and Object type body.** The screenshot below shows the emp\_object object type specification being created with its attributes and members. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes6.png) CREATE TYPE emp_object AS OBJECT( emp_no NUMBER, emp_name VARCHAR2(50), salary NUMBER, manager NUMBER, CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER, p_emp_name VARCHAR2, p_salary NUMBER) RETURN SELF AS RESULT), MEMBER PROCEDURE insert_records, MEMBER PROCEDURE display_records); / The screenshot below shows the emp\_object type body being created with the explicit constructor and the member procedures. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes7.png) CREATE OR REPLACE TYPE BODY emp_object AS CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER,p_emp_name VARCHAR2, p_salary NUMBER) RETURN SELF AS RESULT IS BEGIN Dbms_output.put_line('Constructor fired..'); SELF.emp_no:=p_emp_no; SELF.emp_name:=p_emp_name; SELF.salary:=p_salary; SELF.manager:=1001; RETURN; END; MEMBER PROCEDURE insert_records IS BEGIN INSERT INTO emp VALUES(emp_no,emp_name,salary,manager); END; MEMBER PROCEDURE display_records IS BEGIN Dbms_output.put_line('Employee Name:'||emp_name); Dbms_output.put_line('Employee Number:'||emp_no); Dbms_output.put_line('Salary:'||salary); Dbms_output.put_line('Manager:'||manager); END; END; / **Code Explanation** * **Code line 1-9:** Creating the ’emp\_object’ object type with 4 attributes and 3 members. It contains the definition of a constructor with only 3 parameters. (The actual implicit constructor contains the number of parameters equal to the number of attributes present in the object type.) * **Code line 10:** Creating the type body. * **Code line 11-21:** Defining the explicit constructor. Assigning the parameter values to the attributes and assigning the attribute ‘manager’ the default value ‘1001’. * **Code line 22-26:** Defining the member ‘insert\_records’, in which the attribute values are inserted into the ’emp’ table. * **Code line 27-34:** Defining the member ‘display\_records’, which displays the values of the object type attributes. **Output:** Type created Type body created **Step 2)** Creating an anonymous block to call the created object type through the implicit constructor for emp\_no 1005. The screenshot below shows the anonymous block that calls emp\_object through the implicit constructor. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes8.png) DECLARE guru_emp_det emp_object; BEGIN guru_emp_det:=emp_object(1005,'RRR',20000,1000); guru_emp_det.display_records; guru_emp_det.insert_records; COMMIT; END; **Code Explanation** * **Code line 37-45:** Inserting the records using the implicit constructor. The call to the constructor contains the actual number of attribute values. * **Code line 38:** Declares ‘guru\_emp\_det’ as object type ’emp\_object’. * **Code line 41:** The statement ‘guru\_emp\_det.display\_records’ calls the ‘display\_records’ member and the attribute values are displayed. * **Code line 42:** The statement ‘guru\_emp\_det.insert\_records’ calls the ‘insert\_records’ member and the attribute values are inserted into the table. The block then commits the [transaction](https://www.guru99.com/pl-sql-tcl-statements.html). **Output:** Employee Name: RRR Employee Number: 1005 Salary: 20000 Manager : 1000 **Step 3)** Creating an anonymous block to call the created object type through the explicit constructor for emp\_no 1006. The screenshot below shows the anonymous block that calls emp\_object through the explicit constructor with three arguments. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes9.png) DECLARE guru_emp_det emp_object; BEGIN guru_emp_det:=emp_object(1006,'PPP',20000); guru_emp_det.display_records; guru_emp_det.insert_records; COMMIT; END; / **Output** Employee Name:PPP Employee Number:1006 Salary:20000 Manager:1001 **Code Explanation:** * **Code line 46-53:** Inserting the record using the explicit constructor. * **Code line 46:** Declares ‘guru\_emp\_det’ as object type ’emp\_object’. * **Code line 50:** The statement ‘guru\_emp\_det.display\_records’ calls the ‘display\_records’ member and the attribute values are displayed. * **Code line 51:** The statement ‘guru\_emp\_det.insert\_records’ calls the ‘insert\_records’ member and the attribute values are inserted into the table. Because only three arguments are passed, the explicit constructor sets the manager id to its default value 1001. ## Inheritance in Object Type The inheritance property allows a sub-object type to access all the attributes and members of the super object type, or parent object type. The sub-object type is called the inherited object type, and the super object type is called the parent object type. The syntax below shows how to create parent and inherited object types. The screenshot below shows the parent (SUPER) type syntax marked as NOT FINAL. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes10.png) CREATE TYPE AS OBJECT ( , . . )NOT FINAL; / **Syntax Explanation:** * The syntax above shows the creation of the SUPER type. The NOT FINAL clause allows the type to be inherited. The screenshot below shows the inherited (SUB) type syntax created with the UNDER keyword. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes11.png) CREATE TYPEUNDER ( , . ); / **Syntax Explanation:** * The syntax above shows the creation of the SUB type. It contains all the members and attributes from the parent object type. **Example 1:** In the example below, we use the inheritance property to insert a record with manager id ‘1002’ for the record (‘RRR’, 1007, 20000). We execute the program in the following steps. * Step 1: Create the SUPER type. * Step 2: Create the SUB type and body. * Step 3: Create an anonymous block to call the SUB type. **Step 1) Create SUPER type or Parent type.** The screenshot below shows the emp\_object super type being created with the NOT FINAL clause. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes12.png) CREATE TYPE emp_object AS OBJECT( emp_no NUMBER, emp_name VARCHAR2(50), salary NUMBER, manager NUMBER, CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER,p_emp_name VARCHAR2(50), p_salary NUMBER)RETURN SELF AS RESULT), MEMBER PROCEDURE insert_records, MEMBER PROCEDURE display_records)NOT FINAL; / **Code Explanation:** * **Code line 1-9:** Creating the ’emp\_object’ object type with 4 attributes and 3 members. It contains the definition of a constructor with only 3 parameters. It has been declared as ‘NOT FINAL’, so it is a parent type. **Step 2) Create SUB type under SUPER type.** The screenshot below shows the sub\_emp\_object inherited type and its body being created. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes13.png) CREATE OR REPLACE TYPE sub_emp_object UNDER emp_object (default_manager NUMBER,MEMBER PROCEDURE insert_default_mgr); / CREATE OR REPLACE TYPE BODY sub_emp_object AS MEMBER PROCEDURE insert_default_mgr IS BEGIN INSERT INTO emp VALUES(emp_no,emp_name,salary,manager); END; END; / **Code Explanation:** * **Code line 10-13:** Creating ‘sub\_emp\_object’ as the inherited type with one additional attribute ‘default\_manager’ and a member procedure declaration. * **Code line 14:** Creating the body for the inherited object type. * **Code line 16-21:** Defining the member procedure that inserts records into the ’emp’ table with the values from the SUPER object type, except for the manager value. For the manager value, it uses ‘default\_manager’ from the SUB type. **Step 3) Creating anonymous block to call the SUB type.** The screenshot below shows the anonymous block that calls the SUB type and inserts the default manager id. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes14.png) DECLARE guru_emp_det sub_emp_object; BEGIN guru_emp_det:= sub_emp_object(1007,'RRR',20000,1000,1002); guru_emp_det.insert_default_mgr; COMMIT; END; / **Code Explanation:** * **Code line 25:** Declaring ‘guru\_emp\_det’ as ‘sub\_emp\_object’ type. * **Code line 27:** Initializing the object with the implicit constructor. The constructor has 5 parameters (4 attributes from the PARENT type and 1 attribute from the SUB type). The last parameter (1002) defines the value for the default\_manager attribute. * **Code line 28:** Calling the member ‘insert\_default\_mgr’ to insert the record with the default manager id passed in the constructor. ## Equality of PL/SQL Objects Object instances that belong to the same object type can be compared for equality. To do this, the object type needs a special method called the ORDER method. This ORDER method should be a function that returns a numerical type. It takes two parameters as input (first parameter: the id of the self-object instance; second parameter: the id of another object instance). The ids of the two object instances are compared, and the result is returned as a number. * A positive value indicates that the SELF object instance is greater than the other instance. * A negative value indicates that the SELF object instance is less than the other instance. * Zero indicates that the SELF object instance is equal to the other instance. * If either instance is null, the function returns null. The screenshot below shows the ORDER member function syntax that must be included in the type body for the equality check. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes15.png) CREATE TYPE BODYAS OBJECT ( ORDER MEMBER FUNCTION match( object_type_name_ 1) RETURN INTEGER IS BEGIN IF parameter THEN RETURN -1; --any negative number will do ELSIF id>c.id THEN RETURN 1; --any positive number will do ELSE RETURN 0; END IF; END; . . ); / **Syntax Explanation:** * The syntax above shows the ORDER function that must be included in the type body for the equality check. * The parameter for this function should be an instance of the same object type. * The function can be called as “obj\_instance\_1.match(obj\_instance\_2)”, and this expression returns the numerical value shown, where obj\_instance\_1 and obj\_instance\_2 are instances of the object type. **Example 1:** In the following example, we compare two objects. We create two instances and compare the ‘salary’ attribute between them. We do this in two steps. * Step 1: Create the Object type and body. * Step 2: Create the anonymous block to compare the object instances. **Step 1) Creating the Object type and body.** The screenshots below show the emp\_object\_equality specification and its body, which defines the ORDER function that compares the salary attribute. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes16.png) [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes17.png) CREATE TYPE emp_object_equality AS OBJECT( salary NUMBER, ORDER MEMBER FUNCTION equals(c emp_object_equality)RETURN INTEGER); / CREATE TYPE BODY emp_object_equality AS ORDER MEMBER FUNCTION equals(c emp_object_equality)RETURN INTEGER IS BEGIN IF salaryc.salary THEN RETURN 1; ELSE RETURN 0; END IF; END; END; / **Code Explanation:** * **Code line 1-4:** Creating the ’emp\_object\_equality’ object type with 1 attribute and 1 member. * **Code line 6-16:** Defining the ORDER function that compares the ‘salary’ attribute of the SELF instance and the parameter instance. It returns a negative value if the SELF salary is lesser, a positive value if the SELF salary is greater, and 0 if the salaries are equal. **Code Output:** Type created **Step 2) Creating the anonymous block to compare the object instances.** The screenshot below shows the anonymous block that compares two emp\_object\_equality instances by salary. [](https://www.guru99.com/images/PL-SQL/110215%5F1145%5FObjectTypes18.png) DECLARE l_obj_1 emp_object_equality; l_obj_2 emp_object_equality; BEGIN l_obj_1:=emp_object_equality(15000); l_obj_2:=emp_object_equality(17000); IF l_obj_1.equals(l_obj_2)>0 THEN Dbms_output.put_line('Salary of first instance is greater'); ELSIF l_obj_1.equals(l_obj_2)<0 THEN Dbms_output.put_line('Salary of second instance is greater'); ELSE Dbms_output.put_line('Salaries are equal'); END IF; END; / **Output** Salary of second instance is greater **Code Explanation:** * **Code line 20:** Declaring ‘l\_obj\_1′ of ’emp\_object\_equality’ type. * **Code line 21:** Declaring ‘l\_obj\_2′ of ’emp\_object\_equality’ type. * **Code line 23:** Initializing ‘l\_obj\_1’ with the salary value ‘15000’. * **Code line 24:** Initializing ‘l\_obj\_2’ with the salary value ‘17000’. * **Code line 25-33:** Printing the message based on the number returned by the ORDER function. ## FAQs 🧩 What is the difference between an object type and a collection in PL/SQL? An object type models a single real-world entity with attributes and methods. A [collection](https://www.guru99.com/complex-data-types-pl-sql.html) — a VARRAY, nested table, or associative array — holds many elements of one datatype. Object types describe structure and behaviour, while collections store multiple values. 🗺️ What is the difference between a MAP method and an ORDER method? Both compare object instances. A MAP method returns a single scalar value that Oracle uses to sort or compare objects, while an ORDER method compares two instances directly and returns a negative, zero, or positive number. A type may define one or the other, not both. 🏛️ Can object types be stored in a database table? Yes. You can create an object table where each row is an object instance, or use an object type as the datatype of a table column. This lets Oracle persist structured objects directly in the database rather than only in memory. 🚫 What does NOT INSTANTIABLE mean for an object type? A NOT INSTANTIABLE type cannot create objects directly; it acts as an abstract base that other types extend. Combined with NOT FINAL, it defines a supertype whose subtypes provide the concrete implementation of its declared methods. 🛠️ How do you modify or drop an existing object type? Use ALTER TYPE to add or drop attributes and methods, adding CASCADE to update dependent objects, and use DROP TYPE to remove the type. A type still used by tables or other types cannot be dropped until those dependents are handled first. 📚 How can you view the object types in your schema? Query the data dictionary views USER\_TYPES and ALL\_TYPES for type names, and USER\_TYPE\_ATTRS and USER\_TYPE\_METHODS for their attributes and methods. These views let you audit an existing object model without reading the source code. 🤖 Can GitHub Copilot generate PL/SQL object types? Yes. [GitHub Copilot](https://github.com/features/copilot) drafts CREATE TYPE specifications, TYPE BODY methods, constructors, and ORDER functions from a comment. Review the attribute datatypes, constructor parameters, and inheritance clauses before deploying the generated type. 🧠 How does AI help design a PL/SQL object model? AI assistants analyze your tables and suggest object types, attribute mappings, and inheritance hierarchies. This machine-learning review flags redundant attributes, missing constructors, and over-deep inheritance before the object model reaches production, improving maintainability. #### Summarize this post with: ChatGPT Perplexity Grok Google AI **Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** ##### Sign up for the newsletter Subscribe for Free You have successfully subscribed. Please check your inbox. ![AI-Newsletter]() Chosen by over **350,000+** professionals [Scroll to top ](#wrapper)Scroll to top × Toggle Menu Close Search for: Search ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/oracle-plsql-object-types-create-type.png","url":"https://www.guru99.com/images/oracle-plsql-object-types-create-type.png","width":"700","height":"250","caption":"Oracle PL/SQL Object Types (CREATE TYPE)","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/object-types-pl-sql.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/pl-sql","name":"PL-SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/object-types-pl-sql.html","name":"Oracle PL/SQL Object Types: CREATE TYPE with Examples"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/object-types-pl-sql.html#webpage","url":"https://www.guru99.com/object-types-pl-sql.html","name":"Oracle PL/SQL Object Types: CREATE TYPE with Examples","dateModified":"2026-07-22T18:31:53+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/oracle-plsql-object-types-create-type.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/object-types-pl-sql.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown","description":"I'm Fiona brown, a Full Stack Developer with over a decade of experience, sharing practical guides on robust and scalable application development.","url":"https://www.guru99.com/author/fiona","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/fiona-brown-author.png","url":"https://www.guru99.com/images/fiona-brown-author.png","caption":"Fiona Brown","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"PL-SQL","headline":"Oracle PL/SQL Object Types: CREATE TYPE with Examples","description":"Object-Oriented Programming is especially suited for building reusable components and complex applications. They are organized around "objects" rather than "actions" i.e. the programs are designed to","keywords":"pl-sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown"},"dateModified":"2026-07-22T18:31:53+05:30","image":{"@id":"https://www.guru99.com/images/oracle-plsql-object-types-create-type.png"},"copyrightYear":"2026","name":"Oracle PL/SQL Object Types: CREATE TYPE with Examples","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between an object type and a collection in PL/SQL?","acceptedAnswer":{"@type":"Answer","text":"An object type models a single real-world entity with attributes and methods. A collection \u2014 a VARRAY, nested table, or associative array \u2014 holds many elements of one datatype. Object types describe structure and behaviour, while collections store multiple values."}},{"@type":"Question","name":"What is the difference between a MAP method and an ORDER method?","acceptedAnswer":{"@type":"Answer","text":"Both compare object instances. A MAP method returns a single scalar value that Oracle uses to sort or compare objects, while an ORDER method compares two instances directly and returns a negative, zero, or positive number. A type may define one or the other, not both."}},{"@type":"Question","name":"Can object types be stored in a database table?","acceptedAnswer":{"@type":"Answer","text":"Yes. You can create an object table where each row is an object instance, or use an object type as the datatype of a table column. This lets Oracle persist structured objects directly in the database rather than only in memory."}},{"@type":"Question","name":"What does NOT INSTANTIABLE mean for an object type?","acceptedAnswer":{"@type":"Answer","text":"A NOT INSTANTIABLE type cannot create objects directly; it acts as an abstract base that other types extend. Combined with NOT FINAL, it defines a supertype whose subtypes provide the concrete implementation of its declared methods."}},{"@type":"Question","name":"How do you modify or drop an existing object type?","acceptedAnswer":{"@type":"Answer","text":"Use ALTER TYPE to add or drop attributes and methods, adding CASCADE to update dependent objects, and use DROP TYPE to remove the type. A type still used by tables or other types cannot be dropped until those dependents are handled first."}},{"@type":"Question","name":"How can you view the object types in your schema?","acceptedAnswer":{"@type":"Answer","text":"Query the data dictionary views USER_TYPES and ALL_TYPES for type names, and USER_TYPE_ATTRS and USER_TYPE_METHODS for their attributes and methods. These views let you audit an existing object model without reading the source code."}},{"@type":"Question","name":"Can GitHub Copilot generate PL/SQL object types?","acceptedAnswer":{"@type":"Answer","text":"Yes. GitHub Copilot drafts CREATE TYPE specifications, TYPE BODY methods, constructors, and ORDER functions from a comment. Review the attribute datatypes, constructor parameters, and inheritance clauses before deploying the generated type."}},{"@type":"Question","name":"How does AI help design a PL/SQL object model?","acceptedAnswer":{"@type":"Answer","text":"AI assistants analyze your tables and suggest object types, attribute mappings, and inheritance hierarchies. This machine-learning review flags redundant attributes, missing constructors, and over-deep inheritance before the object model reaches production, improving maintainability."}}]}],"@id":"https://www.guru99.com/object-types-pl-sql.html#schema-1149330","isPartOf":{"@id":"https://www.guru99.com/object-types-pl-sql.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/object-types-pl-sql.html#webpage"}}]} ```