【博客文章2024】Oracle Database 23ai：AI Vector Search笔记与实验2---Ollama和Oracle Apex联合执行AI问答（无RAG）
Author: Bo Tang
Oracle Database 23.5还不能直接支持使用UTL_TO_EMBEDDING去调用Ollama，需要将版本升级到Oracle Database 23.6才支持。为了在 实验1搭建好的环境中使用PL/SQL去调用Ollama来执行AI 问答，得寻找别的方法。这种办法就是：安装Oracle Apex，然后让Oracle Apex提交问题给Ollama，再由Apex接收Ollama返回的数据，最后通过dbms_output.put_line来展示。为了尽快体验这样做能否实现Ollama和Apex的联动，在这个过程中本博客暂时不去添加提示工程。如果上述设想能够实现，将在本系列的下一个博客中添加库内提示工程实现RAG，获得更加准确的AI输出。 1.Oracle Cloud and Engineered Systems Version 23.5.0.24.07的实验环境中的UTL_TO_EMBEDDING报错：
[oracle@station1 ~]$ sqlplus /nolog
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Thu Dec 19 18:44:41 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
SQL> conn hr/cloud_4U@pdb1_1
Connected.
SQL> var embed_ollama_params clob;
SQL> exec :embed_ollama_params := '{"provider": "ollama","host":"local","url":"http://localhost:11434/api/embeddings","model": "all-minilm"}';
PL/SQL procedure successfully completed.
SQL> select dbms_vector.utl_to_embedding('hello', json(:embed_ollama_params))
2 ollama_output from dual;
ERROR:
ORA-20003: invalid value for PROVIDER parameter
ORA-06512: at "SYS.DBMS_VECTOR", line 2150
ORA-06512: at "SYS.DBMS_VECTOR", line 2142
ORA-06512: at line 1
no rows selected
|
2. 安装Oracle Apex 24.1：
1）下载安装包：
[oracle@station1 ~]$ wget https://download.oracle.com/otn_software/apex/apex_24.1.zip
--2024-12-19 22:10:16-- https://download.oracle.com/otn_software/apex/apex_24.1.zip
Resolving download.oracle.com (download.oracle.com)... 23.48.228.98
Connecting to download.oracle.com (download.oracle.com)|23.48.228.98|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 290320880 (277M) [application/zip]
Saving to: ‘apex_24.1.zip’
apex_24.1.zip 100%[===================================================================================================================>] 276.87M 2.21MB/s in 2m 5s
2024-12-19 22:12:22 (2.21 MB/s) - ‘apex_24.1.zip’ saved [290320880/290320880]
|
2）解压缩安装包：
[oracle@station1 ~]$ unzip apex_24.1.zip
......
extracting: apex/images/wwv_right_arrow.gif
inflating: apex/images/wwv_small_folder.gif
inflating: apex/images/wwv_small_help.gif
inflating: apex/images/wwv_small_home.gif
inflating: apex/images/wwv_small_person.gif
inflating: apex/images/wwv_smprt.gif
inflating: apex/images/wwv_stats.gif
inflating: apex/images/wwv_todo.gif
inflating: apex/images/wwv_user_list.gif
inflating: apex/images/wwv_user_quick.gif
inflating: apex/images/yellow.gif
extracting: apex/images/yellow_flag.gif
extracting: apex/images/yes.gif
extracting: apex/images/apex_version.txt
extracting: apex/images/apex_version.js
[oracle@station1 ~]$
|
3）在实验1的Oracle Cloud and Engineered Systems Version 23.5.0.24.07的插件数据库pdb1_1中以sys用户安装Apex：
[oracle@station1 ~]$ cd apex
[oracle@station1 apex]$ sqlplus /nolog
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Thu Dec 19 22:22:55 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
SQL> conn sys/cloud_4U@pdb1_1 as sysdba
Connected.
SQL> @apexins SYSAUX SYSAUX TEMP /i/
...set_appun.sql
PL/SQL procedure successfully completed.
...set_ufrom_and_upgrade.sql
PL/SQL procedure successfully completed.
Session altered.
FOO3
------------------------------
install2024-12-19_22-23-23.log
. ORACLE
.
. Oracle APEX Installation.
..........................................
.
...set_appun.sql
... Checking prerequisites (MANUAL)
.
. SYSDBA Privilege
. pass - Connection with SYSDBA privilege.
. Database rolling upgrade
. pass - No rolling upgrade.
. DB components
. pass - CATPROC: version=23.0.0.0.0
. pass - XDB: version=23.0.0.0.0
. XDB
. pass - is valid
. DB parameters
. pass - workarea_size_policy is AUTO
. PL/SQL Web Toolkit
. pass - version 20.0.0.0.1
. Tablespaces
. pass - found SYSAUX
. pass - found SYSAUX
. pass - found TEMP
. PHASES (1,2,3)...
PL/SQL procedure successfully completed.
.
... Prerequisite checks passed.
......
......
timing for: Validating Installation
Elapsed: 0.03
#
# Actions in Phase 3:
#
ok 1 - BEGIN | 0.00
ok 2 - Updating DBA_REGISTRY | 0.00
ok 3 - Computing Pub Syn Dependents | 0.00
ok 4 - Upgrade Hot Metadata and Switch Schemas | 0.00
ok 5 - Removing Jobs | 0.00
ok 6 - Creating Public Synonyms | 0.03
ok 7 - Granting Public Synonyms | 0.10
ok 8 - Granting to FLOWS_FILES | 0.00
ok 9 - Creating FLOWS_FILES grants and synonyms | 0.00
ok 10 - Syncing ORDS Gateway Allow List | 0.02
ok 11 - Creating Jobs | 0.00
ok 12 - Creating Dev Jobs | 0.00
ok 13 - Installing FLOWS_FILES Objects | 0.00
ok 14 - Installing APEX$SESSION Context | 0.00
ok 15 - Recompiling APEX_240100 | 0.03
ok 16 - Installing APEX REST Config | 0.00
ok 17 - Set Loaded/Upgraded in Registry | 0.00
ok 18 - Setting Patch Status: APPLIED | 0.02
ok 19 - Removing Unused SYS Objects and Public Privs | 0.00
ok 20 - Validating Installation | 0.03
ok 3 - 20 actions passed, 0 actions failed | 0.23
Thank you for installing Oracle APEX 24.1.0
Oracle APEX is installed in the APEX_240100 schema.
The structure of the link to the Oracle APEX administration services is as follows:
http://host:port/ords/apex_admin
The structure of the link to the Oracle APEX development interface is as follows:
http://host:port/ords
timing for: Phase 3 (Switch)
Elapsed: 0.23
timing for: Complete Installation
Elapsed: 5.73
SYS>
|
4）在实验1的Oracle Cloud and Engineered Systems Version 23.5.0.24.07的插件数据库pdb1_1中以sys用户运行utlrp.sql完成Apex的安装：
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2024-12-19 22:47:40
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2024-12-19 22:47:46
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
|
3. Ollama本地运行Qwen2:7b-instruct生成式问答模型：
实验1中已经使用ollama pull all-minilm测试Ollama能够运行，但是为了执行更加有用的问答，让我们运行qwen2:7b-instruct模型。拉取过程中需要Internet连接：
[root@station1 ~]# ollama run qwen2:7b-instruct
pulling manifest
pulling 43f7a214e532... 100% ▕██████████████████████████████████████████████████████████████████████████████
4.4 GB/4.4 GB 2.4 MB/s 0s
pulling 77c91b422cc9... 100% ▕██████████████████████████████████████████████████████████████████████████████▏
1.4 KB
pulling c156170b718e... 100% ▕██████████████████████████████████████████████████████████████████████████████
11 KB
pulling f02dd72bb242... 100% ▕██████████████████████████████████████████████████████████████████████████████▏
59 B
pulling 75357d685f23... 100% ▕██████████████████████████████████████████████████████████████████████████████
28 B
pulling 648f809ced2b... 100% ▕███████████████████████████████████████████████████████████████████████████████████████████████ 485 B
verifying sha256 digest
writing manifest
success
>>> Send a message (/? for help)
|
拉取完毕后，完全可以断掉Internet连接（使用route del -net 0.0.0.0断网），进行本地运行：
[root@station1 ~]# netstat -nr
Kernel IP routing table
Destination Gateway Genmask Flags MSS Window irtt Iface
0.0.0.0 172.25.250.93 0.0.0.0 UG 0 0 0 ens160
172.25.250.0 0.0.0.0 255.255.255.0 U 0 0 0 ens160
192.168.122.0 0.0.0.0 255.255.255.0 U 0 0 0 virbr0
[root@station1 ~]# route del -net 0.0.0.0
[root@station1 ~]# netstat -nr
Kernel IP routing table
Destination Gateway Genmask Flags MSS Window irtt Iface
172.25.250.0 0.0.0.0 255.255.255.0 U 0 0 0 ens160
192.168.122.0 0.0.0.0 255.255.255.0 U 0 0 0 virbr0
[root@station1 ~]# ping 202.101.98.55
connect: Network is unreachable
[root@station1 ~]# ollama list
NAME ID SIZE MODIFIED
all-minilm:latest 1b226e2802db 45 MB 4 hours ago
qwen2:7b-instruct dd314f039b9d 4.4 GB 7 hours ago
|
4. 编写PL/SQL包来实现Ollama和Oracle Apex联合运行： 1）包头部分，声明proc_ollama_norag公共过程：
create or replace package pack_ollama
is
procedure proc_ollama_norag(p_question varchar2);
end;
/ |
2）包体部分，proc_ollama_norag公共过程的变量名已经声明了变量的含义：
create or replace package body pack_ollama
is
procedure proc_ollama_norag(p_question varchar2)
is
v_ollama_in clob;
v_apex_out clob;
v_json apex_json.t_values;
v_result clob;
begin
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
v_ollama_in := '{
"model": "qwen2:7b-instruct",
"messages": [
{"role": "user", "content": "' || p_question || '"}
]
}';
v_apex_out := apex_web_service.make_rest_request(
p_url => 'http://172.25.250.1:11434/v1/chat/completions',
p_http_method => 'POST',
p_body => v_ollama_in,
p_transfer_timeout => 300000
);
apex_json.parse(v_json, v_apex_out);
v_result:= apex_json.get_varchar2(p_path => 'choices[%d].message.content', p0 => 1, p_values => v_json);
dbms_output.put_line('*** Result: ' || chr(10) || v_result);
end;
end;
/ |
对于cpu模式的环境，建议在apex_web_service.make_rest_request中添加以上红色标注的p_transfer_timeout参数来延长超时时间，否则容易产生utl_http超时报错。 3）执行报错：
SET SERVEROUTPUT ON;
exec pack_ollama.proc_ollama_norag('What is new in Oracle 23ai ?');
|
错误报告 -
ORA-29273: HTTP 请求失败
ORA-06512: 在 "APEX_240100.WWV_FLOW_WEB_SERVICES", line 664
ORA-06512: 在 "APEX_240100.WWV_FLOW_WEB_SERVICES_INVOKER", line 982
ORA-06512: 在 "APEX_240100.WWV_FLOW_WEB_SERVICES_INVOKER", line 631
ORA-24247: 网络访问被访问控制列表 (ACL) 拒绝
ORA-06512: 在 "SYS.UTL_HTTP", line 380
ORA-06512: 在 "SYS.UTL_HTTP", line 1189
ORA-06512: 在 "APEX_240100.WWV_FLOW_WEB_SERVICES_INVOKER", line 579
ORA-06512: 在 "APEX_240100.WWV_FLOW_WEB_SERVICES_INVOKER", line 795
ORA-06512: 在 "APEX_240100.WWV_FLOW_WEB_SERVICES_INVOKER", line 1310
ORA-06512: 在 "APEX_240100.WWV_FLOW_WEB_SERVICES", line 624
ORA-06512: 在 "APEX_240100.WWV_FLOW_WEBSERVICES_API", line 661
ORA-06512: 在 line 24
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
|
4）在插件数据库pdb1_1中，以sys用户执行授权：
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve'),
principal_name => 'APEX_240100',
principal_type => xs_acl.ptype_db
)
);
END;
/ |
5）多次执行，发现我们的调用方式是成功的，但是由于没有RAG，Ollama产生了严重的幻觉（hallucination，见以下红字部分）：
SET SERVEROUTPUT ON;
exec pack_ollama.proc_ollama_norag('What is new in Oracle 23ai ?');
|
我们向pack_ollama包提问“What is new in Oracle 23ai ?”，AI系统为了编造答案，进行胡乱回答，这个问题将在下一篇博客中进行解决： 第一次运行：
*** Result:
As
of my current knowledge, Oracle has not specifically announced "Oracle
23AI" or any similar version that includes AI features prominently in
its name. Oracle regularly updates its products with the latest
technological advancements, including Artificial Intelligence (AI)
enhancements.
However, some of the recent AI-related initiatives
and features that Oracle has introduced could be considered part of
potential future versions akin to what you might have heard as "Oracle
23AI":
1. Oracle Cloud Infrastructure (OCI): Oracle continues to
invest in expanding its cloud offering with AI capabilities like Natural
Language Processing (NLP), Computer Vision, and more.
2.
Autonomous Machine Learning: Oracle's Autonomous machine learning option
allows users to build and deploy machine learning models without
specialized expertise.
3. Data Science Tools: New tools for data
science are added periodically through updates and releases that might
be part of a hypothetical "Oracle 23AI".
4. AI in Database:
Enhancements to the in-database AI capabilities in Oracle Database, such
as improved predictive analytics functionalities.
Always refer
to Oracle's official announcements or release notes for accurate details
on any new versions or features related to AI. Oracle typically
provides documentation and tutorials that cover specific updates
included in their releases. If there is a specific version you're
interested in or if the term "Oracle 23AI" refers to a speculative or
upcoming version, please provide additional context so that I can assist
better with information based on actual announcements by Oracle.
PL/SQL 过程已成功完成。 |
第二次运行： *** Result:
Oracle 23AI stands for Oracle Autonomous Data Warehouse version 23, although it's likely you might have misspelled AI (Artificial Intelligence) instead of XI. There are several features and updates that have been introduced under the Oracle Autonomous Data Warehouse (AUTONOMOUS DATA WAREHOUSE - ADVANCED ANALYTICS) which I would summarize as:
1. **Enhancements in AI Models**: The update likely includes updates and enhancements to existing machine learning models, making them more efficient or diverse.
2. **Improved Scalability**: Version 23XI is expected to boast significant improvements in data scaling capabilities on top of the earlier versions' performance gains.
3. **Better Integration**: Enhanced integration with other Oracle services including data streaming services, application services, and AI/ML models across Oracle Cloud services are crucial for a unified enterprise solution.
4. **Security Improvement**: New features focusing on enhancing security protocols and privacy control.
5. **User Interface Upgrades**: The user interface might have been improved to make it more intuitive and easy to use for both technical teams and business users.
6. **Performance Optimizations**: Oracle Autonomous AI is likely optimized in terms of processing power, speed, resource efficiency, and query execution performance compared to previous versions.
7. **Advanced Analytics and BI Capabilities**: Upgrades might be concentrated on the analytics capabilities which are more data-focused improvements, ensuring that data scientists have more robust tools at their disposal for advanced analysis.
8. **Enhanced AI Functionality**: The integration or enhancement of AI in various modules like prediction models, forecasting, anomaly detection, etc., will be a part of this update.
Keep checking official Oracle release notes and announcements because the detailed features with examples, use cases, screenshots, pricing, and how they can influence business intelligence initiatives may not yet be publicly available.
PL/SQL 过程已成功完成。 |
第三次运行：
*** Result:
As of my current knowledge, there was no announcement specifically calling Oracle 23AI; however, it's possible you're referring to updates and advancements within the Oracle database ecosystem that could be associated with AI or machine learning. Here are some key features and improvements in recent Oracle releases, particularly those related to AI/ML capabilities:
1. **Oracle Database XE** (Express Edition) includes a free version of Oracle Machine Learning for In-Memory which allows data scientists to create models using existing SQL queries.
2. **AI with Data Science Tools**: Oracle provides tools that enable AI and machine learning operations. Oracle Cloud Infrastructure (OCI) Analytics offers an integrated experience where data scientists can perform complex computations, manage large datasets across multiple systems, develop AI applications, scale services on demand, and collaborate with team members efficiently using OCI services.
3. **Oracle Autonomous Database**: Provides a self-driving AI-enhanced database that predicts performance issues and automatically optimizes SQL for better query processing efficiency when it comes to machine learning workloads.
4. **Oracle Analytics Cloud (OAC)**: A unified BI and analytics platform that allows the deployment of advanced analytics models developed by data scientists on top of large datasets using familiar business intelligence tools like Oracle's own Tableau and BI solutions or other popular ones like PowerBI, QlikView etc.
Please note, my last update was at Sep 2021 when this information was gathered. Oracle often adds significant new AI functionality with each release cycle. So for the most current updates, you should consult the official Oracle documentation or press releases since I might not have the latest information contained in any specific version named as "Oracle 23AI"
PL/SQL 过程已成功完成。
|
|
