Nano Taboada Nano Taboada - 1 year ago 29
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 Source

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>