Figure 2-6. The Object Navigator in Oracle Forms shows the result of dragging and dropping two packages from the server to the client

Often, PL/SQL developed for the client is not a good candidate to move to the server, because it is likely to contain many things that limit its portability. For example, if the code contains references to client-specific bind variables like :bks.isbn, or uses Oracle Forms-specific built-ins like SHOW_ALERT or EXECUTE_QUERY, it will fail to compile on the server.

If you are doing a lot of work with Oracle's client tools, you may want to get a copy of Oracle Developer Advanced Forms & Reports, by Peter Koletzke and Paul Dorsey. As its title indicates, though, it is not a "starter" book.

2.4 Calling PL/SQL from Other Languages

Sooner or later, you will probably want to call PL/SQL from C, Java, Perl, Visual Basic, or any number of other places. This seems like a reasonable request, but if you've ever done cross-language work before, you may be all too familiar with some of the intricacies of mating up language-specific datatypes—especially composite datatypes like arrays, records, and objects—not to mention differing parameter semantics or vendor extensions to "standard" application programming interfaces (APIs) like Oracle DataBase Connectivity (ODBC).

I will show a few examples. Let's say that I've written a PL/SQL function that accepts an ISBN expressed as a string and returns the corresponding book title:

/* File on web: booktitle.fun */CREATE OR REPLACE FUNCTION booktitle (isbn_in IN VARCHAR2) RETURN VARCHAR2IS l_isbn books.title%TYPE; CURSOR icur IS SELECT title FROM books WHERE isbn = isbn_in;BEGIN OPEN icur; FETCH icur INTO l_isbn; CLOSE icur; RETURN l_isbn;END;/

In SQL*Plus, I could call this in several different ways. The shortest way would be as follows:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(booktitle('0-596-00180-0'))Learning Oracle PL/SQL PL/SQL procedure successfully completed.

Let's see how I might call this function from the following environments:

· C, using Oracle's precompiler (Pro*C)

· Java, using JDBC

· Perl, using Perl DBI and DBD::Oracle

· PL/SQL Server Pages

These examples are very contrived—for example, the username and password are hardcoded, and the programs simply display the output to stdout. Moreover, I'm not even going to pretend to describe every line of code. Still, these examples will give you an idea of some of the patterns you may encounter in different languages.

2.4.1 C: Using Oracle's Precompiler (Pro*C)

Oracle supplies at least two differentC-language interfaces to Oracle: one called OCI (Oracle Call Interface), which is largely the domain of rocket scientists; and the other called Pro*C. OCI provides hundreds of functions from which you must code low-level operations such as open, parse, bind, define, execute, fetch . . . and that's just for a single query. Because the simplest OCI program that does anything interesting is about 200 lines long, I thought I'd show a Pro*C example instead.

Pro*C is a precompiler technology that allows you to construct source files containing a mix of C, SQL, and PL/SQL. You run this through Oracle's proc program, and out comes C code:

/* File on web: callbooktitle.pc */#include <stdio.h>#include <string.h> EXEC SQL BEGIN DECLARE SECTION; VARCHAR uid[20]; VARCHAR pwd[20]; VARCHAR isbn[15]; VARCHAR btitle[400];EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA.H; int sqlerror( ); int main( ){ /* VARCHARs actually become a struct of a char array and a length */ strcpy((char *)uid.arr,"scott"); uid.len = (short) strlen((char *)uid.arr); strcpy((char *)pwd.arr,"tiger"); pwd.len = (short) strlen((char *)pwd.arr); /* this is a cross between an exception and a goto */ EXEC SQL WHENEVER SQLERROR DO sqlerror( ); /* connect and then execute the function */ EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; EXEC SQL EXECUTE BEGIN :btitle := booktitle('0-596-00180-0'); END; END-EXEC; /* show me the money */ printf("%s\n", btitle.arr); /* Disconnect from ORACLE. */ EXEC SQL COMMIT WORK RELEASE; exit(0);} sqlerror( ){ EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1);}

As you can see, Pro*C is not an approach for which language purists will be pining away. And trust me, you don't want to mess with the C code that this generates. Nevertheless, many companies find that Pro*C (or Pro*Cobol or any of several other languages Oracle supports) serves a reasonable middle ground between, say, Visual Basic (too slow and clunky) and OCI (too hard).

For more information about Pro*C, the best source is Oracle's own documentation.

2.4.2 Java: Using JDBC

As with C, Oracle provides a number of different Java approaches to connecting to the database. The embedded SQL approach, known as SQLJ, is similar to Oracle's other precompiler technology, although a bit more debugger-friendly. A more popular and Java-centric approach is known as JDBC, which doesn't really stand for anything, but the usual interpretation is "Java DataBase Connectivity."

/* File on web: callbooktitle.java */import java.sql.*; public class book { public static void main(String[] args) throws SQLException { // initialize the driver and try to make a connection DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ( )); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:o92", "scott", "tiger"); // prepareCall uses ANSI92 "call" syntax CallableStatement cstmt = conn.prepareCall("{? = call booktitle(?)}"); // get those bind variables and parameters set up cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.setString(2, "0-596-00180-0"); // now we can do it, get it, close it, and print it cstmt.executeUpdate( ); String bookTitle = cstmt.getString(1); conn.close( ); System.out.println(bookTitle); }}

This particular example uses the thin driver, which provides great compatibility and ease of installation (all the network protocol smarts exists in a Java library), at some expense of communications performance. An alternative approach would be to use what's known as the oci driver. Don't worry, there's no rocket scientist programming required to use it, despite the name!

To learn more about Java programming with Oracle, see Java Programming with Oracle JDBC by Don Bales and Java Programming with Oracle SQLJ by Jason Price, both from O'Reilly.

2.4.3 Perl: Using Perl DBI and DBD::Oracle

Much beloved by the system administration community, Perl is something of the mother of all open source languages. Now nearly in Version 6, it does just about everything and seems to run everywhere. And, with nifty auto-configuration tools such as CPAN (Comprehensive Perl Archive Network), it's a cinch to install community-supplied modules such as the DataBase Interface (DBI) and the corresponding Oracle driver, DBD::Oracle.

/* File on web: callbooktitle.pl */#!/usr/bin/perl use strict;use DBI qw(:sql_types); # either make the connection or DIEmy $dbh = DBI->connect( 'dbi:Oracle:o92', 'scott', 'tiger', { RaiseError => 1, AutoCommit => 0 }) || die "Database connection not made: $DBI::errstr"; my $retval; # make parse call to Oracle, get statement handleeval { my $func = $dbh->prepare(q{ BEGIN :retval := booktitle(isbn_in => :bind1); END; }); # bind the parameters and execute $func->bind_param(":bind1", "0-596-00180-0"); $func->bind_param_inout(":retval", \$retval, SQL_VARCHAR); $func->execute; }; if( $@ ) { warn "Execution of stored procedure failed: $DBI::errstr\n"; $dbh->rollback;} else { print "Stored procedure returned: $retval\n";} # don't forget to disconnect$dbh->disconnect;

Perl is one of those languages in which it is shamelessly easy to write code that is impossible to read. It's not a particularly fast or small language, either, but there are compiled versions that at least address the speed problem.

For more information about Perl and Oracle, see Perl for Oracle DBAs by Andy Duncan and Jared Still, and Programming the Perl DBI by Alligator Descartes, both from O'Reilly. There are also many excellent books on the Perl language.

2.4.4 PL/SQL Server Pages

Although the PL/SQL Server Pages (PSP) environment is proprietary to Oracle, I thought I would mention it because it's a quick way to get a web page up and running. PSP is another precompiler technology; it gives you the ability to embed PL/SQL into HTML pages.

/* File on web: favorite_plsql_book.psp */<%@ page language="PL/SQL" %><%@ plsql procedure="favorite_plsql_book" %><HTML> <HEAD> <TITLE>My favorite book about PL/SQL</TITLE> </HEAD> <BODY> <%= booktitle( '0-596-00180-0') %> </BODY></HTML>

That <%= %> line means "process this as PL/SQL and return the result to the page." When properly installed on a web server connected to an Oracle database, this page will display as in Figure 2-7.

Figure 2-7. Output from a PL/SQL Server Page

I'm rather fond of PL/SQL Server Pages as a good way to put together data-driven web sites fairly quickly.

For more information about PL/SQL Server Pages, see Learning Oracle PL/SQL by the authors of the book you're reading now.

2.5 And What Else?

We've seen how to use PL/SQL in SQL*Plus and in a number of other common environments and programming languages. There are still more places you can use PL/SQL:

· Embedded in COBOL or FORTRAN and processed with Oracle's precompiler

· Called from Visual Basic, using some flavor of ODBC

· Called from the Ada programming language, via a technology called SQL*Module

· Executed automatically, as triggers on events in the Oracle database such as table updates

· Scheduled to execute on a recurring basis inside the Oracle database, via the DBMS_JOB built-in package

We'll take a look at some of these approaches in upcoming chapters.

Chapter 3. Language Fundamentals

Every language—whether human or computer—has a syntax, a vocabulary, and a character set. In order to communicate within that language, you have to learn the rules that govern its usage. Many of us are wary of learning a new computer language. Change is often scary, but in general, programming languages are very simple tongues, and PL/SQL is a relatively simple programming language. The difficulty of conversing in languages based on bytes is not with the language itself, but with the compiler or computer with which we are having the discussion. Compilers are, for the most part, rather dull-witted. They are not creative, sentient beings. They are not capable of original thought. Their vocabulary is severely limited. Compilers just happen to think their dull thoughts very, very rapidly—and very inflexibly.

If I hear someone ask "gottabuck?", I can readily interpret that sentence and decide how to respond. On the other hand, if I instruct PL/SQL to "gimme the next half-dozen records," I will not get very far in my application. To use the PL/SQL language, you must dot your i's and cross your t's—syntactically speaking. So, this chapter covers the fundamental language rules that will help you converse with the PL/SQL compiler—the PL/SQL block structure, character set, lexical units, and PRAGMA keyword.

3.1 PL/SQL Block Structure

Virtually all programming languages give you a way to organize logically related elements into programming units. In PL/SQL, the fundamental unit of organization is the block, which is at the core of two key language concepts.

 

Modularization

The PL/SQL block is the basic unit of code from which you will build modules, such as procedures and functions, which in turn comprise applications. As the lowest organizational unit, well-designed blocks are fundamental to achieving code that other programmers can easily use and maintain.

 

Scope

The block provides a scope or context for logically related objects. In the block, you group together declarations and executable statements that belong together.

You can create anonymous blocks (blocks of code that have no name) and named blocks, which are procedures and functions. Furthermore, you can build packages in PL/SQL that group together multiple procedures and functions.

3.1.1 Sections of the PL/SQL Block

Each PL/SQL block has up to four different sections, only one of which is mandatory:

 

Header

Used only for named blocks. The header determines the way the named block or program must be called. Optional.

 

Declaration section

Identifies variables, cursors, and sub-blocks that are referenced in the execution and exception sections. Optional.

 

Execution section

Statements the PL/SQL runtime engine will execute at runtime. Mandatory.

 

Exception section

Handles exceptions to normal processing (warnings and error conditions). Optional.

Figure 3-1 shows the structure of the PL/SQL block for a procedure.

Figure 3-1. The PL/SQL block structure

The ordering of the sections in a block generally corresponds to the way you would write your programs and the way they are executed:

1. Define the type of block (procedure, function, anonymous) and the way it is called (header).

2. Declare any variables used in that block (declaration section).

3. Use those local variables and other PL/SQL objects to perform the required actions (execution section).

4. Handle any problems that arise during the execution of the block (exception section).

Figure 3-2 shows a procedure containing all four sections. In practice, there is often quite a bit of iteration among these steps when creating your own blocks; don't expect perfection on your first pass!