| Generate GUID other than sys_GUID() (merged 3) [message #646210] |
Tue, 22 December 2015 04:42  |
 |
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() (merged 3) [message #646217 is a reply to message #646210] |
Tue, 22 December 2015 05:26   |
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   |
 |
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 #646269 is a reply to message #646210] |
Wed, 23 December 2015 09:27   |
 |
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.
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|