In this case i am referring to one of the most useful API functions of Oracle, a function that allows you to peek at the value of an initialization parameter even if you are not the DBA.
In fact if you do not hold DBA rights or select rights on dynamic view V$PARAMETER, you won't be able to query that view and here is where dbms_utility.get_parameter_value comes in handy.
Although the link is pointing to the 10R2 documentation set, i'm fairly sure (correct me if i am wrong) that this function underwent no significant changes since version 8i and the PL/SQL sample code included in the Example section is ready-to-use.
For instance, this morning i wanted to check on a remote db where i have no special rights if parameter UTL_FILE_DIR was set or not:
DECLARE
parnam VARCHAR2(256);
intval BINARY_INTEGER;
strval VARCHAR2(256);
partyp BINARY_INTEGER;
BEGIN
partyp := dbms_utility.get_parameter_value('utl_file_dir',
intval, strval);
dbms_output.put('parameter value is: ');
IF partyp = 1 THEN
dbms_output.put_line(strval);
ELSE
dbms_output.put_line(intval);
END IF;
IF partyp = 1 THEN
dbms_output.put('parameter value length is: ');
dbms_output.put_line(intval);
END IF;
dbms_output.put('parameter type is: ');
IF partyp = 1 THEN
dbms_output.put_line('string');
ELSE
dbms_output.put_line('integer or boolean');
END IF;
END;
The name of the parameter can be specified in either lowercase or uppercase.
Should the parameter name be wrong, the following error will be raised:
ORA-20000: get_parameter_value: invalid or unsupported parameter ...Thanks for your patience, i'll be back shortly with some more intriguing Oracle situations :-)
Updated October 2
As Jacques noted, the parameter name must be supplied in lowercase for Oracle versions prior to 10G.
6 comments:
Nice tip :D
With a 9.0.2 database, the name has to be passed in lowercase.
Wouldn't:
IF partyp = 1 THEN
dbms_output.put_line(strval);
dbms_output.put('parameter value length is: ');
dbms_output.put_line(intval);
dbms_output.put('parameter type is: ');
dbms_output.put_line('string');
ELSE
dbms_output.put_line(intval);
dbms_output.put('parameter type is: ');
dbms_output.put_line('integer');
END IF;
do the same while looking a bit "neater"?
I know: nit-picking here.
Thanks for your comment Francois,
i presume that what you said applies also backwards to 8i.
Bye,
Flavio
Hello Noons,
for once, i wasn't the author of the code that has been taken from the manual as it is, but i certainly agree with you, the lesser IFs, the better...
;-)
Flavio
Byte64,
From the fact that your tip is still useful and valuable in 2009, you can see how good it is!
I had read an incorrect example of GET_PARAMETER_VALUE earlier, and that worked as long as I was retrieving a string value. Your tip showed the fine print of this procedure.
After reading your tip, I used the following SQL script to explore the parameters on the fly, and hope others may find it useful:
declare
parm_name varchar2(100);
ret_val binary_integer;
parm_int_val integer;
parm_str_val varchar2(200);
begin
parm_name := '¶meter_name';
ret_val := dbms_utility.get_parameter_value(parm_name, parm_int_val, parm_str_val);
dbms_output.put_line('parm_name = '||parm_name);
dbms_output.put_line('ret_val = '||ret_val);
dbms_output.put_line('parm_val = '||coalesce(parm_int_val, parm_str_val));
end;
/
Best wishes,
Atish
Byte64,
My apologies.
I didn't test the earlier code fully.
This is what works for me regardless of whether the parameter value is a string or number:
declare
parm_name varchar2(100);
parm_type binary_integer;
parm_int_val integer;
parm_str_val varchar2(200);
begin
parm_name := '¶meter_name';
parm_type := dbms_utility.get_parameter_value(parm_name, parm_int_val, parm_str_val);
dbms_output.put_line('parm_name = '||parm_name);
dbms_output.put_line('parm_type = '||parm_type);
dbms_output.put_line('parm_val = '||case parm_type when 0 then to_char(parm_int_val) else parm_str_val end);
end;
/
Thanks again for a great tip.
Post a Comment