1

I have to insert several rows into an Oracle sql table. Like

insert into table (col1, col2, col_unique_guid) select col1, "bla", sys_guid() from source_table;

Unfortunately sys_guid() is the same for all rows - is there a way maybe over sequences to insert indiviual guids for each row?

Ringelmann
  • 41
  • 3
  • 3
    Did you read this? http://stackoverflow.com/questions/3037462/how-to-generate-a-guid-in-oracle The guids are very similar but they are different. Just to be sure, check it again! – RMH May 19 '17 at 14:42
  • Do you have a unique constraint or primary key on `col_unique_guid`? – APC May 19 '17 at 15:14
  • Can you show generated result set with the same result as you say ? – Seyran May 19 '17 at 18:13
  • @Ringelmann I'm seeing the same behvaiour on Oracle 12.2.0.1 with a less straightforward (but essentially the same) statement INSERT INTO TABLE SELECT * FROM (...) where sys_guid() is called in the (...) portion. I've had to create an INSERT trigger to insert the primary key as a work around. – Aidan Whitehall Oct 01 '18 at 13:02

1 Answers1

0

I could not reproduce your issue, but in some situations oracle may 'optimize' the function call in a query in a way that the function is called only once during query execution.

You can force Oracle to call the function for each row by writing a wrapper function and passing the rownum to it:

CREATE OR REPLACE function get_my_guid(i number) return raw
is
begin
  return sys_guid();
end;
/

e.g.:

insert into test_guid (col1, col2, col_unique_guid) select col1, col2, get_my_guid(rownum) from test_guid_src;
KFx
  • 111
  • 5