All,

Like the other posters, I think that packages will be a valuable
addition to FB. I implemented packages for Fyracle and see below for
some observations & experiences.

Initial releases stored oracle-mode functions and procedures in
system tables, similar to FB. When I added packages I wanted to go
the way that was discussed in the previous posts, but in the end
decided against it, and stored the entire header source and entire
body source instead:

(1) reparsing the package header from a single table read and storing
it in the object cache when needed had much simpler code, also
because the fyracle object cache essentially stores the parse tree.
Why write code to build it from source, and then code again to build
it from disparate system tables?

(2) parsing the header text is very cheap in terms of CPU and memory,
probably cheaper than assembling it from various system tables.

Similar reasoning holds for the body. The body is compiled when
loaded into the database and the binary code is stored too. The
entire binary is recompiled if it depends on another object and that
object (header) changes. I figured that schema changes would occcur
infrequently and that recompiling even a complex package is fast on
current hardware.

When compared to the FB heritage, the above may seem wasteful, but I
doubt it is. Later I studied sqlite which recompiles its entire cache
of the schema on each schema change, and nobody complains about it
(unlike the approach in its prior 2002 releases where the schema was
reloaded on each db write). I am not saying that sqlite compares to
FB, only that in the current environment of ample CPU and memory and
an abundance of simple queries it may be better to have simple, short
code paths.

To be honest, I don't see the added value of being able to alter
individual items in the package body. It sounds cool, but to me the
recompilation cost it avoids is less than the complexity cost it adds
to the code paths.

Another reason for the above design choice was the implementation of
package cursors and package variables. Unlike functions and
procedures I did not have a pre-existing system table to store them
in, and I was growing weary of adding more system tables (upgrade
issues). So now, like the other package items, variables and cursors
only exist as part of the package source text, as parse sub-trees in
cached headers and as (cached) compiled code for the body.

Package variables are a bit special: they exist at execution time as
a cache structure of the connection object, with special opcodes to
access these items. Reason for this design is that in Oracle package
variables exist on a per connection basis. Note that for instance the
DBMS_OUTPUT pacakge depends on this: the PUT_ procedures store the
printed text in a package variable, and the client later reads this
variable to produce the output to the user.

If I had to recode it today, I would still go with the above design
choices.

Just my 2ct worth.

Paul