Step By Step Example For Object Type In Oracle

Create Object Type

-----------------------------------------------------------------------------------

SQL> CREATE TYPE ObjectPersonType AS OBJECT (

  2    ID       NUMBER,

  3    FNAME    VARCHAR2(20),

  4    LNAME    VARCHAR2(25),

  5    PHONE    VARCHAR2(20),

  6    MAP MEMBER FUNCTION get_idno RETURN NUMBER,

  7    MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY ObjectPersonType ));

  8  /

Type created.

-----------------------------------------------------------------------------------

here,  object variables are,

ID       NUMBER,

FNAME    VARCHAR2(20),

LNAME    VARCHAR2(25),

PHONE    VARCHAR2(20)

and

Object Memeber Functions,Procedures are

MAP MEMBER FUNCTION get_idno RETURN NUMBER

-- Return Self ID Number,

MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY ObjectPersonType )

-- In & Out Is Own Type Parameter

-----------------------------------------------------------------------------------

Create/ Replace Type Body

SQL> CREATE OR REPLACE TYPE BODY ObjectPersonType AS

  2    MAP MEMBER FUNCTION get_idno RETURN NUMBER IS

  3    BEGIN

  4      RETURN ID;

  5    END;

  6    MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY ObjectPersonType ) IS

  7            BEGIN

  8      -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details

  9              DBMS_OUTPUT.PUT_LINE(TO_CHAR(ID) || ' ' || FNAME || ' ' || LNAME);

  10      DBMS_OUTPUT.PUT_LINE(PHONE);

 11    END;

 12  END;

 13/

Type body created.

-----------------------------------------------------------------------------------

Create New Table Using Object Type (ObjectPersonType)

Description:

Table Name Is PERSONLIST,

Fields are, RECORDDATE as Date & DETAILS As ObjectPersonType(Using Defined Object Type)

CREATE TABLE PERSONLIST (

  RECORDDATE   DATE,

  DETAILS              ObjectPersonType);

Table created.

-----------------------------------------------------------------------------------

Insert Values For PERSONLIST

INSERT INTO PERSONLIST VALUES (

                SYSDATE,  ObjectPersonType (25, 'Raaj', 'Malik','9876700001'));

1 row created.

INSERT INTO PERSONLIST VALUES (

                SYSDATE,  ObjectPersonType (21, 'Boss', 'Sivaji','9876700002'));

1 row created.

-----------------------------------------------------------------------------------

Select Statements For Object Type

SQL> SELECT * FROM PERSONLIST;

RECORDDAT

---------

DETAILS(ID, FNAME, LNAME, PHONE)

--------------------------------------------------------------------------------

13-JUL-10

OBJECTPERSONTYPE(25, 'Raaj', 'Malik', '9876700001')

13-JUL-10

OBJECTPERSONTYPE(21, 'Boss', 'Sivaji', '9876700002')

SQL> SELECT DETAILS FROM PERSONLIST;

DETAILS(ID, FNAME, LNAME, PHONE)

--------------------------------------------------------------------------------

OBJECTPERSONTYPE(25, 'Raaj', 'Malik', '9876700001')

OBJECTPERSONTYPE(21, 'Boss', 'Sivaji', '9876700002')

SQL> SELECT DETAILS FROM PERSONLIST WHERE RECORDDATE=( SELECT MAX(RECORDDATE) FROM PERSONLIST);

DETAILS(ID, FNAME, LNAME, PHONE)

--------------------------------------------------------------------------------

OBJECTPERSONTYPE(21, 'Boss', 'Sivaji', '9876700002')

SQL> SELECT c.DETAILS.get_idno() FROM PERSONLIST c;

C.DETAILS.GET_IDNO()

--------------------

                  25

                  21

SQL> SELECT Obj.DETAILS.FNAME FROM PERSONLIST Obj;

DETAILS.FNAME

--------------------

Raaj

Boss

SQL> SELECT DETAILS FROM PERSONLIST Obj WHERE Obj.DETAILS.FNAME='Raaj';

DETAILS(ID, FNAME, LNAME, PHONE)

--------------------------------------------------------------------------------

OBJECTPERSONTYPE(25, 'Raaj', 'Malik', '9876700001')

SQL> DECLARE

  Obj OBJECTPERSONTYPE;

BEGIN

  SELECT DETAILS INTO Obj FROM PERSONLIST WHERE RECORDDATE=( SELECT MAX(RECORDDATE) FROM PERSONLIST);

  Obj.display_details();

END;

/

21 Boss Sivaji

9876700002

PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------