ORA-01745:invalid host/bind variable name - Extra colon showing in query??
Hi,
I have a tabular form region based on the following query:
select pcd.project_comm_id,
NVL(pcd.project_id, :P6_PROJECT_ID) project_id,
nvl(pcd.visible_flag,'N') visible_flag,
pcd.comment_text,
nvl(pcd.created_by, :F_EMPLOYEE) created_by,
nvl(pcd.creation_date,sysdate) creation_date,
nvl(pcd.comment_type_id, :P6_CMT_TYPE) comment_type_id,
nvl(test_plan_id, :P6_TEST_PLAN_ID) test_plan_id
from dp_project_comm_dtl pcd
where ( ((pcd.project_id = :P6_PROJECT_ID) AND (pcd.test_plan_id = :P6_TEST_PLAN_ID)) OR
((pcd.project_id = :P6_PROJECT_ID) OR (pcd.test_plan_id = :P6_TEST_PLAN_ID)))
and pcd.comment_type_id = :P6_CMT_TYPE
and nvl(pcd.visible_flag, 'Y') = decode(:P0_VIEW, '0', 'Y', pcd.visible_flag)
order by pcd.creation_date desc NULLS LAST
I have an AddRow process, created from the APEX wizard. When I click the 'Add' button that calls this process, it returns the error ORA-01745:invalid host/bind variable name.
After pulling out my hair for about 2 hours, I noticed during the debug that the query that is being ran has inserted in extra :'s. (In the NULL UNION stmt that gets generated for the blank row...but only for 2 of the variables) Has anyone ever seen this? What am I doing wrong? Is there a way to fix?
From the Debug stmt:
0.08: add row query: select "PROJECT_COMM_ID" "PROJECT_COMM_ID", "PROJECT_ID" "PROJECT_ID", "VISIBLE_FLAG" "VISIBLE_FLAG", "COMMENT_TEXT" "COMMENT_TEXT", "CREATED_BY" "CREATED_BY", "CREATION_DATE" "CREATION_DATE", "COMMENT_TYPE_ID" "COMMENT_TYPE_ID", "TEST_PLAN_ID" "TEST_PLAN_ID" from (select pcd.project_comm_id, NVL(pcd.project_id, :P6_PROJECT_ID) project_id, nvl(pcd.visible_flag,'N') visible_flag, pcd.comment_text, nvl(pcd.created_by, :F_EMPLOYEE) created_by, nvl(pcd.creation_date,sysdate) creation_date, nvl(pcd.comment_type_id, :P6_CMT_TYPE) comment_type_id, nvl(test_plan_id, :P6_TEST_PLAN_ID) test_plan_id from dp_project_comm_dtl pcd where ( ((pcd.project_id = :P6_PROJECT_ID) AND (pcd.test_plan_id = :P6_TEST_PLAN_ID)) OR ((pcd.project_id = :P6_PROJECT_ID) OR (pcd.test_plan_id = :P6_TEST_PLAN_ID))) and pcd.comment_type_id = :P6_CMT_TYPE and nvl(pcd.visible_flag, 'Y') = decode(:P0_VIEW, '0', 'Y', pcd.visible_flag) order by pcd.creation_date desc NULLS LAST) union all select NULL "PROJECT_COMM_ID", to_number(:P6_PROJECT_ID) "PROJECT_ID", NULL "VISIBLE_FLAG", NULL "COMMENT_TEXT", NULL "CREATED_BY", SYSDATE "CREATION_DATE", to_number(::P6_CMT_TYPE) "COMMENT_TYPE_ID", to_number(::P6_TEST_PLAN_ID) "TEST_PLAN_ID" from dual
0.08: determine column headings
0.08: parse query as: XXTPM
0.08: query could not be parsed:
Thanks,
Janel