Python reads Oracle function return value _python

The

Creates a function in Oracle that was intended to return a index table without success. Think of the text can also transfer information, suddenly came to the inspiration, the return value set text format.
The return value type is set to CLOB, considering that the amount of data returned can be very large and the varchar2 type length is tight. &NBSP
I was using the Scott User's Test table EMP, which is the function definition:

Create or Replace function Test_query_func (dept varchar2) return
clob
are
 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: = ';
 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 results:
[(' 7499 ', ' ALLEN ', ' salesman ', ' 1600 '), (' 7521 ', ' WARD ', ' salesman ', ' 1250 '), (' 7654 ', ' MARTIN ', ' salesman ', ' 1250 '), (' 7698 ', ' BLAKE ', ' MANAGER ', ' 2850 '), (' 7844 ', ' TURNER ', ' salesman ', ' 1500 '), (' 7900 ', ' JAMES ', ' clerk ', ' 950 ')]
has actually been defined as a style that contains tuple child elements in a python list.
Here's the code in Python, which requires cx_oracle libraries to connect to Oracle with Python:

Import cx_oracle as Ora;
con = ora.connect (' scott/scott@oradb ');
cur = con.cursor ();
Cur.execute (' Select Test_query_func 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)

The long string value returned by the function in Oracle is then converted to the Dataframe object:

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.