ORCL - TABLE function(1)

I have to say that I am very happy to work on Oracle DB again. I found sometimes the stored procedure we created is not very efficient. We try to avoid to use temp tables and some queries are very long and complex.

Then I thought about the Oracle9i new table function. Again from the expert James Koopmann:

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 (Extraction Transformation Load) 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 here or check at the bottom. 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'));

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

next blog I may study more about this feature, and see if we can use this for our daily job.