Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231

    Unanswered: SELECT FROM tbl WHERE field1 IN (storedproc.field2)

    I'm creating an auth system, and I've got a SP to return all the roles (role_id) that a user is in via a sys_refcursor. I've now got to determine all the pages he can view, as maintained in a role_page table of (role_id, page_id).

    So what I'm trying to accomplish is something like:

    SELECT url
    FROM tbl_pages, tbl_role_page
    WHERE (tbl_pages.page_id = tbl_role_page.page_id)
    AND tbl_role_page.role_id IN (**usp_GetAllRolesForUser**);

    I can obviously write the same sql that makes up the usp_GetAllRolesForUser storedproc, but if the GetAllRoles logic ever changes, I don't want to have multiple places to have to change the code. Is there any way to do what I'm asking, or does anyone know of a best practice that may be different? I'm obviously a beginner at PLSQL, and I'm looking at cursor expressions, and cursor variables and cursor subqueries, but none seem to fit the situation. Thanks for your time.

    *Now looking at using BULK COLLECT to help out.
    Last edited by jmahaffie; 04-16-12 at 10:27. Reason: Found new information

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Quote Originally Posted by jmahaffie View Post
    ... Etc ...
    I can obviously write the same sql that makes up the usp_GetAllRolesForUser storedproc, ... Etc ...
    Perhaps if you have a Function instead of a procedure, it can be done.
    Create package that has both: the procedure and the function and you can share the code between them for easier maintenance.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •