CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
a.naee.ja.e
a.naee.ja.e

Reputation: 1

how to get the output of stored procedure with in and output parameters pl/sql

i have created the stored procedure ...in which getting employee_name ,salary and his manager_name .. procedure is created successfully but when i am executing giving an error of invalid parameter.. how do i execute this procedure ...

create or replace procedure  employee_manager (
  dept_id in number,
  fname out varchar2,
  v_sal out number,
  fname1 out varchar2
)
is
begin
  select a.first_name manager_name,
         b.first_name employee_name,
         b.salary
  into fname,
       fname1,
       v_sal
  from employees a join employees b on a.employee_id = b.manager_id
  where a.department_id = dept_id;
end employee_manager ;

Upvotes: 0

Views: 548

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

If would have helped if you showed us what exactly happened when you called the procedure, or - at least - posted ORA-xxxxx error code. With the info we have so far, we can only guess.

My guess is that you wrongly called it. As it accepts one IN parameter and three OUT ones, you'll have to declare three variables; something like this:

declare
  l_fname   employees.first_name%type;
  l_sal     employees.salary%type;
  l_fname1  employees.first_name%type;
begin
  employee_manager(dept_id     => 10,
                   fname       => l_fname,
                   v_sal       => l_sal,
                   fname1      => l_fname1);

  dbms_output.put_line('Values returned are: ' || l_fname ||', '|| l_sal ||', '|| l_fname1);
end;
/

Upvotes: 1

Related Questions