Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 19, 2003

Returning Rows Through a Table Function in Oracle

By James Koopmann

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


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.

       (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),
CREATE TYPE ind_tab_privs_table AS TABLE OF ind_tab_privs;


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.


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.


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:

» 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);
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;
    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, 
    PIPE ROW(out_rec);
  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;
    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, 
    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;
      FETCH cur1 INTO out_rec.itp_user,
      IF out_rec.itp_user != v_owner THEN
        PIPE ROW(out_rec);
      END IF;
  CLOSE cur0;
  CLOSE cur1;

END itp_grants;

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM