nine year old dating guide - Updating oracle clob


The closest thing I've found is a note saying that in triggers, I can read the :old values, but not the :new values. It fires a PLSQL procedure when an application COMMIT's a database change.

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.

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. Anyway (terrible practice to commit in a procedure -- terrible)... COM declare 2 l_clob clob; 3 begin 4 dbms_lob.create Temporary( l_clob, true ); 5 dbms_lob.open( l_clob, dbms_lob.lob_readwrite ); 6 7 dbms_lob.write Append( 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. I have a simple table with a column defined as clob.

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. 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. COM Your trigger actually works -- WHEN it is fired! COM But it'll not work with the dbms_as you have it coded -- when the trigger fires, it gets an EMPTY_CLOB. In this case, the trigger WILL get the value: [email protected] I'm using sqlldr to load the clob column with the contents of a flat file. I have a pl/sql routine that parses this column and extracts data into multiple tables. have it select the clob column into a local variable and then work with that local variable) my code works as expected.

Thanks, Alan December 21, 2004 - am UTC the way lobs get loaded: create table t ( x int primary key, y clob ); insert into t (x,y) values ( 1, empty_clob() ) returning y into :x; dbms_lob.writeappend(:x,:string) dbms_lob.writeappend(:x,:string) ...