UTL_RAW has already been touched upon in earlier discussions.
According to Oracle, the basic purpose of UTL_RAW is to manipulate
raw datatypes. Many of the 20-plus functions are used to convert, or
cast, one datatype to another. Others are related to attribute
values, such as LENGTH, which was used in earlier examples.
Some of the more commonly used functions are related to VARCHAR2. The
CAST_TO_RAW and CAST_TO_VARCHAR2 functions handle varchar2 to raw and
vice versa conversions. For normal data or conversion operations, one
would be hard pressed to describe a situation where the BIT_XOR
function, as an example, came in handy.
It is usually not a good sign when all the documentation does is
provide copious amounts of syntax examples and no working example
whatsoever. Somewhere one missed the "Use
of these subprograms is left as an exercise at the end of the chapter.
Curious readers are encouraged to try the exercises."
However, the UTL_RAW is not a destination package, so to speak. It is
a helper package that facilitates the functioning of other packages.
The functions are all straightforward, and the three that are most
likely to be used are the varchar2-related ones and length, which has
already been seen in action.
If the developer needs to recompile invalid objects, and needs it done
quickly, UTL_RECOMP may be helpful. It does not do anything that
Oracle does not already do. Oracle will attempt to compile an invalid
object upon first use. The main advantage to having objects in a valid
status to begin with is the time savings; that is, what little bit of
time it takes to compile an object while a procedure is being run will
not be added to the time because the compilation time has been paid
What UTL_RECOMP does allow is parallel recompilation. It is like
having parallel execution operations opened up for compiling objects.
Oracle suggests one thread per CPU. If there are four CPUs, then the
developer could use
There are five areas of consideration when using the package. The
first is that Oracle expects to have STANDARD, DBMS_STANDARD, DBMS_JOB
and DBMS_RANDOM in a valid state. The second is that the developer
must be connected as SYSDBA while (third) running this via SQL*Plus.
The package uses (fourth) the job queue when using the parallel
option, and fifth, there should not be any DDL taking place while
running this package and its procedures.
Optionally, one can recompile objects sequentially as a whole, or
sequentially within a schema. If using the parallel option, one may
find that writes to the SYSTEM tablespace are a bottleneck, so any
gains from the multiple CPU approach are washed out by a less than
optimal disk I/O situation.
Otherwise, use of this package is quite easy to implement and it does
provide an alternative to running UTLRP.SQL, which, by way of
interest, calls a script named UTLPRP.SQL, which is a wrapper for
using UTL_RECOMP. Not surprisingly, UTLPRP.SQL can take a parameter
which is the number of threads for parallel execution. Now the rest of
the story is known.
If one reads in detail what the parameters are for RECOMP_SERIAL
and RECOMP_PARALLEL, note that there is a flags parameter.
It has a default of 0, and its stated purpose is that it is used
for internal testing and diagnosability, suggesting hints of
hidden Oracle features. Without knowing what other flag values
will do, do not use anything other than what Oracle expects in the