Database Reference
In-Depth Information
BEGIN
FOR i IN first_nr .. last_nr LOOP
row_nr.row_nr:=i;
PIPE ROW(row_nr);
END LOOP;
return;
END;
/
When
last_nr
is larger than
first_nr
,
row_factory
returns
last_nr
-
first_nr
plus one
row. The result is very much like
SELECT
ROWNUM
FROM
table
, except that the argument values and
not the number of rows in a table control how many rows are returned. Here's an example:
SQL> SELECT * FROM TABLE(row_factory(1,2));
ROW_NR
----------
1
2
The classic approach for generating a large table consists of selecting from a real table,
possibly using a Cartesian join to arrive at a very large number of rows. Beyond requiring less
coding for the
CREATE
TABLE
statement, this novel approach using a pipelined table function has
the additional benefit of not causing any consistent or physical reads on a segment. By calling
row_factory
with a
first_nr
and
last_nr
setting of 1 and 1000000, we can now create a table
with one million rows.
SQL> CREATE TABLE random_strings AS
SELECT dbms_random.string('a', 128) AS random_string
FROM TABLE(row_factory(1,1000000))
NOLOGGING;
The first argument (
opt
) tells
DBMS_RANDOM
to generate random mixed-case strings consisting
solely of letters. The second argument (
len
) controls the length of the random string. Note that
in releases prior to Oracle11
g
, arguments to PL/SQL routines cannot be passed by name from
SQL.
7
In my test database with
db_block_size=8192
, the previous CTAS (create table as select)
resulted in a segment size of about 150 MB.
DBMS_RANDOM
is also capable of generating random
alphanumeric strings in lower, upper, or mixed case, as well as random numbers.
8
V$SQL_WORKAREA_ACTIVE
A good way to monitor PGA memory management at the session level is to query the dynamic
performance view
V$SQL_WORKAREA_ACTIVE
, which has the following columns:
In Oracle11
g
,
SELECT * FROM TABLE(row_factory(first_nr => 1, last_nr => 3))
is syntactically
correct. In prior releases this statement causes ORA-00907.
7.
The document
A Security Checklist for Oracle9i
lists
DBMS_RANDOM
among a list of packages that might
8.
