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

Oracle : Create File using PLSQL (UTL_FILE)

Definition:

In Oracle PL/SQL, UTL_FILE is an Oracle supplied package which is used for file operations (read and write) in conjunction with the underlying operating system. UTL_FILE works for both server and client machine systems. A directory has to be created on the server, which points to the target file. For the files located on the server machine, the actual path can be given while creating the directory. For the files which are located on the client machines, however, the relative path is required along with the client machine name. In addition, the relative file path must be in shared mode with read and write access for the required users. A DBA must create the directory and then grant Read/Write access to the required users.

In earlier versions of Oracle, the parameter UTL_FILE_DIR was used to specify the file location path. This parameter is now deprecated and directory creation method is recommended. 

UTL_FILE package installation:

SYSDBA Role: Run below scripts to create UTL_FILE package using SYSDBA privilege

$ORACLE_HOME\RDBMS\ADMIN\utlfile.sql

$ORACLE_HOME\RDBMS\ADMIN\prvtfile.plb

ORA-29280: invalid directory path:

This indicates that the Oracle engine is unable to find the file location specified in utl_file.fopen.

Diagnosis and fix:

     1.     UTL_FILE_DIR : Set the utl_file_dir initialization parameter and check the value

SQL> SELECT value

FROM V$PARAMETER

WHERE NAME = 'utl_file_dir';

VALUE

--------------------------------------------------------------------------------

Or

show parameter utl_file_dir

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                         string

If value is null than fix that data

SQL> alter system set utl_file_dir='C:\TEMP' scope = spfile;

As it is static parameter we must bounce the database:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup open

ORACLE instance started.

     2.    DIRECTORY: In the below example, SYSDBA creates a directory MYDIR and grants R/W access to the user HR. The user then creates a text file in the directory and writes a text into it.

  

SQL> CREATE DIRECTORY MYDIR AS 'C:\TEMP';

Grant read and write privilege to the application user (or PUBLIC) on the new directory.

SQL> GRANT READ, WRITE ON DIRECTORY MYDIR TO HR;

SQL> GRANT READ, WRITE ON DIRECTORY MYDIR TO public;

Note: The directory path can be case-sensitive on some operating systems like UNIX.

A typical mistake is to specify a *local* machine folder as the UTL_FILE directory when the Oracle database server is on another machine – this will not work. The UTL_FILE directory must exist on the *server* machine.

Identify the file location:

SQL> select directory_name, directory_path from all_directories;

Check if ‘file location’ in the script has write permissions for the logged in user

SQL> select grantee,privilege from all_tab_privs where table_name = 'MYDIR';

If you do not see WRITE permission granted to PUBLIC or to the logged in user, login as SYS and grant permissions.

SQL> select grantee from all_tab_privs where table_name = 'UTL_FILE';

A directory object is a database object, and database object names are in UPPERCASE by default. Even if the ‘create directory’ command issued had the directory name in lowercase, unless we put it within quotes, the directory name will be stored in UPPERCASE.

             

Notes

Before Oracle 9i, init.ora parameter called utl_file_dir was used in place of Oracle directories to specify the UTL_FILE file location.

utl_file_dir has been deprecated 9i onwards. Oracle recommends using Oracle directories which are more secure, easier to handle and do not require a database restart when created/modified. 

HR User:

declare

    csv_fh  utl_file.file_type;

begin

    csv_fh := utl_file.fopen('MYDIR', 'data_export.csv', 'W');

    for r in   (  select e.first_name,d.department_name

                  from   hr.EMPLOYEES e

                  join hr.DEPARTMENTS d on ( e.department_id=d.department_id )

                ) loop

        utl_file.put_line(csv_fh, r.first_name||'|'||r.department_name);

    end loop;

    utl_file.fclose(csv_fh);

end;

/

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