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!
Readme here https://www.oracle.com/tools/ords/ords-relnotes-22.4.html
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; /
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;