Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

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

klaxyn (TechnicalUser) (OP)
27 Jun 09 20:23
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 (TechnicalUser)
27 Jun 09 20:54
Hi,
Not sure why it is not working - it looks exactly like this example:

CODE

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...

 

profile

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

klaxyn (TechnicalUser) (OP)
27 Jun 09 22:02
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 (TechnicalUser)
27 Jun 09 23:03
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).
 

profile

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

SantaMufasa (TechnicalUser)
28 Jun 09 14:17
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.

santaMufasa
(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 (TechnicalUser) (OP)
28 Jun 09 17:51
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 (TechnicalUser)
28 Jun 09 18:43
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.

santaMufasa
(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."

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close