Home » SQL & PL/SQL » SQL & PL/SQL » about USERENV('SESSIONID')
about USERENV('SESSIONID') [message #275922] Tue, 23 October 2007 06:44 Go to next message
nirajksharmacpr
Messages: 19
Registered: October 2007
Location: Mumbai
Junior Member

Hi All
i am using the USERENV('SESSIONID') for Global Temporary Table Name. USERENV('SESSIONID') it always Return number with length is FIVE character. I want to Know length of USERENV('SESSIONID') is Fixed or It can be Diffrent in Any Other ..

Please Suggest me

Thnx & Rgrds

Niraj
Re: about USERENV('SESSIONID') [message #275927 is a reply to message #275922] Tue, 23 October 2007 06:56 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
No.
I think USERENV('SESSIONID') is same as v$session.audsid
DESC v$SESSION

SADDR   RAW     (4) 	
SID     NUMBER
SERIAL# NUMBER
AUDSID 	NUMBER               <-----
PADDR   RAW     (4)
......
......
By
Vamsi
Re: about USERENV('SESSIONID') [message #275935 is a reply to message #275922] Tue, 23 October 2007 07:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
nirajksharmacpr wrote on Tue, 23 October 2007 13:44

Hi All
i am using the USERENV('SESSIONID') for Global Temporary Table Name.

If I understand you correct, that is a bad idea.
You should create your GTT only once, not once per session!
Re: about USERENV('SESSIONID') [message #276117 is a reply to message #275935] Wed, 24 October 2007 01:12 Go to previous messageGo to next message
nirajksharmacpr
Messages: 19
Registered: October 2007
Location: Mumbai
Junior Member

Hi
Thanks for Suggestion. I am Using the This Table in Stored Procedure(SP) Life of GTT is Begin And End with SP. More than one User use the SP in Front end(i.e ASP.Net ) i was using the GTT without The PostFix and when Any Other User try to Work with this SP which is already Running on Another System. So Please think the Condition
Re: about USERENV('SESSIONID') [message #276121 is a reply to message #276117] Wed, 24 October 2007 01:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A global temporary table in Oracle holds data that is private to the session/transaction. A million users could simultaneously use this table without seeing each other's data. It is created once. The structure is fixed, but the content is volatile, if you will. No need for "SQL Server like" table creation. In Oracle, the creation/drop of objects consumes a lot of resources. You try to avoid that as much as possible.

So again: users will not block each other. That's the whole point of using temporary tables. I suggest you read more about it on http://tahiti.oracle.com (online documentation).

MHE
Re: about USERENV('SESSIONID') [message #276132 is a reply to message #276121] Wed, 24 October 2007 01:40 Go to previous messageGo to next message
nirajksharmacpr
Messages: 19
Registered: October 2007
Location: Mumbai
Junior Member

Hi
Thanks for this

this is Situation
1. SP Create Global Temporary Table When Start the SP
2. Drop GTT when SP is Closed.
3. assume First SP is in Process(GTT is Not DROP )
4. then Anoter Person Open the SP(Same SP which is already ) on another PC During this Time . GTT is already Exist so it will not REPLACE the Old One

May Be I am Doing some Mistake But i got the Problem. For this I was Using the Session id as Postfix.
Regards
Niraj

[Updated on: Wed, 24 October 2007 01:41]

Report message to a moderator

Re: about USERENV('SESSIONID') [message #276139 is a reply to message #276132] Wed, 24 October 2007 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
GTT is not created/droppped in the procedure, it is created ONCE before like permanent tables.
A GTT is just a definition like a view.

You should read Oracle documentation before going on:
Database Concepts
Chapter 5 Schema Objects
Section Overview of Tables
Subsection Temporary Tables

Regards
Michel

Re: about USERENV('SESSIONID') [message #276141 is a reply to message #276139] Wed, 24 October 2007 01:56 Go to previous messageGo to next message
nirajksharmacpr
Messages: 19
Registered: October 2007
Location: Mumbai
Junior Member

Hi
WE Can Use EXECUTE IMMEDIATE and Other Dynamic Statement U Can Drop and Create this thing in SP.

Regrads.

[Updated on: Wed, 24 October 2007 04:35]

Report message to a moderator

Re: about USERENV('SESSIONID') [message #276154 is a reply to message #276141] Wed, 24 October 2007 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Try the EXECUTE IMMEDIATE and Other Dynamic Statement U Can Drop and Create this thing in SP

No, try to use Oracle in a proper way.

By the way, read and follow OraFAQ Forum Guide, especially what is said about IM speak.

Regards
Michel
Re: about USERENV('SESSIONID') [message #276165 is a reply to message #276121] Wed, 24 October 2007 03:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here's what I suggest:create the table ONCE.

Here's a small copy and paste of an example:
SQL> CREATE GLOBAL TEMPORARY TABLE my_gtt(col1 NUMBER, col2 VARCHAR2(30))
  2  ON COMMIT PRESERVE ROWS
  3  /

Table created.

SQL> Begin
  2    INSERT INTO my_gtt VALUES (1, 'One');
  3  End;
  4  /

PL/SQL procedure successfully completed.

We have just created a global temporary table. The data in the table (1 record) is not visible to another session. Let's make sure. I connected to the same database with the same user while leaving the first session open. For your convenience, I changed the prompt to "SQ2>".
SQ2>-- session two
SQ2>Begin
  2    INSERT INTO my_gtt VALUES (2, 'Two');
  3    INSERT INTO my_gtt VALUES (2, 'Three');
  4  End;
  5  /

PL/SQL procedure successfully completed.

SQ2>SELECT * FROM my_gtt;

      COL1 COL2
---------- ------------------------------
         2 Two
         2 Three
It seems I was right: only the two records I inserted in this session are visible. The other record should still be there. I double checked this by going back to my first SQL*Plus session:
SQL>-- session one
SQL> SELECT * FROM my_gtt
  2  /

      COL1 COL2
---------- ------------------------------
         1 One

SQL> truncate table my_gtt
  2  /

Table truncated.
Ok, so far we've established that the data is only visible to the session itself. But what if I wanted to DELETE, or even worse, TRUNCATE the table. As truncate is a DDL command, this might be dangerous. Is the data from session 2 still there?
SQ2>--session two
SQ2>SELECT * FROM my_gtt;

      COL1 COL2
---------- ------------------------------
         2 Two
         2 Three
Apparently it is. Bottom line: I created a table ONCE (I cannot stress this enough, so: ONCE, ONE TIME) and reused it's structure in different sessions, but not it's data. The data stays within your session and you can even create your GTT in such a way that the data is purged once transaction has ended. But that is in the documentation.

I hope I've made myself clear now: forget about creating/dropping tables on the fly. DON'T DO THAT. Your DBA will haunt you. Very Happy

MHE
Re: about USERENV('SESSIONID') [message #276167 is a reply to message #276165] Wed, 24 October 2007 04:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In addition to Maarten stressing the table gets created ONCE.
For clarification: this ONE, SINGLE time the table gets created is also NOT done through a regular stored procedure, but as part of the installation of your application.
Re: about USERENV('SESSIONID') [message #276172 is a reply to message #276167] Wed, 24 October 2007 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With 3 members saying that a GTT is created only once along with the permanent tables, I think OP is starting to be convinced this is the way to do (at least I hope so). Smile

Regards
Michel
Re: about USERENV('SESSIONID') [message #276173 is a reply to message #275922] Wed, 24 October 2007 04:51 Go to previous messageGo to next message
nirajksharmacpr
Messages: 19
Registered: October 2007
Location: Mumbai
Junior Member

Hi
I want To Express My Problem Point wise
1. I Have Sp

a. Sp Create GTT "ABC" Each Time When SP is Call By
Frontend Program.
b. Before Ending of Execution My GTT "ABC" will Drop.
C. GTT "ABC" Create as a Object in Database

So Problem is here.

Assume Any Ohter user Open the Same SP(User A allready Use the SP there ABC is Exist ) is where "ABC" GTT is
Created so SP Again Try to Create The GTT as "ABC"(Which is
Already Exist).

For This I am Using the PostFix as Session ID with Global Temp Name. My Question is What is Max. Length of
USERENV('SESSIONID'). I always Got 5 in Length is it will Differ in Any Other OS and other Possibility.


Thanks And Regrds
Re: about USERENV('SESSIONID') [message #276181 is a reply to message #276173] Wed, 24 October 2007 05:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I give up. Did you read the replies you got? Oracle is not SQL Server. So don't try to force Oracle to act like SQL Server.
IT IS A BAD(!) DESIGN.

Don't use SQL Server temp table rubbish in Oracle.
IT IS A BAD(!) DESIGN.

Don't create or drop in procedures
IT IS A BAD(!) DESIGN.

Read it again. Try to understand this entire thread.

MHE

[Updated on: Wed, 24 October 2007 05:14]

Report message to a moderator

Re: about USERENV('SESSIONID') [message #276185 is a reply to message #276181] Wed, 24 October 2007 05:35 Go to previous messageGo to next message
nirajksharmacpr
Messages: 19
Registered: October 2007
Location: Mumbai
Junior Member

Ok Thnax i am Fully Agree Smile with You. What is The Other Option. Just i want To Solve this Type of Problem.

Thanks and Regards.

Niraj Sharma
Re: about USERENV('SESSIONID') [message #276188 is a reply to message #276185] Wed, 24 October 2007 05:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ok, here's an alternative: use PL/SQL collection types. If the data is to be used only in the procedure, store it there and not in the database.

But why would you want an alternative? A GTT seems to fit your needs, doesn't it?

MHE
Re: about USERENV('SESSIONID') [message #276191 is a reply to message #276185] Wed, 24 October 2007 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is easy to adapt your code: just remove create and drop.

Regards
Michel
Re: about USERENV('SESSIONID') [message #276228 is a reply to message #276172] Wed, 24 October 2007 07:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 24 October 2007 11:43

With 3 members saying that a GTT is created only once along with the permanent tables, I think OP is starting to be convinced this is the way to do (at least I hope so). Smile

Regards
Michel


Well, not quite, apparently...
Re: about USERENV('SESSIONID') [message #276229 is a reply to message #276185] Wed, 24 October 2007 07:43 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe you can start a new club with khresmoi, who in this thread http://www.orafaq.com/forum/t/91451/66800/ seems not be be able to grasp the concept of ROLES.
Re: about USERENV('SESSIONID') [message #276230 is a reply to message #276185] Wed, 24 October 2007 07:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
nirajksharmacpr wrote on Wed, 24 October 2007 12:35

Ok Thnax i am Fully Agree Smile with You. What is The Other Option. Just i want To Solve this Type of Problem.

Thanks and Regards.

Niraj Sharma


You simply do not need another option. The characteristic of a GTT is that sessions cannot see other sessions' data. No need for pl/sql tables or other workarounds.
Like Michel says: remove the create & drop.

Create the table now. From sqlplus. As a global temporary table.
Then create your procedure and use the tablename as created just now.

Then see what happens.... Now isn't that cool?
Previous Topic: Triggers on Views
Next Topic: Major issues with Merge - please help!!
Goto Forum:
  


Current Time: Fri Dec 09 01:57:46 CST 2016

Total time taken to generate the page: 0.08468 seconds