oracle – APEX：从临时表下载BLOB

我正在尝试使用Oracle APEX 4.1.1构建一个简单的查看应用程序.要显示的信息位于不同数据库的表中,然后是包含APEX应用程序访问的模式的数据库.使用View(RemoteTableView)和 Database Link访问此远程表. View按预期工作,包括Oracle无法通过数据库链接选择LOB列项.

在APEX应用程序中,我根据Oracle Application Express Advanced Tutorials中的说明定义了一个过程(DownloadFiles),只要需要下载View中的BLOB,就会运行该过程(DownloadFiles)

当APEX应用程序构建在包含BLOB项目的现有表上时,这非常有用,没有问题.

但是,在RemoteTableView上,该过程略有不同.其他代码行添加到DownloadFiles过程中,每当调用视图中的项目进行下载时,将实际的BLOB从RemoteTableView插入临时表(TempTable).然后在TempTable上调用DownloadFile来下载(现在本地存储的)BLOB. (这样做是为了绕过通过DB-Link直接选择LOB项目).没有COMMIT.

不幸的是,每当调用该项目时,APEX应用程序都会失败并显示“未找到此网页.未找到该网址的网页：… / f？p = 101：7：1342995827199601 :: NO :: P7_DOC_ID ：3001\” .

对这个问题的研究证明是徒劳的.插入过程按预期工作(在PL / SQL Developer中),并且可以轻松下载任何其他本地表中的任何其他BLOB.

因此问题是,为什么APEX应用程序无法处理这种情况.使用临时表或插入我应该注意的语句时是否有限制？此外,下载LOB对象的最佳做法是什么.

详细说明插入行和下载BLOB的过程. (我尝试过不同的方法).此PL / SQL块称为“在标头之前加载”,：P2_BLOB_ID用标识符列值填充到BLOB列.

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],[mime]
  FROM   [RemoteTableView]
  WHERE  [attr1] = :P2_BLOB_ID
  AND    ROWNUM  = 1;
  --
  SELECT [mime],[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;

解决方法

owa_util.mime_header( nvl(v_mime,null)|| '"');
  owa_util.http_header_close;
  wpg_docload.download_file( Lob_loc );
  apex_application.stop_apex_engine;

此外,详细说明：

Are there limitations when working with temporary tables or insert
statements that I should be aware of?

是.但不一定在你的情况下.重要的是要记住apex在数据库会话方面的工作原理. Apex是无状态的,可以使用连接池.顶点会话通常与1个数据库会话不匹配,并且您永远不能保证,例如,在渲染和处理之间使用相同的数据库会话.这也在Understanding Session State Management的文档中简要提及,为方便起见复制：

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 What Is a Session?

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.

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形式的替代方案,它将暂时保存给定顶点会话中的数据.

【免责声明】本站内容转载自互联网，其相关言论仅代表作者个人观点绝非权威，不代表本站立场。如您发现内容存在版权问题，请提交相关链接至邮箱：bqsm@foxmail.com，我们将及时予以处理。