0

I'm trying to insert a record into an Oracle database using PL/SQL Developer.

I have a CLOB datatype in my table and the XML that I want to save has around about 60000 characters.

When I directly try to do it using an insert statement, it gives me the ORA-01704 error. So I read on forums and tried to do it using bind variables, but I am still getting the 'string literal too long' message.

My bind variable code is:

declare
vClobVal varchar2(32767) := 'String of 60000 characters';
update table_name set column where clause.
end;

I understand that varchar2(32767) has limit of 32767 characters. But what should I do when I have to insert 60000 characters? I have also tried with a CLOB variable:

declare
vClobVal clob := 'String of 60000 characters';
update table_name set column where clause.
end;

This also gives me the same error.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
V Joe
  • 271
  • 1
  • 5
  • 23
  • `vClobVal` isn't really a bind variable, it's a PL/SQL local variable (though it is used as a bind variable in your insert, sort of, by he parser). The CLOB version is still trying to have a 60000 character string literal - the bit between the single quotes. You don't get as far as trying to actually create a CLOB with that literal as the literal itself can't be created. You would have to build your CLOB up incrementally using shorter strings, which isn't always ideal. Where is your XML actually coming from? – Alex Poole Sep 10 '15 at 12:33
  • Pretty much a duplicate of [this](http://stackoverflow.com/q/20613348/266304), and the answer to that would work here too, with `dbms_lob.append`. But if the XML is in a file there are better ways to load it. – Alex Poole Sep 10 '15 at 12:45

1 Answers1

4

The problem is that string literals are limited to less than 60000 characters.

declare vClobVal clob := 'String of 60000 characters';

No matter that you declare vClobVal as CLOB, the literal after := is still a string (that you want converted to clob).

As far as I know, Oracle knows no CLOB literals such as CLOB'60000 characters...'. So your only bet is to use smaller strings and add them to the CLOB one by one:

declare
  vstr_part1  varchar2(32767) := 'first 300000 characters';
  vstr_part2  varchar2(32767) := 'next 300000 characters';
  vClobVal    clob;
begin
  vClobVal := vstr_part1;
  vClobVal := vClobVal || vstr_part2;

  ...
end;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • this answer has worked. But i was looking for something else. The only problem here is for every insert statement i will have to manually count the number of characters to be put in var1 and var2 . Guess,if there is no easy way out, i can live with it. – V Joe Sep 10 '15 at 12:55
  • 1
    @Alex Poole: There is no intermediate varchar2 here. There is a CLOB that gets assigned a varchar2, so the varchar2 is converted to clob and stored in that clob variable. Then a seconds varchar2 gets added to the clob. Again the varchar2 gets converted to a clob and then this clob gets appended to the clob variable. No intermediate varchar2 hence, but intermediate clobs. – Thorsten Kettner Sep 10 '15 at 13:04
  • Yes, sorry, my mistake; confusing concatenating two long varchars into a CLOB, which isn't what you're doing. – Alex Poole Sep 10 '15 at 13:08