0

I am new to Oracle with PL SQL since I am a SQL Server guy. The table has a long column with text greater than 4000 characters. I am trying to do a select test to find the record and got an SQL Developer response of string literal too long, use bind variable

I looked up some posting and tried the following

declare
  tempCLOB CLOB;
  old_clob_content CLOB:= 'very long text';
begin
  select ID INTO tempCLOB
  from database.table
  where dbms_lob.compare(CLOB_FIELD, old_clob_content) = 0;
end;

When I run it, I get the following error

ORA-06550 string literal too long

With SQL Server, I did not need to add the INTO, just confused with the error statement.

Thanks

user1250264
  • 897
  • 1
  • 21
  • 54
  • Does this answer your question? [Querying oracle clob column](https://stackoverflow.com/questions/18250014/querying-oracle-clob-column) – thatjeffsmith Nov 25 '19 at 16:34

1 Answers1

2

This is a bit long for a comment.

In a table, a varchar2() is limited to 4000 characters. Hence, there is no reason to compare the value to a varchar2() that exceeds that number. There will be no equality.

That leads me to suspect that testColumnName is really a CLOB (as the error suggests). In that case, you should compare it to a CLOB, not a varchar2().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 3
    Also if you're comparing CLOBs, `=` won't work - use DBMS_LOB.COMPARE to test equality, or LIKE / INSTR for partial matches. – kfinity Nov 25 '19 at 16:33