CODEWITHSUNDEEP
databaseoracle-databaseoracle11gprocedure
Henrique
Henrique

Reputation: 57

Executing Procedure Oracle 11g

So I have the following procedure created on Oracle 11g:

create or replace PROCEDURE calc_fee
(proc_borrowed_date IN Borrowing.borrowed_date%TYPE,
proc_return_date IN Borrowing.return_date%TYPE,
proc_fee OUT number)
IS
BEGIN
proc_fee := (SYSDATE - proc_return_date) * 2;
END calc_fee;​

How can I execute it? I've already tried

EXEC CALC_FEE(SYSDATE - 10, SYSDATE);

and

EXECUTE CALC_FEE(SYSDATE - 10, SYSDATE);

But they all return me ORA-00900: invalid SQL statement

Upvotes: 1

Views: 490

Answers (2)

GolezTrol
GolezTrol

Reputation: 116110

You can call a procedure using call (instead of exec), or in a PL/SQL code block. Also note that you have three parameters, so you'll want to pass a variable to catch the output parameter:

declare
  V_FEE number;
begin
  CALC_FEE(SYSDATE - 10, SYSDATE, V_FEE);
  DBMS_OUTPUT.PUT_LINE(V_FEE);
end;

Instead of a procedure with an out parameter, you can also make a function which returns the value:

create or replace FUNCTION calc_fee
(proc_borrowed_date IN Borrowing.borrowed_date%TYPE,
proc_return_date IN Borrowing.return_date%TYPE) return number
IS
BEGIN
  return (SYSDATE - proc_return_date) * 2;
END calc_fee;

Such a function can even be used in a query:

select CALC_FEE(SYSDATE - 10, SYSDATE) from dual

Upvotes: 2

Joseph B
Joseph B

Reputation: 5669

You need to pass the output parameter as well, as below:

declare
  pf number;
begin
  calc_fee(sysdate, sysdate-10, pf);
  dbms_output.put_line('pf is ' || pf);
end;

Reference:

Developing and Using Stored Procedures on Oracle® Database 2 Day Developer's Guide

Upvotes: 0

Related Questions