Thanks for the question, jamie.
Asked: May 31, 2002 - 9:58 am UTC
Last updated: December 21, 2004 - 9:24 am UTC
Version: 9.0.1
Viewed 10K+ times! This question is 
You Asked
Hi Tom,
Love your site I think it's great how you are willing to share your knowledge and educate others.Anyway
I have a couple of questions and was hoping you could point me in the right direction.First I am wanting to use
clob fields in our 9i database and reference them in triggers for an insert into another table.According to the application developer guide fundamentals
on page 15-17 it says that you can do this.I wanted to do a small test so I created a trigger and procedure as listed below
but the reference value in the trigger doesn't seem the have anything to insert and I don't receive any error messages.
I have tried dbms_lob routine in the trigger to read the clob but still no success.
Could you look at this and see where I am going wrong and possibly offer a proper example.
Second can I use the utl_file package to read an ascii file find specific portions of strings and modify them
and save the changes to the ascii file.Or is this something I need to do with java.
Third I have been trying to run a query to select foreign key constraint name,table it is on,column,
primary key table and column.Would you have any examples of this type of query
If I have listed too many questions could you please look at the first one seeing it's the most important to me..
Any suggestions you have would be greatly appreciated.
Thanks ,
Jamie
select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production
create or replace PROCEDURE COMMENT_ADD
(COMMENT_ IN CLOB)
AS
NEW_PKEY NUMBER;
NEW_COMMENT CLOB;
BEGIN
INSERT INTO TEMP_AUDIT(pkey,text,timestamp)
VALUES
(TMP_PKEY.NEXTVAL,EMPTY_CLOB(),SYSDATE)
RETURNING PKEY INTO NEW_PKEY;
SELECT TEXT INTO NEW_COMMENT FROM TEMP_AUDIT
WHERE PKEY = NEW_PKEY FOR UPDATE;
DBMS_LOB.COPY(NEW_COMMENT,COMMENT_,DBMS_LOB.GETLENGTH(COMMENT_));
COMMIT;
END;
CREATE OR REPLACE TRIGGER hist_insert
AFTER INSERT ON temp_audit
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO HIST_AUDIT(pkey,text,timestamp)
VALUES(HIST_PKEY.NEXTVAL,:new.text,SYSDATE);
END;
/
end result
jamie@J1.US.ORACLE.COM> col text format a20
jamie@J1.US.ORACLE.COM> select * from temp_audit;
pkey text timestamp
----- -------- ------------------
1 test 29-05-2002
2 test2 29-05-2002
3 test3 31-05-2002
jamie@J1.US.ORACLE.COM> select * from hist_audit;
pkey text timestamp
---- ------- -------------------
1 29-05-2002
2 29-05-2002
3 31-05-2002
jamie@J1.US.ORACLE.COM> spool off
and Tom said...
Your trigger actually works -- WHEN it is fired!
The problem is -- you are NOT updating the table when you set the lob value. You might be modifying the lob contents but -- and this is key -- you are NOT modifying the lob locator in the table itself. The row values of the table are not changing, the trigger does NOT fire for the dbms_lob.copy (or write, or trim, or append, or ...)
In short, you cannot fully achieve what you want via your method. This shows this works:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table temp_audit( pkey int, text clob, timestamp date );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create sequence tmp_pkey;
Sequence created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table hist_audit( pkey int, text clob, timestamp date );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create sequence hist_pkey;
Sequence created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE TRIGGER hist_insert
2 AFTER INSERT ON temp_audit
3 REFERENCING NEW AS NEW OLD AS OLD
4 FOR EACH ROW
5 DECLARE
6
7 BEGIN
8
9 INSERT INTO HIST_AUDIT(pkey,text,timestamp)
10 VALUES(HIST_PKEY.NEXTVAL,:new.text,SYSDATE);
11
12 END;
13 /
Trigger created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into temp_audit values ( tmp_pkey.nextval, 'hello world', sysdate );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from temp_audit;
PKEY TEXT TIMESTAMP
---------- -------------------- ---------
1 hello world 02-JUN-02
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from hist_audit;
PKEY TEXT TIMESTAMP
---------- -------------------- ---------
1 hello world 02-JUN-02
ops$tkyte@ORA817DEV.US.ORACLE.COM>
But it'll not work with the dbms_lob.copy as you have it coded -- when the trigger fires, it gets an EMPTY_CLOB. It does NOT get fired for the dbms_lob.copy.
Your routine should just be:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace PROCEDURE COMMENT_ADD(COMMENT_ IN CLOB)
2 AS
3 BEGIN
4 INSERT INTO TEMP_AUDIT(pkey,text,timestamp)
5 VALUES
6 (TMP_PKEY.NEXTVAL,comment_,SYSDATE);
7 END;
8 /
Procedure created.
Anyway (terrible practice to commit in a procedure -- terrible)... In this case, the trigger WILL get the value:
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_clob clob;
3 begin
4 dbms_lob.createTemporary( l_clob, true );
5 dbms_lob.open( l_clob, dbms_lob.lob_readwrite );
6
7 dbms_lob.writeAppend( l_clob, length( 'this is some data' ), 'this is some data' );
8 comment_add( l_clob );
9
10 dbms_lob.close( l_clob );
11 end;
12 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from temp_audit;
PKEY TEXT TIMESTAMP
---------- -------------------- ---------
1 hello world 02-JUN-02
2 this is some data 02-JUN-02
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from hist_audit;
PKEY TEXT TIMESTAMP
---------- -------------------- ---------
1 hello world 02-JUN-02
2 this is some data 02-JUN-02
You just have to bear in mind that any "dbms_lob.write" type of activity will NOT trigger the trigger -- ever.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment