Introduction:
This blog discusses on one of the causes for the issue “Unable to set binding parameter that does not exist ”
Cause of the Issue:
In OAF , our eBS Instance is 12.2.8 and Jdeveloper version is 10.1.3
While using bind variable in the query in the “Named Binding Style” , we got this Error.
Root cause is that , for this particular version of OAF and eBS , even though Configuration allows any binding Style, Runtime Error occurs when using Named Binding.
How do we solve:
We used Oracle Positional Style using query in “With t as ” Query Style and this solved the issue.
Example
WITH t AS (
SELECT
:1 Req_Days
FROM
dual
)
SELECT distinct fcr.request_id,
fcr.parent_request_id,
fcr.priority_request_id,
fcr.requested_by,
fcr.description,
fcp.user_concurrent_program_name,
to_char(fcr.actual_start_date,’DD-MON-YYYY HH24:MI:SS’) start_time,
to_char(fcr.actual_completion_date,’DD-MON-YYYY HH24:MI:SS’) completion_time,
decode(fcr.status_code, ‘D’, ‘Cancelled’, ‘E’, ‘Error’,
‘G’, ‘Warning’, ‘H’, ‘On Hold’, ‘T’,
‘Terminating’, ‘M’, ‘No Manager’, ‘X’, ‘Terminated’,
‘C’, ‘Success’, fcr.status_code) “Status”,
a.user_request_set_name,
info.Req_Days Days_Name,
fcr.logfile_name,
substr(logfile_name,instr(logfile_name,’/log/’)+5) logfile_with_extn,
fcr.outfile_name,
substr(outfile_name,instr(outfile_name,’/out/’)+5)
outfile_with_extn,
fcr.output_file_type
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_vl fcp,
apps.fnd_responsibility_vl resp,
apps.fnd_user usr,
t info,
(SELECT fcr.request_id,rset.user_request_set_name
FROM apps.fnd_concurrent_requests fcr, apps.fnd_request_sets_vl rset
WHERE fcr.argument1 = TO_CHAR (rset.application_id)
AND fcr.argument2 = TO_CHAR (rset.request_set_id)
AND UPPER (rset.user_request_set_name) LIKE
‘OPM%PPM’
) a
WHERE ((fcr.priority_request_id = a.request_id) or (fcr.request_id in ((SELECT
fcr1.request_id
FROM apps.fnd_concurrent_requests fcr1
WHERE 1=1
START WITH fcr1.request_id = a.request_id
CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) ) and fcr.priority_request_id <> a.request_id))
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.responsibility_id = resp.responsibility_id
AND fcr.responsibility_application_id = resp.application_id
AND fcr.requested_by = usr.user_id
AND (fcp.user_concurrent_program_name) NOT in (‘Report Set’,’Request Set Stage’)
AND (SYSDATE-ACTUAL_START_DATE) < info.Req_Days
order by fcr.request_id desc