How to Return Two Types In Pl/Sql Oracle?

8 minutes read

In PL/SQL Oracle, it is not possible to return multiple values of different types from a function or procedure. However, you can achieve this by using a user-defined datatype, a record type, or by returning a collection (such as a nested table or a varray) that contains multiple values of different types.


For example, you can create a record type that contains fields for each of the values you want to return, and then return an instance of this record type from your function or procedure. Alternatively, you can use a nested table or varray to store the multiple values of different types and return it from your function or procedure.


Overall, returning multiple values of different types in PL/SQL Oracle requires some creativity and the use of complex data structures.

Best Oracle Books to Read in December 2024

1
Pro Oracle Database 23ai Administration: Manage and Safeguard Your Organization’s Data

Rating is 5 out of 5

Pro Oracle Database 23ai Administration: Manage and Safeguard Your Organization’s Data

2
Expert Oracle Database Architecture: Techniques and Solutions for High Performance and Productivity

Rating is 4.9 out of 5

Expert Oracle Database Architecture: Techniques and Solutions for High Performance and Productivity

3
Pro Oracle Database 23c Administration: Manage and Safeguard Your Organization’s Data

Rating is 4.8 out of 5

Pro Oracle Database 23c Administration: Manage and Safeguard Your Organization’s Data

4
Oracle PL/SQL by Example (The Oracle Press Database and Data Science)

Rating is 4.7 out of 5

Oracle PL/SQL by Example (The Oracle Press Database and Data Science)

5
Oracle Essentials: Oracle Database 12c

Rating is 4.6 out of 5

Oracle Essentials: Oracle Database 12c

6
OCA Oracle Database SQL Exam Guide (Exam 1Z0-071)

Rating is 4.5 out of 5

OCA Oracle Database SQL Exam Guide (Exam 1Z0-071)

7
Oracle PL/SQL Programming: Covers Versions Through Oracle Database 12c

Rating is 4.4 out of 5

Oracle PL/SQL Programming: Covers Versions Through Oracle Database 12c

8
Oracle Database 12c SQL

Rating is 4.3 out of 5

Oracle Database 12c SQL


How to return two types in PL/SQL Oracle using a procedure?

In PL/SQL Oracle, you can return multiple types using a record type or by creating a custom type to hold the values you want to return. Here is an example using a procedure:

  1. Create a custom type to hold the values you want to return:
1
2
3
4
CREATE OR REPLACE TYPE return_values AS OBJECT (
    result1 VARCHAR2(50),
    result2 NUMBER
);


  1. Create a procedure that uses the custom type to return the values:
1
2
3
4
5
CREATE OR REPLACE PROCEDURE get_values (p_input VARCHAR2, p_output OUT return_values) AS
BEGIN
    -- Perform some operations to get the values
    p_output := return_values('Value1', 100);
END;


  1. Call the procedure and retrieve the values:
1
2
3
4
5
6
7
8
9
DECLARE
    v_result return_values;
BEGIN
    get_values('input_value', v_result);
    
    -- Output the values
    dbms_output.put_line('Result 1: ' || v_result.result1);
    dbms_output.put_line('Result 2: ' || v_result.result2);
END;


In this example, the get_values procedure accepts an input parameter and returns two values using the custom type return_values. The procedure sets the values of result1 and result2 before returning them. In the calling block, you can access and display the returned values.


How to return a Boolean value in PL/SQL Oracle?

In PL/SQL Oracle, you can return a Boolean value by defining a function that returns a Boolean data type. Here is an example of how to create a function that returns a Boolean value:

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE FUNCTION is_even(num IN NUMBER) RETURN BOOLEAN IS
BEGIN
    IF MOD(num, 2) = 0 THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;
/


In this example, the function is_even takes a numeric input num and checks if it is even by using the MOD function to calculate the remainder when dividing num by 2. If the remainder is 0, the function returns TRUE, indicating that the number is even. Otherwise, it returns FALSE.


You can call this function in a PL/SQL block or SQL query to get the Boolean result. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
DECLARE
    result BOOLEAN;
BEGIN
    result := is_even(10);
    IF result THEN
        DBMS_OUTPUT.PUT_LINE('The number is even');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The number is odd');
    END IF;
END;
/


In this code snippet, the is_even function is called with the argument 10, and the result is stored in the result variable. Depending on the value returned by the function, it will print out whether the number is even or odd.


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

In PL/SQL Oracle, you can return a JSON object using the JSON_OBJECT function. Here is an example:

1
2
3
4
5
6
7
DECLARE
   json_obj JSON_OBJECT_T;
BEGIN
   json_obj := JSON_OBJECT_T('{"name" : "John", "age" : 30}');
   DBMS_OUTPUT.PUT_LINE(json_obj.TO_STRING);
END;
/


In this example, we are creating a JSON object with the properties "name" and "age" and then using the TO_STRING method to output the JSON object as a string. You can customize the properties and values of the JSON object based on your requirements.

Facebook Twitter LinkedIn Telegram Whatsapp Pocket

Related Posts:

To generate an XML file from an Oracle SQL query, you can follow these steps:Connect to your Oracle database using a tool like SQL Developer or SQL*Plus. Write your SQL query that retrieves the data you want to include in the XML file. For example, consider a ...
To run Oracle PL/SQL in Python, you can use the cx_Oracle module, which allows Python programs to connect to Oracle databases. With cx_Oracle, you can execute SQL and PL/SQL statements, fetch data from Oracle databases, and interact with Oracle stored procedur...
In Kotlin, you can return multiple values from a function by using either a data class or a pair.One way to return multiple values is to create a data class that contains all the values that you want to return. You can then create an instance of this data clas...
To upload an SQL database to hosting, you need to first ensure that your hosting provider supports SQL databases. Once you have confirmed that, you can access your hosting account's control panel or use an FTP client to upload the SQL database file to your...
To run a stored procedure in Oracle, you first need to ensure that the stored procedure has been created and is stored within the Oracle database. Once the stored procedure is created, you can run it by using the following steps:Connect to the Oracle database ...
To convert minutes to days in Oracle, you can use the following SQL query:SELECT minutes/1440 AS days FROM table_name;This query will return the equivalent number of days when you provide minutes as an input. By dividing the minutes by 1440 (the number of minu...