Returning multiple values from PL/SQL function

Question

I want to retrieve multiple values from function, Here is my code:

CREATE OR REPLACE TYPE user_data_type AS OBJECT(val1 NUMBER(15), val2 NUMBER(15))


CREATE OR REPLACE FUNCTION GET_EMPLOYEE_AND_USER(in_login IN fnd_user.user_name%TYPE)
RETURN user_data_type AS
out_var user_data_type;

CURSOR buffer_cur IS
SELECT f.user_id, f.employee_id FROM Fnd_User f WHERE f.user_name=in_login;
BEGIN 
OPEN buffer_cur;
FETCH buffer_cur INTO out_var;

CLOSE buffer_cur;
RETURN out_var;
END GET_EMPLOYEE_AND_USER;

When i execute:

select * from table(get_employee_and_user('user1'))

It says: Package or function get_employee_and_user is in invalid state
I am new to ORACLE so i probably dont understand basic things but when i return one value it works fine. Unfortunaltely, when i try return rowtype or record it show that error i mentioned above.

Przemyslaw Kruglej · Accepted Answer · 2013-11-05 11:25:02Z

3

You have to select those values into object type's fields, take a look:

CREATE OR REPLACE TYPE user_data_type AS OBJECT(val1 NUMBER(15), val2 NUMBER(15));
/

CREATE OR REPLACE FUNCTION GET_EMPLOYEE_AND_USER(in_login IN fnd_user.user_name%TYPE)
  RETURN user_data_type AS

  out_var user_data_type;

  CURSOR buffer_cur IS
    SELECT f.user_id, f.employee_id FROM Fnd_User f WHERE f.user_name = in_login;

BEGIN 
  OPEN buffer_cur;
  FETCH buffer_cur INTO out_var.val1, out_var.val2;

  CLOSE buffer_cur;
  RETURN out_var;
END GET_EMPLOYEE_AND_USER;

answered Nov 5, 2013 at 11:25

Przemyslaw Kruglej

8,1412 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Returning multiple values from PL/SQL 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