How to transform an Oracle SQL into a Stored Procedure that should iterate through some tables fetching a certain data field?
I need to transform an Oracle SQL statement into a Stored Procedure therefore users with less privileges can access certain data field:
SELECT info_field, data_field FROM table_one WHERE some_id = '<id>' -- I need this <id> to be the procedure's parameter UNION ALL SELECT info_field, data_field FROM table_two WHERE some_id = '<id>' UNION ALL SELECT info_field, data_field FROM table_three WHERE some_id = '<id>' UNION ALL ...
Given that I'm no SP expert I've been unable to figure out a good solution to loop through all the involved tables (12 aprox.).
Any ideas would be helpful. Thanks much!
If you just want to restrict users' access you could create a view and grant them select on the view but not the tables:
CREATE VIEW info_and_data AS SELECT info_field, data_field FROM table_one UNION ALL SELECT info_field, data_field FROM table_two UNION ALL SELECT info_field, data_field FROM table_three ...
The users could then type:
SELECT info_field, data_field FROM info_and_data WHERE some_id = <id>
There are other ways to achieve your goal besides my suggestions below, but I would warn against splitting up data that really belongs in one table just to implement a data access policy that may change in the future.
The simplest solution to limit which table columns a user sees is through views on those tables. Use different views that show or hide specific columns and grant access to those views to different users/roles.
If you don't know in advance which combination of columns a user may be allowed to see, then you could use dynamic sql: You assemble the SQL statment in the stored procedure based on the access privileges of your user (look up from some other table you create to hold this info), meaning that you only include the proper columns in the SELECT portion of your statement. See this document from Orace for more info.
If you are using Oracle 10g, then you may find this Oracle article interesting. It introduces the topic of the Virtual Private Database, or VPD for short, where you can hide certain rows, or columns or even individual column values depending on who is accessing a table.
Is the expectation that, among all these tables, only one will have a match for a given ID?
If no: You need to explain what you want to do when there are multiple matches.
If yes: You simply do the same SQL query, selecting the result into a variable that you then return.
It would look something like this:
PROCEDURE get_fields( the_id NUMBER, info_field_out OUT table_one.info_field%TYPE, data_field_out OUT table_one.data_field%TYPE ) IS BEGIN SELECT info_field, data_field INTO info_field_out, data_field_out FROM ( ... put your full SQL query here, using 'the_id' as the value to match against .. ); EXCEPTION WHEN no_data_found THEN -- What do you want to do here? Set the outputs to NULL? Raise an error? WHEN too_many_rows THEN -- Is this an invalid condition? END;