Return multiple values from a oracle function

Question

Here is my object type

Create or replace type demo_obj is object (
Flag number,
Message varchar2(200))

Table of objects

Create or replace type obj_table is table of demo_obj

Next in function I write a cursor first fetches some id's Next i need to open cursor and check each I'd status with other table contains success or error status Of that I'd

If the cursor is empty i need to return flag as 0 and message null

If all the I'd"s are success o need to return flag 1 and message is concatenated string of all the IDs separated by commas

If few Ids are success and few are error i need to return flag 2 and message as the concatenated IDs which are error

Littlefoot · Accepted Answer · 2022-03-15 06:43:03Z

This is how you'd do it. I didn't bother with logic about empty cursor and errors - you'll do it yourself. This demo shows how to return appropriate type from the function (which is, I believe, what actually bothers you).

Types first:

SQL> CREATE OR REPLACE TYPE demo_obj IS OBJECT
  2  (
  3     Flag NUMBER,
  4     MESSAGE VARCHAR2 (200)
  5  );
  6  /

Type created.

SQL> CREATE OR REPLACE TYPE obj_table IS TABLE OF demo_obj;
  2  /

Type created.

Function:

SQL> CREATE OR REPLACE FUNCTION f_test
  2     RETURN obj_table
  3  IS
  4     l_flag  NUMBER;
  5     l_msg   VARCHAR2 (200);
  6     retval  obj_table := obj_table ();
  7  BEGIN
  8     l_flag := 1;
  9
 10     SELECT LISTAGG (deptno, ',') WITHIN GROUP (ORDER BY deptno)
 11       INTO l_msg
 12       FROM dept;
 13
 14     retval.EXTEND;
 15     retval (1) := demo_obj (l_flag, l_msg);
 16
 17     RETURN retval;
 18  END;
 19  /

Function created.

Testing:

SQL> SELECT * FROM TABLE (f_test);

      FLAG MESSAGE
---------- --------------------
         1 10,20,30,40

SQL>

Collectives™ on Stack Overflow

Return multiple values from a oracle function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related