04-16-12, 10:13 #1Registered User
- Join Date
- Apr 2004
- Kingsland, Georgia
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:
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
04-16-12, 10:55 #2Registered User
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
- Join Date
- Jun 2003
- West Palm Beach, FL