Reading CLOB in before insert,update trigger

Question

I am trying to create a before insert & update trigger on a table in Oracle. While am trying to read the NEW.CLOB_COLUMN_VALUE, its giving me blank.

Table:

CREATE TABLE TEMP_TEST101
(
  ID               NUMBER(2),
  TEST_CLOB_VALUE  CLOB
)

Trigger:

CREATE OR REPLACE TRIGGER TR_Temp_Test101
BEFORE INSERT OR UPDATE
ON Temp_Test101 FOR EACH ROW
DECLARE
BEGIN

If (:NEW.TEST_CLOB_VALUE = 'A')
Then
    raise_application_error(-20010,'Testing Successfull');
End IF;

raise_application_error(-20010,'Testing Failed:['||:NEW.TEST_CLOB_VALUE||']');


END TR_Temp_Test101;
/

Prompt after trying to insert :

ORA-20010: Testing Failed:[]

Is there any way we can read the :NEW.TEST_CLOB_VALUE before insert/update?

The requirement is to validate the data of the CLOB column before insert/update.

Answer 1

Since its a clob column you need to check the condition as

dbms_lob.substr(:NEW.TEST_CLOB_VALUE,1,1) = 'A'

See demo:

CREATE OR REPLACE TRIGGER TR_Temp_Test101
   BEFORE INSERT OR UPDATE
   ON Temp_Test101
   FOR EACH ROW
DECLARE
BEGIN
   IF (dbms_lob.substr(:NEW.TEST_CLOB_VALUE,1,1) = 'A')
   THEN
      raise_application_error (-20010, 'Testing Successfull');
   END IF;

   raise_application_error (
      -20010,
      'Testing Failed:[' || :NEW.TEST_CLOB_VALUE || ']');
END TR_Temp_Test101;

Execution:

SQL> Insert into TEMP_TEST101 values(1,'A');
Insert into TEMP_TEST101 values(1,'A')
            *
ERROR at line 1:
ORA-20010: Testing Successfull
ORA-06512: at "TR_TEMP_TEST101", line 5
ORA-04088: error during execution of trigger 'TR_TEMP_TEST101'