CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
DDT
DDT

Reputation: 41

Oracle SQL - PLS-00049 - bad bind variable

I have table defined like this:

create table "nakup"  (
   "cislo_n"            INTEGER                         not null,
   "id_zak"             INTEGER                         not null,
   "jm_pobocky"         CHAR(15)                        not null,
   "datum_cas"          DATE                            not null
      constraint CKC_DATUM_CAS_NAKUP check ("datum_cas" >= TO_DATE('1.01.1994 8:30:25', 'DD.MM.YYYY HH24:MI:SS')),
   constraint PK_NAKUP primary key ("cislo_n")

I want to create a trigger that would block inserting a date from the future, my code looks like this:

create or replace TRIGGER TRIGGER1 
BEFORE INSERT OR UPDATE ON "nakup" 
FOR EACH ROW 
BEGIN
  if (:new.datum_cas > current_timestamp) then
  raise_application_error(-20000, 'Špatně zadané datum a čas.');
end if;
END;

I keep getting error Error(5,7): PLS-00049: chybná vázaná proměnná 'NEW.DATUM_CAS' (bad bind variable in english). What am I doing wrong?

Upvotes: 1

Views: 5858

Answers (2)

user3134993
user3134993

Reputation: 1

yes...

datum_cas column name is different from datum_cas column name..

Oracle stores column names as uppercase by default,if we use double quotes ("") the column name is stored as-is (upper/lower)

example....

SQL> create table test5 (id number,"id1" number);
      table created.

SQL> insert into  test5 values(1,2);

 1 row created.

SQL> select * from  test5;

  ID        id1
----- ----------
    1          2

id is stored as ID and id1 is stored as id1.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191305

As Gordon Linoff suggested, your trigger will compile if you enclose the column name in double quotes:

create or replace TRIGGER TRIGGER1 
BEFORE INSERT OR UPDATE ON "nakup" 
FOR EACH ROW 
BEGIN
  if (:new."datum_cas" > current_timestamp) then
  raise_application_error(-20000, 'Špatně zadané datum a čas.');
end if;
END;
/

Trigger TRIGGER1 compiled

Quoted identifiers have to be quoted everywhere they are referenced. You seem to have realised that when you referred to the table name in the trigger definition, but it applies to the column names too.

Your life will be much simpler if you use unquoted identifiers (or quoted uppercase identifiers, which is the same thing as long as they don't contain any invalid characters). Oracle does not recommend using quoted identifiers for database object names. So this works with no double-quotes at all:

create table nakup  (
   cislo_n            INTEGER                         not null,
   id_zak             INTEGER                         not null,
   jm_pobocky         CHAR(15)                        not null,
   datum_cas          DATE                            not null,
   constraint CKC_DATUM_CAS_NAKUP
     check (datum_cas >= TO_DATE('1.01.1994 8:30:25', 'DD.MM.YYYY HH24:MI:SS')),
   constraint PK_NAKUP primary key (cislo_n)
);

Table NAKUP created.

create or replace TRIGGER TRIGGER1 
BEFORE INSERT OR UPDATE ON nakup
FOR EACH ROW 
BEGIN
  if (:new.datum_cas > current_timestamp) then
  raise_application_error(-20000, 'Špatně zadané datum a čas.');
end if;
END;
/

Trigger TRIGGER1 compiled

You can then refer to nakup.datum_cas etc. in your code, instead of having to use "nakup"."datum_cas".

Upvotes: 2

Related Questions