Oracle PL/SQL Programming, Third Edition

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