How to tranform an Oracle SQL into a Stored Procedure that should iterate through some tables fetching a certain data field?

Question

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!

Answer 1

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;

Answer 2

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>

Answer 3

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.