Hello I have one procedure like
PROCEDURE PR_SEARCH(P_FABRIC IN VARCHAR2, P_ORG IN NUMBER,P_POS IN VARCHAR2,P_SICIL IN clob,P_EXISTS IN clob, PRC OUT SYS_REFCURSOR) IS BEGIN
OPEN PRC FOR
SELECT SICIL_KOD AS SICIL,ad ||' '||soyad as NAME_SURNAME FROM
BMS.GEN_PERSONEL GP WHERE SICIL_KOD>10000 AND GP.PERSONEL_ALT_ALAN IN ('IZ01','IZ02','IZ03','IZ04') AND
(P_FABRIC='-1' OR GP.PERSONEL_ALT_ALAN=P_FABRIC)
AND
(P_ORG=-1 OR GP.SAP_ORG_KOD=P_ORG)
AND
(P_POS='-1' OR GP.POZISYON_ACIKLAMA=P_POS)
AND
(P_SICIL IS NULL OR GP.SICIL_KOD IN (select regexp_substr(to_char(P_SICIL),'[^,]+', 1, level) AS DEPTS from dual connect by regexp_substr(to_char(P_SICIL), '[^,]+', 1, level) is not null))
AND
(P_EXISTS IS NULL OR GP.SICIL_KOD NOT IN (select regexp_substr(to_char(P_EXISTS),'[^,]+', 1, level) AS DEPTS from dual connect by regexp_substr(to_char(P_EXISTS), '[^,]+', 1, level) is not null))
ORDER BY SICIL_KOD
;
END;
my problem is that P_SICIL or P_EXISTS can be too long so all length can be more then 4000 characters. when I call procedure it gives below error
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 14103, maximum: 4000)
if I dont use to_char for clobs then I take error below
ORA-00932: inconsistent datatypes: expected – got CLOB ORA-06512: at “CARDREADER.PA_KART_READER”, line 285 ORA-06512: at line 2
if I dont use clob and use varchar2 as parameter type then I got error
ORA-01460: unimplemented or unreasonable conversion requested ORA-06512: at “CARDREADER.PA_KART_READER”, line 284 ORA-06512: at line 2
How can I solve this problem in oracle ?
Thanks in advance
Advertisement
Answer
The problem is that you are attempting to convert your CLOBs to VARCHAR2s when using the TO_CHAR function. REGEXP_SUBSTR can take CLOBs as the input parameter so try not casting the CLOBs, instead cast the result of the REGEXP_SUBSTR to be compared with SICIL_KOD.
PROCEDURE PR_SEARCH (P_FABRIC IN VARCHAR2,
P_ORG IN NUMBER,
P_POS IN VARCHAR2,
P_SICIL IN CLOB,
P_EXISTS IN CLOB,
PRC OUT SYS_REFCURSOR)
IS
BEGIN
OPEN PRC FOR SELECT SICIL_KOD AS SICIL, ad || ' ' || soyad AS NAME_SURNAME
FROM BMS.GEN_PERSONEL GP
WHERE SICIL_KOD > 10000
AND GP.PERSONEL_ALT_ALAN IN ('IZ01',
'IZ02',
'IZ03',
'IZ04')
AND (P_FABRIC = '-1' OR GP.PERSONEL_ALT_ALAN = P_FABRIC)
AND (P_ORG = -1 OR GP.SAP_ORG_KOD = P_ORG)
AND (P_POS = '-1' OR GP.POZISYON_ACIKLAMA = P_POS)
AND ( P_SICIL IS NULL
OR GP.SICIL_KOD IN ( SELECT TO_CHAR (REGEXP_SUBSTR (P_SICIL,
'[^,]+',
1,
LEVEL)) AS DEPTS
FROM DUAL
CONNECT BY REGEXP_SUBSTR (P_SICIL,
'[^,]+',
1,
LEVEL)
IS NOT NULL))
AND ( P_EXISTS IS NULL
OR GP.SICIL_KOD NOT IN ( SELECT TO_CHAR (REGEXP_SUBSTR (P_EXISTS,
'[^,]+',
1,
LEVEL)) AS DEPTS
FROM DUAL
CONNECT BY REGEXP_SUBSTR (P_EXISTS,
'[^,]+',
1,
LEVEL)
IS NOT NULL))
ORDER BY SICIL_KOD;
END;
END;