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 emp, which is the function definition:
Create or Replace function Test_query_func (dept varchar2) return Clobis type Test_record is record (Rec_empno Emp.empno%typ E, 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 a 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:
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, which connects Oracle with Python requires cx_oracle libraries:
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 ();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:
The above is the whole content of this article, I hope that everyone's learning has helped, but also hope that we support the script home.