Nano Taboada Nano Taboada - 4 months ago 8
SQL Question

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!

Answer

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>