single-row subquery returns more than one row with multiple OR

I got this error while using multiple looped OR : single-row subquery returns more than one row,

The error only occurs via postman (maybe related to hibernate), the query works in oracle sql develoer

My request :

WITH SPRM_TABLE AS (
  SELECT 
    DISTINCT P.SPRM_KEY, 
    P.SPRM_PARENT_KEY AS PARENTS, 
    P.CPRM_KEY, 
    P.SPRM_PARENT_KEY 
  FROM 
    S4C_SUB_PERIMETER P 
    INNER JOIN S4C_CRITERIA CRI ON CRI.SPRM_KEY = P.SPRM_KEY 
  WHERE 
    (
      (
        CRI.CRI_TYPE = 'OFFER' 
        AND CRI.CRI_VALUE = 'BUSINESS_ETHERNET'
      )
    ) 
    or (
      (
        P.SPRM_PARENT_KEY IS NULL 
        OR (
          CRI.CRI_TYPE IN (
            'SATIN_ACCOUNT', 'SATIN_SUBACCOUNT', 
            'IDENT_RCE', 'ICO1'
          ) 
          AND (
            SELECT 
              CHP.CRI_KEY_PARENT 
            FROM 
              S4C_CRITERIA_HERITANCE CHP 
            WHERE 
              CHP.CRI_KEY_CHILD = CRI.CRI_KEY
          ) IN (
            SELECT 
              CBB.CRI_KEY 
            FROM 
              S4C_CRITERIA CBB 
            WHERE 
              CBB.SPRM_KEY = P.SPRM_PARENT_KEY
          )
        )
      ) 
      AND (
        (
          CRI.CRI_TYPE = 'IDENT' 
          AND CRI.CRI_VALUE = '00010525'
        )
      )
    )
) 
SELECT 
  DISTINCT CPRM.CPRM_KEY AS PRM_ID, 
  'CLIENT' AS PRM_TYPE 
FROM 
  CPRM_TABLE CPRM 
UNION ALL 
SELECT 
  DISTINCT PRM.SPRM_KEY AS PRM_ID, 
  'SUB' AS PRM_TYPE 
FROM 
  SPRM_TABLE PRM START WITH PRM.SPRM_PARENT_KEY IS NULL 
  AND PRM.CPRM_KEY IN (
    SELECT 
      CLI_PRM.CPRM_KEY 
    FROM 
      CPRM_TABLE CLI_PRM
  ) CONNECT BY PRIOR PRM.SPRM_KEY = PRM.SPRM_PARENT_KEY

If Someone could help please? and if someone could explain why does this works in Oracle sql develper but not in java code with hibernate?

You can't do this:

      AND (
            SELECT 
              CHP.CRI_KEY_PARENT 
            FROM 
              S4C_CRITERIA_HERITANCE CHP 
            WHERE 
              CHP.CRI_KEY_CHILD = CRI.CRI_KEY
          ) IN (
            SELECT 
              CBB.CRI_KEY 
            FROM 
              S4C_CRITERIA CBB 
            WHERE 
              CBB.SPRM_KEY = P.SPRM_PARENT_KEY
          )

The left side of the IN operator expects a scalar, and you are providing a subquery that is apparently returning multiple rows.

You will need to combine these two queries into one:

  AND EXISTS (
        SELECT *
          FROM S4C_CRITERIA_HERITANCE CHP ,
               S4C_CRITERIA CBB
         WHERE CHP.CRI_KEY_CHILD = CRI.CRI_KEY
           AND CBB.CRI_KEY = CHP.CRI_KEY_PARENT 
           AND CBB.SPRM_KEY = P.SPRM_PARENT_KEY
        )