CODEWITHSUNDEEP
oracle-databaseplsql
samba
samba

Reputation: 3091

Value returned by Oracle PL/SQL function is not printed out

I wrote a PL/SQL function that updates salary by employee id and returns stopwatch as INTERVAL DAY TO SECOND.

When called, it updates salary but I also want it to print out the stopwatch value. How can it be achieved? I've tried adding this line dbms_output.put_line(stopwatch); but the function still just updates salary and doesn't print out the stopwatch value.

CREATE OR REPLACE function myfunction1(e_empno_in IN NUMBER, e_sal_in IN NUMBER)

RETURN INTERVAL DAY TO SECOND
IS 
e_empno emp.empno%TYPE := e_empno_in;
e_sal emp.sal%TYPE := e_sal_in;

thestart TIMESTAMP;
stopwatch INTERVAL DAY TO SECOND;

BEGIN
    SELECT current_timestamp INTO thestart FROM dual;
    UPDATE emp SET sal = e_sal WHERE empno = e_empno;
    IF SQL%ROWCOUNT = 0 THEN
        raise_application_error (-10001, 'No row found for id ' || e_empno);
    END IF;
    SELECT current_timestamp-thestart INTO stopwatch FROM dual;
RETURN stopwatch;
dbms_output.put_line(stopwatch); --> *trying to print out stopwatch value*

END;

Calling the function:

BEGIN
dbms_output.put_line(myfunction1(7654, 2559));
END;

Upvotes: 0

Views: 952

Answers (1)

MT0
MT0

Reputation: 167822

You need the DBMS_OUTPUT.PUT_LINE before the RETURN statement. You also don't need to use SQL to get the current time (it can be done in PL/SQL) and you don't need to reassign the input parameters to use them as bind variables.

CREATE function myfunction1(
  e_empno_in IN emp.empno%TYPE,
  e_sal_in   IN emp.sal%TYPE
)
RETURN INTERVAL DAY TO SECOND
IS 
  thestart  TIMESTAMP := CURRENT_TIMESTAMP;
  stopwatch INTERVAL DAY TO SECOND;
BEGIN
  UPDATE emp
  SET   sal   = e_sal_in
  WHERE empno = e_empno_in;

  IF SQL%ROWCOUNT = 0 THEN
      raise_application_error (-10001, 'No row found for id ' || e_empno_in );
  END IF;

  stopwatch := CURRENT_TIMESTAMP - thestart;

  DBMS_OUTPUT.PUT_LINE(stopwatch);

  RETURN stopwatch;
END;
/

Upvotes: 1

Related Questions