Supported Versions and Features

You can always discover which version of PL/SQL is running on the client by choosing Help About from the menu of the Developer tool. A partial version matrix appears in Table 20-3, but note that any patches you may have installed could cause the nth decimal place to differ from this table.

The next thing to realize is that just because the client has a particular PL/SQL release, that does not mean you have access to all of the corresponding server features you might expect from that version. With client PL/SQL 9.0.2, for example, you can do any of these things:

· Declare and use a local collection type inside a client program (and declare and use local variables of that type).

· Use a subtype, collection type, record type, or cursor variable type that has been declared in a package spec on the server.

· Declare a variable in a client program of a type that has been declared in a server-based package specification. This includes records and single-level collections.

· Declare and use variables as REFs to a user-defined object type.

· Base a block on a table with a collection-typed attribute.

· Base a block on an object table or on a table with an object column, as long as the object type does not include a collection-typed attribute.

But (and this is still on the client side, remember) you cannot:

· Create a standalone user-defined type (such as an object type or collection type).

· Declare a variable in a client program that is of a standalone user-defined type you have declared on the server.

There are two ways to work around this problem:

· Do not use any unsupported datatypes in the specifications of your server-side stored modules.

· Write a thin "translator" stored procedure to map server-side PL/SQL datatypes to the datatypes available on your client version of PL/SQL (where possible).

I strongly urge you to employ the second workaround. If you have built stored programs that make use of server-side PL/SQL datatypes in the parameter list, and if those datatypes are the most appropriate ones for the parameters, 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 might 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 is thoroughly able to be implemented. If your pre-Oracle9i client-side code must call a procedure that accepts a TIMESTAMP parameter, for example, it may make sense to encapsulate it inside another stored procedure that instead accepts a DATE. In fact, you could just overload the procedure if it's in a package. Here is the specification:

The package body would include the following implementation of the DATE version:

Now logger.note_the_time can be called from a pre-9i client program because it accepts one of the supported datatypes.

When implementing overloading to support different datatypes, do not rely on implicit type conversions in the body of the procedure. I tried that: PROCEDURE note_the_time (event_time_in IN DATE) ISBEGIN note_the_time(event_time_in); /* bad idea */END; Yup, that's an infinite recursive loop, and the means of escaping from it is not obvious (kill the shadow task using orakill.exe on Windows platforms, or kill -9 on Unix). No doubt a real DBA would have long ago imposed CPU limits with Oracle's "profile" feature on a guy like me.

Note that if you are using Forms 9i, you won't have this particular problem with TIMESTAMP because it is a supported datatype. You will, however, run into another annoyance when you attempt to use server-side package variables, as the next section illustrates.

20.5.2 Limitations of Oracle's Remote Invocation Model

With the Oracle software available at the time of this writing, there is no direct way for any PL/SQL program to directly use any of the following package constructs on a remote server:

· Variables (including constants)

· Cursors

· Exceptions

This limitation applies not only to client PL/SQL calling the database server, but also to server-to-server RPCs.

The simple workaround for variables is to use "get-and-set" programs to encapsulate the data. In general, you should be doing that anyway, as it's an excellent programming practice.

The workaround for cursors is to encapsulate them with "open, fetch, and close" subprograms. For example, if you've declared a book_cur cursor in the specification of the book_maint package, you could put this corresponding package body on the server:

Unfortunately, this approach won't work around the problem of using remote exceptions; the exception "datatype" is treated differently from true datatypes. Instead, you can use the RAISE_APPLICATION_ERROR procedure with a user-defined exception number between -20000 and -20999. See Chapter 6 for a discussion of how to write a good package to help your application manage this type of exception.

20.5.3 Client-Side PL/SQL Libraries

Broadly speaking, PL/SQL lives in two major places on the client side:

· In a reusable PL/SQL "library"

· In the application module (form, report, etc.) itself

A library can contain any number of procedures, functions, and packages; it is a kind of "super-package" structure. The library construct is unique to Oracle's client-side tools, and using it properly bears some discussion. The first thing to realize is that, although PL/SQL code may reside in a lot of different places—as Table 20-4 shows—only libraries can contain PL/SQL that you can share among different client modules.

Because PL/SQL libraries can dramatically improve the design and performance of your client-side applications, the next two sections present some of the concepts and non-obvious aspects of putting libraries to use.

20.5.3.1 Client PL/SQL libraries at design time

The usual convention of designing client PL/SQL is to put only module-specific code into the application module, and to put anything used by more than one module into a library.

When you put code into a library, you must "attach" the library to the module to be able to use its programs. This simple task is accomplished with the object navigator in the design-time tools. The PL/SQL library file and the application module file (for example, .FMB) do remain separate, though, and you must remember to copy them both when distributing your code.

One of the aspects of using libraries that confuses beginners is the prompt asking whether to remove path information from the library. Oracle wants to know which of two alternatives you intend to use at runtime:

· Retain full path information (such as c:\apps\forms\libs) because you will be deploying the library in exactly the same path in which you're developing it

· Remove path information and have the application tool search for the library according to certain system variables

The first option may be more expedient during development, but the second is more flexible and probably better in the long run, even though it requires the extra step of setting the path variables. The runtime search order is this:

1. First, check the tool-specific variable, such as FORMS90_PATH, REPORTS90_PATH, or GRAPHICS60_PATH.

2. Then, if the variable is not found, look in ORACLE_PATH.

On Windows environments, you can set these variables either via the control panel ("System Properties") or in the registry. Apparently, you cannot simply define them in a batch file.

To reveal a bit more of the story, the Oracle client tools I have tested seem to resolve external program references using the following search sequence:

1. Client-side built-in programs

2. "Program units" defined in the module itself (.FMB)

3. PLX versions of attached PL/SQL libraries on the path supplied by the environment variables discussed earlier

4. If no PLX version, the PLL versions of the attached PL/SQL libraries

5. Database server (using standard server name resolution)

When you are factoring programs into their best location during development, you will want to load both the PL/SQL library and an attaching application module (form, report, etc.) into the development environment at the same time. You will want to be conscious of saving a modified library before attempting to use any new features in the attaching module.

When modifying a library and an attaching module during the same editing session, you will need to do one or two things before the attaching module can see the changes. If you are using only the PLL version at runtime, you will need to save the PL/SQL library (File Save). If you have also generated a PLX version of the library on the path, you will need to generate it (File Administration Compile File, or Program Compile).

20.5.3.2 Client PL/SQL libraries at runtime

The runtime environment searches for PL/SQL libraries in the same way that the development environment does: if the library isn't called with explicit path information, the tool looks in the path given by the environment variables.

One of the selling points of PL/SQL libraries is the way that the client tools load them into memory. At startup, the runtime environment reads only a "directory" portion of the attached library into memory. Then, the first time that your module invokes a particular procedure, function, or package, the environment loads only that code into memory, in 4K chunks. Contrast this with what happens with PL/SQL code in an application module, where all of its local code gets loaded into memory at startup.

Because PL/SQL libraries load into memory as needed rather than all at once, putting large client programs into libraries instead of into individual application module files can help optimize memory use.

If you attach a PL/SQL library to multiple forms, you have the option of sharing library state—that is, using package variables as globals. The way to tell the runtime environment to do that is to specify a particular argument to CALL_FORM. For example:

If you don't supply this value for the data_mode parameter, the default behavior is NO_SHARE_LIBRARY_DATA, which means that each running form separately instantiates any referenced program(s) in the library.

• ⇐ Назад
• 248
• 249
• 250
• 251
• 252
• 253
• 254255256
• 257
• 258
• 259
• 260
• 261
• 262
• 263
• Далее ⇒