Get the init.ora parameter value using DBMS_UTILITY

“Init.ora” is a text file, which can be viewed using a text
editor. Alternatively, we can use DBMS_UTILITY, a PL/SQL package that
comes along with the standard database installation. The DBMS_UTILITY
contains many useful functions, including GET_PARAMETER_VALUE. The GET_PARAMETER_VALUE
procedure gets “init.ora” parameter values. The parameter name is
supplied as an input to the procedure.


DBMS_UTILITY.GET_PARAMETER_VALUE (
parnam IN VARCHAR2,
intval IN OUT BINARY_INTEGER,
strval IN OUT VARCHAR2)
RETURN BINARY_INTEGER;

Where:

Parnam

Is the PARAMETER name

Intval

Is the VALUE of an integer
parameter or the VALUE length of a string parameter

Strval

Is the VALUE of a string parameter.

Return value is:

Partyp

Returns Parameter type:

0 if parameter is an
integer/boolean parameter

1 if parameter is a
string/file parameter

Here
is a sample SQL *PLUS script that uses GET_PARAMETER_VALUE:


SET SEVEROUTPUT ON
DECLARE
parnam VARCHAR2(256);
intval BINARY_INTEGER;
strval VARCHAR2(256);
partyp BINARY_INTEGER;
BEGIN
partyp := dbms_utility.get_parameter_value
(‘db_files’,intval, strval);

— check for parameter type
IF partyp = 1 THEN
dbms_output.put_line(‘The parameter type is string’);
ELSE
dbms_output.put_line(‘ The parameter type is integer’);
END IF;

— print value
dbms_output.put(‘The parameter value is: ‘);
IF partyp = 1 THEN
dbms_output.put_line(strval);
ELSE
dbms_output.put_line(intval);
END IF;

— if paramter type is string Print Value length
IF partyp = 1 THEN
dbms_output.put(‘Length of the string is: ‘);
dbms_output.put_line(intval);
END IF;
END;
/
show errors;
SET SEVEROUTPUT OFF


SQL> @c:ajaydu.sql
The parameter type is: integer
The parameter value is: 1024

PL/SQL procedure successfully completed.

No errors.
SQL>

Change
the parameter type in the above example to ‘db_name‘ and the output will
be as shown below;


SQL> @c:ajaydu.sql
The parameter type is: string
The parameter value is: mydb
Length of the string is: 4

PL/SQL procedure successfully completed.

No errors.

Summary

You can use the DBMS_UTILITY package to
get values of parameters or use the Oracle provided dynamic view V$PARAMETER
to get parameter values. The TYPE column of the view contains the values
1,2 and 3 for BOOLEAN, STRING and INTEGER values respectively.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles