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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access