Home » SQL & PL/SQL » SQL & PL/SQL » in function how to use v$parameter
in function how to use v$parameter [message #49334] Fri, 04 February 2005 01:30 Go to next message
gaurav dantre
Messages: 24
Registered: June 2004
Junior Member
Hi ,
I have got the one problem
I want to use the V$PARAMETER table in my stored
function , procedure or package , i m not able to use.
I am getting the error .
the error is : table or view does not exist.
how to use in stored fuction or procedure .
plz heelp me .
Regards
Gaurav Dantre
Re: in function how to use v$parameter [message #49339 is a reply to message #49334] Fri, 04 February 2005 02:53 Go to previous messageGo to next message
Arun Srinath
Messages: 12
Registered: January 2005
Junior Member
Seems you dont have enough privileges to select from the view V$parameter.
Try connecting as dba or equivalent privilege and try again.It should work.

Cheers
Arun
Re: in function how to use v$parameter [message #49340 is a reply to message #49339] Fri, 04 February 2005 03:20 Go to previous messageGo to next message
gaurav dantre
Messages: 24
Registered: June 2004
Junior Member
Thanks Arun ,
can u give me one example .
accually , i am not getting , what should i do for this thing .
plz give me one example .
Thanks & Regards
Gaurav Dantre
Re: in function how to use v$parameter [message #49341 is a reply to message #49334] Fri, 04 February 2005 03:21 Go to previous messageGo to next message
gaurav dantre
Messages: 24
Registered: June 2004
Junior Member
Thanks Arun , can u give me one example . accually , i am not getting , what should i do for this thing .plz give me one example .Thanks & Regards Gaurav Dantre
Re: in function how to use v$parameter [message #49393 is a reply to message #49339] Sun, 06 February 2005 12:04 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
You need either SELECT on SYS.V_$PARAMETER or EXECUTE on DBMS_UTILITY, which has a handy GET_PARAMETER_VALUE function.

Well actually not that handy, as it's a function that returns a dummy value and two OUT parameters, requiring you to write a block of code instead of a simple query (thanks, Oracle). You could try something like this:

CREATE OR REPLACE FUNCTION sys_parameter_value  <i>-- or better, put into a package</i>
	( p_parameter VARCHAR2 )
	RETURN VARCHAR2
AS
	v_value_integer INTEGER;
	v_value_string  VARCHAR2(1000);
	v_junk BINARY_INTEGER;
BEGIN
	v_junk :=
		DBMS_UTILITY.GET_PARAMETER_VALUE
		( LOWER(p_parameter)
		, v_value_integer
		, v_value_string );

	IF v_value_string IS NOT NULL THEN
		RETURN v_value_string;
	ELSE
		RETURN v_value_integer;
	END IF;
END;
/

Function created.

SQL> SELECT SYS_PARAMETER_VALUE('USER_DUMP_DEST') FROM dual;

SYS_PARAMETER_VALUE('USER_DUMP_DEST')
----------------------------------------------------------------
/Users/oracle/udump

1 row selected.
Re: in function how to use v$parameter [message #183832 is a reply to message #49334] Mon, 24 July 2006 01:58 Go to previous message
dinavahi
Messages: 1
Registered: July 2006
Junior Member
Grant SELECT on v_$PARAMETER to the schema to get rid of the error table or view doesn't exist
Previous Topic: how to give privileges to synonyms
Next Topic: data_length in user_tab_columns
Goto Forum:
  


Current Time: Tue Dec 06 06:42:03 CST 2016

Total time taken to generate the page: 0.05665 seconds