4 Replies Latest reply on Feb 15, 2011 2:47 PM by Helios-GunesEROL

    frm-40502: oracle error: unable to read list of values

    user250934
      Hi All,

      I am personalizing the assignment form, where we need to restrict the JOB LOV based on Organization value.

      In Forms Personalization we are creating the record group from query and attaching to Job field.

      The query is,

      SELECT DISTINCT j.NAME, DECODE (1, 2, 1, NULL) c_valid_job_flag,
      j.job_id job_id
      FROM per_jobs_v j
      WHERE j.business_group_id + 0 = :CTL_GLOBALS.BUSINESS_GROUP_ID
      AND j.date_from <= :CTL_GLOBALS.SESSION_DATE
      AND ( (j.date_to IS NULL)
      OR (j.date_to >= :CTL_GLOBALS.SESSION_DATE)
      )
      ORDER BY j.NAME

      When we use this query, we are getting the error "frm-40502: oracle error: unable to read list of values"

      If i replace the bind variable with values we are not getting the error and its working fine.

      Replace query:

      SELECT DISTINCT j.NAME, DECODE (1, 2, 1, NULL) c_valid_job_flag,
      j.job_id job_id
      FROM per_jobs_v j
      WHERE j.business_group_id + 0 = 202
      AND j.date_from <= TRUNC(SYSDATE)
      AND ( (j.date_to IS NULL)
      OR (j.date_to >= TRUNC(SYSDATE))
      )
      ORDER BY j.NAME


      how to use bind variables (Block.field) here? We are getting this error only when using the bind variable in the query.

      Please share your ideas.

      Thanks.