Hello, I have code like this:
procedure getAllTasks(o_cursor in out PKG_APPLICATION.rc,
in_filter_project_id in T_FCT_TASK.project_id%type default -1,
in_filter_scope_id in T_FCT_TASK.scope_id%type default -1,
in_filter_user_creator_id in T_FCT_TASK.user_creator_id%type default -1,
in_filter_priority_id in T_HST_TASK.priority_id%type default -1,
in_filter_status_id in T_HST_TASK.status_id%type default -1,
in_filter_user_responsible_id in T_HST_TASK.user_responsible_id%type default -1,
in_pagination_first in integer,
in_pagination_last in integer) is
v_query varchar2(4000) := v_query_allTasks || v_query_whereFilter;
begin
PKG_APPLICATION.setPagination(v_query);
PKG_LOG.debug('query: ' || v_query, PCK);
open o_cursor for v_query
using in_filter_project_id, in_filter_scope_id, in_filter_user_creator_id, in_filter_priority_id, in_filter_status_id, in_filter_user_responsible_id, in_pagination_last, in_pagination_first;
end;
The
v_query variable contains:
SELECT * FROM (SELECT A.*, ROWNUM AS RNUM
FROM (
SELECT task_id, project_id, project_name, scope_id, scope_name, user_creator_id, user_creator_login, title, description,
priority_id, priority_name, status_id, status_name, user_responsible_id, user_responsible_login, due_date,
last_modified_by_id, last_modified_by_login, last_modified
FROM V_FCT_TASK
WHERE project_id = decode(:1, -1, project_id, :1)
and scope_id = decode(:2, -1, scope_id, :2)
and user_creator_id = decode(:3, -1, user_creator_id, :3)
and priority_id = decode(:4, -1, priority_id, :4)
and status_id = decode(:5, -1, status_id, :5)
and user_responsible_id = decode(:6, -1, user_responsible_id, :6)
) A
WHERE ROWNUM <= :LAST)
WHERE :FIRST <= RNUM
Note, that placeholders :1, :2, ... :5 appear 2 times in the query.
Is there a more elegant way to tell that *:1* should be replaced by
in_filter_project_id, *:2* by
in_filter_scope_id without actually having these variables twice in the USING clause? If not, why do placeholders have name, if you can only reference them by position(order)?
Thanks for all replies.