Section 2.3.  Calling PL/SQL from Other Languages

2.3. Calling PL/SQL from Other Languages Sooner or later, you will probably want to call PL/SQL from C, Java, Perl, PHP, 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 datatypesespecially composite datatypes like arrays, records, and objectsnot to mention differing parameter semantics or vendor extensions to "standard" application programming interfaces (APIs) like Open DataBase Connectivity (ODBC). I will show a few very brief examples of calling PL/SQL from the outside world. 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 VARCHAR2 IS 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 PHP PL/SQL Server Pages These examples are very contrivedfor 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.3.1. C: Using Oracle's Precompiler (Pro*C) Oracle supplies at least two different C-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 the following through Oracle's proc program, and out will come 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.3.2. Java: Using JDBC As with C, Oracle provides a number of different 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 Oracle in a Nutshell by Rick Greenwald and David C. Kreines, both from O'Reilly. 2.3.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 die my $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 handle eval { 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, not to mention http://www.perl.com (an O'Reilly site), http://www.perl.org, and http://www.cpan.org. 2.3.4. PHP: Using Oracle Extensions If you are the kind of person who (like me) might use the free and wildly popular web server known as Apache, you might also enjoy using the free and wildly popular programming language known as PHP. Commonly employed to build dynamic web pages, PHP can also be used to build GUI applications or to run command-line programs. As you might expect, Oracle is one of many database environments that work with PHP; somewhat surprisingly, Oracle Corporation is itself distributing a version of PHP with Oracle Application Server 10g Release 2. This example uses the family of PHP functions known as OCI8. Don't let the "8" in the name fool you; it should work with everything from Oracle7 to Oracle Database 10g. /* File on web: callbooktitle.php */ <?PHP // Initiate the connection to the o92 database $conn = OCILogon ("scott", "tiger", "o92"); // Make parse call to Oracle, get statement identity $stmt = OCIParse($conn, "begin :res := booktitle('0-596-00180-0'); end;"); // Show any errors if (!$stmt) { $err = OCIError( ); echo "Oops, you broke it: ".$err["message"]; exit; } // Bind 200 characters of the variable $result to placeholder :res OCIBindByName($stmt, "res", &$result, 200); // Execute OCIExecute($stmt); // Stuff the value into the variable OCIResult($stmt,$result); // Display on stdout echo "$result\n"; // Relax OCILogoff($conn); ?> When executed at the command line, it looks something like this: $ php callbooktitle.php Learning Oracle PL/SQL By the way, these Oracle OCI functions are not available in PHP by default, but it shouldn't be too difficult for your system administrator to rebuild PHP with the Oracle extensions. You can find more information about PHP at http://www.php.net or in one of O'Reilly's many books on the subject. For PHP tips specific to Oracle, visit the Oracle Technology Network web site: http://otn.oracle.com. 2.3.5. 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 lets you 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 <%= %> construct 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 displays as in Figure 2-5. Figure 2-5. 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 (O'Reilly) by the authors of the book you're reading now. 2.3.6. And Where 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 and ways that 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 are not able, (un)fortunately, to address all these topics in this book.