Hi
Sorry, But I searche in forum and Can not to find some solution that work fine in my case.
I musto to build queries for to insert or update in other table, but in the table (I am reading) there is a column Clob and when I tried to concat show me error, for some records:
ERROR: ORA-01489: result of string concatenation is too long
The name of my column with CLOB type is
value
I already to use substr, but no work
My query is as:
SELECT'MERGE INTO OAGLBGF.TMP_FORM_VALUE A USING'|| CHR(10)
||'(SELECT ' || CHR(39)|| T1.formid || CHR(39)||' AS "FORMID",'||CHR(10)
|| CHR(39)||substr(TRIM(replace(dbms_lob.substr(value, 4000, 1),'-')),1,2000)||CHR(39) || ' AS "' || UPPER(T1.ATTRIBUTE_NAME)||'"' ||CHR(10)
||' FROM DUAL) B ' || CHR(10)
||' ON (A.FORMID = B.FORMID)'||CHR(10)
||' WHEN NOT MATCHED THEN'||CHR(10)
||' INSERT ( '||' FORMID,'||UPPER(T1.ATTRIBUTE_NAME)||')'||chr(10)
||' VALUES ( B.FORMID,'|| CHR(39)||substr(TRIM(replace(dbms_lob.substr(value, 4000, 1),'-')),1,2000)||CHR(39)||')'||chr(10)
|| ' WHEN MATCHED THEN '||chr(10)
|| ' UPDATE SET ' || chr(10)
|| ' A.'||UPPER(T1.ATTRIBUTE_NAME)||' = '||'B.'||UPPER(T1.ATTRIBUTE_NAME)||';', rownum
FROM form_value_text T1
WHERE T1.FORMTYPE = 42
AND instr(t1.attribute_name,'ARRAY')=0
and TRIM(dbms_lob.substr(value, 4000, 1)) is not null
and t1.attribute_name != 'FA_HISTORICO'
and formid > '113362'
-- and rownum<8000
order by t1.formid, t1.attribute_name
The Output work fine , but in some row, show me error, look example
spool c:\myquery.sql
...
MERGE INTO OAGLBGF.TMP_FORM_VALUE A USING
(SELECT '113362' AS "FORMID",
'0.00' AS "C_JUROS"
FROM DUAL) B
ON (A.FORMID = B.FORMID)
WHEN NOT MATCHED THEN
INSERT ( FORMID,C_JUROS)
VALUES ( B.FORMID,'0.00')
WHEN MATCHED THEN
UPDATE SET
A.C_JUROS = B.C_JUROS;
MERGE INTO OAGLBGF.TMP_FORM_VALUE A USING
(SELECT '113362' AS "FORMID",
'0.00' AS "C_MULTA"
FROM DUAL) B
ON (A.FORMID = B.FORMID)
WHEN NOT MATCHED THEN
INSERT ( FORMID,C_MULTA)
VALUES ( B.FORMID,'0.00')
WHEN MATCHED THEN
UPDATE SET
A.C_MULTA = B.C_MULTA;
ERROR:
ORA-01489: result of string concatenation is too long
Using ORACLE 9.2.08
Edited by: muttleychess on Sep 3, 2012 8:55 AM