Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01400 Error in application

656867Sep 8 2008 — edited Sep 11 2008
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.
This post has been answered by ATD on Sep 10 2008
Jump to Answer

Comments

60437

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
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
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
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
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
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
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
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
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
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
656867
Hi,

Thank you for taking the time to help me out.

This is the trigger I created. It works perfectly for incrementing and inserting my primary key. What I also need the trigger to do is take the password that is typed in and encrypt it.
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;
These are the two tables that are being used with the package and functions:
CREATE TABLE ENCRYPT_PW 
( 
PW_ID INTEGER CONSTRAINT PW_ID2_PK PRIMARY KEY,
CLIENTID VARCHAR2(50 BYTE),
CLIENT_CODE VARCHAR2(50 BYTE),
PASSWORD VARCHAR2(256 BYTE),
CLIENT_NAME VARCHAR2(256 BYTE)
);


CREATE TABLE ED_ENCRYPT 
( 
PK VARCHAR2(128 BYTE), 
EK RAW(128) 
); 
This is the package and functions being created:
CREATE OR REPLACE PACKAGE ENC_CLIENT 
AS 
FUNCTION ENC_PASSWORD( 
p_password in VARCHAR2,
p_clientid in VARCHAR2,
p_unlock_code in VARCHAR2 DEFAULT NULL) 
RETURN VARCHAR2; 

FUNCTION DEC_PASSWORD( 
p_password in VARCHAR2,
p_clientid in VARCHAR2,
p_unlock_code in VARCHAR2 DEFAULT NULL) 
RETURN VARCHAR2; 
END; 
/ 

CREATE OR REPLACE PACKAGE BODY ENC_CLIENT 
AS 
g_master_key VARCHAR2(32) := 'ThisIsTheSuperSe'; 
g_unlock_code VARCHAR2(10) := 'OpenSesame'; 
l_mod number := dbms_crypto.ENCRYPT_AES128 
+ dbms_crypto.CHAIN_CBC 
+ dbms_crypto.PAD_PKCS5; 

FUNCTION enc_password ( 
p_password in VARCHAR2,
p_clientid in VARCHAR2,
p_unlock_code in VARCHAR2 DEFAULT NULL) 
RETURN VARCHAR2 
AS 
l_key RAW(256); 
l_stored_key RAW(256); 
BEGIN 
IF (p_unlock_code IS NULL 
OR p_unlock_code != g_unlock_code) 
THEN 
RETURN NULL; 
END IF; 

-- Random key generation 
l_key := dbms_crypto.randombytes(16); 

-- Encrypts random key with master key into a stored key 
l_stored_key := dbms_crypto.encrypt(l_key, 
l_mod, 
UTL_I18N.STRING_TO_RAW(g_master_key, 'AL32UTF8') 
); 

-- store unique value and stored key in table 
BEGIN
DELETE ED_ENCRYPT WHERE PK = p_clientid;
END;

INSERT INTO ED_ENCRYPT 
VALUES (p_clientid, l_stored_key); 

-- returns encrypted salary that is encrypted with the random key 
RETURN UTL_ENCODE.BASE64_ENCODE(dbms_crypto.encrypt(UTL_I18N.STRING_TO_RAW(p_password,'AL32UTF8'), 
l_mod, 
l_key)); 
END; 

FUNCTION dec_password ( 
p_password in VARCHAR2,
p_clientid in VARCHAR2,
p_unlock_code in VARCHAR2 DEFAULT NULL) 
RETURN VARCHAR2 
AS 
l_key RAW(256); 
BEGIN 
IF (p_unlock_code IS NULL 
OR p_unlock_code != g_unlock_code) 
THEN 
RETURN NULL; 
END IF; 

SELECT dbms_crypto.decrypt(ek, 
l_mod, 
UTL_I18N.STRING_TO_RAW(g_master_key,'AL32UTF8')) 
INTO l_key 
FROM ED_ENCRYPT 
WHERE pk = p_clientid; 
RETURN UTL_I18N.RAW_TO_CHAR(dbms_crypto.decrypt(UTL_ENCODE.BASE64_DECODE(p_password),l_mod,l_key),'AL32UTF8'); 
END; 
END; 
/
If there is anything else that is needed, please let me know.

Thanks again for your help.
ATD
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
Marked as Answer by 656867 · Sep 27 2020
656867
Hi Andy,

Thank you for the help. I actually was not that far off. Instead of referencing :NEW_PASSWORD, I was referencing password.

I will test this out and see how it goes.

Thanks a bunch. You were very helpful.
656867
Hi Andy,

I just wanted to give you an update. Your fix worked. As I suspected, the error of my way was using the actual field names from the table and not the :NEW references.

Thank you and Thank you again. You were a great help!
ATD
You're welcome!
1 - 15
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 9 2008
Added on Sep 8 2008
15 comments
851 views