python reads oracle function return value

Creating a function in Oracle originally wanted to return an index table, but it failed. Thinking that text can also transmit information, I suddenly had the inspiration to set the return value in text format.
Considering that the amount of returned data may be large and the length of the varchar2 type is tight, the return value type is set to clob.
I am using scott user’s test table emp. This is the function definition:

create or replace function test_query_func(dept varchar2)
return clob
is
 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:
[('7499', 'ALLEN', 'SALESMAN', '1600'),('7521', 'WARD', 'SALESMAN', '1250'),('7654', 'MARTIN', 'SALESMAN' , '1250'),('7698', 'BLAKE', 'MANAGER', '2850'),('7844', 'TURNER', 'SALESMAN', '1500'),('7900', 'JAMES' , 'CLERK', '950')]
In fact, it has been defined as a style that contains tuple sub-elements in a list in python.
The following is the code in python. Using python to connect to oracle requires the cx_Oracle library:

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 function in Oracle is converted into a DataFrame object:

The above is the entire content of this article. I hope it will be helpful to everyone’s study. I also hope that everyone will support Script Home.

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Show More

Hot Article

Assassin's Creed Shadows: Seashell Riddle Solution

3 weeks ago By DDD

What's New in Windows 11 KB5054979 & How to Fix Update Issues

2 weeks ago By DDD

Where to find the Crane Control Keycard in Atomfall

3 weeks ago By DDD

Saving in R.E.P.O. Explained (And Save Files)

1 months ago By 尊渡假赌尊渡假赌尊渡假赌

Assassin's Creed Shadows - How To Find The Blacksmith And Unlock Weapon And Armour Customisation

4 weeks ago By DDD

Show More

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Show More

Hot Topics

Where is the login entrance for gmail email?

7564

15

CakePHP Tutorial

1386

52

What is the format of the account name of steam

87

11

win11 activation key permanent

61

19

nyt connections hints and answers

28

100

Show More