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.
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.
Hot Article
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)
Hot Topics
How to Use Python to Find the Zipf Distribution of a Text File
Mar 05, 2025 am 09:58 AM
This tutorial demonstrates how to use Python to process the statistical concept of Zipf's law and demonstrates the efficiency of Python's reading and sorting large text files when processing the law. You may be wondering what the term Zipf distribution means. To understand this term, we first need to define Zipf's law. Don't worry, I'll try to simplify the instructions. Zipf's Law Zipf's law simply means: in a large natural language corpus, the most frequently occurring words appear about twice as frequently as the second frequent words, three times as the third frequent words, four times as the fourth frequent words, and so on. Let's look at an example. If you look at the Brown corpus in American English, you will notice that the most frequent word is "th
How Do I Use Beautiful Soup to Parse HTML?
Mar 10, 2025 pm 06:54 PM
This article explains how to use Beautiful Soup, a Python library, to parse HTML. It details common methods like find(), find_all(), select(), and get_text() for data extraction, handling of diverse HTML structures and errors, and alternatives (Sel
Image Filtering in Python
Mar 03, 2025 am 09:44 AM
Dealing with noisy images is a common problem, especially with mobile phone or low-resolution camera photos. This tutorial explores image filtering techniques in Python using OpenCV to tackle this issue. Image Filtering: A Powerful Tool Image filter
How to Work With PDF Documents Using Python
Mar 02, 2025 am 09:54 AM
PDF files are popular for their cross-platform compatibility, with content and layout consistent across operating systems, reading devices and software. However, unlike Python processing plain text files, PDF files are binary files with more complex structures and contain elements such as fonts, colors, and images. Fortunately, it is not difficult to process PDF files with Python's external modules. This article will use the PyPDF2 module to demonstrate how to open a PDF file, print a page, and extract text. For the creation and editing of PDF files, please refer to another tutorial from me. Preparation The core lies in using external module PyPDF2. First, install it using pip: pip is P
How to Cache Using Redis in Django Applications
Mar 02, 2025 am 10:10 AM
This tutorial demonstrates how to leverage Redis caching to boost the performance of Python applications, specifically within a Django framework. We'll cover Redis installation, Django configuration, and performance comparisons to highlight the bene
How to Perform Deep Learning with TensorFlow or PyTorch?
Mar 10, 2025 pm 06:52 PM
This article compares TensorFlow and PyTorch for deep learning. It details the steps involved: data preparation, model building, training, evaluation, and deployment. Key differences between the frameworks, particularly regarding computational grap
How to Implement Your Own Data Structure in Python
Mar 03, 2025 am 09:28 AM
This tutorial demonstrates creating a custom pipeline data structure in Python 3, leveraging classes and operator overloading for enhanced functionality. The pipeline's flexibility lies in its ability to apply a series of functions to a data set, ge
Introduction to Parallel and Concurrent Programming in Python
Mar 03, 2025 am 10:32 AM
Python, a favorite for data science and processing, offers a rich ecosystem for high-performance computing. However, parallel programming in Python presents unique challenges. This tutorial explores these challenges, focusing on the Global Interprete
