Database Interaction with PL/SQL: OBJECT and OBJECT (Page 1 of 4 )
Jagadish Chatarji has been writing about database interactions with Oracle PL/SQL. The last part examined using TABLE, RECORD and NESTED TABLES with PL/SQL. This one now introduces OBJECT TYPE in Oracle, and explains both SQL and PL/SQL ways of working with OBJECTs. This article is the fourth in the series.
Please note that all the examples in this series have been tested only with Oracle 10g, not with all the previous versions of Oracle. I suggest you to refer the documentation of respective version you are using if any of the programs failed to execute.
Introduction to OBJECT TYPE:
It is worthwhile to introduce the concept of OBJECT here, as we can also work with OBJECTs in PL/SQL pleasantly. I will not go much into the depth of OOPS with MEMBER methods etc at this moment. My up-coming articles will look into the depth of OOPS in Oracle 10g. For now we will just concentrate on minimum basics of OBJECT together with PL/SQL.
For the time being, just consider OBJECT type as similar to RECORD type in PL/SQL (RECORD was explained in part-2 and part-3 of my articles). RECORD type works only in PL/SQL. But OBJECT type gets stored in database and can be used in both SQL and PL/SQL (without redefining it in PL/SQL).
Let us consider the following example.
CREATE TYPE t_experience AS OBJECT
(
Ename varchar2(20),
CompanyName varchar2(20),
Position varchar2(20),
NoOfYears number(2)
);
/
The above script just creates only an OBJECT TYPE (not a table). Remember it is TYPE (which means something like a datatype). The OBJECT TYPE can be used to create a table based on its definition.
CREATE TABLE Employees OF t_experience;
The above statement creates a new table named ‘Employees’ with exactly the same structure of ‘t_experience’. The following statement inserts a row based on the OBJECT TYPE structure.
insert into employees values
('jag','xyz company','software engineer',5);
I don’t think you would find any difference between above statement and ordinary INSERT, as they work the same way. Even though the above INSERT is valid, for better readability, it is always suggested to issue the above statement as follows:
insert into employees values (t_experience('jag','xyz
company','software engineer',5));
The only difference is that we are enclosing all the values into the specification of OBJECT TYPE ‘t_experience’. All the other DML commands (INSERT, UPDATE, DELETE and SELECT) can be issued just like ordinary SQL statements without any difference.
Next: Accessing OBJECT TYPE using PL/SQL >>
More Oracle Articles
More By Jagadish Chatarji
/ 20 
