Home » SQL & PL/SQL » SQL & PL/SQL » How to Read CLOB
How to Read CLOB [message #232888] Mon, 23 April 2007 17:06 Go to next message
viral_ht
Messages: 1
Registered: April 2007
Junior Member
Hello,

I am trying to read CLOB into a variable and send that variable as part of the email body. But it send an email with blank body.

I would really appreciate if somebody can show me or point me into the direction where I can get the answer

I have table T1

Create Table T1
(ID Number,COMMENTS CLOB,TextArea Varchar2(500);

### Following PL/SQL Block sends an email as expected
Declare
vComment Varchar2(200);
Begin
Select substr(Comments,0,200) into vComment from T1 where ID = 552;

send_mail('ChangeRequest','user@domain.com',123,vComment);
End;

####### But, when I try to use a trigger, it only sends the email with Blank body


CREATE OR REPLACE TRIGGER TRIG_T1
BEFORE UPDATE
ON T1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE

tmpVar NUMBER;
vComment Varchar2(200);
tmpComment Varchar2(200);



BEGIN

vComment := substr(:NEW.COMMENTS,0,200);

send_mail('change-ok','user@domain.com','OK', :NEW.COMMENTS);

END TRIG_T1;

/




The trigger does not throw any exception, it just would not send the comments in the email body part.





Re: How to Read CLOB [message #260423 is a reply to message #232888] Sun, 19 August 2007 13:49 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I have some concerns in your code.
1. Have you written
WHEN OTHERS THEN
    NULL;
in the procedure send_mail?
2. I didn't understand why you have declared 3 variables in your trigger?
Quote:
tmpVar NUMBER;
vComment Varchar2(200);
tmpComment Varchar2(200);
Did you want to pass vComment to send_mail?
3. Also you have used
Quote:
vComment := substr(:NEW.COMMENTS,0,200);
I hope you know that if you give zero as the starting position it will treat that as 1, which is the first position of the string unlike php,C,....

By
Vamsi

[Updated on: Sun, 19 August 2007 14:25]

Report message to a moderator

Previous Topic: Insert rows with no duplicate
Next Topic: SQL Query for Last Comment
Goto Forum:
  


Current Time: Sat Dec 10 07:00:14 CST 2016

Total time taken to generate the page: 0.10200 seconds