Here is my problem

I have a table with three columns :

jo_title varchar2(4000)
jo_desc clob
result_cd number

Limitations :
We use Designer TAPI to generate logic to both validate and trigger events on this table.

In the pre-insert and pre-update triggers (Logic stored in TAPI package)
we need to call a function which will derive the result_cd cg$rec.result_cd := auto_code(cg$rec.jo_title,cg$rec.jo_desc);


Problem:
Use of Clob columns in triggers not available (not allowed) so when code is called the cg$rec.jo_desc is not available at the record level


Solutions:
1. add additional column to table which strips jo_desc into a varchar2.
(Since we only need 3000 of the cg$rec.jo_desc for the auto_code function)
This new column would be a varchar2 and allow us to use it in the TAPI logic.
Then our insert logic would look like this
cg$rec.result_cd := auto_code(cg$rec.jo_title,cg$rec.ac_store_str);

drawbacks: Everywhere I insert/update into the table I have to prep the new column and
add it to my statements, and also additional storage for the field.
ac_store_str:=matching_pkg.prep_jo_desc(jo_desc);

I am leaning to this cause it allows my TAPI to be generated (100%) from designer with no after generation
modifications... But I don't really like the additional column and space requirements and relying on the
application to pass the correct information. (Multiple applications which feed this table (web, forms, db_packages)


2. Virtual Column
I created a virtual column "ac_store_str as (matching_pkg.prep_jo_desc(jo_desc))"
matching_pkg.prep_jo_desc(jo_desc) returns a stripped string of 3000 or less.

Then in my TAPI logic I can use ac_store_str in my evaluation to get the result_cd, auto_code(cg$rec.jo_title,cg$rec.ac_store_str)

** when the table gets created it shows the column with a default_value of matching_pkg.prep_jo_desc(jo_desc)

Question:

1. Will the value of the ac_store_str be available from my TAPI package? It doesn't seem to be accessible by cg$rec.ac_store_str?

2. Am I totally missing a better solution to address my problem?
(Yes I realize getting rid of Designer TAPI is a solution but we have to tackle that another time)

Thanks for your feedback in advance.

*** Update Information :
drop table tab1;
create table tab1 (c1 clob);
create or replace procedure p (c clob) is
begin
null;
end;
/

create or replace trigger trg1
before update on tab1 for each row
begin
dbms_output.put_line('length old ='||length(:old.c1));
:new.c1 := :new.c1 || to_clob('update');
dbms_output.put_line('length new ='||length(:new.c1));
p(:new.c1);
end;
/
create or replace trigger trg2
before insert on tab1 for each row
begin
dbms_output.put_line('proposed new value of clob column: '||chr(10));
dbms_output.put_line(:new.c1);
:new.c1 := :new.c1 || to_clob(chr(10)||'insert');
dbms_output.put_line('final value of clob column: '||chr(10));
dbms_output.put_line(:new.c1);
p(:new.c1);
end;


After setting up a test case with the information you sent me, you are right. You can see the value of the clob field in the trigger. With the following caveat.
If you insert into tab(c1) values (to_clob('Hi there clob'));
The triggers show the information and you can work with the clob. BUT
I am using FORMS to insert the data into the table and so I think it is doing a insert with and empty_clob() and returning the lobloc then writing to the clob directly when it saves. When this update of the clob happens NO triggers fire. So in my code when I try working with the clob it is an empty_clob().

Short of rewriting the insert statements in my forms (not recommended), I think my initial method of preparing the data in the application (FORMS,WEB,DB) before issuing the call is going to be the workable solution here.

Thanks for the clarification.

Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not fire triggers defined on the table that contains the columns or attributes.

SQL> declare
  2    lob_loc clob;
  3    doc     varchar2(1000) := lpad('x', 10, 'x');
  4  begin
  5
  6     insert into tab1 (c1)
  7     values (empty_clob())
  8     returning c1 into lob_loc;
  9
 10     dbms_lob.write(lob_loc, length(doc), 1, doc);
 11  end;
 12  /
proposed new value of clob column:


final value of clob column:


insert

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.24
SQL>
SQL> select * from tab1;

C1
-----------------------------------------------------
xxxxxxxxxx

SQL> update tab1 set c1 = 'a';
length old =10
length new =7

1 row updated.