varSQL := 'INSERT INTO TBL_EMAIL( MESSAGE)'||CHR(10); varSQL := varSQL || 'VALUES (tblEmail_SEQ.NEXTVAL,'||varT||')'; execute immediate varSql;
VarT should be declared as CLOB and you won't even hit the limit of 32000 chars for PL/SQL strings anymore.
INSERT INTO TBL_EMAIL( MESSAGE) VALUES (tblEmail_SEQ.NEXTVAL, varT);
with the simpler
varT := P_MSG; varSQL := 'INSERT INTO TBL_EMAIL( MESSAGE)'||CHR(10); varSQL := varSQL || 'VALUES (tblEmail_SEQ.NEXTVAL,'||varT||')'; EXECUTE IMMEDIATE varSQL;
Secondly, you want to use the DBMS_LOB.COMPARE function to determine whether the contents of the LOBs match. So replace
INSERT INTO tbl_email( <<primary key column>>, message ) VALUES( tblEmail_Seq.nextval, p_msg );
SELECT 1 INTO varTemp FROM tblEmail WHERE TO_CHAR(MESSAGE) = P_MSG
Of course, it is going to be relatively expensive to run this query every time you insert a new message unless the table is always going to be very small, which seems unlikely. It also doesn't prevent duplicate entries if there are multiple threads executing at the same time.
SELECT 1 INTO varTemp FROM tblEmail WHERE dbms_lob.compare( message, p_msg ) = 0
Of course, you'd also want to use bind variables for performance and security.
EXECUTE IMMEDIATE 'INSERT INTO some_table( clob_column ) VALUES( :1 )' USING p_clob_parameter