Session Variable

[fatcodeguy]

Hi,

How do I set a session variable, and how to I load it into a pl/sql variable? Can I do something like

select <session_variable> from dual

Also, how do I invalidate a session, like a pseudo-logout?

Thanks!!

 

[SantaMufasa]

Fatcodeguy,

(Do you have nickname that you answer to? Calling you "Fat" just seems a bit...I dunno...)

Okay, to your questions. There are two ways to create a "persistent" session variable that you can access throughout your session. One way is with a bind variable, the other is with a global PL/SQL variable. Once you create them, you access them pretty much the same way.

Method 1 -- Bind variable:

SQL> var x number
SQL> exec :x := 150

PL/SQL procedure successfully completed.

SQL> exec :x := :x + 1

PL/SQL procedure successfully completed.

SQL> select :x from dual;

        :X
----------
       151

1 row selected.

Method 2 -- PL/SQL Global variable:

SQL> create or replace package vars
  2  is
  3   x number;
  4   function getx return number;
  5  end;
  6  /

Package created.

SQL> create or replace package body vars is
  2   function getx return number is
  3   begin return x;
  4   end;
  5  end;
  6  /

Package body created.

SQL> select vars.getx from dual;

      GETX
----------


1 row selected.

SQL> exec vars.x := 150;

PL/SQL procedure successfully completed.

SQL> exec vars.x := vars.x + 1;

PL/SQL procedure successfully completed.

SQL> select vars.getx from dual;

      GETX
----------
       151

1 row selected.

Now,

"how do I invalidate a session, like a pseudo-logout?"

Method 1 -- Just "disconnect" from SQL*Plus (You will, however, need to re-connect as someone before you can access the database.)

SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL>

Method 2 -- Just "re-connect" as yourself. (Clean and simple.):

SQL> connect test/test
Connected.
SQL>

Let us know if these suggestions resolve your needs.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:28 (26Nov04) UTC (aka "GMT" and "Zulu"),
@ 10:28 (26Nov04) Mountain Time

 

[fatcodeguy]

Hi Mufasa,

I suppose I should explain what it was all for. I'm using the Oracle Webserver, and all my PL/SQL packages are for a web interface (using the htp package). There is functionality for the user to login, so I wanted to set a session variable for USERNAME.

The second option I know I can't use, because both users can modify the global variable.

As for the first, can I create a bind variable in a pl/sql procedure?

I tried

var username varchar2(50);
exec :username := 'myuser';

but got a whole slew of errors starting with

LINE/COL ERROR
-------- -----------------------------------------------------------------
46/10    PLS-00103: Encountered the symbol "USERNAME" when expecting one
         of the following:
         := . ( @ % ;

I think my other alternative is calling userenv('sessionid') and storing it in a table associated with their username. When a user logs in, it gets the sid, checks username/password and stores. Then in each page I can check the table.

I would prefer your first method, however, as it would handle logging off with simply closing the browser. I'd have to have some cleaning script on the table every now and then.

What do you think?

P.S Seeing as you've answered like a zillion of my questions (thanks LOADS!! by the way)... my name is Ali

Ali

 

[SantaMufasa]

Ali,

I don't believe that your problem is with the code you posted, because when I copy and paste your exact code, I show no errors:

var username varchar2(50);
exec :username := 'myuser';

PL/SQL procedure successfully completed.

The fact that your error message refers to "Line 46, column 10" leads me to believe that your reference to ":username" is in error. Did you refer to ":username" in your PL/SQL code and if so, did you properly refer to ":username" with the prefixing colon? For example, on Line 46 of your PL/SQL code, did your reference to the SQL*Plus variable read "... :username..."?

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 15:49 (29Nov04) UTC (aka "GMT" and "Zulu"),
@ 08:49 (29Nov04) Mountain Time

 

[fatcodeguy]

Hi,

This is what I've done, and the errors i obtain:

SQL> CREATE OR REPLACE procedure test is
  2  begin
  3  var username varchar2(50);
  4  exec :username := 'myuser';
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5      PLS-00103: Encountered the symbol "USERNAME" when expecting one
         of the following:
         := . ( @ % ;

4/6      PLS-00049: bad bind variable 'USERNAME'
4/6      PLS-00103: Encountered the symbol "" when expecting one of the
         following:
         constant exception <an identifier>
         <a double-quoted delimited-identifier> table LONG_ double ref
         char time timestamp interval date binary national character
         nchar

LINE/COL ERROR
-------- -----------------------------------------------------------------

6/0      PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         end not pragma final instantiable order overriding static
         member constructor map

SQL>

Regards,

Ali

 

[SantaMufasa]

Aaah-haaa ! This shows the importance of disclosing a copy-and-paste of the "full code" plus error listing...it all becomes clear:

"var" and "exec" commands are SQL*Plus commands, not PL/SQL commands. You cannot embed those commands in a PL/SQL block since PL/SQL understands neither verb. If you execute those commands from a SQL*Plus prompt (and not embedded in a PL/SQL block), you will see the success that I had.

You may, however, refer to the SQL*Plus bind variable from within a PL/SQL block by referring to it in the block wherever PL/SQL allows an expression so long as you refer to it as ":username".

Does that resolve your issue?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:16 (29Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:16 (29Nov04) Mountain Time

 

[fatcodeguy]

Not really, because I'd still have to bind the variable in SQL*Plus, and the users won't have access to it, or any tool to directly connect to the database. They only have access to the web app.

Do you have any other alternatives to simulate a log in? If not, I'll go with the user/session id table.

Regards,

Ali

 

[fatcodeguy]

So,

I've discovered that I can't do it with this method, as

the PL/SQL Cartridge is unique in connecting to the database and all the states are maintained within the database, the concept of sessions does not apply to the PL/SQL Cartridge

So instead I'm going with cookies.

Thanks for all your help Mufasa!

P.S The way things are going, chances are I'm going to start a new thread on cookies. Cheers!!