ORA-01704- string literal too long : on an insert from a record just extracted with SQL Nav

SQL Navigator
1

Hi

I often have to copy record from one database to another. Therefore I use the CTRL+E (export data) Table INSERTs feature.

In this case, I’ve a record containing a clob field with dynamic code in it. When I try to insert the record on my other database, I get this error : ORA-01704: string literal too long

The whole extracted record makes 6686 bytes, it’s not really big. I don’t understand why I get this error. Here is the record :

INSERT INTO prg_tests (NUM_ERREUR,DAT_INSERT,DAT_UPDATE,MAT_INSERT,MAT_UPDATE,ACTIF,BLOCKING,COD_PAYS,PLDA,NCTS,NOTIF,GLOB,ACC4,EMCS,DELTA_C,DELTA_D,LIB_TEST,PRG_TEST,CASES_TESTEES,COD_ERR_DOUA,DAT_INACTIF,MAT_INACTIF,RAISON_INACTIF,REMARQUE)

VALUES(11786,TO_DATE(‘18/12/2015 10:22:45’,‘DD/MM/RRRR HH24:MI:SS’),TO_DATE(‘21/12/2015 13:05:03’,‘DD/MM/RRRR HH24:MI:SS’),99999,99998,1,1,‘BE’,‘Y’,‘N’,‘N’,‘Y’,‘N’,‘N’,‘N’,‘N’,‘Un de ces documents #2 est obligatoire pour taric #1’,'DECLARE

vn_count PLS_INTEGER := 0;

vn_rec_d_docu PLS_INTEGER := 1;

vn_rec_d_docu_new PLS_INTEGER := 1;

vs_cond_doc VARCHAR2(50);

CURSOR c_doc(

pn_num_lg_artc IN NUMBER

, ps_c33_03 IN VARCHAR2

, ps_c33_04 IN VARCHAR2

, ps_c33_05 IN VARCHAR2

, ps_c33_06 IN VARCHAR2

, ps_c33_07 IN VARCHAR2

, ps_c39_01 IN VARCHAR2) IS

SELECT DISTINCT cod_document

FROM TABLE(pk_read_taric.f_read_taric)

WHERE num_lg_artc = pn_num_lg_artc

AND NVL(cod_taric_addit, ‘’’’) = NVL(ps_c33_03, ‘’’’) – Code additionnel EUR 1

AND NVL(cod_quota, ‘’’’) = NVL(ps_c39_01, ‘’’’) – Code quotas

AND NVL(cod_meas_cond, ‘’’’) IN (’‘B’’, ‘‘H’’, ‘‘Q’’, ‘‘Y’’) – Uniquement ceux qui réclament un document

AND NVL(cod_meas_act, ‘’’’) NOT IN (’‘27’’, ‘‘36’’) – Pour les réductions de droits/restitutions à l’'exportation

AND cod_document IS NOT NULL

UNION

SELECT DISTINCT cod_document

FROM TABLE(pk_read_taric.f_read_taric)

WHERE num_lg_artc = pn_num_lg_artc

AND NVL(cod_taric_addit, ‘’’’) = NVL(ps_c33_04, ‘’’’) – Code additionnel EUR 2

AND NVL(cod_quota, ‘’’’) = NVL(ps_c39_01, ‘’’’) – Code quotas

AND NVL(cod_meas_cond, ‘’’’) IN (’‘B’’, ‘‘H’’, ‘‘Q’’, ‘‘Y’’) – Uniquement ceux qui réclament un document

AND NVL(cod_meas_act, ‘’’’) NOT IN (’‘27’’, ‘‘36’’) – Pour les réductions de droits/restitutions à l’'exportation

AND cod_document IS NOT NULL – Les codes documents exigés

UNION

SELECT DISTINCT cod_document

FROM TABLE(pk_read_taric.f_read_taric)

WHERE num_lg_artc = pn_num_lg_artc

AND NVL(cod_taric_addit, ‘’’’) = NVL(ps_c33_05, ‘’’’) – Code additionnel BE 1

AND NVL(cod_quota, ‘’’’) = NVL(ps_c39_01, ‘’’’) – Code quotas

AND NVL(cod_meas_cond, ‘’’’) IN (’‘B’’, ‘‘H’’, ‘‘Q’’, ‘‘Y’’) – Uniquement ceux qui réclament un document

AND NVL(cod_meas_act, ‘’’’) NOT IN (’‘27’’, ‘‘36’’) – Pour les réductions de droits/restitutions à l’'exportation

AND cod_document IS NOT NULL – Les codes documents exigés

UNION

SELECT DISTINCT cod_document

FROM TABLE(pk_read_taric.f_read_taric)

WHERE num_lg_artc = pn_num_lg_artc

AND NVL(cod_taric_addit, ‘’’’) = NVL(ps_c33_06, ‘’’’) – Code additionnel BE 2

AND NVL(cod_quota, ‘’’’) = NVL(ps_c39_01, ‘’’’) – Code quotas

AND NVL(cod_meas_cond, ‘’’’) IN (’‘B’’, ‘‘H’’, ‘‘Q’’, ‘‘Y’’) – Uniquement ceux qui réclament un document

AND NVL(cod_meas_act, ‘’’’) NOT IN (’‘27’’, ‘‘36’’) – Pour les réductions de droits/restitutions à l’'exportation

AND cod_document IS NOT NULL – Les codes documents exigés

UNION

SELECT DISTINCT cod_document

FROM TABLE(pk_read_taric.f_read_taric)

WHERE num_lg_artc = pn_num_lg_artc

AND NVL(cod_taric_addit, ‘’’’) = NVL(ps_c33_07, ‘’’’) – Code additionnel BE 3

AND NVL(cod_quota, ‘’’’) = NVL(ps_c39_01, ‘’’’) – Code quotas

AND NVL(cod_meas_cond, ‘’’’) IN (’‘B’’, ‘‘H’’, ‘‘Q’’, ‘‘Y’’) – Uniquement ceux qui réclament un document

AND NVL(cod_meas_act, ‘’’’) NOT IN (’‘27’’, ‘‘36’’) – Pour les réductions de droits/restitutions à l’'exportation

AND cod_document IS NOT NULL; – Les codes documents exigés

BEGIN

:val1 := ‘‘OK’’;

:val2 := ‘‘000’’;

IF pk_decl_var.vt_taric.COUNT > 0 THEN

FOR i IN 1 … pk_decl_var.vn_rec_d_artc LOOP

vn_rec_d_docu := vn_rec_d_docu_new;

vn_count := 0;

FOR r_doc

IN c_doc(

pk_decl_var.vt_d_artc(i).num_lg_artc

, pk_decl_var.vt_d_artc(i).c33_03

, pk_decl_var.vt_d_artc(i).c33_04

, pk_decl_var.vt_d_artc(i).c33_05

, pk_decl_var.vt_d_artc(i).c33_06

, pk_decl_var.vt_d_artc(i).c33_07

, pk_decl_var.vt_d_artc(i).c39_01) LOOP

vn_count := vn_count + 1;

IF vn_count = 1 THEN

vs_cond_doc := r_doc.cod_document;

ELSE

vs_cond_doc := vs_cond_doc || ‘’,’’ || r_doc.cod_document;

END IF;

END LOOP;

IF vn_count != 0 THEN

vn_count := 0;

FOR j IN vn_rec_d_docu … pk_decl_var.vn_rec_d_docu LOOP

IF pk_decl_var.vt_d_docu(j).num_lg_artc = pk_decl_var.vt_d_artc(i).num_lg_artc THEN

IF vs_cond_doc LIKE ‘’%’’ || pk_decl_var.vt_d_docu(j).c44_21 || ‘’%’’ THEN

vn_count := vn_count + 1;

END IF;

ELSIF pk_decl_var.vt_d_docu(j).num_lg_artc > pk_decl_var.vt_d_artc(i).num_lg_artc THEN

vn_rec_d_docu_new := j;

EXIT;

END IF;

END LOOP;

IF vn_count = 0 THEN

:val1 := ‘‘ERR’’ || ‘’|’’ || pk_decl_var.vt_d_artc(i).c33_01 || pk_decl_var.vt_d_artc(i).c33_02 || ‘’|’’ || vs_cond_doc;

:val2 := pk_decl_var.vt_d_artc(i).num_lg_artc;

EXIT;

END IF;

EXIT WHEN :val1 != ‘‘OK’’;

END IF;

END LOOP;

END IF;

EXCEPTION

WHEN OTHERS THEN

:val1 := SQLCODE || ‘’ - ‘’ || SQLERRM;

END;’,‘DOC_TARIC’,‘TARIC CEE’,NULL,NULL,NULL,NULL);

Regards

Martin

2

I wouldn’t trust these exports anyway :slight_smile: There are so many things where something could go wrong. Like NLS settings, timestamps, formatting masks etc.

3

Hi Martin

Never got a problem, until now. We use this feature very often.

Regards

The other Martin :wink:

4

ORA-01704 is raised because the length of a string is limited to 4000 by *SQL *, i.e., what you have inside the double quotes can only be up to 4000 characters. It seems we normally need to construct the long string chunk by chunk if we need to use them in a SQL. Quick sum up: table insert is not a good way to handle CLOBs.

5

Many thanks Vincent.