Read Oracle function return value in Python

A function was created in Oracle that originally wanted to return an index table without success. Think of text can also transfer information, suddenly came to the inspiration, the return value to set the text format.

Given that the amount of return data can be large and the varchar2 type length is tight, the return value type is set to CLOB.

I'm using the Scott User's test sheet, which is the function definition:

Create or replace function test_query_func (DEPT&NBSP;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;    &NBSP;&NBSP;&NBSP;&NBSP;I&NBSP;INTEGER&NBSP;:=&NBSP;0;&NBSP;SS&NBSP;&NBSP;VARCHAR2 ($)  :=  ';  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 (')); end;

Output Result:

[(' 7499 ', ' ALLEN ', ' salesman ', ' a ') ', (' 7521 ', ' WARD ', ' salesman ', ' 1250 '), (' 7654 ', ' MARTIN ', ' salesman ', ' 1250 '), (' 7698 ', ' BLAKE ', ' MANAGER ', ' 2850 '), (' 7844 ', ' TURNER ', ' salesman ', ' the ') ', (' 7900 ', ' JAMES ', ' clerk ', ' 950 ')]

has actually been defined as a style in a Python list that contains the tuple child elements.

Here's the code in Python:

Import cx_oracle as Ora;con = Ora.connect (' scott/[email protected] '); cur = con.cursor (); Cur.execute (' Select Test_query_ Func (() from dual '), res = Cur.fetchall () [0][0].read (); Cur.close (); Con.close ();d ata = eval (res); import Pandas as Pd;df = Pd. DataFrame (data, columns = [' empno ', ' ename ', ' job ', ' Sal ']);p rint (DF)

In this way, the long string value returned by the function in Oracle is converted to the Dataframe object:

	empno	ename	job	Sal
0	7499	ALLEN	Salesman	1600
1	7521	WARD	Salesman	1250
2	7654	MARTIN	Salesman	1250
3	7698	BLAKE	MANAGER	2850
4	7844	TURNER	Salesman	1500
5	7900	JAMES	Clerk	950

This article is from the "Kevan Technical Codex" blog, please be sure to keep this source http://kevan.blog.51cto.com/7204808/1827178

Read Oracle function return value in Python