 |
|
EnterpriseDB: Forward Declarations
Oracle Tips by
Burleson
|
A
forward declaration is a specification for a function or a procedure
that is declared before its body so that other objects may use it. An
example is easier than trying to explain it.
CREATE OR REPLACE PACKAGE yada
AS
PROCEDURE yada;
END;
CREATE OR REPLACE PACKAGE BODY yada
AS
PROCEDURE yada2;
PROCEDURE yada
AS
BEGIN
yada2;
END;
PROCEDURE yada2
AS
BEGIN
NULL;
END;
END;
The
procedure yada2 is a private procedure that is only used within this
package. It is being called from the first procedure in the package
so a forward declaration is created to allow that procedure to know
the signature of the second procedure.
You will
run into this most often in recursive procedures or two procedures
that call each other. The procedure needs to know the spec of the
other before it can be called but which will be defined first? To
resolve this question, forward declarations are used.
The
above package compiles and works fine without changes. If I compile
it in EnterpriseDB however:
ERROR:
syntax error at or near ";" at character 60
LINE 4: PROCEDURE yada2;
^
I have
not discovered a work around for this issue beyond creating multiple
packages out of a single package. Many people have not heard of
forward declarations and even less use them. However, if you do need
a forward declaration, this can be a huge problem.
Package Level Constants
There is
not a lot to be said about this subject. At this point in time,
constant declarations in package specifications are not supported.
For example, this code will not compile in EnterpriseDB:
CREATE OR REPLACE PACKAGE yada
AS
yada CONSTANT VARCHAR2(30) := 'YadaYada';
END;
The
error I get is:
ERROR:
syntax error at or near "VARCHAR2" at character 51
LINE 3: yada CONSTANT VARCHAR2(30) := 'YadaYada';
The
easiest work around here is to remove the CONSTANT keyword. This is
more of an annoyance than a real problem. Global search and replace
will fix this easy enough.
User-defined Composite Parameters
Currently, you cannot use a record type or SPL table type as an input
or return result for a procedure or function. For example, you cannot
declare a record in a package spec and use it as a parameter for a
function in that package.
A good
example of this would be the UTL_FILE package found in Oracle. The
package specification includes both a record declaration and it uses
that declaration as inputs and returns.
CREATE OR REPLACE PACKAGE utl_file AS
TYPE file_type IS RECORD (
fh TEXT );
FUNCTION FOPEN(
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN INTEGER DEFAULT 1)
RETURN FILE_TYPE;
--
More declarations below
END;
The
fopen function returns a file_type type which is declared just above.
This code will compile fine.
I will
create a body for the code just to test it:
CREATE OR REPLACE PACKAGE BODY utl_file AS
FUNCTION FOPEN(
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN INTEGER DEFAULT 1)
RETURN FILE_TYPE
IS
fh file_type;
BEGIN
fh.fh := 'Test';
RETURN fh;
END;
END;
The body
also compiles just fine. Now, if I try to run it, I get an error:
edb=# DECLARE
edb-# fh utl_file.file_type;
edb$# BEGIN
edb$# fh := utl_file.fopen('/tmp', 'dat.txt', 'w', 1 );
edb$# END;
ERROR:
EDB-0A000: input of anonymous composite types is not implemented
CONTEXT: SPL Procedure "FOPEN" while casting return value to
function's return type
edb=#
The
short story here is that user defined composite types are not
supported as parameters or return types. I show one way to work
around this issue below where I show how to implement UTL_FILE in
EnterpriseDB.
Alter Package Signature
When
changing a package specification that has a package body, you must
first drop the package specification. That is, if a package has a
body and you need to change the way the procedures in the package are
called, you must first execute a drop package command.
This is
another issue that is just easier to show. If I compile the package
below (in Oracle and EnterpriseDB):
CREATE OR REPLACE PACKAGE yada
AS
PROCEDURE yada;
END;
CREATE OR REPLACE PACKAGE BODY yada
AS
PROCEDURE yada
AS
BEGIN
NULL;
END;
END;
Then I
try to change the spec:
CREATE OR REPLACE PACKAGE yada
AS
PROCEDURE yada(p_change IN NUMBER );
END;
In
Oracle, this compiles without error. In EnterpriseDB, I get an error
message:
ERROR:
EDB-00323: package body should contain definition for all public
elements
HINT:
You may use DROP PACKAGE [BODY] statement before redefining this
package.
There is
no work around for this. The problem with dropping the body, of
course, is the loss of permissions that may have been assigned. Make
sure you save off any permissions before dropping the package.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
|
