ORA-01400 Error in application

656867 · Sep 8, 2008 3:01PM

Hello:

I'm receiving the following error when I try to create a new client.
ORA-01400: cannot insert null into ("client.id")

This is my table structure:
id Number Not Null PK
clientid varchar2
client_code varchar2
client_name varchar2

I created a report which displays all the clients from the table. I also have a CREATE button on the report so my users can create new clients. The CREATE button branches to the next page which is a form.

I click CREATE and the form appears. The form displays all the fields except for id which is the primary key and in the Items area of Page Rendering it appears as Hidden.

I attempt to add a client, I click Create, and I receive the above message.

I changed the id item from Hidden to Text Field and attempted another create and this time it worked.

The id field should also be auto-incremented which I am unable to figure out how to set it. I have a sequence created, ID2_SEQ.NEXTVAL, which when I do an actual insert statement via SQL*Plus the ID field does increment by one.

When I created the report, I couldn't find a way to reference that sequence for the CREATE button. I think that my error message may have something to do with the fact that I'm not inserting a value for the primary key. Yet, in APEX I read that the primary key is not supposed to be editable. I am totally confused here.

Basically, when the user clicks CREATE I want the following INSERT statement to work:
INSERT INTO client (id, clienid,client_code,client_name) VALUES (id2.seq.nextval, :P1_clientid, :P1_client_code, :P1_client_name);

The documentation for APEX is not very clear on how to do this.

Can someoene help me out? This shouldn't be this complicated.

Thanks for all the help.

ATD · Sep 10, 2008 8:54AM

Hi,

I hope that I'm reading your code correctly.... I think your trigger should be:

CREATE OR REPLACE TRIGGER ENCRYPT_PW_T1
BEFORE INSERT OR UPDATE ON ENCRYPT_PW
FOR EACH ROW
BEGIN
  IF :NEW.PW_ID IS NULL
    THEN SELECT PW_ID2_SEQ.NEXTVAL INTO :NEW.PW_ID FROM DUAL;
  END IF;
  IF INSERTING THEN
    :NEW.PASSWORD := ENC_CLIENT.ENC_PASSWORD(:NEW.PASSWORD, :NEW.CLIENTID, 'OpenSesame');
  ELSE
    IF :NEW.PASSWORD &lt;&gt; :OLD.PASSWORD THEN
      :NEW.PASSWORD := ENC_CLIENT.ENC_PASSWORD(:NEW.PASSWORD, :NEW.CLIENTID, 'OpenSesame');
    END IF;
  END IF;
END;

This will handle inserts and updates. The code should first check if a new PW_ID PK value is needed, and set it if it is. Then, for inserts only, it will encrypt the password entered - I'm assuming here that the password will be "plain text" and requires encryption. For updates only, check if the current password matches the old password (comparing a new decrypted version to the old decrypted version) - if it doesn't match (ie, the password has been changed), then update the password value with the encrypted new password.

Unfortunately, the dbms_crypto package does not seem to be available on my OTN workspace (not sure why), so I have not been able to test this.

Andy

60437 · Sep 8, 2008 3:21PM

Terry - You need to create a database trigger on the table. The trigger would reference your sequence.

For example:

 


create or replace trigger T_biu_client

    before insert or update on client

    for each row

declare

  l_new_id number;

begin

    if inserting and :new.id is null then

         select ID2_SEQ.NEXTVAL into l_new_id from dual;

        :new.id := l_new_id;

    end if;

end;

Scott

656867 · Sep 8, 2008 3:27PM

Hi Scott,

I did some exploring and found a post about triggers. I created the following:
create or replace TRIGGER encrypt_pw_t1
BEFORE INSERT on encrypt_pw
FOR EACH ROW
BEGIN
IF :new.pw_id IS NULL
THEN SELECT pw_id2_seq.nextval
INTO :new.pw_id
FROM DUAL;
END IF;
END;

It appears to be working at least for now. My code appears different than yours. Is it still ok?

Where in the application is this trigger being referenced? I can't seem to find it.

Edited by: nyc2292 on Sep 8, 2008 12:50 PM

60437 · Sep 8, 2008 4:55PM

That trigger is not defined on your "clients" table so I don't know if this is a different use case or what but the form of the code is fine, even though it differs slightly from my example. If it works, it works.

The trigger is not referenced from within your application. When you define a trigger on a table, the database fires the trigger whenever DML is performed against the table. So it's "automatic" as some would say.

Scott

510477 · Sep 8, 2008 4:55PM

Your trigger code is fine.

A note about triggers: triggers are database-side code pieces that activate based on conditions in the code itself. In your case, it will fire every time ANYONE (triggers are database-level items not application-level items) inserts a record into encrypt_pw. You could also create a trigger that activates before update, after update, etc. and can be specific even to which column gets updated.

Triggers are commonly used for incrementing unique counters (primary keys) and setting specific values in fields (not the same as defaults). I've also used them to trigger other database actions such as creating records in other tables. Triggers are commonly used for table auditing (but they are not quite as good as no trigger exists for just a SELECT statement). For more information, do a quick search on triggers. They are incredibly powerful and very useful.

656867 · Sep 9, 2008 7:26AM

Can you include a function from a package within a trigger?

In my above code, I not only need to auto-increment my primary key column, I need to apply a function to another column. The function is to be used with an INSERT and UPDATE statements to the field within that table.

Can I do those two things in the same code? Or, would I have to create another trigger?

ATD · Sep 9, 2008 9:32AM

Hi,

Yes, you can use functions to set the value on the other field. One important thing you can not do, though, is access any data in the table that's being updated apart from the current record - but as long as you don't need to do that, you should be ok.

To update any field on the current record when it's being created or updated, just reference the field by preceding it with *:NEW.* (eg, :NEW.MYFIELD2). Field values are assigned using the *:=* assignment operator. So, for example:

DECLARE
  ..
  ..
  vNumber NUMBER;
BEGIN
  ..
  SELECT MAX(FIELDZZ) INTO vNumber FROM ANOTHER_TABLE;
  :NEW.FIELDXX := vNumber;
  ...
END;

Andy

656867 · Sep 9, 2008 9:40AM

Hi,

Thank you for the info.

Only the current record will be udpated.

The user will select a client or insert a new client.

I would like the trigger to do two things: one, auto-increment the primary key (which I'm able to do) and insert/update the record with the function for another field in the same table.

I would like the trigger to do both and not have seperate triggers for each of the two.

I've tried sticking the function within the trigger in various places and have not had much luck.

ATD · Sep 9, 2008 9:49AM

Hi,

OK- then you should be able to handle it all within the same trigger code.

How this is done exactly would depend on the function (it may help to see any code you've tried so far), but if the function simply receives values and returns a number, for example, you would need to do something like:

CREATE ....
...
DECLARE
...
 vNumber NUMBER;
BEGIN
...
  bit that updates the ID 
...
...
  SELECT MyFunction(value1, value2) INTO vNUMBER FROM DUAL;
  :NEW.NUMBERFIELD := vNUMBER;
...
END;

So, the principle is that you declare a variable, select the function into that variable and then assign the variable to the field on the table.

Andy

656867 · Sep 9, 2008 9:56AM

I would be quite happy to post some code. Which specific code would you like to see? The package that created the functions? The functions themselves? The trigger?

Also, I have not posted code yet on this forum. How can I post code that will appear neat?

ATD · Sep 10, 2008 2:08AM

Hi,

I would only need enough to be able to detail the trigger that you should use. So, the trigger itself, how you call the function and with what values or fieldnames and the type of data that the function returns.

When posting code, use the following format:

[code]
Your code
[/code]

Andy

Forum Stats

Quick Links

Categories

ORA-01400 Error in application

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

ORA-01400 Error in application

Best Answer

Answers