<p>I need to transform an Oracle SQL statement into a Stored Procedure therefore users with less privileges can access certain data field:</p>

<pre><code>SELECT
    info_field, data_field
FROM
    table_one
WHERE
    some_id = '&lt;id&gt;' -- I need this &lt;id&gt; to be the procedure's parameter
UNION ALL

SELECT
    info_field, data_field
FROM
    table_two
WHERE
    some_id = '&lt;id&gt;'
UNION ALL

SELECT
    info_field, data_field
FROM
    table_three
WHERE
    some_id = '&lt;id&gt;'
UNION ALL

...
</code></pre>

<p>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.).</p>

<p>Any ideas would be helpful. Thanks much!</p>

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;
```
```