Re: One insert many function calls

I was able to reproduce the results you obtained.

Although I don't have an explanation for them, I did find a way to avoid what apparently is multiple calls to the test5 function. The following procedure uses a variable x to store the results of the test5 function prior to the insert statement.

SQL> declare
  2 x mdsys.sdo_geometry := test5();   3 begin
  4 insert into mygeom values (111,x);   5 end;
  6 /
TEST5 PL/SQL procedure successfully completed.

Frank Hubeny

gaop_at_my-deja.com wrote:

> I have a function which creates an instance of object type. When
> I use this function to generate an instance for an insert, the
> function is called many time. The following is a tes I have run.
> The function TEST5 gets called 8 time for one row inserted.
>
> Any ideas?
>
> Regards,
>
> Gao Peng
>
> *********
>
> SQL>
> SQL> CREATE OR REPLACE FUNCTION TEST5 RETURN MDSYS.SDO_GEOMETRY AS
> 2 BEGIN
> 3 dbms_output.put_line('TEST5');
> 4 RETURN MDSYS.SDO_GEOMETRY(0, 0, null, null, null);
> 5 END;
> 6 /
>
> Function created.
>
> SQL> CREATE TABLE MYGEOM (gid number, geom MDSYS.SDO_GEOMETRY);
>
> Table created.
>
> SQL> INSERT INTO MYGEOM values (111, TEST5());
> makeOne: [B_at_2862355c 109 SYS.ODCIINDEXINFO class oracle.sql.STRUCT
> makeOne returns: class oracle.sql.STRUCT
> TEST5
> TEST5
> TEST5
> TEST5
> TEST5
> TEST5
> TEST5
> TEST5
>
> 1 row created.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Sep 23 1999 - 00:15:53 CDT