APEX

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

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

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

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;

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) 
); 

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; 
/