bug: pl/sql object generating function executes too many times
- From: eric@xxxxxxxxxxxxx
- Date: 27 Feb 2006 15:32:17 -0800
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@xxxxxxxxxxxxx
.
- Prev by Date: Re: Oracle licence question
- Next by Date: Temporary tablespaces using tempfile and concurrent insert/update
- Previous by thread: Could not open the Enterprise Manager
- Next by thread: Temporary tablespaces using tempfile and concurrent insert/update
- Index(es):