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 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

• 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.