I'm using the Oracle Cloud Database.
I have a table with a column - location SDO_GEOMETRY.
I have a function that generates a random value to populate the column, generate_random_location().
When I run the below update statement, the function is called 8 times for each row updated.
update dd_members_t
set location = generate_random_location(v_island)
where member_id = 3;
To troubleshoot, I removed all of the code from the function except a single dbms_output.putline('Start'), set the in parameter to default null and set it to return null. It is still called 8 times.
update dd_members_t
set location = generate_random_location()
where member_id = 3;
I set the function to deterministic, which, of course, only gets called once, but that defeats the purpose of returning a random value.
I have solved the problem by using a bulk collect (the function only gets called once per row in the select statement)
select member_id,
generate_random_location(v_dino_island)
bulk collect into l_member_ids
from dd_members
where.....and then
forall indx in 1 .. l_member_ids.count
update dd_members_t
set location = l_member_ids (indx).new_location
where member_id = l_member_ids (indx).member_id;But that still leaves the question of why is the function being called 8 times per record in a simple update statement, and is there a way to stop that?
It would seem to pertain to the complexity of the object type.
For example
SQL> drop table t purge;
Table dropped.
SQL> drop type complex_obj;
Type dropped.
SQL>
SQL> create or replace
2 type complex_obj as object ( a int, b int, c int, d int, e int );
3 /
Type created.
SQL>
SQL> create table t (
2 member_id int,
3 location complex_obj );
Table created.
SQL>
SQL> insert into t values (1,null);
1 row created.
SQL>
SQL> exec dbms_application_info.set_client_info('0');
PL/SQL procedure successfully completed.
SQL>
SQL> create or replace
2 function generate_random_location return complex_obj is
3 begin
4 dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
5 return null;
6 end;
7 /
Function created.
SQL>
SQL> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
0
1 row selected.
SQL>
SQL> update t
2 set location = generate_random_location
3 where member_id = 1;
1 row updated.
SQL>
SQL> select userenv('client_info') executions from dual;
EXECUTIONS
----------------------------------------------------------------
6
1 row selected.
SQL>
SQL>
And now we repeat with 10 elements
SQL> drop table t purge;
Table dropped.
SQL> drop type complex_obj;
Type dropped.
SQL>
SQL> create or replace
2 type complex_obj as object ( a int, b int, c int, d int, e int ,
3 a1 int, b1 int, c1 int, d1 int, e1 int );
4 /
Type created.
SQL>
SQL> create table t (
2 member_id int,
3 location complex_obj );
Table created.
SQL>
SQL> insert into t values (1,null);
1 row created.
SQL>
SQL> exec dbms_application_info.set_client_info('0');
PL/SQL procedure successfully completed.
SQL>
SQL> create or replace
2 function generate_random_location return complex_obj is
3 begin
4 dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
5 return null;
6 end;
7 /
Function created.
SQL>
SQL> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
0
1 row selected.
SQL>
SQL> update t
2 set location = generate_random_location
3 where member_id = 1;
1 row updated.
SQL>
SQL> select userenv('client_info') executions from dual;
EXECUTIONS
----------------------------------------------------------------
11
1 row selected.
SQL>
SQL>
My hypothesis is that the statement is under the covers processed something *similar* to:
update t
set
location.a = generate_random_location.a,
location.b = generate_random_location.b,
location.c = generate_random_location.c,
location.d = generate_random_location.d,
location.e = generate_random_location.e,
...
where member_id = 1;
A simple workaround is to use a scalar select
SQL> drop table t purge;
Table dropped.
SQL> drop type complex_obj;
Type dropped.
SQL>
SQL> create or replace
2 type complex_obj as object ( a int, b int, c int, d int, e int ,
3 a1 int, b1 int, c1 int, d1 int, e1 int );
4 /
Type created.
SQL>
SQL> create table t (
2 member_id int,
3 location complex_obj );
Table created.
SQL>
SQL> insert into t values (1,null);
1 row created.
SQL>
SQL> exec dbms_application_info.set_client_info('0');
PL/SQL procedure successfully completed.
SQL>
SQL> create or replace
2 function generate_random_location return complex_obj is
3 begin
4 dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
5 return null;
6 end;
7 /
Function created.
SQL>
SQL> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
0
1 row selected.
SQL>
SQL> update t
2 set location = ( select generate_random_location from dual )
3 where member_id = 1;
1 row updated.
SQL>
SQL> select userenv('client_info') executions from dual;
EXECUTIONS
----------------------------------------------------------------
1
1 row selected.
SQL>
SQL>