Working with Oracle Object Types part 1

I have been working with Oracle db for the past few days and found out that I can store objects in a table. Basically I can create a table with only one column of type ‘TYPES’ but I wouldn’t go to that extent :).

Here is an example on creating a type

CREATE OR REPLACE
TYPE MYSCHEMA.USER_TYPE AS OBJECT
(
	ID INTEGER,
        FIRSTNAME VARCHAR2(20),
        SURNAME VARCHAR2(20)
);

We can also create a collection of Types based on existing Objects

CREATE OR REPLACE TYPE MYSCHEMA.USER_TAB  AS TABLE OF  USER_TYPE;

It’s getting more interesting when I know that I can pass the object or the collection of objects to a procedure just like a normal parameter. this means that I don’t have to do a loop in .Net side when dealing with a collection of objects.

FUNCTION GetUsers(
    userId IN User.ID%TYPE
) RETURN USER_TAB
AS
  returnTab   USER_TAB;
BEGIN
    SELECT   USER_TYPE(
                ID,
                FIRSTNAME,
                SURNAME)
        BULK COLLECT
        INTO returnTab
        FROM
            USER
        WHERE
            ID = userId;

    RETURN returnTab;
END;