A lot of developers see Object Types as confusing, esoteric, or simply unrelated to their SQL work. To be fair, some objects can be quite complex and some of the data cartridge (ODCI) features will never find a home in some developer’s applications. But, that still shouldn’t preclude an examination of some of the fundamentals of this extremely useful feature.
The most basic feature of an object is that it can be used to consolidate multiple values into attributes of a single entity. Similar to a record declared in PL/SQL an object type allows you to have a reference to a single value that acts as a container for other values.
Why might that be useful? After all, a SQL statement can already reference multiple values (columns) from a table, or invoke multiple functions calls, or return values from sub-queries. So each row returned by a SQL statement can already be viewed as a consolidated set of values. What does an object type have to offer beyond what we already have?
First – lets look at functions. Lets say I have list of sales to various customers and I want to call a function that returns detail information about those sales.
I might have a function that returns product name, another function returns weight, and yet another returning price. So, if I want to gather all of that information I have to call all 3 functions. Behind the scenes, each of those functions will likely execute a SQL statement to fetch a row from the product table and extract the relevant column.
Obviously that’s not very efficient. Better would be to have a single function call that could return all 3 values. But, we all know a function can only return one value, so we’re kind of stuck! There are, of course, other options, such as caching values within PL/SQL records or collections thereby reducing the IO penalties, but still necessitating multiple function calls and possibly context switches. While still viable for some problems; those options are outside the scope of this article.
So, what would the Object Type solution look like?
First, let’s create an object to hold our 3 product values.
CREATE OR REPLACE TYPE product_info
AS OBJECT(
product_name VARCHAR2(50),
product_weight NUMBER,
product_price NUMBER
);
Next, create a function to return a PRODUCT_INFO value (i.e. an “instance” of the class.)
CREATE OR REPLACE FUNCTION get_product_info(p_product_id IN products.id%TYPE)
RETURN product_info
IS
v_info product_info;
BEGIN
SELECT product_info(name, weight, price)
INTO v_info
FROM products
WHERE id = p_product_id;
RETURN v_info;
END;
Now we can use our new function and type within a SQL statement.
SELECT order_date, product_id, quantity, get_product_info(product_id) product
FROM sales
WHERE customer_id = 1234 AND order_date = DATE '2017-01-11';
ORDER_DATE PRODUCT_ID QUANTITY PRODUCT(PRODUCT_NAME, PRODUCT_WEIGHT, PRODUCT_PRICE)
------------------- ---------- -------- -----------------------------------------------------
2017-01-11 00:00:00 50 1 PRODUCT_INFO('Bananas', 1, .86)
2017-01-11 00:00:00 35 3 PRODUCT_INFO('Apples-Gala', .33, .4)
Depending on the tool used, the object type may be represented in different forms. OBJECT_NAME(FIELD_1, FIELD_2, FIELD_3, etc.) is how SQL*Plus displays objects.
The most important at this stage though is confirming our function works. We got the expected results. In particular, the three values we need are returned as a single column containing our object.
Next though, because we’re using SQL, it’s likely we’ll want the results returned in rows and columns of simple, scalar values. So, let’s extract the individual attributes of our object out into their respective columns for final output. We’ll do that with an inline view of the previous query and then pull the attributes out by name. Note, when dereferencing an object to get to the attributes, you must wrap the object name in parentheses to create an object expression.
SQL> SELECT order_date, 2 product_id, 3 quantity, 4 (product).product_name product_name, 5 (product).product_weight weight, 6 (product).product_price price 7 FROM (SELECT order_date, 8 product_id, 9 quantity, 10 get_product_info(product_id) product 11 FROM sales 12 WHERE customer_id = 1234 AND order_date = DATE '2017-01-11'); ORDER_DATE PRODUCT_ID QUANTITY PRODUCT_NAME WEIGHT PRICE ------------------- ---------- ---------- ------------- ------- ------ 2017-01-11 00:00:00 50 1 Bananas 1 .55 2017-01-11 00:00:00 35 3 Apples-Gala .33 .4
You can also use objects within a scalar subquery in order to consolidate multiple values from a lookup table into a single object as the subquery’s result. When doing so you should test if a simple join to pull the related values might be more efficient or equivalent but simpler.
For example, if the function above was not available and I had direct access to the underlying product table then I could write the query like this:
SELECT order_date,
product_id,
quantity,
(product).product_name product_name,
(product).product_weight weight,
(product).product_price price
FROM (SELECT order_date,
product_id,
quantity,
(SELECT product_info(name, weight, price)
FROM products
WHERE products.id = sales.product_id)
product
FROM sales
WHERE customer_id = 2576 AND product_id = 35);
Elapsed: 00:00:00.03
Statistics
---------------------------------------------------------
0 recursive calls
0 db block gets
4464 consistent gets
0 physical reads
0 redo size
972 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
or, with a simple join as shown below (in this case a referential constraint makes an inner join equivalent to the functionality above, but if not then an outer join to products could be used.)
SELECT s.order_date,
s.product_id,
s.quantity,
p.name,
p.weight,
p.price
FROM sales s, products p
WHERE s.customer_id = 2576
AND s.product_id = 35
AND p.id = s.product_id;
Elapsed: 00:00:00.03
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4464 consistent gets
0 physical reads
0 redo size
964 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
In this case the join is simpler syntax for equivalent performance and resource consumption. Also consider, this was a simple example. Some data may not be available through a table and may require a function. Either because of api design and user privileges or because the data comes from a non-sql source, such as as a web service, remote file, or other pl/sql routines.
When building such functions it is important to consider use cases that may require multiple pieces of output. When those are found, it might be appropriate to return an XML or JSON document; but it may be easier and more efficient to return an object type.