How to create a repoert query in APEX and custom report layout

1) Create report query in APEX
Shared Components -> Report Queries -> Create
Report Query Name: first_query
Output Format: PDF



Click on Next button

SQL guery:

select  "PHOTOS"."PHOTO_ID" as "PHOTO_ID",
  "PHOTOS"."MIME_TYPE" as "MIME_TYPE" 
 from  "PHOTOS" "PHOTOS"

Click on Next button

Click on Add Query button

Click on Create button

Click on Next button

Click on Next button

Click on Finish button

At now you get the following result (because we used the default report layout):
- we can't see the second query (select sysdate from dual), we can only see the first query in pdf


2) Create custom report layout
At first we have to see the xml data:
Shared Components -> Report Queries ->Click on first_query (which was created by us)
-> Source Queries -> click on Download button

Xml data which was generated by APEX:

<?xml version="1.0" encoding="UTF-8"?>
<DATA>
<ROWSET1>
   <ROWSET1_ROW>
      <PHOTO_ID>1</PHOTO_ID>
      <MIME_TYPE>image/jpeg</MIME_TYPE>
   </ROWSET1_ROW>
</ROWSET1>
<ROWSET2>
   <ROWSET2_ROW>
      <SYSDATE>31-MAY-10</SYSDATE>
   </ROWSET2_ROW>
</ROWSET2>
</DATA>

If you see the xml data you can write layout for it. (Or you can use BI Publisher Desktop and MS Word for it)

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      xmlns:fo="http://www.w3.org/1999/XSL/Format">
  <xsl:output method="xml" indent="yes"/>
  <xsl:template match="/">
    <fo:root>
      <fo:layout-master-set>
        <fo:simple-page-master master-name="A4-portrait"
              page-height="29.7cm" page-width="21.0cm" margin="2cm">
          <fo:region-body/>
        </fo:simple-page-master>
      </fo:layout-master-set>
      <fo:page-sequence master-reference="A4-portrait">
        <fo:flow flow-name="xsl-region-body"> <!-- START of PAGE BODY --> 
          <fo:block > <!-- START of MAIN BLOCK in page body --> 
    

<xsl:for-each select="DATA" ><!-- start of DATA -->    



    

<fo:block font-weight="bold" font-family="Helvetica" font-size="18pt" padding-top="1pt" padding-bottom="20pt" text-align="center">PHOTOS</fo:block>


      


<fo:block padding-before="10pt" padding-bottom="10pt">

  <fo:table border="0.5pt solid #000000">
  <fo:table-column column-width="20mm"/>
  <fo:table-column column-width="43mm"/>
  <fo:table-column column-width="120mm"/>
  <fo:table-body >

      <fo:table-row>
     <fo:table-cell border-right="0.5pt solid #000000" border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
       <fo:block  font-size="12pt" font-family="Helvetica">Photo_id</fo:block>
     </fo:table-cell> 
     <fo:table-cell border-right="0.5pt solid #000000" border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
       <fo:block  font-size="12pt" font-family="Helvetica">mime_type </fo:block>
     </fo:table-cell>     
     <fo:table-cell border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
       <fo:block font-size="12pt" font-family="Helvetica">Photo        
       </fo:block>
     </fo:table-cell>
                   </fo:table-row>  
  
<!-- start of first query -->
    <xsl:for-each select="ROWSET1" ><!-- start of ROWSET1 --> 
      <xsl:for-each select="ROWSET1_ROW" ><!-- start of ROWSET1_ROW -->
      
      <fo:table-row>
     <fo:table-cell border-right="0.5pt solid #000000" border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
       <fo:block  font-size="12pt" font-family="Helvetica">
        <xsl:value-of select="PHOTO_ID"/>       
       </fo:block>
     </fo:table-cell> 
     <fo:table-cell border-right="0.5pt solid #000000" border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
       <fo:block  font-size="12pt" font-family="Helvetica">
        <xsl:value-of select="MIME_TYPE"/>        
       </fo:block>
     </fo:table-cell>     
     <fo:table-cell border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
       <fo:block font-size="12pt" font-family="Helvetica">       
<!-- start of image -->        

<!-- end of image -->         
       </fo:block>
     </fo:table-cell>
                   </fo:table-row>
  

   </xsl:for-each><!-- end of ROWSET1_ROW -->
         </xsl:for-each><!-- end of ROWSET1 -->    
<!-- end of first query  -->

<!-- start of second query -->
    <xsl:for-each select="ROWSET2" ><!-- start of ROWSET2 --> 
      <xsl:for-each select="ROWSET2_ROW" ><!-- start of ROWSET2_ROW -->
      
      <fo:table-row>
     <fo:table-cell cell-spanding="2" border-right="0.5pt solid #000000"  padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
       <fo:block  font-size="12pt" font-family="Helvetica">Sysdate </fo:block>
     </fo:table-cell>      
     <fo:table-cell border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
       <fo:block font-size="12pt" font-family="Helvetica">                    
        <xsl:value-of select="SYSDATE"/>        
       </fo:block>
     </fo:table-cell>
                   </fo:table-row>
  

   </xsl:for-each><!-- end of ROWSET2_ROW -->
         </xsl:for-each><!-- end of ROWSET2 --> 
<!-- end of second query  -->
      
        </fo:table-body>
        </fo:table>

</fo:block>


      
 </xsl:for-each><!-- end of DATA --> 
 
</fo:block><!-- END of MAIN BLOCK in page body --> 

        </fo:flow> <!-- END of PAGE BODY --> 



  
      </fo:page-sequence>
    </fo:root>
  </xsl:template>
</xsl:stylesheet>

And now add the picture for report query and layout

Shared Components -> Report Queries ->Click on first_query (which was created by us)

select  "PHOTOS"."PHOTO_ID" as "PHOTO_ID",
  "PHOTOS"."MIME_TYPE" as "MIME_TYPE" ,
         'http://YOUR_WEBSERVER/DAD/USER.my_image_display?p_image_id='||PHOTO_ID as FILEPATHVALUE
 from  "PHOTOS" "PHOTOS"

YOUR_WEBSERVER => the server's IP or canonical name
DAD => Database Access Descriptor
USER => schema name where my_images_display procedure is located
my_image_display => see the my_image_display procedure

Add the following code your custom layout:

....
<!-- start of image -->        
       <fo:block  font-size="10pt"> 
       
        <xsl:variable name="filepathValue" select="FILEPATHVALUE" /> 
       
      <fo:external-graphic >
      <xsl:attribute name="src">
      <xsl:value-of select="$filepathValue"/>
      </xsl:attribute>
      <xsl:attribute name="width">
      <xsl:value-of select="'2cm'"/>
      </xsl:attribute>      
      </fo:external-graphic> 


       </fo:block> 
<!-- end of image --> 

....

Upload your layout to APEX

Shared Components -> Report Layouts -> Click on Create button -> select Named Columns (XSL-FO)



Layou name: first_layout_query
Report Layout File: select your custom layout file


Click on Create Layout button

Shared Components -> Report Queries ->Click on first_query (which was created by us) -> Change "Report Layout" ->select "first_layout_query"





At the end you can see the picture in pdf

How to show BLOB type Column as image in APEX

1)Create table which stores the blob data
At first we had created a table with sequence and trigger. It is used for storing data.

CREATE TABLE PHOTOS
  (
    "PHOTO_ID"        NUMBER NOT NULL ENABLE,
    "PHOTO_BLOB"      BLOB,
    "MIME_TYPE"       VARCHAR2(64 BYTE));

CREATE SEQUENCE PHOTO_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 1000000 MINVALUE 1;

create or replace
trigger photos_trg
      before insert 
         on photos for each row
    begin
      select photo_seq.nextval     into :new.photo_id    from dual;
    end;
    commit; 

2) Create pocedure which shows picture
We had created a procedure which can display our blobs data (as picture on a page) from our table.

create or replace
PROCEDURE my_image_display( p_image_id IN NUMBER)
AS
    
 l_mime        VARCHAR2 (255);
   l_length      NUMBER;
   l_file_name   VARCHAR2 (2000);
   lob_loc       BLOB;
BEGIN

         SELECT mime_type, photo_blob, DBMS_LOB.getlength (photo_blob)
         INTO l_mime, lob_loc, l_length
         FROM photos
         WHERE photo_id = p_image_id;    


   OWA_UTIL.mime_header (NVL (l_mime, 'application/octet'), FALSE);
   HTP.p ('Content-length: ' || l_length);
   OWA_UTIL.http_header_close;
   WPG_DOCLOAD.download_file (lob_loc);
   
END my_image_display;

3) Create public synonym:
When we want to call my_image_display procedure then we have to be public it and also have to create synonym for it:

connect sys as sysdba

--grant all users the ability to execute this procedure
GRANT EXECUTE ON #user#.my_image_display TO PUBLIC;

--create synonym
CREATE PUBLIC SYNONYM my_image_display FOR #user#.my_image_display; 

4) IN APEX:
When you want to display blob data as picture on apex page, you can do it on two way:
a) create new region on a page (Type: PL/SQL):

begin
for rec in (select *
            from photos
             )
loop
    
  IF   rec.mime_type is not null or rec.mime_type != '' THEN 
  htp.p( '<img src="my_image_display?p_image_id='||NVL(rec.photo_id,0)||'" height="'||100||'"/>' );
 else
   htp.p( 'No Image ');
 END IF;
htp.p( '
 ');
end loop;
end;

b)or create new report on an apex page:

select photo_id, mime_type, '<img src="#OWNER#.my_image_display?p_image_id='|| NVL (photo_id, 0)|| '" height="'|| 100|| '" />' photo
from photos

5) how to upload images via apex
when you want to upload images into table, you can do the following:
A) create following items:
P10_MESSAGE (Type: display as text)
P10_PHOTO (Type: File Browse)
a button

B) Create process for button:

BEGIN

:P10_MESSAGE := 
 save_data.upload_images (  in_name       =>:P10_PHOTO);


END;

C) create package:

create or replace
PACKAGE SAVE_DATA AS
FUNCTION upload_images (  in_name       IN VARCHAR2) RETURN VARCHAR2;
END SAVE_DATA;
/

create or replace
PACKAGE BODY SAVE_DATA AS
FUNCTION upload_images (  in_name       IN VARCHAR2) RETURN VARCHAR2
IS
p_image_id VARCHAR2(256);
image_size number;
image_type varchar2(256);
out_message VARCHAR2(4000);
v_blob     blob;
BEGIN
 
   -- We want to keep the image in our own table.
   -- Copy the image from WWV_FLOW_FILES to our own PHOTOS table.
   --

   -- check file size
   select doc_size into image_size from   wwv_flow_files  where  name = in_name;
   select mime_type into image_type from   wwv_flow_files  where  name = in_name;
   if image_size <= 204800 AND upper(image_type) = upper('image/jpeg') then
    

         select blob_content into v_blob from wwv_flow_files  where  name = in_name;
   
                INSERT INTO photos (photo_blob, mime_type)
                 VALUES  (v_blob,image_type);             

   else
                out_message := 'Note: Max image size is 200k.<br />
            And only .jpg files are allowed<br />
            <a href="http://www.shrinkpictures.com" target="_blank">Resize your images here </a>';
   end if;
   
   -- delete it from the WWV_FLOW_FILES table.  That way we can keep
   delete from wwv_flow_files where name = in_name;
   commit;
                     
RETURN out_message;

  EXCEPTION
    WHEN OTHERS THEN
RETURN 'Error';
END upload_images;


END SAVE_DATA;

List of the keyboard shortcuts

General keyboard shortcuts

* CTRL+C (Copy)
* CTRL+X (Cut)
* CTRL+V (Paste)
* CTRL+Z (Undo)
* DELETE (Delete)
* SHIFT+DELETE (Delete the selected item permanently without placing the item in the Recycle Bin)
* CTRL while dragging an item (Copy the selected item)
* CTRL+SHIFT while dragging an item (Create a shortcut to the selected item)
* F2 key (Rename the selected item)
* CTRL+RIGHT ARROW (Move the insertion point to the beginning of the next word)
* CTRL+LEFT ARROW (Move the insertion point to the beginning of the previous word)
* CTRL+DOWN ARROW (Move the insertion point to the beginning of the next paragraph)
* CTRL+UP ARROW (Move the insertion point to the beginning of the previous paragraph)
* CTRL+SHIFT with any of the arrow keys (Highlight a block of text)
* SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document)
* CTRL+A (Select all)
* F3 key (Search for a file or a folder)
* ALT+ENTER (View the properties for the selected item)
* ALT+F4 (Close the active item, or quit the active program)
* ALT+ENTER (Display the properties of the selected object)
* ALT+SPACEBAR (Open the shortcut menu for the active window)
* CTRL+F4 (Close the active document in programs that enable you to have multiple documents open simultaneously)
* ALT+TAB (Switch between the open items)
* ALT+ESC (Cycle through items in the order that they had been opened)
* F6 key (Cycle through the screen elements in a window or on the desktop)
* F4 key (Display the Address bar list in My Computer or Windows Explorer)
* SHIFT+F10 (Display the shortcut menu for the selected item)
* ALT+SPACEBAR (Display the System menu for the active window)
* CTRL+ESC (Display the Start menu)
* ALT+Underlined letter in a menu name (Display the corresponding menu)
* Underlined letter in a command name on an open menu (Perform the corresponding command)
* F10 key (Activate the menu bar in the active program)
* RIGHT ARROW (Open the next menu to the right, or open a submenu)
* LEFT ARROW (Open the next menu to the left, or close a submenu)
* F5 key (Update the active window)
* BACKSPACE (View the folder one level up in My Computer or Windows Explorer)
* ESC (Cancel the current task)
* SHIFT when you insert a CD-ROM into the CD-ROM drive (Prevent the CD-ROM from automatically playing)
* CTRL+SHIFT+ESC (Open Task Manager)

Back to the top
Dialog box keyboard shortcuts
If you press SHIFT+F8 in extended selection list boxes, you enable extended selection mode. In this mode, you can use an arrow key to move a cursor without changing the selection. You can press CTRL+SPACEBAR or SHIFT+SPACEBAR to adjust the selection. To cancel extended selection mode, press SHIFT+F8 again. Extended selection mode cancels itself when you move the focus to another control.

* CTRL+TAB (Move forward through the tabs)
* CTRL+SHIFT+TAB (Move backward through the tabs)
* TAB (Move forward through the options)
* SHIFT+TAB (Move backward through the options)
* ALT+Underlined letter (Perform the corresponding command or select the corresponding option)
* ENTER (Perform the command for the active option or button)
* SPACEBAR (Select or clear the check box if the active option is a check box)
* Arrow keys (Select a button if the active option is a group of option buttons)
* F1 key (Display Help)
* F4 key (Display the items in the active list)
* BACKSPACE (Open a folder one level up if a folder is selected in the Save As or Open dialog box)

Back to the top
Microsoft natural keyboard shortcuts

* Windows Logo (Display or hide the Start menu)
* Windows Logo+BREAK (Display the System Properties dialog box)
* Windows Logo+D (Display the desktop)
* Windows Logo+M (Minimize all of the windows)
* Windows Logo+SHIFT+M (Restore the minimized windows)
* Windows Logo+E (Open My Computer)
* Windows Logo+F (Search for a file or a folder)
* CTRL+Windows Logo+F (Search for computers)
* Windows Logo+F1 (Display Windows Help)
* Windows Logo+ L (Lock the keyboard)
* Windows Logo+R (Open the Run dialog box)
* Windows Logo+U (Open Utility Manager)

Back to the top
Accessibility keyboard shortcuts

* Right SHIFT for eight seconds (Switch FilterKeys either on or off)
* Left ALT+left SHIFT+PRINT SCREEN (Switch High Contrast either on or off)
* Left ALT+left SHIFT+NUM LOCK (Switch the MouseKeys either on or off)
* SHIFT five times (Switch the StickyKeys either on or off)
* NUM LOCK for five seconds (Switch the ToggleKeys either on or off)
* Windows Logo +U (Open Utility Manager)

Back to the top
Windows Explorer keyboard shortcuts

* END (Display the bottom of the active window)
* HOME (Display the top of the active window)
* NUM LOCK+Asterisk sign (*) (Display all of the subfolders that are under the selected folder)
* NUM LOCK+Plus sign (+) (Display the contents of the selected folder)
* NUM LOCK+Minus sign (-) (Collapse the selected folder)
* LEFT ARROW (Collapse the current selection if it is expanded, or select the parent folder)
* RIGHT ARROW (Display the current selection if it is collapsed, or select the first subfolder)

Back to the top
Shortcut keys for Character Map
After you double-click a character on the grid of characters, you can move through the grid by using the keyboard shortcuts:

* RIGHT ARROW (Move to the right or to the beginning of the next line)
* LEFT ARROW (Move to the left or to the end of the previous line)
* UP ARROW (Move up one row)
* DOWN ARROW (Move down one row)
* PAGE UP (Move up one screen at a time)
* PAGE DOWN (Move down one screen at a time)
* HOME (Move to the beginning of the line)
* END (Move to the end of the line)
* CTRL+HOME (Move to the first character)
* CTRL+END (Move to the last character)
* SPACEBAR (Switch between Enlarged and Normal mode when a character is selected)

Back to the top
Microsoft Management Console (MMC) main window keyboard shortcuts

* CTRL+O (Open a saved console)
* CTRL+N (Open a new console)
* CTRL+S (Save the open console)
* CTRL+M (Add or remove a console item)
* CTRL+W (Open a new window)
* F5 key (Update the content of all console windows)
* ALT+SPACEBAR (Display the MMC window menu)
* ALT+F4 (Close the console)
* ALT+A (Display the Action menu)
* ALT+V (Display the View menu)
* ALT+F (Display the File menu)
* ALT+O (Display the Favorites menu)

Back to the top
MMC console window keyboard shortcuts

* CTRL+P (Print the current page or active pane)
* ALT+Minus sign (-) (Display the window menu for the active console window)
* SHIFT+F10 (Display the Action shortcut menu for the selected item)
* F1 key (Open the Help topic, if any, for the selected item)
* F5 key (Update the content of all console windows)
* CTRL+F10 (Maximize the active console window)
* CTRL+F5 (Restore the active console window)
* ALT+ENTER (Display the Properties dialog box, if any, for the selected item)
* F2 key (Rename the selected item)
* CTRL+F4 (Close the active console window. When a console has only one console window, this shortcut closes the console)

Back to the top
Remote desktop connection navigation

* CTRL+ALT+END (Open the Microsoft Windows NT Security dialog box)
* ALT+PAGE UP (Switch between programs from left to right)
* ALT+PAGE DOWN (Switch between programs from right to left)
* ALT+INSERT (Cycle through the programs in most recently used order)
* ALT+HOME (Display the Start menu)
* CTRL+ALT+BREAK (Switch the client computer between a window and a full screen)
* ALT+DELETE (Display the Windows menu)
* CTRL+ALT+Minus sign (-) (Place a snapshot of the entire client window area on the Terminal server clipboard and provide the same functionality as pressing ALT+PRINT SCREEN on a local computer.)
* CTRL+ALT+Plus sign (+) (Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer.)

Back to the top
Microsoft Internet Explorer navigation

* CTRL+B (Open the Organize Favorites dialog box)
* CTRL+E (Open the Search bar)
* CTRL+F (Start the Find utility)
* CTRL+H (Open the History bar)
* CTRL+I (Open the Favorites bar)
* CTRL+L (Open the Open dialog box)
* CTRL+N (Start another instance of the browser with the same Web address)
* CTRL+O (Open the Open dialog box, the same as CTRL+L)
* CTRL+P (Open the Print dialog box)
* CTRL+R (Update the current Web page)
* CTRL+W (Close the current window)

http://support.microsoft.com/default.aspx?scid=kb;en-us;301583