cx_Oracle callfunc()

I like wrapping database queries in stored procedures: it keeps the SQL in the database, where Oracle can track its dependencies and flag if some change makes it invalid.  Here’s an example using Oracle’s demo schema SCOTT:

view plaincopy to clipboardprint?

1. CREATE OR REPLACE PACKAGE emp_extract_k  
2. AS  
3.    TYPE emp_t  
4.    IS  
5.       RECORD( ename  varchar2( 10 ), hiredate date, sal     number( 7, 2 ) );  
6.    TYPE emp_csr_t IS REF CURSOR  
7.       RETURN emp_t;  
8.    FUNCTION get_emps  
9.       RETURN emp_csr_t;  
10. END;  
11. /  
12. CREATE OR REPLACE PACKAGE BODY emp_extract_k  
13. AS  
14.    FUNCTION get_emps  
15.       RETURN emp_csr_t  
16.    IS  
17.       c   emp_csr_t;  
18.    BEGIN  
19.       OPEN c FOR SELECT   ename, hiredate, sal FROM scott.emp;  
20.       RETURN c;  
21.    END;  
22. END;/  

CREATE OR REPLACE PACKAGE emp_extract_k
AS
   TYPE emp_t
   IS
      RECORD( ename  varchar2( 10 ), hiredate date, sal     number( 7, 2 ) );
   TYPE emp_csr_t IS REF CURSOR
      RETURN emp_t;
   FUNCTION get_emps
      RETURN emp_csr_t;
END;
/
CREATE OR REPLACE PACKAGE BODY emp_extract_k
AS
   FUNCTION get_emps
      RETURN emp_csr_t
   IS
      c   emp_csr_t;
   BEGIN
      OPEN c FOR SELECT   ename, hiredate, sal FROM scott.emp;
      RETURN c;
   END;
END;/

Now suppose I want to take the output from a stored procedure like the one above and write it to a CSV file.  In Python this is trivially easy:

view plaincopy to clipboardprint?

1. import cx_Oracle  
2. import csv  
3. reportName = 'emp_extract_k.get_emps'  
4. connection = cx_Oracle.connect(userid)  
5. cursor = connection.cursor()  
6. rs = cursor.callfunc(reportName, cx_Oracle.CURSOR)  
7. fd = open(reportName+'.csv', 'w')  
8. csvFile = csv.writer(fd)  
9. header = []  
10. [header.append(col_defn[0]) for col_defn in rs.description]  
11. csvFile.writerow(header)  
12. for r in rs:  
13.  csvFile.writerow(r)  
14. fd.close()  

 import cx_Oracle
 import csv
 reportName = 'emp_extract_k.get_emps'
 connection = cx_Oracle.connect(userid)
 cursor = connection.cursor()
 rs = cursor.callfunc(reportName, cx_Oracle.CURSOR)
 fd = open(reportName+'.csv', 'w')
 csvFile = csv.writer(fd)
 header = []
 [header.append(col_defn[0]) for col_defn in rs.description]
 csvFile.writerow(header)
 for r in rs:
  csvFile.writerow(r)
 fd.close()