Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00049: Error (Oracle 9)
PLS-00049: Error [message #385436] Mon, 09 February 2009 11:04 Go to next message
psdev
Messages: 4
Registered: February 2009
Junior Member
I am getting the following error when trying to apply a trigger to an Oracle 9 database.

PLS-00049: bad bind variable 'NEW.KEY13'

I am wondering why I would get an error using the :NEW and :OLD variable designations? I thought that was common usage in determining old and new values?

Please help.
Re: PLS-00049: Error [message #385438 is a reply to message #385436] Mon, 09 February 2009 11:13 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you



>Please help.
use valid syntax & you won't get any errors.
Re: PLS-00049: Error [message #385444 is a reply to message #385436] Mon, 09 February 2009 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am wondering why I would get an error using the :NEW and :OLD variable designations?

Because you use them when it is not valid.

Regards
Michel
Re: PLS-00049: Error [message #385484 is a reply to message #385436] Mon, 09 February 2009 21:31 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
in general, if you make delete operation, :new. :old. has make no sense, and that may be the reason you are getting bad binding variable. check it.
yours
dr.s.raghunathan
Re: PLS-00049: Error [message #385594 is a reply to message #385484] Tue, 10 February 2009 03:42 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
dr.s.raghunathan wrote on Tue, 10 February 2009 03:31
in general, if you make delete operation, :new. :old. has make no sense, and that may be the reason you are getting bad binding variable. check it.
yours
dr.s.raghunathan


It is perfectly valid to reference :new and :old in delete and insert row level triggers. It's just that new variables will always be NULL in delete triggers and old variables will always be null in insert triggers.
These facts will in no way prevent a trigger from compiling which is the issue the OP is having.
Re: PLS-00049: Error [message #385595 is a reply to message #385594] Tue, 10 February 2009 03:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Really, the OP needs to show us some code.
Re: PLS-00049: Error [message #385658 is a reply to message #385444] Tue, 10 February 2009 07:42 Go to previous messageGo to next message
psdev
Messages: 4
Registered: February 2009
Junior Member
Obviously, but why. The code should be recognized by Oracle, does not make sense why it does not recognize the variable names.
Re: PLS-00049: Error [message #385659 is a reply to message #385484] Tue, 10 February 2009 07:44 Go to previous messageGo to next message
psdev
Messages: 4
Registered: February 2009
Junior Member
Understood. But, the program has if logic in it to handle updates, inserts, and deletes. The code is not recognizing the :old and :new variable declarations. I thought Oracle would recognize them since that is standard PL/SQL.
Re: PLS-00049: Error [message #385660 is a reply to message #385659] Tue, 10 February 2009 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 09 February 2009 19:27
Because you use them when it is not valid.

Regards
Michel

Re: PLS-00049: Error [message #385674 is a reply to message #385436] Tue, 10 February 2009 08:49 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want us to tell you what the problem is you're going to have to show us the trigger code you're using. We're not psychic!
Re: PLS-00049: Error [message #385697 is a reply to message #385674] Tue, 10 February 2009 10:56 Go to previous messageGo to next message
psdev
Messages: 4
Registered: February 2009
Junior Member
Here is the code:


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 PS_AS_SS_PSAUDIT VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'I',:NEW.RECNAME,:NEW.FIELDNAME,:NEW.OLDVALUE,:NEW.NEWVALUE,:NEW.KEY1,:NEW.KEY2,:NEW.KEY3,:NEW.K EY4,:NEW.KEY5,:NEW.KEY6,:NEW.KEY7,:NEW.KEY8,:NEW.KEY9,:NEW.KEY10,:NEW.KEY11,:NEW.KEY12,:NEW.KEY13,:NEW.KEY14,:NEW.KEY15);
ELSE
IF :NEW.RECNAME IS NULL THEN
INSERT INTO PS_AS_SS_PSAUDIT VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'D',:OLD.RECNAME,:OLD.FIELDNAME,:OLD.OLDVALUE,:OLD.NEWVALUE,:OLD.KEY1,:OLD.KEY2,:OLD.KEY3,:OLD.K EY4,:OLD.KEY5,:OLD.KEY6,:OLD.KEY7,:OLD.KEY8,:OLD.KEY9,:OLD.KEY10,:OLD.KEY11,:OLD.KEY12,:OLD.KEY13,:OLD.KEY14,:OLD.KEY15);
ELSE
INSERT INTO PS_AS_SS_PSAUDIT VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATETIME,'B',:OLD.RECNAME,:OLD.FIELDNAME,:OLD.OLDVALUE,:OLD.NEWVALUE,:OLD.KEY1,:OLD.KEY2,:OLD.KEY3,:O LD.KEY4,:OLD.KEY5,:OLD.KEY6,:OLD.KEY7,:OLD.KEY8,:OLD.KEY9,:OLD.KEY10,:OLD.KEY11,:OLD.KEY12,:OLD.KEY13,:OLD.KEY14,:OLD.KEY15);
INSERT INTO PS_AS_SS_PSAUDIT VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATETIME,'A',:NEW.RECNAME,:NEW.FIELDNAME,:NEW.OLDVALUE,:NEW.NEWVALUE,:NEW.KEY1,:NEW.KEY2,:NEW.KEY3,:N EW.KEY4,:NEW.KEY5,:NEW.KEY6,:NEW.KEY7,:NEW.KEY8,:NEW.KEY9,:NEW.KEY10,:NEW.KEY11,:NEW.KEY12,:NEW.KEY13,:NEW.KEY14,:NEW.KEY15);
END IF;
END IF;

END JOB_TR;
/
Re: PLS-00049: Error [message #385699 is a reply to message #385697] Tue, 10 February 2009 11:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So is there really a KEY13 column in PS_JOB?

Post the FORMATTED session where you try to create the trigger, AND the table definitions.
Re: PLS-00049: Error [message #385701 is a reply to message #385436] Tue, 10 February 2009 11:13 Go to previous message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
It doesn't answer your question but that code could be rewritten as:

IF inserting OR updating THEN

<insert new values into table>

END IF;

IF deleting OR updating THEN

<insert old values into table> 

END IF;
Previous Topic: Outer join logic
Next Topic: translate data trunction problem
Goto Forum:
  


Current Time: Tue Dec 06 10:07:19 CST 2016

Total time taken to generate the page: 0.10965 seconds