Forum Stats

  • 3,840,001 Users
  • 2,262,557 Discussions
  • 7,901,121 Comments

Discussions

ORA-01400 Error in application

656867
656867 Member Posts: 172
edited Sep 11, 2008 1:32AM in APEX Discussions
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.
Tagged:

Best Answer

  • ATD
    ATD Member Posts: 11,184
    edited Sep 10, 2008 8:58AM Answer ✓
    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 <> :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
«1

Answers

  • 60437
    60437 Member Posts: 16,564
    edited Sep 8, 2008 3:23PM
    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
    656867 Member Posts: 172
    edited Sep 8, 2008 3:50PM
    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
    60437 Member Posts: 16,564
    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
    510477 Member Posts: 1,451
    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
    656867 Member Posts: 172
    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
    ATD Member Posts: 11,184
    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
    656867 Member Posts: 172
    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
    ATD Member Posts: 11,184
    edited Sep 9, 2008 9:51AM
    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
    656867 Member Posts: 172
    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
    ATD Member Posts: 11,184
    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
This discussion has been closed.