Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Apr 4, 2003

Get the init.ora parameter value using DBMS_UTILITY

By Ajay Gursahani

"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:\ajay\du.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:\ajay\du.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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date