Hiding the Source Code of a Stored Program
When you create a PL/SQL program as described above, the source code will be available in clear text in the data dictionary, and any DBA will be able to view or even alter it. To protect trade secrets or to prevent tampering with your code, you might want some way to encrypt or otherwise obscure your PL/SQL source code before delivering it.
Oracle provides a command-line utility called wrap that converts many CREATE statements into a combination of plain text and hex. It's not true encryption, but it does go a long way toward hiding your code. Here are a few extracts from a wrapped file:
CREATE OR REPLACE FUNCTION wordcount wrapped0abcdabcd...snip...1WORDS:10:1LEN:1NVL:1LENGTH:1INSIDE_A_WORD:1BOOLEAN:...snip...a5 b 81 b0 a3 a0 1c 81b0 91 51 a0 7e 51 a0 b42e 63 37 :4 a0 51 a5 b a5b 7e 51 b4 2e :2 a0 7e b42e 52 10 :3 a0 7e 51 b4 2ed :2 a0 d b7 19 3c b7 :2 a0d b7 :2 19 3c b7 a0 47 :2 a0If you need true encryption—for example, to deliver information such as a password that really needs to be secure—you may not be able to rely on this facility.[6]
[6] Oracle does provide a way to incorporate DES (Data Encryption Standard) security into your own applications using the built-in package DBMS_OBFUSCATION_TOOLKIT.
To learn more about the wrap utility, skip to Chapter 19.
2.3 Oracle's PL/SQL-Based Developer Tools
If you want to use PL/SQL for all of your programming needs, including the user interface, one way to accomplish that goal is to use two of Oracle's developer tools, commonly known as Forms and Reports. The programmer's tools are part of what Oracle currently calls the Oracle9i Developer Suite, which also includes a Java developer environment and a software configuration manager. Another tool, which was known as Oracle Graphics, is no longer sold as a separate product, but its functionality is available in Forms and Reports.
Included with the Forms Builder and Reports Developer products is a runtime engine that allows programmers to run their own applications; actually deploying your applications for end users, though, involves licensing a runtime environment such as Oracle9i Forms Services or Oracle9i Reports Services. These "services" are components of yet another product, Oracle9i Application Server, which would typically run on a mid-tier server machine, offering forms and reports to end users via Java-enabled browsers. However, older versions of Oracle Forms and Oracle Reports—still in use in many Oracle shops—run in a so-called "fat client" arrangement, in which the runtime software resides on every end user's desktop machine.
Throughout this book, you will notice references to Oracle's "client-side" developer tools, which refer to these tools. From the perspective of the database, everything is a client, even though you may be running multi-tier arrangements instead of just client and server.
Figure 2-5 shows what the Oracle9i Forms Builder user interface looks like. Look closely and you can see the built-in PL/SQL editor in the second subwindow from the right.
Figure 2-5. The programmer's user interface in Oracle Forms Builder
To help provide the kind of functionality that most users expect, Oracle provides an assortment of extensions to PL/SQL that are unique to the developer tools. For example, in Oracle Forms, PL/SQL programs can:
· Use on-screen item values as bind variables—for example, in a block named bk with a user-enterable field named isbn, the notation :bk.isbn would bind its value for the current record.
· Populate drop-down lists using data obtained from a database table.
· Automatically execute logic on end-user events such as clicking the mouse or navigating out of an input item.
· Control the appearance of the user interface, for example by displaying or hiding input items or windows.
Client-side PL/SQL can exist in one of three places in the application.
Triggers
These PL/SQL blocks fire on specific application events such as startup, mouse click, or data validation.
Program units inside a particular application
These are available to call from any other PL/SQL program in the same application.
A PL/SQL library of your own reusable client-side programs
You can call these from any client-side application.
And, of course, you can call server-side PL/SQL programs from inside these applications as well. However, there are a number of limitations in the way that Oracle has implemented these calls. For example, you can't use server-side package variables or cursors directly in a client-side program (you have to put a wrapper function around them instead).
2.3.1 Moving PL/SQL Programs Between Client and Server
Moving PL/SQL code between the client and the server can be easy, but only if you haven't used any nonportable aspects of the language. For example, Figure 2-6 shows the result of doing a "drag and drop" of two stored packages from the database to a client-side PL/SQL library; the bodies didn't compile, as indicated by an asterisk. This reflects the fact that the packages use PL/SQL features that are not supported on the client side. One of the features is native dynamic SQL. To resolve this problem, I could eliminate the offending programs from the package body, or possibly convert the code to use the client-side EXEC_SQL built-in.
I had another problem with these packages: while the client-side PL/SQL engine can handle programmer-defined exceptions raised with the RAISE_APPLICATION_ERROR built-in on the server, client applications cannot themselves raise this exception. A workaround might involve centralizing your programmer-defined client exceptions in a client package specification.