Mastering Oracle+Python, Part 5: Stored Procedures, Programming Python

Date Added: Jan 2010
Format: HTML

This paper introduces methods for calling stored PL/SQL procedures and functions inside the Oracle Database from Python using the cx_Oracle module as well as some aspects of programming that are either unachievable or very complex to code in PL/SQL. It also deals with Oracle Berkeley DB, which comes built-in into Python out-of-the-box. Oracle procedures and functions are database objects that combine SQL capabilities with programming language functionality. Arguments of procedures are of three types i.e. IN, OUT, IN OUT. An illustration is also given showing the interaction between Python and Oracle procedure. This paper also brings out certain basic rules which regulate calling stored procedures from Python. PL/SQL is a powerful language and combined with the possibilities of Oracle Database can dramatically reduce development efforts. Python is a great choice for short development time and fast results In Python, data serialization and de-serialization is handled by the pickle module and its C counterpart cPickle. Oracle Berkeley DB is a transactional key-value storage solution with fine-grained locking, high availability, and replication. It fits in all those problems where extreme efficiency is required and the overhead of a full size relational database is too high. Overall, this paper covers several core areas of the Oracle-Python bridge including PL/SQL stored procedure calls and handling PL/SQL function results.