ORA-01704: string literal too long

Tae hoje apanhei neste problema simples. =]

Então como minha decisão de criar este blog ser para catalogar conhecimento proprio decidi escrever aqui a solução para nunca mais esquecer.

Um campo long deveria permitir que você insira mais de 4000 caracteres, porem se vc tentar isto no sqlplus você irá receber este erro em tela.

ORA-01704: string literal too long

Conforme abaixo.

SQL> CREATE TABLE LANA.TESTE_LONG (
2 nr_linh NUMBER(5,0) NOT NULL,
3 ds_linh LONG NULL
4 );

Table created.

SQL> Insert into LANA.TESTE_LONG values ( 1,
2 '0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
3 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
4 ...
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
39 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');
'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
*
ERROR at line 2:
ORA-01704: string literal too long

SQL> SELECT COUNT(*) FROM LANA.TESTE_LONG;

COUNT(*)
----------
0


Para resolver este erro sugiro jogar o campo long para dentro de uma variavel e fazer o insert com a variavel conforme abaixo.


SQL> declare
2 ww_TEXTO LONG;
3
4 BEGIN
5
6 ww_texto :=
7 '0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
8 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
9 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
10
...
41 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
42 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
43 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000';
44 45
46
47
48 Insert into LANA.TESTE_LONG values (
49 2,
50 Ww_texto
51 );
52 end;
53 /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM LANA.TESTE_LONG;

COUNT(*)
----------
1

SQL>