A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

Materialized View Log

When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.

Privileges required

create materialized viewcreate any materialized viewdrop any materialized viewdelete any tableinsert any tablelock any tableselect any tableunder any tableupdate any tablecreate tablecreate view

Suppose you've got a function that is based on a dynamically generated query that returns a ref cursor variable. Now suppose you want to use this ref cursor variable in your procedure, but you don't know the record structure. So how do you make a "FETCH l_ref_cur INTO record_variable" when you don't know the record variable structure.

Because ref cursors do not (directly) support description, the solution is quite complicated and requires that the function (or package) returns not only a ref cursor variable but a (dynamically) generated query, too.

I am going to use "a good old" DBMS_SQL package and its PARSE and DESCRIBE_COLUMNS procedures in order to make an unknown record variable.

1. Make the "generic" package.

First I am going to make a "dyn_fetch" package in which the "describe_columns" procedure (using query recorded in a global "g_query" variable) creates a "g_desc_tab" PL/SQL table used by the "record_def" function for making a record structure:

CREATE OR REPLACE PACKAGE dyn_fetch IS

TYPE ref_cur_t IS REF CURSOR;

g_queryVARCHAR2 (32000);

g_countNUMBER;

g_desc_tab DBMS_SQL.DESC_TAB;

varchar2_type CONSTANT PLS_INTEGER := 1;

number_typeCONSTANT PLS_INTEGER := 2;

date_typeCONSTANT PLS_INTEGER := 12;

rowid_typeCONSTANT PLS_INTEGER := 11;

char_typeCONSTANT PLS_INTEGER := 96;

long_typeCONSTANT PLS_INTEGER := 8;

raw_typeCONSTANT PLS_INTEGER := 23;

mlslabel_type CONSTANT PLS_INTEGER := 106;

clob_typeCONSTANT PLS_INTEGER := 112;

blob_typeCONSTANT PLS_INTEGER := 113;

bfile_typeCONSTANT PLS_INTEGER := 114;

PROCEDURE describe_columns;

FUNCTION record_def RETURN VARCHAR2;

END;

/

CREATE OR REPLACE PACKAGE BODY dyn_fetch IS

PROCEDURE describe_columns IS

l_cur INTEGER;

BEGIN

l_cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(l_cur, g_query, DBMS_SQL.NATIVE);

DBMS_SQL.DESCRIBE_COLUMNS (l_cur, g_count, g_desc_tab);

DBMS_SQL.CLOSE_CURSOR(l_cur);

EXCEPTION

WHEN OTHERS THEN

IF DBMS_SQL.IS_OPEN (l_cur) THEN

DBMS_SQL.CLOSE_CURSOR (l_cur);

END IF;

RAISE;

END;

FUNCTION record_def RETURN VARCHAR2 IS

l_record_defVARCHAR2 (32000);

l_typeVARCHAR2 (100);

l_col_typePLS_INTEGER;

l_col_max_lenPLS_INTEGER;

l_col_precision PLS_INTEGER;

l_col_scalePLS_INTEGER;

BEGIN

FOR i IN 1..g_count LOOP

l_col_type:= g_desc_tab(i).col_type;

l_col_max_len:= g_desc_tab(i).col_max_len;

l_col_precision := g_desc_tab(i).col_precision;

l_col_scale:= g_desc_tab(i).col_scale;

IFl_col_type = varchar2_type THEN

l_type := 'VARCHAR2(' || l_col_max_len || ')';

ELSIF l_col_type = number_type THEN

l_type := 'NUMBER(' || l_col_precision || ',' || l_col_scale || ')';

ELSIF l_col_type = date_type THEN

l_type := 'DATE';

ELSIF l_col_type = rowid_type THEN

l_type := 'ROWID';

ELSIF l_col_type = char_type THEN

l_type := 'CHAR(' || l_col_max_len || ')';

-- ELSIFl_col_type = ...

-- long_type, raw_type ...

END IF;

l_record_def := l_record_def || ' col_' || i || ' ' || l_type || ',';

END LOOP;

l_record_def := RTRIM (l_record_def, ',');

RETURN l_record_def;

END;

END;

/

Note that the RECORD_DEF procedure creates column names as col_1 (col_2, ...) because the SELECT clause in the query can be without aliases, for example, "SELECT deptno || dname FROM dept".

2. Create the package that returns the query and ref cursor.

The function that returns the ref cursor variable should return the query, too. So it's better to make two separate functions and put them into the package.

The "set_query" procedure saves the query into the global package variable and the ref cursor is returned by the "ref_cur" function:

CREATE OR REPLACE PACKAGE test IS

PROCEDURE set_query (p_queryVARCHAR2 := NULL);

FUNCTION ref_cur RETURN dyn_fetch.ref_cur_t;

END;

/

CREATE OR REPLACE PACKAGE BODY test IS

PROCEDURE set_query (p_queryVARCHAR2 := NULL) IS

l_query VARCHAR2 (32000) :=

'SELECTe.empno, e.ename,'||

'e.deptno, d.dname'||

'FROM empe,'||

'dept d'||

'WHERE e.deptno = d.deptno';

BEGIN

IF p_query IS NULL THEN

dyn_fetch.g_query := l_query;

ELSE

dyn_fetch.g_query := p_query;

END IF;

END;

FUNCTION ref_cur RETURN dyn_fetch.ref_cur_t IS

l_ref_cur dyn_fetch.ref_cur_t;

BEGIN

OPEN l_ref_cur FOR dyn_fetch.g_query;

RETURN l_ref_cur;

END;

END;

/

So why do I need two separate procedures (functions) in the package?

a) The receiving program must use dynamic SQL, but in the dynamic block I can access only PL/SQL code elements that have a global scope (standalone functions and procedures, and elements defined in the specification of a package). Unfortunately, cursor variables cannot be defined in the specification of a package (so they cannot be global variables).

b) The receiving program must get the column list before ref cursor.

So, there are two options:

a.) Call (in the receiving program) the same function two times (once to get the column list and once to return a ref cursor), or

b.) Use one procedure (or function) for returning query (to get the column list) and a second function for returning a ref cursor.

3. Create the receiving program.

Finally I create a procedure that reads the ref cursor. First, the procedure calls the "test.set_query" and "dyn_fetch.describe_columns" in order to get dynamically generated record structure through the "dyn_fetch.record_def" function and to get process definition through (internal) "process_def" function (in this case, to show rows with DBMS_SQL.PUT_LINE):

Note that I can try to use a more generic solution. If I take a look at the "test_fetch_ref_cur" procedure, I can see that the part I use for loop can be used in more cases. So I move this part into the "dyn_fetch" package, into the "fetch_ref_cur" procedure to which two parameters has to be sent: the process description and the function name that returns ref cursor.

Disclaimer

This blog contains things about technology that I jot about, keep for my records. Some articles in this blog are not owned by me. They are for my reference only. In older posts the original links may not be there, its not intentional. Thanks for understanding.