Account
Sign in to you account
Sign In Sign Up

How to Return Two Types In Pl/Sql Oracle?

4 minutes read

In PL/SQL Oracle, you can return two types by using a record type or an object type.


For example, you can create a custom record type that contains fields of different data types you want to return, and then use that record type as the return type of your function or procedure.


Alternatively, you can create an object type that represents the composite data structure you want to return, define methods for that type, and then use it as the return type of your function or procedure.


Both approaches allow you to return multiple values of different types from your PL/SQL code.


How to return a JSON object and a varchar2 in PL/SQL Oracle?

In PL/SQL Oracle, you can return a JSON object and a VARCHAR2 by wrapping them in a nested table type and returning that type from a function. Here's an example of how you can accomplish this:

  1. Create a type that represents your JSON object and VARCHAR2:
1
2
3
4

CREATE OR REPLACE TYPE json_vchar_type AS OBJECT (
   json_data JSON_OBJECT_T,
   vchar_data VARCHAR2(100)
);


  1. Create a function that returns the JSON object and VARCHAR2:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE OR REPLACE FUNCTION get_json_and_vchar_data RETURN json_vchar_type IS
   json_obj JSON_OBJECT_T := JSON_OBJECT_T();
   vchar_data VARCHAR2(100) := 'Hello, JSON!';
   result json_vchar_type;
BEGIN
   -- Add some key-value pairs to the JSON object
   json_obj.PUT('key1', 'value1');
   json_obj.PUT('key2', 'value2');

   -- Assign the JSON object and VARCHAR2 to the result variable
   result := json_vchar_type(json_obj, vchar_data);

   RETURN result;
END;


  1. Call the function and retrieve the JSON object and VARCHAR2:
1
2
3
4
5
6
7
8

DECLARE
   json_vchar_data json_vchar_type;
BEGIN
   json_vchar_data := get_json_and_vchar_data();
   
   DBMS_OUTPUT.PUT_LINE('JSON Data: ' || json_vchar_data.json_data.TO_STRING);
   DBMS_OUTPUT.PUT_LINE('Varchar Data: ' || json_vchar_data.vchar_data);
END;


This code will return a JSON object with key-value pairs 'key1' and 'key2', and a VARCHAR2 with the value 'Hello, JSON!'. You can customize the JSON object and VARCHAR2 data as needed in your function.


What is the syntax for returning multiple types in PL/SQL Oracle?

In PL/SQL Oracle, you can return multiple types using a PL/SQL record or object type.


Here is an example using a PL/SQL record type:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE OR REPLACE TYPE employee_record AS OBJECT (
    employee_id NUMBER,
    employee_name VARCHAR2(50),
    employee_salary NUMBER
);
/
CREATE OR REPLACE FUNCTION get_employee_info RETURN employee_record IS
    emp_info employee_record;
BEGIN
    SELECT employee_id, employee_name, employee_salary
    INTO emp_info.employee_id, emp_info.employee_name, emp_info.employee_salary
    FROM employees
    WHERE employee_id = 100;
    
    RETURN emp_info;
END;
/


You can also use a PL/SQL table type to return multiple values:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE OR REPLACE TYPE employee_tab AS TABLE OF employee_record;
/
CREATE OR REPLACE FUNCTION get_all_employees RETURN employee_tab IS
    emp_list employee_tab := employee_tab();
BEGIN
    FOR emp_record IN (SELECT employee_id, employee_name, employee_salary FROM employees) 
    LOOP
        emp_list.extend;
        emp_list(emp_list.count) := employee_record(emp_record.employee_id, emp_record.employee_name, emp_record.employee_salary);
    END LOOP;
    
    RETURN emp_list;
END;
/


You can then call these functions to retrieve the returned values:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

DECLARE
    emp_info employee_record;
    emp_list employee_tab;
BEGIN
    emp_info := get_employee_info();
    
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_info.employee_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_info.employee_name);
    DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_info.employee_salary);
    
    emp_list := get_all_employees();
    
    FOR i IN 1..emp_list.count 
    LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_list(i).employee_id || ', Employee Name: ' || emp_list(i).employee_name || ', Employee Salary: ' || emp_list(i).employee_salary);
    END LOOP;
END;
/


This is how you can return multiple types in PL/SQL Oracle.


How to return a boolean and a record in PL/SQL Oracle?

To return a boolean and a record in PL/SQL Oracle, you can define a custom record type that includes both the boolean and the desired record fields. Here's an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE OR REPLACE TYPE ReturnData AS OBJECT (
  success BOOLEAN,
  data YOUR_RECORD_TYPE
);
/
CREATE OR REPLACE FUNCTION MyFunction RETURN ReturnData IS
  result ReturnData;
  rec YOUR_RECORD_TYPE;
BEGIN
  -- Your logic to populate the record 'rec' and set the boolean 'success'
  
  result := ReturnData(TRUE, rec);
  RETURN result;
END;
/


In this example, ReturnData is a custom object type that contains a boolean success and a record of type YOUR_RECORD_TYPE. The function MyFunction returns an instance of ReturnData where you can set the boolean success and populate the record rec before returning it.


How to return a UDT and a nested table in PL/SQL Oracle?

To return a UDT and a nested table in PL/SQL Oracle, you can define a function that returns a UDT with a nested table as one of its attributes. Here is an example:

  1. First, define the UDT and the nested table type:
1
2
3
4
5
6

CREATE OR REPLACE TYPE Employee AS OBJECT (
    emp_id NUMBER,
    emp_name VARCHAR2(50)
);

CREATE OR REPLACE TYPE EmployeeList AS TABLE OF Employee;


  1. Next, create a function that returns the UDT with the nested table as one of its attributes:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE OR REPLACE FUNCTION getEmployees RETURN EmployeeList IS
    employees EmployeeList := EmployeeList();
BEGIN
    employees.EXTEND(2);
    
    employees(1) := Employee(1, 'John Doe');
    employees(2) := Employee(2, 'Jane Smith');
    
    RETURN employees;
END;


  1. You can then call the function to return the UDT and the nested table:
1
2
3
4
5
6
7
8
9

DECLARE
    employees EmployeeList;
BEGIN
    employees := getEmployees();
    
    FOR i IN 1..employees.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employees(i).emp_id || ' - Employee Name: ' || employees(i).emp_name);
    END LOOP;
END;


This code will return a UDT (Employee) with a nested table (EmployeeList) containing two employee records. You can then iterate over the nested table to access the individual elements and display them as needed.

Facebook Twitter LinkedIn Telegram Whatsapp

Related Posts:

How to Run Oracle Pl/Sql In Python?
To run Oracle PL/SQL in Python, you can use the cx_Oracle module which provides an interface for Python to interact with Oracle databases. First, you need to install the cx_Oracle module using pip. Then, establish a connection to your Oracle database using the...
How to Increase Date By 1 Month In Oracle Sql?
To increase a date by 1 month in Oracle SQL, you can use the ADD_MONTHS function. This function takes two arguments: a date value and the number of months you want to add to that date.For example, if you have a date value '2021-06-15' and you want to i...
How to Parse Json Data In Oracle?
To parse JSON data in Oracle, you can use the JSON functions provided in Oracle Database. These functions allow you to extract data from JSON documents and work with JSON data in SQL queries.Some commonly used JSON functions in Oracle include JSON_VALUE, JSON_...
How to Check Previous Value After Update In Oracle?
After updating a record in Oracle, you can check the previous value using Oracle Flashback Query. This feature allows you to query the previous value of a row before it was updated. You can use the AS OF TIMESTAMP or AS OF SCN clause in your SQL query to retri...
How to Parse Json In Oracle?
To parse JSON in Oracle, you can use the JSON functions and operators provided by Oracle Database. These functions allow you to extract data from JSON structures, navigate through the JSON hierarchy, and manipulate JSON objects and arrays. Some of the key func...