Oracle APEX中BLOB类型列的使用

Use of BLOB type column in Oracle APEX

提问by user2755525

I am using a table having a BLOB column used to store a user's resume (it can be a word document, a pdf file or any other binary format).

我正在使用一个带有 BLOB 列的表，用于存储用户的简历（它可以是 word 文档、pdf 文件或任何其他二进制格式）。

Now in Oracle Apex 4.2, when I create a form on this table, I automatically get a browse button for selecting a file for the BLOB column. I can browse through files and select a file and then I press another button (also provided automatically) to upload the file. Now if I see this record in SQL Developer or Pl/SQL developer, I see that the BLOB data is there, and I can save it to my disk or even can view it directly from database. This is all nice!

现在在 Oracle Apex 4.2 中，当我在此表上创建表单时，我会自动获得一个浏览按钮，用于为 BLOB 列选择文件。我可以浏览文件并选择一个文件，然后按另一个按钮（也自动提供）来上传文件。现在如果我在 SQL Developer 或 Pl/SQL developer 中看到这条记录，我看到 BLOB 数据在那里，我可以将它保存到我的磁盘，甚至可以直接从数据库中查看它。这一切都很好！

But now when I create a report on same table In Oracle APEX, and then run that report, it doesn't give me any option to view or download the binary file, instead, it only gives the text "[unsupported data type]" in the BLOB column space. I saw in Oracle APEX online documentation that When we run a report having a BLOB column APEX automatically provides a Download button.... but in actual it is not happening, and I am seeing no way of retreiving BLOB column data in Oracle APEX. Can someone please help me???

但是现在当我在 Oracle APEX 中的同一个表上创建报告，然后运行该报告时，它没有给我任何查看或下载二进制文件的选项，而是只提供文本“[不支持的数据类型]”在 BLOB 列空间中。我在 Oracle APEX 在线文档中看到，当我们运行具有 BLOB 列的报告时，APEX 会自动提供一个下载按钮....但实际上并没有发生，而且我认为无法在 Oracle APEX 中检索 BLOB 列数据。有人可以帮帮我吗？？？

Thanks in Advance.

提前致谢。

回答by Richard Pascual

Storing and Accessing File Attachments in BLOB Data Types Through Oracle APEX

通过 Oracle APEX 存储和访问 BLOB 数据类型的文件附件

This solution was also performed on an Apex 4.2 release (specifically, 4.2.5); Special thanks to the Oracle Corp for hosting my test instance at: http://apex.oracle.com.

该解决方案也在 Apex 4.2 版本（特别是 4.2.5）上执行；特别感谢 Oracle 公司在http://apex.oracle.com 上托管我的测试实例。

Here is the schema design for the table I used which contains a BLOB typed data column. Note: this will not be the design of the final solution; just follow with the changes as they come so that you can understand what I found out about a few limitations of the APEX Form and Report creation wizards.

这是我使用的包含 BLOB 类型数据列的表的架构设计。注意：这不会是最终解决方案的设计；只需跟随更改，您就可以了解我发现的有关 APEX 表单和报告创建向导的一些限制的信息。

First Attempt: Setting Up the APEX Table, Form and Report

第一次尝试：设置 APEX 表、表单和报告

Table: MY_DOC_STACK First Layout Attempt

表：MY_DOC_STACK 第一次布局尝试

The column DOC_FILEis the BLOB type which stores the actual document attachment. This is the look of the Form and the Report created using the APEX Application wizard which points directly to the table:

该列DOC_FILE是存储实际文档附件的 BLOB 类型。这是使用直接指向表格的 APEX 应用程序向导创建的表单和报告的外观：

ADDING a DOCUMENT to the BLOB Typed Field

将文档添加到 BLOB 类型字段

The report query seems to work as shown below:

报告查询似乎如下所示：

Here is a list of more records with document attachments:

以下是带有文档附件的更多记录列表：

Sample Report Output With Multiple Records

具有多条记录的示例报告输出

The problem is when trying to download the file that was put into the BLOB field:

问题是在尝试下载放入 BLOB 字段的文件时：

It's subtle from the picture, but the identified mime type: Application/Octet-Streamis an indicator that the APEX form has lost track of the type of file (Microsoft Word, docx) that I had just uploaded. The file saved is just a bunch of garbage characters. Trying to change the file extension doesn't help either.

从图片上看很微妙，但识别出的 mime type:Application/Octet-Stream是 APEX 表单丢失了我刚刚上传的文件类型（Microsoft Word、docx）的指示符。保存的文件只是一堆垃圾字符。尝试更改文件扩展名也无济于事。

Second (Revised) Attempt: Adjustments to APEX Application Design for Blob/Document Handling

第二次（修订后）尝试：调整用于 Blob/文档处理的 APEX 应用程序设计

Although the application regions and their components did not work immediately after the wizard completed, there are only a few minor edits to put it into working condition. Closer inspection of the form element PX_DOC_FILEshows that BLOB form elements require some additional meta-information about the file attached to the record:

虽然向导完成后应用程序区域及其组件并没有立即工作，但只有少量的小编辑使其进入工作状态。仔细检查表单元素会PX_DOC_FILE发现 BLOB 表单元素需要一些关于附加到记录的文件的额外元信息：

What's missing from the blob field Form input item:

1. Mime Type
2. File Name
3. Character Set
4. BLOB Last Updated (date) Column

blob 字段表单输入项中缺少的内容：

1. 哑剧类型
2. 文档名称
3. 字符集
4. BLOB 上次更新（日期）列

I went ahead and defined the additional columns and added it to the BLOB-containing table (MY_DOC_STACK), the uploading Apex form and the report region definition.

我继续定义了附加列，并将其添加到包含 BLOB 的表 (MY_DOC_STACK)、上传的 Apex 表单和报告区域定义中。

Note that the column names (for simplicity) have been made the same as the requirements of the Blob form element DOC_FILE.

请注意，列名（为简单起见）已与 Blob 表单元素的要求相同DOC_FILE。

Revised Document Attachment Apex Form

修订后的文件附件 Apex 表格

I initially thought one had to be clever to anticipate all the possible values of Mime Types (msword, pdf, zip, etc.) but that was unnecessary. Likewise for the other fields reserved for character type, and last updated columns.

我最初认为必须聪明地预测 Mime 类型（msword、pdf、zip 等）的所有可能值，但这是不必要的。对于为字符类型保留的其他字段和最后更新的列也是如此。

IMPORTANT NOTE:You can actually skip form inputs for the supporting Blob meta-data fields. Values such as MIME_TYPEand CHARACTER_TYPEare automatically detected when the document attachment is uploaded. The Apex form ITEM storing the document blob just needs the names of the columns that will store this information.

ADDITIONAL NOTE:After adding the new columns, expanding the form and report column references, you will need to clear (or truncate) the existing table or reload each document attachment to be sure. You may be able to still use the uploads from the first attempts, but you'll need to verify that for yourself to be sure.

重要说明：您实际上可以跳过支持 Blob 元数据字段的表单输入。上传文档附件时会自动检测诸如MIME_TYPE和 之CHARACTER_TYPE类的值。存储文档 blob 的 Apex 表单 ITEM 只需要将存储此信息的列的名称。

附加说明：添加新列、展开表单和报告列引用后，您需要清除（或截断）现有表格或重新加载每个文档附件以确保。您可能仍然可以使用第一次尝试上传的内容，但您需要自己验证以确保。

Revised Document Blob Upload Report

修订后的文档 Blob 上传报告

Revised Report Output Discussion

修订后的报告输出讨论

1. [Owner: AUDREY HEPBURN]: I forced the MIME_TYPEwith my form to "Application/msword"; although the file I uploaded was ".docx" type, downloading it back through the Apex page saved it to my local client as a ".doc" format (the old MS Word format).

2. [Owner: CHEVY CHASE]: This time, MIME_TYPEwas not inputted and the Apex form process/action added this to the record when it was created:

   application/vnd.openxmlformats-officedocument.wordprocessingml.document

   This probably is the format designated by Microsoft Office 2013. The FILE_NAMEvalue was user defined and the .docx extension was added explicitly. The result was that downloading the file prompted the user defaulted to open up the file using the correct application on my client computer: MS Word (Version 2013).

3. [Owner: CARRIE FISHER]: Same as test case (2) but using an Adobe PDF (Portable Document Format) Instead. Same behavior except the MIME_TYPEidentified itself as application/pdf; file opened as expected.

1. [所有者：AUDREY HEPBURN]：我MIME_TYPE用我的表格强制“申请/msword”；尽管我上传的文件是“.docx”类型，但通过 Apex 页面将其下载回来，将其作为“.doc”格式（旧的 MS Word 格式）保存到我的本地客户端。

2. [所有者：CHEVY CHASE]：这次MIME_TYPE没有输入，Apex 表单流程/操作在创建记录时将其添加到记录中：

   应用程序/vnd.openxmlformats-officedocument.wordprocessingml.document

   这可能是指定的格式Microsoft Office 2013。该FILE_NAME值是用户定义的，并且显式添加了 .docx 扩展名。结果是下载文件提示用户默认使用客户端计算机上的正确应用程序打开文件：MS Word（版本 2013）。

3. [所有者：CARRIE FISHER]：与测试用例 (2) 相同，但使用 Adob​​e PDF（便携式文档格式）代替。除了将MIME_TYPE自身标识为 application/pdf之外的相同行为；文件按预期打开。

More Discussion:

更多讨论：

All this trouble is from the the generic DML API's that Apex uses to manage inserts, updates and deletes from the application's schema, most likely it is part of Apex's hardening against SQL injection attacks. The direct INSERTand SELECTstatements used in your SQL client is not the same way that a default form design (from an application wizard) is set up to manage DML transactions.

所有这些问题都来自 Apex 用于管理应用程序架构中的插入、更新和删除的通用 DML API，这很可能是 Apex 加强对 SQL 注入攻击的一部分。SQL 客户端中使用的直接INSERT和SELECT语句与设置默认表单设计（来自应用程序向导）以管理 DML 事务的方式不同。

Note that the page process: Process Row of MY_DOC_STACKlooks more parameter driven. If there is a DML operation in there somewhere, it will be based first on the careful screening of each input variable submitted through the Apex form.

注意页面进程： Process Row of MY_DOC_STACK看起来更多的是参数驱动。如果某处有 DML 操作，它将首先基于对通过 Apex 表单提交的每个输入变量的仔细筛选。

There are many other ways that Apex can manage DML transactions; ... this solution focuses on what was most likely encountered by the OP.

Apex 可以通过许多其他方式管理 DML 事务；...此解决方案侧重于 OP 最有可能遇到的问题。

Good Luck!

祝你好运！