Alex Rivera | Logout

Oracle SQL PLS-00049: bad bind variable

Asked 2011-08-30T14:21:12.880
16

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.

Edit
Report

1 Answer

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; 
answered 2012-12-26T10:48:10.163

Your Answer