String Literal too long / CLOB Issue
I have a table with a "Clob" column called Message. This is where I store the message of an email. We have an internal email app. What is happening is I can't insert anything bigger then around 3990 or something around there. In my procedure I have the parameter coming in as clob and I use a TO_CHAR() around the table column and my parameter or my procedure gives me this error
PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB
But when it runs and I put in a 9000 character message it says
Oracle.DataAccess.Client.OracleException was unhandled by user code
Message="ORA-01704: string literal too long
I'm using C# with
Oracle.DataAccess.Client.OracleConnection to create my oracle connection and Oracle 10g.
I'm calling this procedure from my app. By doing this am I causing it to only hold 4000 characters?
Here is a scaled down version of my code on just that column
CREATE OR REPLACE PROCEDURE EMAILINS (
P_MSG IN CLOB
AS
varT VARCHAR2(10000);
varSQL VARCHAR2(20000);
varTemp NUMBER;
BEGIN
-- SEE IF STRING EXISTS
SELECT 1 INTO varTemp
FROM tblEmail
WHERE TO_CHAR(MESSAGE) = P_MSG
;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
varSQL := varT||CHR(10)||'***Multiple Rows Exist in Table tblEmail***';
DBMS_OUTPUT.PUT_LINE(varSQL);
WHEN NO_DATA_FOUND THEN
varT := P_MSG;
varSQL := 'INSERT INTO TBL_EMAIL( MESSAGE)'||CHR(10);
varSQL := varSQL || 'VALUES (tblEmail_SEQ.NEXTVAL,'||varT||')';
EXECUTE IMMEDIATE varSQL;
END EMAILINS;