Error Text = Pls-00049 Bad Bind Variable
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring pls-00049 bad bind variable trigger developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask
Bad Bind Variable In Oracle Procedure
Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join
Bad Bind Variable In Oracle Forms
them; it only takes a minute: Sign up Oracle SQL PLS-00049: bad bind variable up vote 8 down vote favorite 1 I'm getting this error and I have googled and googled and all seem to be an issue with
Pls 00049 Bad Bind Variable New Trigger
column spelling. However I am 99% percent sure I have spelled everything correct and have read over and over my code but I can't 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 pls-00049 bad bind variable 'old 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. I am so stumped on this! 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) ); Sigh this assignment is due in 7 hours lol, and this trigger is driving me nuts. Thanks in advance for your help. sql oracle share|improve this question edited Aug 30 '11 at 14:28 asked Aug 30 '11 at 14:21 hamstar 83731221 1 Please add the definition of the sales table –Jim Garrison Aug 30 '11 at 14:32 add a comment| 4 Answers 4 active oldest votes up vote 12 down vote accepted 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, which begins with the word DECLARE. share|improve this answer answered Aug 30 '11 at 14:23 Tony Andrews 87.9k
bad variable [message #571326] Fri, 23 November 2012 06:27 ishika_20 Messages: 339Registered: December 2006 Location: delhi Senior Member Dear All, When i am compliing trigger with only "after bad bind variable in oracle reports update", trigger is running fast. But when i am running with "After what does bad bind variable mean insert or update or delete" event on trigger, trigger comes with compilation error. Please suggest me to resolve this pls-00049 bad bind variable in function issue SQL> drop trigger trg_stk_upd_pur; Trigger dropped. SQL> create or replace trigger trg_stk_upd_pur 2 after update on O_STOCK_EFFECTS 3 REFERENCING NEW AS new OLD AS old 4 FOR EACH ROW 5 begin http://stackoverflow.com/questions/7244714/oracle-sql-pls-00049-bad-bind-variable 6 prc_stk_upd_pur(:old.seff_comp_suid, :old.seff_area_suid, :old.seff_tran_suid); 7 end; 8 / Trigger created. SQL> drop trigger trg_stk_upd_pur; Trigger dropped. SQL> drop trigger trg_stk_upd_pur; Trigger dropped. SQL> create or replace trigger trg_stk_upd_pur 2 after insert or update or delete on i_purchase_h 3 REFERENCING NEW AS new OLD AS old 4 FOR EACH ROW 5 begin 6 if updating then 7 prc_stk_upd_pur(:old.seff_comp_suid, :old.seff_area_suid, :old.seff_tran_suid); 8 9 elsif deleting then http://www.orafaq.com/forum/t/185057/ 10 prc_stk_upd_pur(:old.seff_comp_suid, :old.seff_area_suid, :old.seff_tran_suid); 11 12 elsif inserting then 13 prc_stk_upd_pur(:new.seff_comp_suid, :new.seff_area_suid, :new.seff_tran_suid); 14 15 end if; 16 end; 17 / Warning: Trigger created with compilation errors. SQL> sho err Errors for TRIGGER TRG_STK_UPD_PUR: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/23 PLS-00049: bad bind variable 'OLD.SEFF_COMP_SUID' 3/44 PLS-00049: bad bind variable 'OLD.SEFF_AREA_SUID' 3/65 PLS-00049: bad bind variable 'OLD.SEFF_TRAN_SUID' 6/18 PLS-00049: bad bind variable 'OLD.SEFF_COMP_SUID' 6/39 PLS-00049: bad bind variable 'OLD.SEFF_AREA_SUID' 6/60 PLS-00049: bad bind variable 'OLD.SEFF_TRAN_SUID' 9/18 PLS-00049: bad bind variable 'NEW.SEFF_COMP_SUID' 9/39 PLS-00049: bad bind variable 'NEW.SEFF_AREA_SUID' 9/60 PLS-00049: bad bind variable 'NEW.SEFF_TRAN_SUID' SQL> Thanks, Ishika Report message to a moderator Re: trigger is showing bad variable [message #571327 is a reply to message #571326] Fri, 23 November 2012 06:41 ishika_20 Messages: 339Registered: December 2006 Location: delhi Senior Member sorry..... it was my mistake... please ignore my post. correct answer is SQL> drop trigger trg_stk_upd_pur; Trigger dropped. SQL> create or replace trigger trg_stk_upd_pur 2 after insert or update or delete on i_purchase_h 3 REFERENCING NEW AS new OLD AS old 4 FOR EACH ROW 5 begin 6 if updating then 7 prc_stk_upd_pur(:old.pih_company_suid, :old.pih_area_suid, :old.pih_suid); 8 9 els
Technology and Trends Enterprise Architecture and EAI ERP Hardware IT Management and Strategy Java Knowledge http://oracle.ittoolbox.com/groups/technical-functional/oracle-sql-l/bad-bind-variable-in-trigger-2583344 Management Linux Networking Oracle PeopleSoft Project and Portfolio Management SAP SCM Security Siebel Storage UNIX Visual Basic Web Design and Development Windows < Back CHOOSE A DISCUSSION GROUP Research Directory TOPICS Database Hardware Networking SAP Security Web Design MEMBERS Paul_Pedant DACREE MarkDeVries Inside-ERP MacProTX VoIP_News Inside-CRM I_am_the_dragon maxwellarnold Michael Meyers-Jouan TerryCurran Chris_Day bad bind Andrew.S.Baker Ramnath.Awate JoeTorre bracke Locutus PCMag Dennis Stevenson Craig Borysowich DukeGanote Richard mircea_luca iudithm Nikki Klein AbhaiTripathi knowscognosdoi Clinton Jones Iqbalyk bluesguyAZ59 COMPANIES Compuware Wave Direct Cisco EdgeWave View All Topics View All Members View All Companies Toolbox for IT Topics Oracle Groups Ask a New Question Oracle T-SQL For discussion bad bind variable on Oracle T-SQL , please visit the Oracle Applications group. More Oracle Groups Your account is ready. You're now being signed in. Solve problems - It's Free Create your account in seconds E-mail address is taken If this is your account,sign in here Email address Username Between 5 and 30 characters. No spaces please The Profile Name is already in use Password Notify me of new activity in this group: Real Time Daily Never Keep me informed of the latest: White Papers Newsletter Jobs By clicking "Join Now", you agree to Toolbox for Technology terms of use, and have read and understand our privacy policy. Bad Bind Variable in Trigger btucker007 asked Feb 9, 2009 | Replies (14) What's wrong with this script? CREATE OR REPLACE TRIGGER JOB_TR AFTER INSERT OR UPDATE OR DELETE ON PS_JOB FOR EACH ROW DECLARE V_AUDIT_OPRID VARCHAR2(64); BEGIN DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT _OPRID); IF :OLD.RECNAME IS NULL THEN INSERT INTO PSAUD