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