Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00049: bad bind variable (merged)
PLS-00049: bad bind variable (merged) [message #382762] Sat, 24 January 2009 08:55 Go to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Hello Ezperts Please help me to rectify where I am wrong in this code

CREATE OR REPLACE TRIGGER ar_transaction_detail_ins
after insert on transaction_detail for each row
WHEN (
new.sub_account_code is not null
      )
declare
nDepot number(3);
nDocNo number(12);
dtTran date;
sCrtype Char(1);
nSman number(5);
sTtype transaction.transaction_type%TYPE;
nDecimal number(1);
begin
  begin
   select decode( nvl( hht_multiplier, 0), 1, 2, 3) into nDecimal
   from depots where rownum = 1;
  exception
   when others then
    raise_application_error(-20001, 'Error in Sales Depot Details File ... ');
  end;
  begin
        select transaction_reference, transaction_date, transaction_type
    into     nDocNo, dtTran, sTtype
    from   transaction
    where  transaction_serial_number = :new.transaction_serial_number;
  exception
    when others then
     raise_application_error(-20001, 'Transaction Headder file Error .... ');
  end;
  if sTtype = 'RR' then
   begin
        select depot_code into nDepot
    from   depots where rownum = 1;
    select a.credit_type, b.salesman_code
    into   sCrtype, nSman
    from   ar_customer_view a, routes b
    where  a.customer_code = :new.sub_account_code
    and    a.route_code = b.route_code;
   exception
    when others then
     raise_application_error(-20001, 'Unable to Update Receivable !!! Error in Master Files.....');
   end;
   insert into ar_transaction ( depot_code, customer_code, session_no,
    document_no, document_type, document_date, credit_type, salesman_code,
    amount, status)
    values (nDepot, :new.sub_account_code, 0, nDocNo, 'R',
    dtTran, sCrtype, nSman, nvl( :new.incurred_amount, nDecimal), 'N');
  end if;
end;
/



PLS-00049: bad bind variable 'NEW.DEPOT_CODE'
Re: PLS-00049: bad bind variable [message #382763 is a reply to message #382762] Sat, 24 January 2009 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
At which line the error?
Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: PLS-00049: bad bind variable [message #382770 is a reply to message #382762] Sat, 24 January 2009 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And don't multipost your questions.

Regards
Michel
Re: PLS-00049: bad bind variable (merged) [message #382772 is a reply to message #382762] Sat, 24 January 2009 09:27 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
after insert.. where is the question of :new.
Re: PLS-00049: bad bind variable (merged) [message #382774 is a reply to message #382762] Sat, 24 January 2009 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a remark: 'NEW.DEPOT_CODE' is not in the code you posted.

Regards
Michel
Re: PLS-00049: bad bind variable (merged) [message #382812 is a reply to message #382762] Sun, 25 January 2009 01:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think the error might come from a trigger on ar_transaction.
Also, to improve your code, why not select everything you need from the depots table in one go? It is way cheaper to select multiple columns at once than to query for individual columns multiple times.
Re: PLS-00049: bad bind variable (merged) [message #382813 is a reply to message #382812] Sun, 25 January 2009 02:18 Go to previous message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

I got it ...
Actually it was an error because of the Column Name.


Instead of :NEW.DEPOT_CODE

it was :NEW.LOCATION_CODE



Smile Thanx Guys...
Previous Topic: Concatenation query
Next Topic: How to find a specific special characters from a string (merged 3)
Goto Forum:
  


Current Time: Tue Dec 06 13:57:48 CST 2016

Total time taken to generate the page: 0.06349 seconds