A BLOB (Binary Large Object) is an Oracle data type that is used to store digitized information, such as images, audio, video, PDF, CSV, XLSX, DOC, and more. A BLOB column can hold up to 4 GB of data. In this tutorial, I'll demonstrate how to preview this digitized information in a region via REST.
1. Create a new Interactive Report page based on the following SQL
query. I created Page 32 for this tutorial.
2. After creating the page, add the following two text field items to
it:
|
Property |
Text Field 1 |
Text Field 2 |
|
Name |
P32_PRODUCT_ID |
P32_URL |
|
Label |
Product
ID |
URL |
|
Width |
10 |
50 |
3. Expand the IR region's Columns node and click the PRODUCT_ID column to set the following
attributes. The column is being
transformed into a link. When you click a link, a region on the same page will
display the image of the selected product, as illustrated in Figure-1. The CSS code set for
the Text Link attribute will show an eye icon as the link instead
of product ids.
|
Property |
Value |
|
Type |
Link |
|
Heading |
View |
|
Target Type |
Page
in this application |
|
Page |
32 |
|
Name (under Set Items) |
P32_PRODUCT_ID |
|
Value |
#PRODUCT_ID# |
|
Text Link |
<span
class="t-icon fa fa-eye"></span> |
4.
Add a PL/SQL Dynamic
Content region to the page using the following attributes.
|
Property |
Value |
|
Title |
Preview
BLOB Content |
|
Type |
PL/SQL Dynamic Content |
|
PL/SQL Code |
DECLARE Vurl varchar2(2000) := :P32_URL; BEGIN htp.p('<embed src="'||Vurl||'"
width="500" height="500">'); END; |
|
Start New Row |
No |
|
Server-side Condition |
PL/SQL Function Body |
|
PL/SQL Function Body |
declare begin if :P32_PRODUCT_ID is not null then for c1 in (select nvl(dbms_lob.getlength(product_image),0) A from demo_product_info where product_id = :P32_PRODUCT_ID) loop if c1.A > 0 then return true; end if; end loop; end if; return false; end; |
Next, you need to configure RESTful Services as instructed in the following steps:
5. Access RESTful Services page from SQL Workshop menu.
6. Click the Register Schema with ORDS button to register your schema with ORDS
RESTful Data Services.
7. Enter a Schema Alias (for example, cfws), and select the other three options as depicted in the following screenshot. Click the Save Schema Attributes button. A message “Schema enabled for use with ORDS RESTful Services” will appear.
8. Click the Modules node,
and then click the Create Module
button. Set the following values on the ORDS
Module Definition page and create the module. The value displayed in the Full URL box in the
following figure appears after you create a module.
9. Click the Create Template button on the ORDS Module Definition screen. Enter blob/{id} for URI Template and click the Create Template button. The case-sensitive URI Template is used to
access the specified resource. The URI Template for the resource is appended to
the server path and module base path. The URI Template may also include a bind
variable appended after a forward slash. This allows a value to be passed to
the service as part of the URI – see Step 12.
10. Click the Create Handler button on the ORDS Template Definition page to create a GET handler, as illustrated
in the following figure.
11. Enter the following SQL query in the Source area on the ORDS Handler Definition page:
SELECT mimetype,product_image
FROM demo_product_info
WHERE product_id = :id
12. In Page Designer, click the P32_URL page item and set the following default static value for this item.
|
Property |
Value |
|
Type (under Default) |
Static |
|
Static Value |
https://apex.oracle.com/pls/apex/cfws/get/blob/&P32_PRODUCT_ID. |
Save your work and test it by adding different types of files, such as pdf, video, and more to the DEMO_PRODUCT_INFO table.
