Oracle PL/SQL Programming, 5th Edition

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 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 Microsoft’s 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 */
FUNCTION booktitle (isbn_in IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_title books.title%TYPE;
   CURSOR icur IS SELECT title FROM books WHERE isbn = isbn_in;
BEGIN
   OPEN icur;
   FETCH icur INTO l_title;
   CLOSE icur;
   RETURN l_title;
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 contrived—for example, the username and password ...