| about USERENV('SESSIONID') [message #275922] |
Tue, 23 October 2007 06:44  |
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 #275935 is a reply to message #275922] |
Tue, 23 October 2007 07:41   |
Frank
Messages: 7901 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 #276132 is a reply to message #276121] |
Wed, 24 October 2007 01:40   |
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 #276165 is a reply to message #276121] |
Wed, 24 October 2007 03:58   |
 |
Maaher
Messages: 7065 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. 
MHE
|
|
|
|
| Re: about USERENV('SESSIONID') [message #276167 is a reply to message #276165] |
Wed, 24 October 2007 04:23   |
Frank
Messages: 7901 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 #276181 is a reply to message #276173] |
Wed, 24 October 2007 05:12   |
 |
Maaher
Messages: 7065 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 #276230 is a reply to message #276185] |
Wed, 24 October 2007 07:44  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
| nirajksharmacpr wrote on Wed, 24 October 2007 12:35 | Ok Thnax i am Fully Agree 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?
|
|
|
|