Ulrike Brenner Bilder in APEX.key

EinBildsagtmehrals1000
Worte-
BilderinAPEX
April 2016
APEX Connect 2016
Ulrike Brenner
© click-click IT Solutions
Bilder in APEX
Ulrike Brenner
• Seit 1999 Oracle Entwicklerin
• Oracle DB 8-12c
• SQL PL/SQL Oracle Forms/Reports
APEX
• Entwicklerin Teamleiterin Kundenbetreuerin
Bilder in APEX
© click-click IT Solutions
• Datenbank
- Shared Components
- eigene DB-Tabelle
• am Web-Server
© click-click IT Solutions
Shared Components
Shared Components
© click-click IT Solutions
• Bilder im APEX Repository
• APEX-Views (APEX 5)
- APEX_WORKSPACE_STATIC_FILES
- APEX_APPLICATION_STATIC_FILES
Static Files
vor APEX 5
© click-click IT Solutions
• Cascading Style Sheets
• Images
• Static Files
Static Files
vor APEX 5
• Bilder einzeln hochladen
- Applikation zuordnen
Dateiname eindeutig pro Applikation
- oder No Application Associated
© click-click IT Solutions
Dateiname eindeutig pro Workspace
Static Files
vor APEX 5
• Bilder sind nicht im Export-File enthalten
• Export Images
• Supporting Objects / Installation Scripts /
© click-click IT Solutions
Create Script to Install Files
ACHTUNG: eine Änderung des Bildes hat
keine Auswirkung auf das Installations-Skript
Static Files
APEX 5
• Static Application Files
• Static Workspace Files
© click-click IT Solutions
• für alle Static Files (css, js, png, jpg…)
Static Files
APEX 5
• alle Bilder auf einmal in einer zip-Datei
uploaden
• Bilder werden entzippt im APEXRepository gespeichert
• ev. vorhandene Verzeichnisstruktur wird im
© click-click IT Solutions
Repository gespeichert
© click-click IT Solutions
Static Files
APEX 5
© click-click IT Solutions
Static Files
APEX 5
Static Files
APEX 5
• im Export enthalten (wie auch alle anderen Static Files)
© click-click IT Solutions
• Download als zip
© click-click IT Solutions
Static Files
Verwendung
• Statische Referenz
- #APP_IMAGES#Images/Logo.png
- #WORKSPACE_IMAGES#Logo.png
• Dynamische Referenz
- :APP_IMAGES||’Images/Logo.png’
- :WORKSPACE_IMAGES||’Logo.png’
© click-click IT Solutions
Finder
© click-click IT Solutions
Finder
Static Files
Verwendung
© click-click IT Solutions
• Region Image
• Logo in einer Applikation
•…
Static Files
Verwendung
© click-click IT Solutions
• Logo
Static Files
vor APEX 5
© click-click IT Solutions
<img src="wwv_flow_file_mgr.get_file?
p_security_group_id=919401922659555&p
_flow_id=1400&p_fname=Logo.png">
Static Files
APEX 5
© click-click IT Solutions
<img src="ulibrenner/r/44678/files/static/
v27/Images/Logo.png">
Static Files
APEX 5
• Images werden beim Upload überschrieben
(Löschen und Neuanlegen nicht mehr
notwendig)
© click-click IT Solutions
• Versionsnummer wird erhöht (mit Lücken)
Static Files
• html-Region, html-Header, html-Footer
• <img © click-click IT Solutions
src="#APP_IMAGES#Images/Image.png" height="300" width="350" alt="image"/>
© click-click IT Solutions
Display Image
Display Image
© click-click IT Solutions
Based on:
“Image URL stored in Page Item Value”
sys.htp
• Package HTP im sys-Schema
• generiert html-Tags
• HTP.TITLE('Hello'); erzeugt <TITLE>Hello</TITLE>
• HTP.HEADER(1, 'Hello');
© click-click IT Solutions
erzeugt <H1>Hello</H1>
sys.htp.p
• htp.p kurz für htp.print
• \n (Zeilenumbruch) am Schluss
nicht gleichzusetzen mit <br>
© click-click IT Solutions
HTP.PRINT (
cbuf
IN
);
VARCHAR2
© click-click IT Solutions
sys.htp.p
sys.htp.p
© click-click IT Solutions
• PL/SQL Dynamic Content Region
HTP.P('
<img
id="PackedAppImg"
src="#APP_IMAGES#Images/
PackagedApps.png"
alt="PackagedApps">
');
© click-click IT Solutions
Web Server
Bilder am Webserver
vs. in Shared Components
• Shared Components
- seit APEX 5: kein Caching-Problem
- ggf. Datenbank-Perfomance-Issue bei
© click-click IT Solutions
vielen Zugriffen
Bilder am Webserver
vs. in Shared Components
• Webserver
- gleiche Verwendungsmöglichkeiten wie
© click-click IT Solutions
Static Files in APEX
-
zusätzlicher Prozess beim Deployment
-
Datenbank wird nicht extra ‘belastet’
außerhalb des Deploymentzyklus
aktualisierbar
Referenzieren von Bilder
• /i/
- virtuelle image-Verzeichnis v. Apex
- /i/ Default-Bezeichnung
- verweist auf den aktuellen Pfad im
© click-click IT Solutions
Dateisystem
Referenzieren von Bilder
• #IMAGE_PREFIX#
- zeigt auf /i/ (bzw. das virtuelle ImageVerzeichnis)
© click-click IT Solutions
-
<img src="#IMAGE_PREFIX#go.gif">
Referenzieren von Bilder
• via URL
© click-click IT Solutions
<img src="http://g-images.amazon.com/
images/G/01/associates/navbar2000/logono-border(1).gif">
© click-click IT Solutions
Bilder in eigener DB-Tabelle
© click-click IT Solutions
Upload BLOB
Page Item
Upload BLOB
• File Browse Item
• Storage Type
- BLOB column specified in Item Source
attribute
© click-click IT Solutions
-
Tabelle
APEX_APPLICATION_TEMP_FILES
© click-click IT Solutions
File Browse Item
File Browse Item
• BLOB column specified in Item Source
© click-click IT Solutions
attribute
• MIME Type Column
• Filename Column
• Character Set Column
•…
© click-click IT Solutions
File Browse Item
File Browse Item
• Source (bei File Browse Item)
- Always, replacing any existing value in
session state
-
Database Column
© click-click IT Solutions
• DML Prozess (Automatic Row Processing)
File Browse Item
Storage Type
• APEX_APPLICATION_TEMP_FILES
vor APEX5: WWV_FLOW_FILES
© click-click IT Solutions
• Geladene Daten werden automatisch
gelöscht
© click-click IT Solutions
File Browse Item
File Browse Item
• End of Session
Löscht die Datei am Ende der aktuellen
APEX Session
• End of Request
© click-click IT Solutions
Löscht die Datei am Ende des aktuellen
HTTP request
File Browse Item
• PL/SQL-Prozess zum Speichern
• Loop über View
APEX_APPLICATION_TEMP_FILES
WHERE Name = :P13_FILE_BROWSE
© click-click IT Solutions
31032627182659273533/MyImage.png
• FILENAME: MyImage.png
• BLOB_CONTENT, MIME_TYPE, …
© click-click IT Solutions
Display Image
© click-click IT Solutions
Display Image
© click-click IT Solutions
Display Image
<img id="P9_DISPLAY_IMAGE_SQL"
class="display_image"
title="Display image sql"
alt="Display image sql"
src="wwv_flow.show?
p_request=NATIVE
%3D86E0648760651182732A0C23A2E
61CEC5290&
p_instance=111572656601179
&p_flow_id=44678&p_flow_step_i
d=9">
Session ID
Display Image
© click-click IT Solutions
• Form mit Automatic Row Fetch
Display Image
© click-click IT Solutions
• Form mit Automatic Row Fetch
© click-click IT Solutions
Display Image
© click-click IT Solutions
Display Image
<img
id="P14_PRODUCT_IMAGE_DISPLAY
" class="display_image"
…
src="apex_util.get_blob_file?
a=44678&
s=111572656601179&
p=14&
…
Session ID
BLOB in a Report
© click-click IT Solutions
SELECT OI.ORDER_ID
, OI.PRODUCT_ID
, OI.UNIT_PRICE
, OI.QUANTITY
FROM DEMO_ORDER_ITEMS OI
WHERE OI.ORDER_ID = :P6_ORDER
BLOB-Tabelle: DEMO_PRODUCT_INFO
PK: PRODUCT_ID
BLOB in a Report
vor APEX 5
• PK der Image-Tabelle muss in ReportQuery sein! zumindest als Alias P_ID
AS PROCUCT_ID
• beliebige Number-Spalte als ‘Download’© click-click IT Solutions
Link (Wert != 0)
• dbms_lob.getlength(…) nicht notwendig
• Number/Date Format: BLOB Format
© click-click IT Solutions
BLOB in a Report
vor APEX 5
© click-click IT Solutions
BLOB in a Report
vor APEX 5
• Download oder Image
• Blob Column Attributes
- Blob Tabelle
- Blob Column
- Primary Key Column 1
-…
© click-click IT Solutions
BLOB in a Report
vor APEX 5
BLOB in a Report
© click-click IT Solutions
SELECT OI.ORDER_ID
, OI.PRODUCT_ID
, OI.UNIT_PRICE
, OI.QUANTITY
FROM DEMO_ORDER_ITEMS OI
WHERE OI.ORDER_ID = :P6_ORDER
BLOB-Tabelle: DEMO_PRODUCT_INFO
PK: PRODUCT_ID
© click-click IT Solutions
Download-Link
© click-click IT Solutions
Display BLOB in a Report
© click-click IT Solutions
Display BLOB in a Report
© click-click IT Solutions
Display BLOB in a Report
Display BLOB in a Report
© click-click IT Solutions
<img src="apex_util.get_blob?
s=14691605876776
&a=44678
&c=30373689676338576770
&p=6
Session ID
&k1=%33
&k2=
&ck=MEzn61qNrdGgEPxNflI3jh6Gn7sJnvk
5oTfdQHIYGIgmeUUb1DGe8v-H_JVXCBtB7bRPYXn-2hcdG0wx37_yQ&rt=CR
">
BLOB Report/Form
• APEX_UTIL.GET_BLOB_FILE_SRC
• SELECT
© click-click IT Solutions
<img src=
apex_util.get_blob_file_src
( ‘Px_ITEM’, ID)
FROM myTable
• Formatierung des angezeigten Bildes
möglich (Höhe, Breite etc.)
BLOB Report/Form
• APEX5: Escape special characters: NO
• Funktioniert nur mit einem File Browse
Item, muss nicht auf der selben APEX-Seite
sein
• Prozess um File zu laden (Automated Row
© click-click IT Solutions
Fetch) - managed on PK! (nicht ROWID)
© click-click IT Solutions
BLOB Report/Form
CASE
WHEN NVL(dbms_lob.getlength
(document),0) = 0
THEN NULL
ELSE
'<img src="'||
apex_util.get_blob_file_src
('P4_DOCUMENT',id)||
'" />'
END
© click-click IT Solutions
BLOB Report/Form
BLOB Report/Form
return
© click-click IT Solutions
'<img src="'||
apex_util.get_blob_file_src
( 'P6_PRODUCT_IMAGE'
, :P6_PRODUCT_ID
)||
'" />';
© click-click IT Solutions
BLOB Report/Form
<img
src="apex_util.get_blob_file?
a=42135&
s=116424848558594
&p=6&d=16760024273049625196&i=16
460868170336031860&p_pk1=8&p_pk2
=&p_ck=7X3wfe6r7x_xzDYyQA5yAENk6
YiOS89e9tZEJ88hObc4Z3MEEu7VwE2kS
H-mnpJdHxPpga234NSzJ500qbj4Nw">
</span>
Session ID
PL/SQL Procedure in URL
© click-click IT Solutions
• PL/SQL Prozedur in der Datenbank
• wird direkt aus der URL aufgerufen
PL/SQL Procedure in URL
Joel Kallman
Director of Software Development at
Oracle, and manages the development
and product management of Application
Express (APEX) at Oracle.
http://joelkallman.blogspot.co.at/
© click-click IT Solutions
PL/SQL Procedure in URL
Joel Kallman
• I see this "solution" commonly
documented on the Internet, and in
general, it should not be followed.
© click-click IT Solutions
• The default configuration of Oracle
Application Express has a white list of
entry points, callable from a URL. For
security reasons, you absolutely want to
leave this restriction in place and not
relax it.
PL/SQL Procedure in URL
Alternativen:
• On-Demand Prozess
• RESTful Service
© click-click IT Solutions
joelkallman.blogspot.co.at/2014/03/yetanother-post-how-to-link-to.html
On-Demand Prozess
© click-click IT Solutions
• APEX-Authorisation/Authentication
• Browser-Caching
On-Demand Prozess
• Erstellen eines Application Items
(z.B: FILE_ID)
• Erstellen eines Application Processes
© click-click IT Solutions
mit On Demand: Run this application
process when requested by a page
process
© click-click IT Solutions
On-Demand Prozess
© click-click IT Solutions
On-Demand Prozess
On-Demand Prozess
© click-click IT Solutions
for c1 in (select *
from my_image_table
where id = :FILE_ID)
loop
—> nächste Folie
end loop;
© click-click IT Solutions
On-Demand Prozess
sys.htp.init;
sys.owa_util.mime_header( mimetype… );
sys.htp.p(Content-length: ‘…);
sys.htp.p('Content-Disposition: attachment;
filename=… );
sys.htp.p('Cache-Control: max-age=3600');
sys.owa_util.http_header_close;
sys.wpg_docload.download_file(….);
apex_application.stop_apex_engine;
On-Demand Prozess
Cache-Control: max-age=3600
© click-click IT Solutions
Info an den Browser die Bilder für 1 Stunde
zu cachen
Angabe in Sekunden
On-Demand Prozess
• Application Process in einer URL
© click-click IT Solutions
verwenden:
f?p=&APP_ID.:0:
&APP_SESSION.:
APPLICATION_PROCESS=GETIMAGE
:::FILE_ID:<some_valid_id>
On-Demand Prozess
• HTML Expression einer Spalte in einem
Report
© click-click IT Solutions
<img src="f?p=&APP_ID.:
0:&APP_SESSION.:
APPLICATION_PROCESS=GETIMAGE::
:FILE_ID:#PRODUCT_ID#">
© click-click IT Solutions
On-Demand Prozess
© click-click IT Solutions
On-Demand Prozess
© click-click IT Solutions
RESTful Service
RESTful Service
© click-click IT Solutions
• SQL-Workshop -> RESTful Services
• Create Module
- Name: IMAGE
- URI Template: z.B: images/{id}
RESTful Service
© click-click IT Solutions
• Create Handler
• Method: GET
• Source Type: Media Resource
• Source:
SELECT
,
FROM
WHERE
MIMETYPE
PRODUCT_IMAGE
DEMO_PRODUCT_INFO
PRODUCT_ID = :id
© click-click IT Solutions
RESTful Service
© click-click IT Solutions
RESTful Service
© click-click IT Solutions
Bilder manipulieren
• Datentyp ORDIMAGE
• Funktionen
- PROCESS
- PROCESSCOPY
-…
Thumbnail
ORDIMAGE.PROCESSCOPY
( IMAGEBLOB => vImageBLOB
, COMMAND
=> 'maxScale=50 50'
, DEST
=> vThumbBLOB
© click-click IT Solutions
);
http://sql-plsql-de.blogspot.co.at/
2013/06/image-processing-in-derdatenbank.html
© click-click IT Solutions
Bilder in APEX
• Shared Components: Static Files
- mit APEX5 stark verbessert
• Web-Server
• DB-Tabelle
- On-Demand Prozess
- RESTful Webservice
Ulrike Brenner
click-click IT Solutions
Aspettenstraße 48
A-2380 Perchtoldsdorf
[email protected]
http://www.click-click.at