B.2.1 No Server-Side PL/SQL Datatypes

Parameters in stored procedures, as well as the RETURN datatype of stored functions, can only have one of these datatypes if they are to be used by Oracle Forms. This rule applies both to standalone and package modules. Remember: when you work in a Oracle Developer/2000 tool, you are using PL/SQL Version 1.1; any code you write, including calls to stored modules, must conform to Version 1.1 compiler syntax rules. The specification or "public" side of a stored module must look like a Version 1.1 module. Behind the specification -- in other words, the implementation of that module -- can have all the server-side PL/SQL features you can pack into it. You just can't let any of that show outside of the body of the module or package.

Suppose you define a stored procedure as follows:

FUNCTION get_row RETURN BINARY_INTEGER;

If you try to execute the function inside an Oracle Forms PL/SQL block, the compiler will tell you:

Identifier 'GET_ROW' must be declared

It literally cannot even find a match for the function if the datatype does not conform to PL/SQL Version 1 valid datatypes. The SYS.PSTUB procedure will have been unable to generate a stub and so the name remains unresolved.

There are two ways to work around this problem:

• Do not use any server-side PL/SQL datatypes in the specifications of your stored modules.

• Write an overlay stored procedure which maps server-side PL/SQL datatypes to Version 1 datatypes (where possible).

I strongly urge you to employ the second workaround. If you have built stored objects which make use of server-side PL/SQL datatypes in the parameter list, and if that datatype is the most appropriate one for the parameter, you shouldn't change that module's specification. You should always try to take advantage of the most advanced features of a language. Don't choose a lowest common denominator solution unless there are no other options.

In many situations, you won't have the opportunity to change the specification (parameter list) of a stored module. It will have been written by others, perhaps for another application, and cannot be modified without possibly affecting those other applications.

In this case, the second workaround is annoying but thoroughly able to be implemented. If your Oracle Developer/2000 code must call the get_row function, for example, you can create another stored object (let's call it Oracle Developer/2000_get_row) which does not return a BINARY_INTEGER, but instead returns a NUMBER. The specification and body for Oracle Developer/2000_get_row would be:

FUNCTION Oracle Developer 2000_get_row RETURN INTEGER IS
BEGIN
   RETURN get_row;
END;

The Oracle Developer/2000_get_row can be called from a Oracle Developer/2000 program because it returns one of the supported datatypes. In this case, PL/SQL will certainly perform the necessary implicit conversions because BINARY_INTEGER and NUMBER are compatible datatypes.

B.2.2 No Direct Stored Package Variable References

This is the most serious drawback of the implementation for accessing stored objects from Oracle Developer/2000. You simply cannot use the dot notation to reference a package variable, whether it be a string, exception, or cursor. Consider the pet maintenance package which we discussed earlier in this book.

PACKAGE pet_maint
IS
   /*------------------ Global Variables ------------------*/
   max_pets_in_facility INTEGER := 120;
   pet_is_sick EXCEPTION;
   CURSOR pet_cur RETURN pet%ROWTYPE;

   /*------------------- Public Modules -------------------*/
   FUNCTION next_pet_shots (pet_id_in IN NUMBER) RETURN DATE;
   PROCEDURE set_schedule (pet_id_in IN NUMBER);
   PROCEDURE check_activity (pet_id_in IN NUMBER);

END pet_maint;

You can call the modules using dot notation, as in:

pet_maint.check_activity (1503);

but you cannot make reference to any of the package variables using this same dot notation. All of the statements shown in boldface will fail to compile:

BEGIN
   IF pet_maint.max_pets_in_facility < new_count
   THEN
      ...
      OPEN pet_maint.pet_cur;
      ...
   END IF;
EXCEPTION
   WHEN pet_maint.pet_is_stick
   THEN
      ...
END;

This restriction puts you in a tough situation. You really need to build packages; there are just too many advantages to this structure to ignore it. You also need to store as many of your objects as possible in the database. When you are done creating your elegant package, filled with overloaded programs and variables and exceptions, however, you find that you cannot use it in Oracle Forms or Oracle Reports or Oracle Graphics.

What can you do? You simply have to get rid of that dot notation. This is one instance where the workaround actually results in better code! Whenever you build a package with variables like pet_maint.max_pets_in_facility, you should avoid letting programmers directly reference those variables. Instead you are much better off building a pair of "get and set" modules around the package variable. This way, a programmer accesses the variable through a programmatic interface. This gives you more control over that variable. You can make sure that any changes to the variable are valid. You also retain the freedom to change the name or data structure of the variable. If programmers embed direct references to pet_maint.max_pets_in_facility in their code, you can never change how you store that value. If you hide it behind modules, you could decide to store that value in a PL/SQL table or record and not have any impact outside of the package.

The following example shows a new specification for the pet_maint package. In this version the max_pets_in_facility variable has been moved to the body of the package and is replaced by the get_max and set_max modules.

PACKAGE pet_maint
IS
   /*------------------ Global Variables ------------------*/
   pet_is_sick EXCEPTION;
   CURSOR pet_cur RETURN pet%ROWTYPE;

   /*------------------- Public Modules -------------------*/
   FUNCTION get_max_pets RETURN INTEGER;
   PROCEDURE set_max_pets (max_in IN INTEGER);
   FUNCTION next_pet_shots (pet_id_in IN NUMBER) RETURN DATE;
   PROCEDURE set_schedule (pet_id_in IN NUMBER);
   PROCEDURE check_activity (pet_id_in IN NUMBER);

END pet_maint;

The conversion to a programmatic interface is fairly straightforward for variables. However, it is considerably more complex to manipulate cursors through a procedural interface, and it is impossible to do so for exceptions.

Prior to PL/SQL Release 2.2, any reference to a cursor had to have the cursor name hardcoded. In subsequent releases, you can create cursor variables (explained in Chapter 6, Database Interaction and Cursors ). Without cursor variables, you will need to build a set of modules in order to make reference to a cursor declared in a package.The following example contains examples of the kinds of modules you can write to hide the cursor and then access it from Oracle Developer/2000.

PROCEDURE open_pet_cur IS
BEGIN
   /* Open the cursor if not already open */
   IF NOT pet_maint.pet_cur%ISOPEN
   THEN
      OPEN pet_maint.pet_cur;
   END IF;
END;

PROCEDURE fetch_pet_cur
   (pet_rec_out OUT pet%ROWTYPE, fetch_status_out OUT VARCHAR2)
/*
|| Fetch next record from the cursor. Also set a status variable
|| to indicate if a record was fetched (corresponds to
|| the %FOUND attribute).
*/
IS
   	BEGIN
   FETCH pet_maint.pet_cur INTO pet_rec_out;
   IF pet_maint.pet_cur%FOUND
   THEN
      fetch_status_out := 'FOUND';
   ELSE
      fetch_status_out := 'NOTFOUND';
   END IF;
END;

PROCEDURE close_pet_cur IS
BEGIN
   /* Close the cursor if still open */
   IF pet_maint.pet_cur%ISOPEN
   THEN
      CLOSE pet_maint.pet_cur;
   END IF;
END;

That wasn't a whole lot of fun, but at least it is doable. The last variable left exposed in the pet maintenance package is an exception: pet_is_sick. I do not know of any way to build a programmatic interface which would return an exception that you could then raise in your program and reference in an exception handler. A function cannot have an EXCEPTION as a RETURN datatype. The exception "datatype" is treated differently from true datatypes. As a result, you will not be able to trap and handle package-specific exceptions in a stored package unless the stored package uses the RAISE_APPLICATION_ERROR procedure with a user-defined exception number between -20000 and -20999.

B.2.3 No Direct Remote Procedure Calls

This very powerful feature is unavailable from Oracle Developer/2000. Instead, you will have to create a synonym for the remote procedure and then execute the synonym rather than the procedure directly. (Don't forget to grant EXECUTE authority on the synonym!)

Suppose I want to execute the following procedure from an Oracle Forms application:

new_schedule@HQ_repository;

I need to do the following:

1. Create a synonym:

   CREATE SYNONYM HQ_new_schedule FOR new_schedule@HQ_repository;

2. Grant EXECUTE authority on that synonym:

   GRANT EXECUTE ON HQ_new_schedule TO <user_or_role>;

3. Call the synonym in my Oracle Forms trigger or program unit:

   HQ_new_schedule;

B.2.4 No Default Parameter Values

PL/SQL Version 2.0 does not allow you to use default parameter values when you are performing a remote procedure call. (You must leave any arguments which have default values in the specification out of the module execution.) Even if you do not append an @ symbol on a call to a stored procedure, PL/SQL does consider that a remote procedure call because the client-side application is "remote" from the server.

Unfortunately, if you do try to call a stored object from a Oracle Developer/2000 component and rely on a default value, the PL/SQL error does not offer much help. It will not ask you to include values for all parameters. It will simply tell you:

Error at line N:
Identifier 'STORED_OBJECT' must be declared

The first time I encountered this error, I panicked. Why couldn't Oracle Forms find the stored procedure? I logged in through SQL*Plus and could run the module there. So I knew it was defined and stored in the database. Was it a security issue within Oracle Forms? Did I have to do something special to get Oracle Forms to realize it was looking for a stored object and not a local program unit? In the end, I discovered that Oracle Forms could find the object, it just couldn't use it (create a stub for it) because I hadn't passed the full set of arguments.

So when your Oracle Developer/2000 PL/SQL compiler tells you that an identifier "must be declared", make sure you that you included an argument for each parameter in the stored module parameter list. You should not consider this too much of a hardship; good programming practice dictates that you never rely on the default values anyway.