session user variable 1

[GilMerc]

Hi,

I find how to define a session variable where each session can use and modify it without other session see this one.

Somebody know how to do this.

Thank's in advance.

Gilles.

 

[lewisp]

What application are you running? Forms? SqlPlus?

Global temporary tables can provid such functionality within the database, as can package variables within forms.

 

[GilMerc]

I'm running Delphi and connecting to oracle with sqlnet. In fact, all users connect to database with a same user but some parameters are setting in logon.
I would like to pass this parameters to session and setting them in a session variables to be able to reuse them after in the same session.

Gilles.

 

[SantaMufasa]

Gil,

I believe the following code does what you want: "defines a session variable where each session can use and modify it without other session see this one."

The code is in the form of a package which uses global, persistent variables that remain visible only to the session which uses them, and they "persist", that is, stay accessible and visible to just that session during the entire life of the session. They are similar to SQL*Plus bind variables, but are easily visible to all aspects of your SQL connection. For generic convenience, the package creates two dates, two numbers, two varchars along with functions for each so that the data in the variables are fully accessible from SQL.

Section 1 -- Package definition:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor "Dasages, LLC" makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave@dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM This is a generalized package to produce two persistent 
REM variables each for dates, numbers, and varchar variables.
REM The package also creates functions to return the values stored
REM in the persistent variables.
REM
REM **************************************************************
create or replace package dh_ws is
	ws_date1	date;
	ws_date2	date;
	WS_NUMBER1	number;
	WS_NUMBER2	number;
	ws_varchar1	varchar(2000);
	ws_varchar2	varchar(2000);
	function get_n (x in varchar2) return number;
	function get_d (x in varchar2) return date;
	function get_x (x in varchar2) return varchar2;
	pragma restrict_references(get_n,WNDS);
	pragma restrict_references(get_d,WNDS);
	pragma restrict_references(get_x,WNDS);
end;
/
create or replace package body dh_ws is
	function get_n (x in varchar2) return number is
	begin
		if upper(x) = 'WS_NUMBER1' then
			return WS_NUMBER1;
		end if;
		if upper(x) = 'WS_NUMBER2' then
			return WS_NUMBER2;
		end if;
	end get_n;
	function get_d (x in varchar2) return date is
	begin
		if upper(x) = 'WS_DATE1' then
			return ws_DATE1;
		end if;
		if upper(x) = 'WS_DATE2' then
			return ws_DATE2;
		end if;
	end get_d;
	function get_x (x in varchar2) return varchar2 is
	begin
		if upper(x) = 'WS_VARCHAR1' then
			return ws_VARCHAR1;
		end if;
		if upper(x) = 'WS_VARCHAR2' then
			return ws_VARCHAR2;
		end if;
	end get_x;
end;
/

Section 2 -- Sample invocations and output:

SQL> exec dh_ws.ws_number1 := 50

PL/SQL procedure successfully completed.

SQL> exec dh_ws.ws_number2 := 5

PL/SQL procedure successfully completed.

SQL> exec dh_ws.ws_number1 := dh_ws.ws_number1 + dh_ws.ws_number2

PL/SQL procedure successfully completed.

Section 3 -- PL/SQL-based access method:

SQL> set serveroutput on
SQL> exec dbms_output.put_line(dh_ws.ws_number1);
55

PL/SQL procedure successfully completed.

Section 4 -- SQL-based access method:

SQL> select dh_ws.get_n('ws_number1') from dual;

DH_WS.GET_N('WS_NUMBER1')
-------------------------
                       55

Note that you can do the same types of things for DATE and VARCHAR data types, as well.

If you have questions, please follow up with another post. Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:34 (16Jun04) UTC (aka "GMT" and "Zulu"), 17:34 (15Jun04) Mountain Time)

 

[GilMerc]

Hi Mufasa,

It's work, thank's for your help.

Gilles.