APEX:从临时表下载 BLOB [英] APEX: Download BLOB from temporary table
问题描述
我正在尝试使用 Oracle APEX 4.1.1 构建一个简单的查看应用程序.要显示的信息位于不同数据库的表中，然后是包含 APEX 应用程序访问的架构的数据库.使用视图 (RemoteTableView) 和数据库链接访问该远程表.视图按预期工作，包括 Oracle 无法通过数据库链接选择 LOB 列项.
I'm trying to build a simple viewing application using Oracle APEX 4.1.1. The info to be displayed is in a table on a different database then the database containing the schema the APEX application accesses. This remote table is accessed using a View (RemoteTableView) and a Database Link. The View works as expected, including Oracle's disability to Select a LOB column item through a Database Link.
在 APEX 应用程序中，我定义了一个过程 (DownloadFiles)，根据 Oracle Application Express 高级教程
In the APEX application I have defined a Procedure (DownloadFiles) that is run whenever a BLOB from the View is required to be downloaded, according to the instructions in the Oracle Application Express Advanced Tutorials
当 APEX 应用程序构建在包含 BLOB 项目的现有表上时，这非常有效，没有问题.
This works perfect when the APEX application is build on an existing table containing BLOB items, no problem there.
但是，在 RemoteTableView 上，过程略有不同.额外的代码行被添加到 DownloadFiles 过程中，每当调用视图中的一个项目来下载时，将来自 RemoteTableView 的实际 BLOB 插入到临时表中(临时表).然后在 TempTable 上调用 DownloadFile 来下载(现在本地存储的)BLOB.(这样做是为了避免通过 DB-Link 直接选择 LOB 项).没有提交.
However, on the RemoteTableView the process differs slightly. Additional lines of code are added to the DownloadFiles Procedure that, whenever an item in the view is called to be downloaded, Insert the actual BLOB from RemoteTableView into a Temporary Table (TempTable). DownloadFile is then called on TempTable to download the (now locally stored) BLOB. (This is all done to circumvent the direct Selection on LOB items through a DB-Link). There is no COMMIT.
不幸的是，每当调用项目下载时，APEX 应用程序都会失败，并显示找不到此网页.未找到以下网址的网页:.../f?p=101:7:1342995827199601::NO::P7_DOC_ID:3001".
Unfortunately, the APEX applications fails whenever the item is called to download with a "This webpage is not found. No webpage was found for the web address: .../f?p=101:7:1342995827199601::NO::P7_DOC_ID:3001".
对这个问题的研究已被证明是徒劳的.插入过程按预期工作(在 PL/SQL Developer 中)，并且可以轻松下载任何其他本地表中的任何其他 BLOB.
Research into this problem has proven fruitless. The Insert Procedure is working as expected (in PL/SQL Developer) and any other BLOB in any other local table can be downloaded easily.
因此问题是，为什么 APEX 应用程序不能处理这种情况.使用临时表或插入语句时是否有我应该注意的限制?此外，下载 LOB 对象的最佳做法是什么.
Thus the question is, why can't the APEX application handle this situation. Are there limitations when working with temporary tables or insert statements that I should be aware of? Also, what are the best practices for downloading a LOB object.
详细说明插入行和下载 BLOB 的过程.(我尝试过不同的方法).这个 PL/SQL 块被称为'on load before header'，:P2_BLOB_ID 用标识符列值填充到 BLOB 列.
To elaborate on the procedure to Insert the rows and Download the BLOB. (I've tried different approaches). This PL/SQL block is called 'on load before header', :P2_BLOB_ID is filled with the identifier column value to the BLOB column.
DECLARE
v_mime VARCHAR2(48);
v_length NUMBER(38);
v_file_name VARCHAR2(38);
Lob_loc BLOB;
BEGIN
DELETE FROM [TemporaryTable];
--
INSERT INTO [TemporaryTable]( [attr1]
, [attr2]
, [blob]
, [mime] )
SELECT [attr1]
, [attr2]
, [blob]
, [mime]
FROM [RemoteTableView]
WHERE [attr1] = :P2_BLOB_ID
AND ROWNUM = 1;
--
SELECT [mime]
, [blob]
, [attr1]
, DBMS_LOB.GETLENGTH( [blob] )
INTO v_mime
, lob_loc
, v_file_name
, v_length
FROM [TemporaryTable]
WHERE [attr1] = :P2_BLOB_ID;
--
owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );
htp.p('Content-length: ' || v_length);
htp.p('Content-Disposition: attachment; filename="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
owa_util.http_header_close;
wpg_docload.download_file( Lob_loc );
END;
推荐答案
尝试添加 apex_application.stop_apex_engine 在 wpg_docload 调用之后.这将避免进一步输出 HTTP 标头，可能会因为生成更多的顶点代码而导致下载失败.
Try to add apex_application.stop_apex_engine after the wpg_docload call. This will avoid further output of HTTP headers, potentially screwing up your download because further apex code is generated.
owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );
htp.p('Content-length: ' || v_length);
htp.p('Content-Disposition: attachment; filename="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
owa_util.http_header_close;
wpg_docload.download_file( Lob_loc );
apex_application.stop_apex_engine;
<小时>
进一步说明:
Furthermore, to elaborate on :
使用临时表或插入时是否有限制我应该注意哪些陈述?
Are there limitations when working with temporary tables or insert statements that I should be aware of?
是的.但不一定在你的情况下.记住 apex 在数据库会话方面的工作方式很重要.Apex 是无状态的，可与连接池配合使用.apex 会话通常不会与 1 个数据库会话匹配，并且您永远无法保证，例如，在渲染和处理之间使用相同的数据库会话.这也在 Understanding Session 的文档中简要提到状态管理，为了方便复制:
Yes. But not necessarily in your case. It is important to remember how apex works with regard to database sessions. Apex is state-less and works with connection pooling. An apex session does not generally match up with 1 database session, and you're never guaranteed that, for example, the same database session is used between render and processing. This is also briefly mentioned in the documentation on Understanding Session State Management, copied for convenience:
HTTP，HTML 页面最常通过的协议，是无状态协议.Web 浏览器仅连接到服务器只要下载一个完整的页面.此外，每页面请求被服务器视为一个独立的事件，与之前发生或可能发生的任何页面请求无关将来发生.访问在一个页面上输入的表单值后续页面，这些值必须存储为会话状态.甲骨文Application Express 透明地维护会话状态并提供能够从以下位置获取和设置会话状态值的开发人员应用程序中的任何页面.
HTTP, the protocol over which HTML pages are most often delivered, is a stateless protocol. A web browser is only connected to the server for as long as it takes to download a complete page. In addition, each page request is treated by the server as an independent event, unrelated to any page requests that happened previously or that may occur in the future. To access form values entered on one page on a subsequent page, the values must be stored as session state. Oracle Application Express transparently maintains session state and provides developers with the ability to get and set session state values from any page in the application.
2.4.1 什么是会话?
会话是建立持久性(或状态行为)跨页面视图.每个会话被分配一个唯一标识符.Application Express 引擎使用此标识符(或会话 ID)来存储和检索应用程序的工作集每次页面浏览前后的数据(或会话状态).
A session is a logical construct that establishes persistence (or stateful behavior) across page views. Each session is assigned a unique identifier. The Application Express engine uses this identifier (or session ID) to store and retrieve an application's working set of data (or session state) before and after each page view.
因为会话完全相互独立，所以任何数量会话可以同时存在于数据库中.一个用户可以还同时运行一个应用程序的多个实例不同的浏览器.
Because sessions are entirely independent of one another, any number of sessions can exist in the database at the same time. A user can also run multiple instances of an application simultaneously in different browsers.
会话在逻辑和物理上与 Oracle 数据库不同用于为页面请求提供服务的会话. 用户在从登录到注销的单个 Oracle Application Express 会话以分钟或小时为单位的典型持续时间.每一页在该会话期间请求的结果是 Application Express引擎创建或重用 Oracle 数据库会话来访问数据库资源.通常这些数据库会话只持续一小部分一秒钟.
Sessions are logically and physically distinct from Oracle database sessions used to service page requests. A user runs an application in a single Oracle Application Express session from log in to log out with a typical duration measured in minutes or hours. Each page requested during that session results in the Application Express engine creating or reusing an Oracle database session to access database resources. Often these database sessions last just a fraction of a second.
在全局临时表的情况下，这意味着在许多情况下使用它毫无意义，因为数据仅存在于当前数据库会话中.这方面的一个例子是，人们将在 onload 某处的 GTT 中加载数据，并意味着在提交后流程或 ajax 调用中使用它.桌子很可能是空的.
然而，Apex 以 apex_collection，它将在给定的顶点会话中临时保存数据.
In the case of a global temporary table this means that it is pointless to use in many cases since the data will only exist in that current database session. An example of this is where one would load data in a GTT somewhere in the onload and means to use it in the after-submit processes or an ajax call. Big chance the table will be empty.
Apex however provides an alternative in the form of apex_collection, which will temporarily hold data within a given apex session.
这篇关于APEX:从临时表下载 BLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
