oracle-xe trigger PLS-00049: bad bind variable 'NEW.USER_IDX'

[klaxyn]

I just installed oracle-xe (10g) with application express on a linux dev server running centos 5.x. Enabled archivelog mode and created a new user/schema to create my tables and application under. I created a table in application express and get a PLS-00049: bad bind variable 'NEW.USER_IDX' error. I don't see what's wrong with the sql or pl/sql. The code is below. Any help is appreciated. I'm scratching my head on this seemingly simple problem.

CREATE table "users" (
"user_idx" NVARCHAR2(255) NOT NULL,
"user_email" VARCHAR2(255) NOT NULL,
"user_pw" VARCHAR2(255) NOT NULL,
constraint "users_PK" primary key ("user_idx")
)
/

CREATE sequence "USERS_SEQ"
/

CREATE trigger "BI_users"
before insert on "users"
for each row
begin
select "USERS_SEQ".nextval into :NEW.user_idx from dual;
end;
/

alter table "users" add
constraint USERS_UK1
unique ("user_idx","user_email")
/


Thanks!

 

[Turkbear]

Hi,
Not sure why it is not working - it looks exactly like this example:

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;
/

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

The only difference I see is the lack of a test for an incoming user_idx ( the WHEN clause in the example) and your forcing of mixed case into the mix...Try a more standard table creation, maybe..but it should not matter...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[klaxyn]

Thanks but that produces this error.
ORA-00904: "USER_IDX": invalid identifier

create or replace trigger "BI_users"
BEFORE insert on "users" for each row when (new.user_idx is null)
BEGIN SELECT "BI_users".NEXTVAL INTO :new.user_idx FROM dual;
END;

 

[Turkbear]

Which line triggers ( no pun intended) that error - if the first occurance of user_idx throws that error then something is causing the parser not to see that field in your table..Does the account running that code have full rights to the USERS table - are there more than one USERS tables maybe?
Try fully qualifying the table name (schema.table).




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Klaxyn,

You have inadvertently discovered why, in the "Oracle World" it is generally bad form to code user-defined names within double quotes. You see, whenever you define a name in Oracle using double quotes and any alpha character that is not UPPER CASE, then you must always use double quotes and the same mixed-case configuration. If you do not use double-quotes, then Oracle presumes that regardless of your case in the code, that your Oracle name is uppercase !

So, when your referred, in your code, to "...INTO :new.user_idx...", Oracle looks for "USER_IDX", which it cannot find, since you defined that column as *"user_idx"* -- "user_idx" <> "USER_IDX".

If you sanitize your code of all double quotes, then your problem(s) should disappear.

Please advise us of your findings and results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[klaxyn]

Yep that was it. I put quotes around "user_idx" and it worked. Apex used quotes around the column names when creating the table. Looks like a bug in apex to me. I bet if I installed the latest version of application express this would be fixed. The rpm for oracle-xe contains version 2.x of apex. I guess lesson here is avoid gui wizard tools and use the command line

Thanks Mufasa for pointing out the issue with quotes.

 

[SantaMufasa]

Glad it worked for you, Klaxyn.

My rule of thumb when I generate SQL code with tools that add double quotes is to do a global remove of all double quotes prior to processing.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”