Clever Geek Handbook

How do I convert Oracle SQL to a stored procedure that needs to traverse multiple tables getting a specific data field?

I need to convert an Oracle SQL statement to a stored procedure so lesser users can access a specific data field:

SELECT
    info_field, data_field
FROM
    table_one
WHERE
    some_id = '<id>' -- I need this <id> to be the procedure 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

...

Considering that I am not an SP expert, I was unable to find a suitable solution for scrolling through all the tables involved (12 aprox.).

Any ideas would be helpful. Many thanks!

+1


source to share


3 answers


If you just want to restrict user access, you can create a view and give them a choice in the view, but not in 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
    ...


Users can then enter:

SELECT info_field, data_field
FROM   info_and_data
WHERE  some_id = <id>
+4


source


There are other ways to achieve your goal besides my suggestions below, but I would caution against splitting data that does belong to the same table in order to enforce a data access policy that may change in the future.

The simplest solution for limiting what columns of a table the user sees is a view over those tables. Use different views that show or hide specific columns and give access to those views to different users / roles.



If you don't know in advance what combination of columns the user can allow to see, then you can use dynamic sql: you collect the SQL statute in a stored procedure based on your user's permissions (look up from some other table that you create to store this information), which means that you only include the correct columns in the SELECT section of your statement. See this doc from Orace for more information.

If you are using Oracle 10g, you may find this Oracle article interesting. It briefly covers the topic of Virtual Private Database or VPD, where you can hide specific rows or columns, or even individual column values ​​depending on who is accessing the table.

+2


source


Is the expectation that among all these tables, only one will match for a given ID?

If not: you need to explain what you want to do when there are multiple matches.

If yes: you are just executing the same SQL query, fetching the result into a variable, which you then return.

It will 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;
+1


source






More articles:


All Articles