In Oracle9i, we are now able to call a table function within the FROM clause of a SQL statement and have it return a result set that mimics what we would normally expect from a traditional SQL SELECT statement.

Recently I was posed a SQL problem where I needed to pull from the database all users who had permissions defined on a given table. What seemed to be a very simplistic question is really a complex query when you start trying to determine users through an infinite nesting of roles that have been also been granted permission on the table in question. While I could have easily done a very complex set of nesting and union operations on the SQL and given this to over to development, I decided to de-couple the logic and present the information to them using a table function. This had a three-fold impact. First and foremost, the development staff would only have to use this function just as they would normally query a typical table in Oracle. Second, since the SQL was de-coupled, the SQL would be easier to maintain. Lastly, new functionality to determining privileges on tables could be implemented and I wouldn't get lost in my own SQL.

So stay with me as I give you a brief introduction to the definition of table functions and actually solve a problem that I know you can use in your day to day DBA operations.

So What Are Table Functions

Table functions are a new feature in Oracle9i that allow you to define a set of PL/SQL statements that will, when queried, behave just as a regular query to table would. The added benefit to having a table function is that you can perform transformations to the data in question before it is returned in the result set. This is of great use when performing ETL operations.

The Pieces

CREATE OBJECT

Here we create our own object type called IND_TAB_PRIVS. Then we create a table of IND_TAB_PRIVS called IND_TAB_PRIVS_TABLE. The table IND_TAB_PRIVS_TABLE is what we will use to return the rows from the table function within a simple select statement.

CREATE TYPE ind_tab_privs AS OBJECT
       (ITP_USER         VARCHAR2(50),
        ITP_USER_ROLE    VARCHAR2(50),
        ITP_GRANTEE      VARCHAR2(50),
        ITP_PRIVILEGE    VARCHAR2(50),
        ITP_OWNER        VARCHAR2(50),
        ITP_TABLE_NAME   VARCHAR2(50),
        ITP_GRANTED_ROLE VARCHAR2(50));
/
CREATE TYPE ind_tab_privs_table AS TABLE OF ind_tab_privs;
/

CREATE FUNCTION

I have created a set of PL/SQL statements in Figure 1. There is nothing particularly interesting here but the additional clauses and statements are explained in further detail in the article. Specifically the PIPELINED, PIPE ROW, and RETURN clause are discussed.

PIPELINED Clause

Within the CREATE FUNCTION clause, there is a new option called PIPELINED. This option tells Oracle to return the results of the function as they are processed, and not wait for a complete execution or completion of the result set. This pipelining of the result set to one row at a time has the immediate advantage of not requiring excessive memory or disk staging resources.

PIPE ROW(out_rec)

The PIPE ROW statement is the interface or mechanism to send a piped row through the PIPELINED option through to the caller of the function.

RETURN Clause

This statement is only required because we are writing a function here and it is expected to RETURN something. You do not need to supply an argument to return since you are returning results through the PIPE ROW statement. Actually, the only real purpose of the RETURN clause is to give control back to the caller and allow for a NO_DATA_FOUND exception.

The SELECT Statement

Nothing too exciting here except for the TABLE operator and function call. Our newly created function ITP_GRANTS just requires you to send the owner and table name. I have supplied SCOTT' and EMP' for demonstration purposes only. You should supply your own owner and table name for which you wish to get the table permissions.

Issue at the SQL/Plus Prompt the following:

SQL> SELECT * from TABLE(itp_grants('SCOTT','EMP'));

Where to Go Now

Aside from building data cartridges into Oracle, table functions can provide great diversity into the methods you allow your users to go after data. While I have only touched on a very small and singular aspect of using table functions, namely just data query and consolidation of result sets, I encourage you read the dozen or so pages of documentation on this great new feature. As you read, you will gain insight to the true power of pipelining and along with that, the extended functionality of parallel processing that can do a lot to aid in data transformation and boost performance through the use of table functions.

Additional Resources:

• Accepting and Returning Multiple Rows with Table Functions
• Returning Large Amounts of Data from a Function
• Transformation Using Table Functions

» See All Articles by Columnist James Koopmann

Figure 1

CREATE FUNCTION itp_grants (owner VARCHAR2, table_name VARCHAR2) 
         RETURN ind_tab_privs_table PIPELINED IS
TYPE         ref0 IS REF CURSOR;
cur0         ref0;
TYPE         ref1 IS REF CURSOR;
cur1         ref1;
v_owner      VARCHAR2(50);
v_table_name VARCHAR2(50);
out_rec      ind_tab_privs 
          := ind_tab_privs(NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
v_owner      := owner;
v_table_name := table_name;
--return simple grants on the object where the grantee is not a role
OPEN cur0 FOR 
  'SELECT grantee  "user", NULL "user_role", grantee,    '||
         'privilege, owner,table_name, NULL granted_role '||
    'FROM (SELECT grantee, privilege, owner, table_name  '||
            'FROM dba_tab_privs '||
           'WHERE owner = :1 AND table_name = :2 '||
             'AND grantee NOT IN (SELECT role FROM dba_roles)) '
USING v_owner, v_table_name;
  LOOP
    FETCH cur0 INTO out_rec.itp_user,    out_rec.itp_user_role,  
                    out_rec.itp_grantee, out_rec.itp_privilege,   
                    out_rec.itp_owner,   out_rec.itp_table_name, 
                    out_rec.itp_granted_role;
    EXIT WHEN cur0%NOTFOUND;
    PIPE ROW(out_rec);
  END LOOP;
  CLOSE cur0;

--return grants on the object where the grantee is a role
--get the roles
OPEN cur0 FOR 
  'SELECT grantee "role", NULL "user_role", grantee,      '||
         'privilege, owner, table_name, NULL granted_role '||
    'FROM (SELECT grantee, privilege, owner, table_name   '||
            'FROM dba_tab_privs '||
           'WHERE owner = :1 AND table_name = :2 '||
             'AND grantee IN (SELECT role FROM dba_roles)) '
USING v_owner, v_table_name;
  LOOP
    FETCH cur0 INTO out_rec.itp_user,    out_rec.itp_user_role,  
                    out_rec.itp_grantee, out_rec.itp_privilege,   
                    out_rec.itp_owner,   out_rec.itp_table_name, 
                    out_rec.itp_granted_role;
    IF cur0%NOTFOUND THEN
      RETURN;
    END IF;
    -- evaluate for each role and get all users
    OPEN cur1 FOR
    'SELECT user_role, granted_role                 '||
    'FROM ( SELECT grantee user_role, granted_role  '||
             'FROM dba_role_privs                   '||
          'CONNECT BY PRIOR grantee  = granted_role '||
                'START WITH granted_role = :1 )     '||
    'WHERE user_role NOT IN (SELECT role FROM dba_roles) '
    USING out_rec.itp_user;
    LOOP
      FETCH cur1 INTO out_rec.itp_user,
                      out_rec.itp_granted_role;
      EXIT WHEN cur1%NOTFOUND;
      IF out_rec.itp_user != v_owner THEN
        PIPE ROW(out_rec);
      END IF;
    END LOOP;
  END LOOP;
  CLOSE cur0;
  CLOSE cur1;

RETURN;
END itp_grants;
/