CODEWITHSUNDEEP
sqloracle-databasewhere-clausetruncateclob
Mathew Linton
Mathew Linton

Reputation: 33

SQL truncate clob field

In Oracle I have a query similar to:

CREATE TABLE my_table
(
    my_field CLOB,
    my_field2 VARCHAR2(50 CHAR),
    my_field3 VARCHAR2(5 CHAR),
)

and I would like to run this query:

select count(*)
from my_table
where my_field = substr('...',4000,1)

When I run the query, I get an error:

  1. 00000 - "string literal too long"
    *Cause: The string literal is longer than 4000 characters.
    *Action: Use a string literal of at most 4000 characters.
    Longer values may only be entered using bind variables.

Please advice!

Upvotes: 2

Views: 2547

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522499

As the error message says, Oracle is rolling over because you are trying to use a string literal with more than 4000 bytes in it. One alternative is to use bind variables (as suggested):

VARIABLE my_field CLOB;
EXEC :my_field := 'some really long text here longer than 4000 bytes';

SELECT COUNT(*)
FROM my_table
WHERE my_field = :my_field;

Upvotes: 1

Bhanu Yadav
Bhanu Yadav

Reputation: 169

You are using substr with 4000 character it may be possible that your character set is a multibyte character set due to which memory is more than 4k bytes. One more approach I can suggest is to use plsql varchar2 variable. It has a limit of 32767.

Declare
 my_field varchar2(32767) := <Long text>;
 v_count number;
begin
SELECT COUNT(*) into v_count FROM my_table
WHERE my_field = my_field;

end;
/

Upvotes: 0

Related Questions