Oracle SQL PLS-00049: bad bind variable

Question

I'm getting this error which seems to be an issue with column spelling. However I am 99% percent sure I have spelled everything correct, but I can't see any reason to be getting the error I do...

Here's the source:

CREATE OR REPLACE TRIGGER update_qoh_trigger
    AFTER INSERT ON sales
    FOR EACH ROW
DECLARE
    v_qoh products.qoh%TYPE;
    v_new_qoh products.qoh%TYPE;
BEGIN
    SELECT qoh INTO v_qoh
    FROM products
    WHERE id = :new.product_id;

    v_new_qoh := v_qoh - new.quantity; // ERROR HERE

    UPDATE products
    SET qoh = :v_new_qoh
    WHERE id = :new.product_id;
END;
/
sho err

And that gives a:

12/12 PLS-00049: bad bind variable 'V_NEW_QOH'

I have tried replacing line 12 with the following combinations:

v_new_qoh := :v_qoh - :new.quantity;
:v_new_qoh := :v_qoh - :new.quantity;
:v_new_qoh = :v_qoh - :new.quantity;
:v_new_qoh := v_qoh - :new.quantity;
:v_new_qoh := :v_qoh - new.quantity;
v_new_qoh := v_qoh - :new.quantity;

But it still gives me the error.

The products table looks like this:

CREATE TABLE products (
    id NUMBER,
    name VARCHAR2,
    price NUMBER,
    qoh NUMBER(2)
);

CREATE TABLE sales (
    id NUMBER(10) AUTO_INCREMENT,
    customer_id NUBMER(3),
    product_id NUMBER(3),
    quantity NUMBER(2),
    price NUMBER(5,2),
    sale_date DATE,
    despatch_id NUMBER(10)
);

Thanks in advance for your help.

Please add the definition of the sales table

Jim Garrison
– Jim Garrison

2011-08-30 14:32:06 +00:00
Commented Aug 30, 2011 at 14:32 — Jim Garrison
– Jim Garrison, Commented Aug 30, 2011 at 14:32

YotamW Constantini · Accepted Answer · 2018-08-06 21:45:31Z

20

Change the update to:

UPDATE products
SET qoh = v_new_qoh
WHERE id = :new.product_id;

i.e. no colon in front of v_new_qoh.

The line number (12) refers to the line number of the PL/SQL block. The block begins with the word DECLARE, so the 12th line is the one starting with:

SET qoh = :v_new_qoh

edited Aug 6, 2018 at 21:45

YotamW Constantini

4103 silver badges11 bronze badges

answered Aug 30, 2011 at 14:23

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hamstar Over a year ago

Thank you very much! But I don't understand why it calls DECLARE line 12? Is that in some code we can't see (cos I would say it is line 4)?

Brian Over a year ago

The DECLARE is line 1. That makes line 12: "SET qoh = :v_new_qoh".

Tony Andrews Over a year ago

No, I mean that the line with the error is line 12 if you count starting with DECLARE as line 1.

Florin Ghita · Accepted Answer · 2011-08-30 14:28:07Z

8

and new.quantity should be :new.quantity

answered Aug 30, 2011 at 14:28

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Comments

dimcookies · Accepted Answer · 2012-12-26 11:14:25Z

0

for inserting same value of sample_insp_id in to Sir_no:-

update TRIGGER "PT"."BI_PROJECT_PART_SAMPLE_INSP"   
  before insert on "PROJECT_PART_SAMPLE_INSP"               
  for each row  
begin   
  if :NEW."SAMPLE_INSP_ID" is null then
    select "PROJECT_PART_SAMPLE_INSP_SEQ".nextval into :NEW."SAMPLE_INSP_ID",:NEW."SIR_NO"from dual;
  end if;
end;

edited Dec 26, 2012 at 11:14

dimcookies

1,9307 gold badges31 silver badges37 bronze badges

answered Dec 26, 2012 at 10:48

Mangesh Modiraj

71 bronze badge

Comments

anuj arora · Accepted Answer · 2012-12-28 10:02:18Z

-1

Create or replace trigger

"BI_PROJECT_PART_SAMPLE_INSP"

before insert on

"PROJECT_PART_SAMPLE_INSP"

for each row

begin   
    if :NEW."SAMPLE_INSP_ID" is null then
        select "PROJECT_PART_SAMPLE_INSP_SEQ".nextval into :NEW."SAMPLE_INSP_ID" from dual;
        :NEW."SIR_NO":= :NEW."SAMPLE_INSP_ID";
    end if;
end;

edited Dec 28, 2012 at 10:02

anuj arora

83112 silver badges22 bronze badges

answered Dec 28, 2012 at 9:42

Mangesh Modiraj

71 bronze badge

Collectives™ on Stack Overflow

Oracle SQL PLS-00049: bad bind variable

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Related