How can i keep sys_guid() unique in multiple table insert
I want to use unique sys_guid() for insert multiple table but out put are difference sys_guid()
drop table T1;
drop table T2;
drop table T3;
create table T1 (ROW_ID varchar2(50));
create table T2 (ROW_ID varchar2(50));
create table T3 (ROW_ID varchar2(50));
insert all
into T1 values (NEW_ROW_ID)
into T2 values (NEW_ROW_ID)
into T3 values (NEW_ROW_ID)
select sys_guid() as NEW_ROW_ID from dual;
select * from t1 union all select * from t2 union all select * from t3;
ROW_ID
--------------------------------
CEFF10B45BFD3705E044002128808A0C
CEFF10B45BFE3705E044002128808A0C
CEFF10B45BFF3705E044002128808A0C
Software/Hardware used:
Oracle 9
Software/Hardware used:
Oracle 9
ASKED:
November 26, 2012 12:40 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
SYS_GUID() was designed to generate a ’globally unique identifier’, so when you use it in inserts or updates it is executed once for each row (see Oracle® Database SQL Language Reference
11g Release 1 (11.1)) – SYS_GUID).
If you want to insert the same value into all 3 tables, you would probably need to do something like this:
declare
new_row_id varchar2(50);
begin
select sys_guid() into new_row_id from dual;
insert all into t1 values (new_row_id)
into t2 values (new_row_id)
into t3 values (new_row_id)
select new_row_id from dual;
end;
/