Error when retrieving oracle clobs

[Former Member]

Here's the sql statement that we are running:

SELECT dbms_lob.substr(sql_statement,dbms_lob.getlength(sql_statement),1) sql_statement FROM query

WHERE query_tk = [Param.1]

If the sql_statement is > 4000 chars, we are getting this error:

Fatal Error

A SQL Error has occurred on query, ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 .

[Former Member]

Maria,

You are seeing this error because you used 'dbms_lob.substr'.

substr is not a function of DBMS_LOB package. It is a standard function. Use just substr. But getlength is a function of DBMS_LOB.

substr(string,startposition,length) where length is optional parameter.

The statement you are trying to write will return only the last character of the sql_statement column!!! Did you really mean the length 1?

John

[Former Member]

John,

Just so you know , substr (standalone) is used for varchar2 datatype and dbms_lob.substr() is used for CLOB types.

The basic difference between these two is that the parameters are reversed so for the CLOB it is dbms_lob.substr(CLOB,Length,offset) rather than (offset,Length).

[Former Member]

Do you see this behaviour only in xMII? How about executing the same from some ORACLE Query Analyzer.

After reading some Oracle Forum posts it seems that data type varchar2 has the 4000 character limit ,could it be that the substr function returns a varchar2 and for such cases you might have to explicitly convert the returned value.

[Former Member]

We are seeing this outside of xmii as well.

Here's the background. We originally tried to do a normal insert to the clob (thru xmii) but we got errors when inserting values of more than 4000 chars. So, we inserted the clob differently (code used below). That's when we started getting this new error. Is there another way to insert/retrieve clobs?

--INSERT "base" row if it does not already exist.

BEGIN

SELECT query_tk

INTO v_dummy

FROM query

WHERE query_tk = p_query_tk;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO query

(query_tk

,last_updated_timestamp

,sql_statement

)

VALUES

(p_query_tk

,SYSDATE

,'DUMMY');

END;

--Initialize buffer with data to be inserted

v_copy_length := LENGTH(p_sql_statement);

-- get LOB handle

SELECT sql_statement

INTO v_lobloc

FROM query

WHERE query_tk = p_query_tk

FOR UPDATE;

dbms_lob.write(v_lobloc,v_copy_length,1,p_sql_statement);

[Former Member]

Not sure.

Can you specify a Return type after dms_lob.substr()? Maybe that would help.

[Former Member]

Oracle (like many DB vendors) uses a very non-standard mechanism for storing/retrieving BLOBs and CLOBs via JDBC. As such, I would suspect you'll have many challenges trying to do so with xMII. You might be best off building a "helper servlet", web service, or xMII "action", that xMII can invoke which would deal with the load/save operations.

[Former Member]

The issue here seems to be that the SQL statement itself is failing (xMII or no xMII). In that case I am not sure how even the options suggested by you will work.

But yes if there's a way to standardize on the DB layer itself, then a custom action/webservice would be a better way of going about this.