Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bind variables (how to replace more placeholders with same value?)

591357May 2 2009 — edited May 2 2009
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.
This post has been answered by Christian Balz on May 2 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 30 2009
Added on May 2 2009
2 comments
2,334 views