CODEWITHSUNDEEP
sqloracle-databasestored-procedures
Adam Araujo
Adam Araujo

Reputation: 13

Don't understand what PLS-00049: bad bind variable

I'm getting a PLS-00049 (bad binding variable) when creating a stored procedure in Oracle SQL. Not sure what's wrong here:

    CREATE OR REPLACE PROCEDURE ADD_REGISTRATION(
    customer_id_arg IN DECIMAL,
    cus_account_id_arg IN DECIMAL,
    registration_date_arg IN DATE)
    IS
    BEGIN
      SELECT *
      FROM REGISTRATION
      WHERE CUSTOMER_ID = :NEW.CUSTOMER_ID;
      IF CUSTOMER_ID = :NEW.CUSTOMER_ID THEN
         INSERT INTO REGISTRATION(customer_id, cus_account_id, 
         registration_date)
         VALUES(customer_id_arg, CUS_ACCOUNT_ID_SEQ.NEXTVAL, CAST(SYSDATE AS 
         DATE));
      ELSE
         RAISE_APPLICATION_ERROR(-20001, 'Customer ID does not exist. Choose an existing Customer ID in order to 
         register an extra account under an existing customer.');
     END IF;
   END;

These are the specific errors: Error(8,25): PLS-00049: bad bind variable 'NEW.CUSTOMER_ID' Error(9,22): PLS-00049: bad bind variable 'NEW.CUSTOMER_ID'

Upvotes: 0

Views: 915

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

The :NEW and :OLD pseudo-rows only exist in a trigger. If you want to call a subprogram which uses data from :NEW or :OLD you can do that, but you'll have to pass each field as an argument to the procedure or function. For example:

CREATE OR REPLACE PROCEDURE ADD_REGISTRATION(customer_id_arg IN DECIMAL)
IS
  rowRegistration  REGISTRATION%ROWTYPE;
BEGIN
  -- The following SELECT will raise NO_DATA_FOUND if the passed-in CUSTOMER_ID
  -- doesn't exist, in which case we want to insert the new customer into the database.
  -- If the CUSTOMER_ID arg already exists in REGISTRATION we want to raise an
  -- application error.

  SELECT *
    INTO rowRegistration
    FROM REGISTRATION
    WHERE CUSTOMER_ID = customer_id_arg;

  RAISE_APPLICATION_ERROR(-20001, 'Customer ID does not exist. Choose an existing Customer ID in order to 
     register an extra account under an existing customer.');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO REGISTRATION(customer_id, cus_account_id, registration_date)
       VALUES(customer_id_arg, CUS_ACCOUNT_ID_SEQ.NEXTVAL, SYSDATE);
END ADD_REGISTRATION;

Then in your trigger you would call the above routine:

CREATE OR REPLACE TRIGGER REGISTRATION_VIEW_II
  INSTEAD OF INSERT ON REGISTRATION_VIEW
  FOR EACH ROW
BEGIN
  REGISTRATION_ADD(:NEW.CUSTOMER_ID);
END REGISTRATION_VIEW_II;

(Here I'm assuming that this trigger is supposed to be an INSTEAD OF INSERT trigger on a view, because that's the only thing that makes sense to me without further context, but you probably have some other use case I'm unaware of).

Best of luck.

Upvotes: 1

Related Questions