O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required