How to tranform 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!

Answers


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>

Need Your Help

GetVSTOObject returns Nothing

vb.net vsto excel-2007

as a workaround for the Locale bug in Office Interop I wanted to use VSTO but if I call

MS-Access Get price of product at certain order date

sql ms-access join subquery

I have a table filled with purchase prices, like this: