Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

String Literal too long / CLOB Issue

user989357Feb 19 2009 — edited Feb 19 2009
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;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 19 2009
Added on Feb 19 2009
6 comments
6,363 views