Oracle Forms

I have a requirement to audit several tables and all of the columns in those tables. I need to do this via triggers on the tables in order to catch inserts, updates, and deletes via forms, SQL, etc... This works great for VARCHAR2, NUMBER, DATE, and BLOB columns, but not with CLOB columns. I have broken it down into a little example if anyone can help.

1. Create a table:
create table temp_table
(pk_value number,
orig_clob clob);

2. Create an Audit table:
CREATE TABLE ccw_audit_clob
(aud_clob_id NUMBER,
clob_object CLOB);

3. Add a trigger to the original table to fire on insert:
CREATE OR REPLACE TRIGGER temp_ins_trg
BEFORE
INSERT
ON temp_table
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
v_seq number;
begin
select application_seq.nextval -- Pre-existing sequence
into v_seq
from dual;
insert into ccw_audit_clob
values (v_seq,:NEW.ORIG_CLOB);
end;
/

4. Build a Form Module based on the TEMP_TABLE (I used the wizard and let it build the layout).
- Add a SUBMIT_BUTTON to the TEMP_TABLE block.
- Add a WHEN-BUTTON-PRESSED trigger on the SUBMIT_BUTTON with COMMIT_RECORD;
- Add a PRE-INSERT trigger to the TEMP_TABLE block with the following (Note - I did this to emulate my production tables):
select application_seq.nextval -- Pre-existing sequence.
into :temp_table.pk_value
from dual;

5. Run the form. Enter the word TEST in the ORIG_CLOB item and press the submit button. Then check the tables:
select * from temp_table;

PK_VALUE ORIG_CLOB
---------- --------------------------------------------------------------------------------
349748 test

SQL> select * from ccw_audit_clob
2 /

AUD_CLOB_ID CLOB_OBJECT
----------- --------------------------------------------------------------------------------
349749

As you can see, the original value made it to the table, but the audit table was not able to populate the CLOB column. I did this exact sort of thing using a BLOB column and it works just fine - it is only CLOB that is giving me a problem.

My database is 10.2.0.4 and the version of forms I am using is 9.0.4.0.19.

As most of you probably know, there is not a CLOB datatype in the Property Palette. The Wizard automatically created my ORIG_CLOB as a datatype of Long with a Maximum Length of 240. At first, I thought may the trigger was somehow seeing the data has a LONG and not a CLOB, but that's evidently not the case because if I try to use TO_LOB() or DBMS_LOB.CONVERTTOCLOB() functions - they fail because they already see it as a CLOB.

One more thing, if I put the logic from the trigger on the table into the PRE-INSERT trigger in the form module the CLOB_OBJECT is populated in the AUDIT table, but this is unacceptable because someone could go in via SQL*PLUS and bypass the audit logic.

If anyone has any ideas, please help. I have already submitted a Service Request with Oracle, but I know that could take awhile.

Help! - mike