Forum Stats

  • 3,757,485 Users
  • 2,251,236 Discussions
  • 7,869,842 Comments

Discussions

Getting error 'wrong number or types of arguments in call to 'DISP_EMP_DETAILS' while calling a SP

Albert Chao
Albert Chao Member Posts: 60 Green Ribbon
CREATE OR REPLACE PROCEDURE disp_emp_details (
    iempno   IN   NUMBER,
    semail   OUT  VARCHAR2,
    ssalary  OUT  VARCHAR2
) IS
    lv_count NUMBER(10);
    invalid_emp EXCEPTION;
BEGIN
    SELECT
        employee_id
    INTO lv_count
    FROM
        hr.employees
    WHERE
        employee_id = iempno;

    IF lv_count IS NULL THEN
        RAISE invalid_emp;
    ELSE
        EXECUTE IMMEDIATE 'Select employee_id, email, salary from hr.employees';
    END IF;
EXCEPTION
    WHEN invalid_emp THEN
        dbms_output.put_line('Record Not Found');
END;

I am executing using exec DISP_EMP_DETAILS(198);

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    edited Sep 3, 2021 8:16AM

    When you call the procedure you need to provide some variables as placeholders for the OUT values that passed back.


    p.s. would also be a good idea if your procedure actually populated those OUT parameters with something.

    Other problems...

    Your first select statement doesn't do a count so could raise a NO_DATA_FOUND exception, before you even get to the check for NULL.

    Your EXECUTE IMMEDIATE statement is performing a select statement that goes nowhere. You don't even need to use EXECUTE IMMEDIATE.

    Your code would be better just doing something like ...

    CREATE OR REPLACE PROCEDURE disp_emp_details (
        iempno   IN   NUMBER,
        semail   OUT  VARCHAR2,
        ssalary  OUT  VARCHAR2
      ) IS
        lv_count NUMBER(10);
        invalid_emp EXCEPTION;
    BEGIN
      select email, salary
      into   semail, ssalary
      from   hr.employees
      where  employee_id = iempno;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('Record Not Found');
    	-- Note DBMS_OUTPUT is not an appropriate error logging mechanism
    	-- Better would be to simply let the error raise up
    END;
    
    -- To call it, something like...
    declare
      email varchar2(100);
      sal   varchar2(20);
    begin
      disp_emp_details(198, email, sal);
      .. then do what you want with those variables
    end;
    
     
    


  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    @BluShadow okay..but why can't we call it using exec procedure_name(parameters)??

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    edited Sep 3, 2021 8:48AM

    You can use EXEC if you're client is something like SQL*Plus... but all that is really doing is telling SQL*Plus to wrap the statement in a BEGIN ... END; statement anyway. e.g.

    SQL> CREATE OR REPLACE PROCEDURE disp_emp_details (
      2      iempno   IN   NUMBER,
      3      sename   OUT  VARCHAR2,
      4      ssalary  OUT  NUMBER
      5    ) IS
      6      lv_count NUMBER(10);
      7      invalid_emp EXCEPTION;
      8  BEGIN
      9    select ename, sal
     10    into   sename, ssalary
     11    from   emp
     12    where  empno = iempno;
     13  EXCEPTION
     14    WHEN NO_DATA_FOUND THEN
     15      dbms_output.put_line('Record Not Found');
     16     -- Note DBMS_OUTPUT is not an appropriate error logging mechanism
     17     -- Better would be to simply let the error raise up
     18  END;
     19  /
    
    Procedure created.
    
    SQL> var ename char(10)
    SQL> var sal   number
    SQL> exec disp_emp_details(7788,:ename,:sal);
    
    PL/SQL procedure successfully completed.
    
    SQL> print ename;
    
    ENAME
    --------------------------------
    SCOTT
    
    SQL> print sal;
    
           SAL
    ----------
          3000
    
    SQL> exec disp_emp_details(1234,:ename,:sal);
    Record Not Found
    
    PL/SQL procedure successfully completed.
    


  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    @BluShadow You have declared lv_count and an exception name but haven't used it anywhere.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond