oracle-databasestored-proceduresplsqloracle11gr2

PLS-00049: bad bind variable ' while creating procedure

Hi I'm trying to create a procedure for which the code is given below:

CREATE PROCEDURE profit_calcs(profit OUT NUMBER,
                              em     IN NUMBER,
                              eq     IN NUMBER,
                              p      IN NUMBER,
                              u      IN NUMBER,
                              income IN NUMBER,
                              ex     IN NUMBER) AS
BEGIN
  SELECT SUM(amount) INTO em FROM a_em;
  SELECT SUM(amount) INTO eq FROM a_eq;
  SELECT SUM(amount) INTO p FROM a_p;
  SELECT SUM(amount) INTO u FROM a_u;
  SELECT SUM(amount) INTO income FROM sales;
  ex     := :em + :eq + :p + :u;
  profit := :income - :ex;
END;
/

whenever I'm trying to create it I'm getting the following error:

LINE/COL  ERROR
--------- -------------------------------------------------------------
10/7      PLS-00049: bad bind variable 'EM'
10/13     PLS-00049: bad bind variable 'EQ'
10/18     PLS-00049: bad bind variable 'P'
10/22     PLS-00049: bad bind variable 'U'
11/11     PLS-00049: bad bind variable 'INCOME'
11/21     PLS-00049: bad bind variable 'EX'

It will be very helpful if someone helps me to solve this error.

Solution

  • Because, you need to define those variables at the declaration section just after the keyword AS, before BEGIN such as

    CREATE OR REPLACE PROCEDURE profit_calcs(profit OUT NUMBER,
                                         ex     OUT NUMBER) AS
  em     INT;
  eq     INT;
  p      INT;
  u      INT;
  income INT;                             
BEGIN

    or convert them to OUT type parameters for this stored procedure such as

    CREATE OR REPLACE PROCEDURE profit_calcs(profit OUT NUMBER,
                                         em     OUT NUMBER,
                                         eq     OUT NUMBER,
                                         p      OUT NUMBER,
                                         u      OUT NUMBER,
                                         income OUT NUMBER,
                                         ex     OUT NUMBER) AS                           
BEGIN

    where