Home » SQL & PL/SQL » SQL & PL/SQL » Generate GUID other than sys_GUID() (merged 3) (Oracle 12c)
Generate GUID other than sys_GUID() (merged 3) [message #646210] Tue, 22 December 2015 04:42 Go to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Hi,


I want to use procedure "DBMS_Session.Set_Context", in this procedure second parameter is "attribute" that accepts a value type varchar2 with maximum size 30, I wanted to use sys_GUID() for generating an GUID and passing it to procedure as parameter, but there is a problem:

sys_GUID() generates a GUID with length 32 but as I mentioned before second parameter of procedure accepts a value with maximum size 30 (varchar2 type).

is there any other way for generating GUID with less size in oracle?
or other way for setting a unique name for the parameter?
Re: Generate GUID other than sys_GUID() [message #646214 is a reply to message #646210] Tue, 22 December 2015 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

1/ SYS_GUID does not return a VARCHAR2 but a RAW of 16 bytes.
2/ Nothing prevents you from creating your own function, for instance using one of the timestamp functions.

Re: Generate GUID other than sys_GUID() (merged 3) [message #646217 is a reply to message #646210] Tue, 22 December 2015 05:26 Go to previous messageGo to next message
John Watson
Messages: 9002
Registered: January 2010
Location: Global Village
Senior Member
I think you have misunderstood the arguments for dbms_session.set_context. The second argument is indeed limited to 30 bytes, but that is the name of the attribute, not the value. You'ld put the sys_guid() into the third argument, which can be 4KB.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646220 is a reply to message #646217] Tue, 22 December 2015 07:33 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
thanks for your useful answer
Quote:

1/ SYS_GUID does not return a VARCHAR2 but a RAW of 16 bytes.
2/ Nothing prevents you from creating your own function, for instance using one of the timestamp functions.


yes you are right, I converted output to varchar2, like this:

Declare
x varchar2(40);
val varchar2(40);
Begin
Select sys_guid() into x from dual;
pkg.set_parameter(x,'Testvalue');
select sys_context('parameter',x) into val from dual;
dbms_output.put_line(x);
end;


procedure set_parameter is:
PROCEDURE Set_Parameter
(
p_name   IN  VARCHAR2,
p_value  IN  VARCHAR2
) IS
BEGIN
  DBMS_SESSION.set_context('parameter', p_name, p_value);
END set_parameter;


When I executed above query, the following error raised:

ORA-28106: input value for argument #2 is not valid
ORA-06512: at "SYS.DBMS_SESSION", line 122
ORA-06512: at "PKG", line 77
ORA-06512: at line 7
28106. 00000 -  "input value for argument #%s is not valid"
*Cause:    Input values for the argument is missing or invalid.
*Action:   Correct the input values.


for example generated GUID is like this:
'2767D6E88E142439E053630502C8FDRT'
this value caused to raise above error, but When I removed 2 last charchter ('RT'),and pass it as input parameter('2767D6E88E142439E053630502C8FD'),then query completed successfully without any problem.

Quote:

I think you have misunderstood the arguments for dbms_session.set_context. The second argument is indeed limited to 30 bytes, but that is the name of the attribute, not the value. You'ld put the sys_guid() into the third argument, which can be 4KB.

the following code is related to set_context:
 procedure set_context(namespace varchar2, [b]attribute varchar2[/b], value varchar2,
                        username varchar2 default null,
                        client_id varchar2 default null);
  --  Input arguments:
  --    namespace
  --      Name of the namespace to use for the application context
  --    attribute
  --      Name of the attribute to be set
  --    value
  --      Value to be set
  --    username
  --      username attribute for application context . default value is null.
  --    client_id
  --      client identifier that identifies a user session for which we need
  --      to set this context.
  --
  --


I want to generate unique attribute name.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646221 is a reply to message #646220] Tue, 22 December 2015 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Why do you want to create a unique attribute name? It is like, "I want to create unique variable name in my code" or "I want to create a unique column name in my table".
What is your final goal?

[Updated on: Tue, 22 December 2015 07:40]

Report message to a moderator

Re: Generate GUID other than sys_GUID() (merged 3) [message #646222 is a reply to message #646220] Tue, 22 December 2015 07:40 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why do you want unique attribute names?
How would you find the attribute you want after creation?
What really are you trying to achieve here?
Re: Generate GUID other than sys_GUID() (merged 3) [message #646223 is a reply to message #646222] Tue, 22 December 2015 07:41 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Wrong question actually - attribute names need to be unique, but they also need to be meaningful - like column and variable names.
So why do you want the attribute name to be random?
Re: Generate GUID other than sys_GUID() (merged 3) [message #646244 is a reply to message #646223] Wed, 23 December 2015 03:30 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Hi,

It is used in dynamic SQL, there is no need to meaningful name for attribute, because the columns names have already been written in query.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646246 is a reply to message #646244] Wed, 23 December 2015 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

What will be the value for this attribute?

Re: Generate GUID other than sys_GUID() (merged 3) [message #646247 is a reply to message #646246] Wed, 23 December 2015 03:47 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
value will be assign based on input parameters of procedure.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646248 is a reply to message #646247] Wed, 23 December 2015 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Why the attribute name should be unique all over the world and time?

Re: Generate GUID other than sys_GUID() (merged 3) [message #646249 is a reply to message #646248] Wed, 23 December 2015 04:01 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Because multiple user may run the query at the same time at one session.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646250 is a reply to message #646249] Wed, 23 December 2015 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

But a context attribute/variable is private (unless you choose to create a global context which would be a bad choice for your case) just like package variables.

[Updated on: Wed, 23 December 2015 04:55]

Report message to a moderator

Re: Generate GUID other than sys_GUID() (merged 3) [message #646254 is a reply to message #646250] Wed, 23 December 2015 05:13 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Thank you Mr.Cadot for you useful reply as always.

Yes a context is private per session, But if the query is executed multiple times in a session at the same time, each query affected on variables.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646256 is a reply to message #646254] Wed, 23 December 2015 05:58 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can't execute something "multiple times in a (database) session at the same time", so you seem to talk about something that is not a database session?
Re: Generate GUID other than sys_GUID() (merged 3) [message #646258 is a reply to message #646256] Wed, 23 December 2015 06:40 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member

For example there is a procedure, can not we execute the procedure multiple time in one session simultaneously?!!!
Re: Generate GUID other than sys_GUID() (merged 3) [message #646259 is a reply to message #646258] Wed, 23 December 2015 07:11 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No, you can't. You can only run the procedure sequentially in one session.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646260 is a reply to message #646258] Wed, 23 December 2015 07:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
fabi88 wrote on Wed, 23 December 2015 04:40

For example there is a procedure, can not we execute the procedure multiple time in one session simultaneously?!!!


Please post Test Case that shows above is true; that one session can execute single procedure multiple times simultaneously.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646263 is a reply to message #646260] Wed, 23 December 2015 07:56 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
For example in a .net application there is a session to database, two thread ( or more) call a function from data access layer, that this function calls the procedure from database, two thread can call this function simultaneously so procedure is called two times at same time.

Re: Generate GUID other than sys_GUID() (merged 3) [message #646264 is a reply to message #646263] Wed, 23 December 2015 08:01 Go to previous messageGo to next message
John Watson
Messages: 9002
Registered: January 2010
Location: Global Village
Senior Member
In that case, each thread will use a different database session.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646265 is a reply to message #646263] Wed, 23 December 2015 08:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
fabi88 wrote on Wed, 23 December 2015 05:56
For example in a .net application there is a session to database, two thread ( or more) call a function from data access layer, that this function calls the procedure from database, two thread can call this function simultaneously so procedure is called two times at same time.



But each will be separate & distinct Oracle session.
Please stop dreaming about some mythical fantasy & just join us in the real World.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646269 is a reply to message #646210] Wed, 23 December 2015 09:27 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Quote:

In that case, each thread will use a different database session.

One static connection to database is open and all threads use it.
Quote:

But each will be separate & distinct Oracle session.
Please stop dreaming about some mythical fantasy & just join us in the real World.

mythical fantasy. Embarassed
No, that is not imaginary, I faced with this problem and discussed it here.

[Updated on: Wed, 23 December 2015 09:28]

Report message to a moderator

Re: Generate GUID other than sys_GUID() (merged 3) [message #646270 is a reply to message #646269] Wed, 23 December 2015 09:30 Go to previous messageGo to next message
John Watson
Messages: 9002
Registered: January 2010
Location: Global Village
Senior Member
Quote:

One static connection to database is open and all threads use it.

Yes, but not at the same time. Talk to your apps server administrator, ask him to explain how it manages connection pooling.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646273 is a reply to message #646269] Wed, 23 December 2015 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>One static connection to database is open and all threads use it.
Then all SQL statements are serialized.
One Oracle session can only process one SQL statement at a time.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646275 is a reply to message #646269] Wed, 23 December 2015 09:35 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So if you have a .net application that has "some sort of sessions" that you want to "identify in some way" then you would need to do that identification "somehow" in the .net application.

For example, an ID like "username-yyyyddmmhhmiss" might be "good enough" for a lot of use cases.

[Updated on: Wed, 23 December 2015 09:36]

Report message to a moderator

Re: Generate GUID other than sys_GUID() (merged 3) [message #646277 is a reply to message #646275] Wed, 23 December 2015 09:59 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Thanks all for your replies.


Quote:
Yes, but not at the same time. Talk to your apps server administrator, ask him to explain how it manages connection pooling.

I will definitely do it.thanks

Quote:
For example, an ID like "username-yyyyddmmhhmiss" might be "good enough" for a lot of use cases

This is a good idea, thanks.
Re: Generate GUID other than sys_GUID() (merged 3) [message #646282 is a reply to message #646277] Wed, 23 December 2015 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
This is a good idea, thanks.


Kind of what I said in my first answer:

Quote:
2/ Nothing prevents you from creating your own function, for instance using one of the timestamp functions.


Re: Generate GUID other than sys_GUID() (merged 3) [message #646283 is a reply to message #646282] Wed, 23 December 2015 23:15 Go to previous message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Quote:

Kind of what I said in my first answer:

Exactly, thank you so much.
Previous Topic: TO_NUMBER Doesn't Support These Elements: RN, rn, TM, and V ?
Next Topic: how to fetch deleted row
Goto Forum:
  


Current Time: Sun Jun 28 20:00:02 CDT 2026