Oracle Procedure call too long string

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

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;