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