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