Python reads the return value of the oracle function, pythonoracle

Python reads the return value of the oracle function, pythonoracle

Create a function in oracle to return an index table. When we thought that the text could also transmit information, we suddenly came to the inspiration and set the return value to the text format.
Considering that the returned data volume may be large and the varchar2 type is too long, set the returned data type to clob.
I use the test table emp of scott. This is the function definition:

create or replace function test_query_func(dept varchar2)return clobis type test_record is record (rec_empno emp.empno%type, rec_ename emp.ename%type, rec_job  emp.job%type, rec_sal  emp.sal%type); type test_query_arr is table of test_record index by binary_integer; cursor cur is select empno, ename, job, sal from emp where deptno = dept; test_query test_query_arr; i integer := 0; ss varchar2(200) := ''; res clob := '[';begin for c in cur loop  i := i + 1;  test_query(i) := c; end loop; for q in 1..test_query.count loop  ss := '(''' || test_query(q).rec_empno || ''', ''' || test_query(q).rec_ename || ''', ''' || test_query(q).rec_job || ''', ''' || test_query(q).rec_sal || ''')'; if q < test_query.count then ss := ss || ','; end if; res := res || ss; end loop; res := res || ']'; return res;end;

You can test the return value of this function in pl/SQL developer:

 begin dbms_output.put_line(test_query_func('30')); end; 

Output result:
[('20140901', 'allen', 'salesman', '20160301'), ('20160301', 'ward ', 'salesman', '20160301 '), ('20140901', 'martin ', 'salesman', '20160301'), ('20160301', 'bucket', 'manager', '20160301 '), ('123456', 'turner ', 'salesman', '123456'), ('1234568', 'James', 'cler', '1234568')]
In fact, it has been defined as a style that contains sub-elements in the list of python.
The following is the code in python. To connect to oracle using python, The cx_Oracle database is required:

import cx_Oracle as ora;con = ora.connect('scott/scott@oradb');cur = con.cursor();cur.execute('select test_query_func(30) from dual');res = cur.fetchall()[0][0].read();cur.close();con.close();data = eval(res);import pandas as pd;df = pd.DataFrame(data, columns = ['empno', 'ename', 'job', 'sal']);print(df)

In this way, the long string value returned by the oracle function is converted into a DataFrame object:

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.