bug: pl/sql object generating function executes too many times

From: <eric_at_barrodale.com>
Date: 27 Feb 2006 15:32:17 -0800
Message-ID: <1141083137.220527.147670@v46g2000cwv.googlegroups.com>

I've written some types that contain pl/sql static functions that generate instances of these types (what I would call constructors in an object oriented programming language).
What I'm observing is that, although I only execute the function once, the server runs the function multiple times. I have two problems with this:

1. these functions take a fair bit of time to execute (they call external procedures), so having their body execute multiple times really slows things down.
2. part of the internals of these functions is to create files and add rows to tables, which means my tables are getting several times as many rows in them as desired.

Below I've included a small test case which illustrates the problem. Note how many times the dbms_output.put_line call is executed. The number of times the function's body gets invoked seems to be related to the number of members in the object type. Is there some qualifier I need to add the function declaration so that it only gets called once?

CREATE OR REPLACE TYPE Penta AS OBJECT (

   aa integer,
   ab float,
   ac float,
   ad integer,
   ae float,
  static FUNCTION buildPenta(a integer) return Penta );
/

show err;

CREATE OR REPLACE TYPE BODY Penta AS
  static FUNCTION buildPenta(a integer)
  RETURN Penta
  AS
  BEGIN

     dbms_output.put_line('hello');
     return new Penta(a,a,a,a+1,a-1);

  END;
END;
/

show err;

set serveroutput on;

create table ptable( a penta);

insert into ptable values( penta.buildPenta(4));

SQL> insert into ptable values( penta.buildPenta(4)); hello
hello
hello
hello
hello
hello

1 row created.

SQL> select * from ptable;

A(AA, AB, AC, AD, AE)

---

PENTA(4, 4, 4, 5, 3) SQL> Thank you.
Eric_at_barrodale.com Received on Mon Feb 27 2006 - 17:32:17 CST