CODEWITHSUNDEEP
oracle-databaseplsql
weikuan86
weikuan86

Reputation: 11

How can I return multiple values in record type

I'd like to know how can I return multiple values with my PL/SQL in record type.

Below is my example code :-

CREATE OR REPLACE FUNCTION "FN_TESTING"
(
  TESTING1 IN VARCHAR2
) RETURN TEST4_TEST as

 TEST2 TEST4_TEST%ROWTYPE; 

CURSOR TEST1 IS 
 SELECT '1','2' FROM DUAL;

BEGIN 
 OPEN TEST1;
  FETCH TEST1
    INTO TEST2;
  CLOSE TEST1;

 RETURN TEST2;
END FN_TESTING;

I do check my function, it shows me warning message that my TEST4_TEST must be declared.

Can I know what is the problem of this function? and how I do the declaration for this TEST4_TEST?

Upvotes: 1

Views: 10400

Answers (3)

user2001117
user2001117

Reputation: 3777

Try this also:

declare TYPE t_deptrec IS RECORD
(
   name dept.dname%type,
   location dept.loc%type
);

CURSOR c_emp is

select ename,deptno from emp;
r_dept t_deptrec;

function getDept(p_deptno dept.deptno%type) return t_deptrec is
r_dept t_deptrec;

begin
   select dname,loc into r_dept
   from dept where deptno = p_deptno;

   return r_dept;
end;

BEGIN
   for r_emp in c_emp
   loop

      r_dept := getDept(r_emp.deptno);
      dbms_output.put_line(r_emp.ename || ',' || r_dept.name || ',' || r_dept.location);

   end loop;
END;

Upvotes: 0

Ashu
Ashu

Reputation: 5

Think TEST4_TEST as a variable which is of TYPE Record. This variable is just like NUMBER, VARCHAR, DATE. Only difference being that these are already defined by Oracle but in case of Collections and Records we have to define our own. As per your example it seems that you want to return a record with 2 numbers values then you should define as follow

CREATE OR REPLACE PACKAGE TEST4_TEST1
AS 
TYPE TEST4_TEST Is record
(
COL1 INTEGER,
COL2 INTEGER
);
END;

CREATE OR REPLACE FUNCTION FN_TESTING (testing1 IN VARCHAR2)
   RETURN TEST4_TEST1.test4_test
AS
 test3 TEST4_TEST1.test4_test;


   CURSOR test2
   IS
      SELECT '1', '2' FROM DUAL;
A
BEGIN
   OPEN test2;
   FETCH test2 INTO test3;
   CLOSE test2;
   RETURN test3;
END fn_testing;

Upvotes: 0

user2001117
user2001117

Reputation: 3777

Yes we can return the record variable from PLSQL Function/Procedure. But first it must be declare.

create or replace function get_employee
(p_empl_no in employee.empl_no%type)
return employee%rowtype
as
l_cust_record employee%rowtype;
begin
select * into l_cust_record from employee
where empl_no = p_empl_no;
return(l_cust_record);
end;
/

Upvotes: 1

Related Questions