Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter