Oracle subprograms and Python Programming

 Oracle code:

CREATE OR REPLACE PACKAGE pkg_hr_sample AS

  PROCEDURE add_department(p_department_id   OUT NUMBER,

                           p_department_name IN VARCHAR2,

                           p_manager_id      IN NUMBER,

                           p_location_id     IN NUMBER);

  FUNCTION get_employee_count(p_department_id IN NUMBER) RETURN NUMBER;

  PROCEDURE find_employees(p_query   IN VARCHAR2 DEFAULT NULL,

                           p_date    IN DATE DEFAULT NULL,

                           p_results OUT SYS_REFCURSOR);

END pkg_hr_sample;

/

CREATE OR REPLACE PACKAGE BODY pkg_hr_sample AS

  PROCEDURE add_department(p_department_id   OUT NUMBER,

                           p_department_name IN VARCHAR2,

                           p_manager_id      IN NUMBER,

                           p_location_id     IN NUMBER) AS

  BEGIN

    INSERT INTO departments

      (department_id, department_name, manager_id, location_id)

    VALUES

      (departments_seq.nextval,

       p_department_name,

       p_manager_id,

       p_location_id)

    RETURNING department_id INTO p_department_id;

 

    COMMIT;

  END add_department;

  FUNCTION get_employee_count(p_department_id IN NUMBER) RETURN NUMBER AS

    l_count NUMBER;

  BEGIN

    SELECT COUNT(*)

      INTO l_count

      FROM employees

     WHERE department_id = p_department_id;

 

    RETURN l_count;

  END get_employee_count;

  PROCEDURE find_employees(p_query   IN VARCHAR2 DEFAULT NULL,

                           p_date    IN DATE DEFAULT NULL,

                           p_results OUT SYS_REFCURSOR) AS

  BEGIN

    IF p_query IS NOT NULL THEN

      OPEN p_results FOR

        SELECT *

          FROM employees e

         WHERE (upper(first_name || ' ' || last_name || ' ' || email) LIKE

               '%' || upper(p_query) || '%');

    ELSE

      OPEN p_results FOR

        SELECT * FROM employees e WHERE e.hire_date = p_date;

    END IF;

  END find_employees;

END pkg_hr_sample;

/

Three different access methods are introduced in the above example: a procedure with IN and OUT parameters, a function returning a number and a procedure with an OUT REF CURSOR parameter. Each one requires a different way of calling as shown below.

Python code:

import cx_Oracle

 

class HR:

  def __enter__(self):

    self.__db = cx_Oracle.Connection('hr/hr@pdborcl')

    self.__cursor = self.__db.cursor()

    return self

 

  def __exit__(self):

    self.__cursor.close()

    self.__db.close()

   

  '''

  def __exit__(self, type, value, traceback):

    self.__cursor.close()

    self.__db.close()

  '''

  def add_department(self, p_department_name, p_manager_id, p_location_id):

    l_department_id = self.__cursor.var(cx_Oracle.NUMBER)

    self.__cursor.callproc("PKG_HR_SAMPLE.ADD_DEPARTMENT",

      [l_department_id, p_department_name, p_manager_id, p_location_id])

 

    # there are no OUT parameters in Python, regular return here

    return l_department_id   

 

  def get_employee_count(self, p_department_id):

    l_count = self.__cursor.callfunc("PKG_HR_SAMPLE.GET_EMPLOYEE_COUNT",

      cx_Oracle.NUMBER, [p_department_id])

    return l_count

   

 

  def find_employees(self, p_query, p_date):

    # as it comes to all complex types we need to tell Oracle Client

    # what type to expect from an OUT parameter

    l_cur = self.__cursor.var(cx_Oracle.CURSOR)

    l_query, l_date, l_emp = self.__cursor.callproc("PKG_HR_SAMPLE.FIND_EMPLOYEES", [p_query, p_date, l_cur])

    return list(l_emp)

  def __init__(self):

    HR.__enter__(self) 

    o_count=HR.get_employee_count(self, p_department_id=90)

    print('No. of Employees: ',o_count)

    print('')

    o_cur= HR.find_employees(self,None,"28-Sep-05")

    print('Start: Date output')

    for result in o_cur:

        #print(result)

        print(result[0],",",result[1]," ",result[2])

    print('End: Date output')

    print('')

    print('Start: Name output')

    o_cur2= HR.find_employees(self,"Steven",None)

    for result2 in o_cur2:

        #print(result)

        print(result2[0],",",result2[1]," ",result2[2])

    print('End: Name output')

    HR.__exit__(self)

       

HR()

Output:

             

No. of Employees:  3.0

Start: Date output

110 , Fname1   Lname1

End: Date output

Start: Name output

100 , Fname1   Lname1

128 , Fname2   Lname2

End: Name output

Based upon the above example, calling stored procedures from Python is regulated through some basic rules:

          ·       Procedures are called with cx_Oracle.Cursor.callproc(proc, [params]) whereas functions with              cx_Oracle.Cursor.callfunc(proc, returnType, [params]). Functions require their return type to              be  defined in advance - the get_employee_count() method declares the return type from                           

                  PKG_HR_SAMPLE.GET_EMPLOYEE_COUNT to be a cx_Oracle.NUMBER

         ·       Complex types such as REF CURSORs can be returned using cx_Oracle Variable objects as               parameters in callproc/callfunc calls.

I hope you all have enjoyed reading this article. Comments are welcome....


Related Posts:
- Setting up Oracle, Python and Eclipse
- Connect Python 3.6 with Oracle 12c on Windows 7 x64