Returning Multiple Tables from an Oracle Function Using Object Types

Using Object Types in Oracle to Return Multiple Tables from a Function

Introduction

In this article, we will explore how to use object types in Oracle to return multiple tables from a function. We will also dive into the details of creating and using an object type in a stored procedure.

Background

Oracle has been supporting the concept of object types for over two decades now. These types can contain values of different data types, including other objects such as tables and records. One of the key features of Oracle’s object types is their ability to return multiple tables from a function or stored procedure.

Creating an Object Type

To create an object type that returns multiple tables, we first need to define the structure of our object type. In this case, let’s create an object type called test_type that contains two fields: conid and cov_lineid. These fields will be of type sys.odcinumberlist, which is a table type in Oracle.

CREATE TYPE test_type AS OBJECT (
  conid      sys.odcinumberlist,
  cov_lineid sys.odcinumberlist
)
/

Using the Object Type in a Function

Now that we have created our object type, we can use it in a function to return multiple tables. In this case, let’s create a function called function_name that takes an id parameter and returns a value of type test_type.

CREATE OR REPLACE FUNCTION function_name (id NUMBER) RETURN test_type
AS
  res test_type;
BEGIN
  SELECT /*+ parallel(64)*/ con_id,cov_lineid
    BULK COLLECT INTO res.conid, res.cov_lineid
    FROM table_name where sava_id  like '%'||id||'%';

  RETURN res;
END;
/

Understanding the Code

In this code snippet, we first declare a variable res of type test_type. We then use a SQL query to select data from the table_name and bulk collect it into our res.conid and res.cov_lineid fields. Finally, we return the value of res.

Using Object Types in Oracle

One of the key benefits of using object types in Oracle is their ability to simplify complex database operations. By returning multiple tables from a function or stored procedure, you can reduce the amount of code that needs to be written and make your database operations more efficient.

For example, let’s say we want to return both the conid and cov_lineid fields for each row in our table_name. We could use an object type like this:

CREATE TYPE test_type AS OBJECT (
  conid NUMBER,
  cov_lineid NUMBER
)

CREATE OR REPLACE FUNCTION function_name (id NUMBER) RETURN test_type
AS
  res test_type;
BEGIN
  SELECT /*+ parallel(64)*/ con_id,cov_lineid
    BULK COLLECT INTO res.conid, res.cov_lineid
    FROM table_name where sava_id  like '%'||id||'%';

  RETURN res;
END;
/

In this example, we declare an object type called test_type that contains two fields: conid and cov_lineid. We then use a SQL query to select data from the table_name and bulk collect it into our res.conid and res.cov_lineid fields. Finally, we return the value of res.

Handling Null Values

One of the key considerations when using object types in Oracle is how you will handle null values. In this case, let’s say we want to use an object type that contains a null value for either the conid or cov_lineid field.

CREATE TYPE test_type AS OBJECT (
  conid NUMBER,
  cov_lineid NUMBER DEFAULT NULL
)

CREATE OR REPLACE FUNCTION function_name (id NUMBER) RETURN test_type
AS
  res test_type;
BEGIN
  SELECT /*+ parallel(64)*/ con_id,cov_lineid
    BULK COLLECT INTO res.conid, res.cov_lineid
    FROM table_name where sava_id  like '%'||id||'%';

  IF res.conid IS NULL THEN
    res.conid := 0;
  END IF;

  RETURN res;
END;
/

In this example, we declare an object type called test_type that contains a null value for the cov_lineid field. We then use a SQL query to select data from the table_name and bulk collect it into our res.conid and res.cov_lineid fields. If the conid is null, we set it to 0.

Handling Multiple Null Values

Let’s say we want to use an object type that contains multiple null values for either the conid or cov_lineid field. In this case, let’s create an object type called test_type that contains two fields: conid and cov_lineid. We then use a SQL query to select data from the table_name and bulk collect it into our res.conid and res.cov_lineid fields.

CREATE TYPE test_type AS OBJECT (
  conid sys.odcinumberlist,
  cov_lineid sys.odcinumberlist DEFAULT NULL
)

CREATE OR REPLACE FUNCTION function_name (id NUMBER) RETURN test_type
AS
  res test_type;
BEGIN
  SELECT /*+ parallel(64)*/ con_id,cov_lineid
    BULK COLLECT INTO res.conid, res.cov_lineid
    FROM table_name where sava_id  like '%'||id||'%';

  IF res.conid IS NULL THEN
    res.conid := sys.odcinumberlist;
  END IF;

  RETURN res;
END;
/

In this example, we declare an object type called test_type that contains two fields: conid and cov_lineid. We then use a SQL query to select data from the table_name and bulk collect it into our res.conid and res.cov_lineid fields. If the conid is null, we set it to an empty object type called sys.odcinumberlist.

Conclusion

Using object types in Oracle can simplify complex database operations and make your code more efficient. By returning multiple tables from a function or stored procedure, you can reduce the amount of code that needs to be written and make your database operations more efficient.

In this article, we explored how to use object types in Oracle to return multiple tables from a function. We also discussed handling null values and multiple null values when using object types.


Last modified on 2024-02-05