Instead of trigger -> how to handle CLOBs?

[Taoism]

Does anyone have a suggestion on how I get an INSTEAD OF trigger to work with a CLOB? Code and error posted below...

Thanks in advance for any help!

Cheers,
Keith.



-- create trigger

CREATE OR REPLACE TRIGGER upd_fip_news
INSTEAD OF UPDATE ON v_fip_news
FOR EACH ROW
BEGIN
UPDATE v_fip_news
SET news_headln = :NEW.news_headln,
news_body = :NEW.EMPTY_CLOB(),
news_desc = :NEW.news_desc,
news_grp_id = :NEW.news_grp_id,
news_created_usr = :NEW.news_created_usr,
news_modified_usr = :NEW.news_modified_usr,
news_created_dt = :NEW.news_created_dt,
news_modified_dt = :NEW.news_modified_dt,
news_eff_dt = :NEW.news_eff_dt,
news_exp_dt = :NEW.news_exp_dt,
news_grp_lock = :NEW.news_grp_lock
WHERE news_id = :NEW.news_id
AND news_lang = :NEW.news_lang
RETURNING news_body
INTO :NEW.news_body;
IF SQL%ROWCOUNT = 0 THEN
BEGIN
INSERT
INTO v_fip_news
(news_id,
news_lang,
news_headln,
news_body,
news_desc,
news_grp_id,
news_created_usr,
news_modified_usr,
news_created_dt,
news_modified_dt,
news_eff_dt,
news_exp_dt,
news_grp_lock)
VALUES (:NEW.news_id,
:NEW.news_lang,
:NEW.news_headln,
:NEW.EMPTY_CLOB()
:NEW.news_desc,
:NEW.news_grp_id,
:NEW.news_created_usr,
:NEW.news_modified_usr,
:NEW.news_created_dt,
:NEW.news_modified_dt,
:NEW.news_eff_dt,
:NEW.news_exp_dt,
:NEW.news_grp_lock);
END;
END IF;
END;



Warning: Trigger created with compilation errors.

Errors for TRIGGER UPD_FIP_NEWS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/30 PLS-00049: bad bind variable 'NEW.EMPTY_CLOB'
38/15 PLS-00049: bad bind variable 'NEW.EMPTY_CLOB'
39/15 PLS-00103: Encountered the symbol "" when expecting one of the
following:
. ( ) , * % & | = - + < / > at in mod not range rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is dangling
The symbol "(" was substituted for "" to continue.

47/34 PLS-00103: Encountered the symbol ";" when expecting one of the
following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
. ( ) , * % & | = - + < / > at in mod not range rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is dangling
The symbol ")" was substituted for ";" to continue.

[rotem_fo]

Hi,
don't user :NEW.EMPTY_CLOB(), the new record does not contain this function. EMPTY_CLOB is a system function.
use:




CREATE OR REPLACE TRIGGER upd_fip_news
INSTEAD OF UPDATE ON v_fip_news
FOR EACH ROW
BEGIN
UPDATE v_fip_news
SET news_headln = :NEW.news_headln,
news_body = EMPTY_CLOB(),
...

INSERT
INTO v_fip_news
(news_id,
news_lang,
news_headln,
news_body,
...
VALUES (:NEW.news_id,
:NEW.news_lang,
:NEW.news_headln,
EMPTY_CLOB(),
...


Cheers.