Donnerstag, 9. August 2018

APEX Show PDF in Region that stored in the Database

Hello, everybody, 

a colleague had the task of displaying a PDF from a customer which is available in the database with a click as a preview into a region.
Without additional cost of plugins or tools much it relatively difficult.
After my work I thought about how to solve this problem.

After about 2 hours reading various posts and documentations I came to a simple solution.

I want to share it with you and show you how easy it is.


 INFO

!!!! Rendering a PDF in a region works best in google Chrome, Firefox offers a link!

As in all my other blogs I will give step by step instructions.


 Download

  1. THDEVELOP_UTIL_PKG 
  2. PDFObjects 

1. Integrate downloaded files into the application.

  1. The package thdeveloputilpkg can be imported via SQL Workshop > SQL Commander.
  2. Integrate the JS file pdfobject.js as Static Applicationfile Shared Components > Staticfilies
    1. Add it on your Page where your want to show your PDF in a Region 

2. Create a separate page (Normal) for the preview display.

  1. Everyone should be familiar with creating a page. This page contains a region with a hidden item that receives the ID of the PDF.
  2. Erstellen eines Prozess Before Header.
    • here you best use the package from me, you can download it under thdevelop_util_pkg.
    • In the package is a procedure get_preview, which is needed to pass the PDF (blob file) from the database to the application.

    1
    2
    3
    4
    5
    6
    get_preview(pi_pdf_id_column in varchar2,
                pi_filename_column in varchar2,
                pi_bolb_column in varchar2,
                pi_tablename in varchar2,
                pi_pdf_id in number
             );
    
    The parameters should be self-explanatory.

3. Go to the page where the PDF should be displayed in a region.

  1. Create a new region from the
    • Type: Static Content
    • Add a Static ID.
    • You can define the size of the region under Custom Attributes e.g. STYLE="height:1000px"
    • Recap
      Type: Static Content
      Static ID: e.g. pdf_region
      Custom Attributes: STYLE="height:1000px"
  2. Embedding magic.
    Here there are 2 ways, the difference is that the PDF is to be displayed via Select List and the other via a report column.
    • Display via select list
      • Add a hidden item that receives a URL. e.g. P2PREVIEWURL
      • On the select list ,this must have the ID of the PDF in the database as return value, you create a Dynamic Action (on Change)
      1. TURE
        • Action: SetValue
          Set Type: PL/SQL Function Body
          PL/SQL Function Body
        • 1
          2
          3
          4
          5
          return APEX_PAGE.GET_URL (
            p_page => 3, -- Page step 2 
            p_items => 'P3_REPORTID', -- Hidden item step 2.1 
            p_values => :P2_BLOB 
          ); -- select list where you make your dynamic action
          
        •  Affected Elements 
        •     Selection Type: Item(s)
              Item(s): Your hidden item to hold the URL e.g. P2PREVIEWURL
      2. TRUE

        • Action: Execute JavaScriptCode
          Code:
          1
          2
          var src = $v("P2_PREVIEW_URL"); // get the value of the item (the pre URL) 
          PDFObject.embed(src, "#pdf_region"); // Render the pdf in the region 
    • Display per Report Column
      • Creating a Dynamci Action
            Name: ShowPDF

            When
                Event: Custom
                CustomEvent: ShowPDF
                Selection Type: JavaScript Expression
                JavaScript Expression: document

            TURE Action
                Action: Execute JavaScript
                Code Code:
        1
        PDFObject.embed(this.data.url, "#pdf_region");
        

    • Create Region if there is no report yet, you have to create it
      • The SQL statement of the report should look something like the example code.


1
2
3
4
5
6
select PDF_REGION.FILENAME as FILENAME ,
       PDF_REGION.PK_ID as PK_ID    ,
       APEX_PAGE.GET_URL ( p_page   => 3,
                           p_items  => 'P3_REPORTID',
                           p_values => PDF_REGION.PK_ID ) as URL
from PDF_REGION PDF_REGION

The required link is already generated in the query. After the report has been created, select a column you want to use to display the PDF in the region created for it with one click.


  • Type: Link
  • Target Type: URL
  • URL :                                   

1
 javascript:apex.event.trigger(document, 'ShowPDF', [{url:'#URL#'}]);void(0);

This Method i had Read by a Block Post of Tobias Arnorld

Explanation Javascript Code in URL


1
 javascript:apex.event.trigger(document, 'ShowPDF', [{url:'#URL#'}]);void(0);
For explanation I will describe the function in such a way that the required parameters should explain the explanation.


1
javascript:apex.event.trigger(document, 'HereYourDynamicActionFunction', [{url:'#HereYourColumnThatContainsTheLink#'}]);void(0);


This is how easy it is to display a PDF in a region.

Thanks and have fun doing it.

Update:With so many requests that I could release the application. I provide the Dwonload link here

Sonntag, 17. Dezember 2017

APEX Magic Dynamic Links on Buttons

Hello, everyone,
today I want to show you how to create dynamic links for buttons.

Benefits?
In a project I needed a dynamic link on a button. I wanted to transport a selected value from a Selected List to a Modal Page.
Once you understand the rendering of Oracle APEX, the solution is simple.
A button and link is rendered when loading a page. In my case the link had to be adjusted with the new value from the selected list.
If you don't do this, the value that the item had at the last submit is always transmitted.
I had to avoid this and came up with this simple solution.


What is needed?
some JQuery
Additional Item (This holds the new link)

I'll show you two different methods.
One is for Modal Dialog Pages and the other for Normal.
Steps 1 to 4 are the same for both methods

I assume here that you have created a page which is a modal dialog to referend and a button that is define by dynamic action.


1. Creates a hidden item (e. g. PX_LINK) on page were you want to use the dynamic link.




2. Set Static ID on button (e.q. modal_btn)

3. Create a DA on the item to trigger the creation of the dynamic link.

in my case it was a Selected List item.



4. Create in true a Set Value action.


Code:


return APEX_PAGE.GET_URL (
            p_page   => 2,
            p_clear_cache => '2',
            p_items  => 'P2_DEPTNO',
            p_values => :P1_SL_DEPT ); 
Here I use an api call to generate the link.
In my case I have an item on the page which should be set.


Here it is important to set Escape Special Characters to no, otherwise the HMTL codes will be output instead of the characters we need.
Now comes the magic behind Dynmamic link
5. Create a 2nd True action (Execute JavaScript Code) This ist for Modal Dialog

Code:

var link = $('#P1_LINK').val();
$("#modal_btn").attr("onclick", link);

With this piece of code the link of the button is changed.

5. For Normal Page
Code:

$("#link_btn").attr("onclick","apex.navigation.redirect('"+ $('#P1_LINK').val() + "')");

The difference between modal dialog link is,
that apex. navigation. redirect is not generated in the Api call for Normal Pages.

In the demo I have 2 display only items to showing you the generated link.

Thank you for reading

Freitag, 17. November 2017

APEX Rendering Button in Report Column part 2

Hello everyone,

after a long time i managed to write the 2nd part of APEX rendering button in report Column.

History:
A long time ago I read an article from Scott Wesley about how buttons can be rendered and used in reports. After now 2 years in Oracle APEX technology, I would like to introduce you to another approach, how it is still to be realized.

Here you will get a step-by-step instruction, how to implement and use the buttons in a report.

For those who would like to participate I provide the BasicFiles.
(
It contains a demo Table, thdevelop_util_pkg and Basic Application).

For all the others you just have to download the package only.

Presteps:

  1.    Download BasicFiles
  2.    Execute Table.sql 
  3.    Execute thdevelop_util_pkg
  4. Import Application BlogBasicApplication.sql


Presteps Finished.





Button in Report
Let's start by displaying the button in the report.

1. Open the SQL code from the report region with the built in Code Edtior. 




2. Add after Salary the following in the SQL statement.


1
2
3
4
5
6
7
8
9
thdevelop_util_pkg.apex_item_button(p_item_label     => 'SET',
                                    p_item_class     => 'SET'
                                    ) ||
thdevelop_util_pkg.apex_item_button(p_item_label     => 'SAVE',
                                    p_item_class     => 'SAVE'
                                   ) ||
thdevelop_util_pkg.apex_item_button(p_item_label     => 'RESET',
                                    p_item_class     => 'RESET'
                                    ) buttons

For all those who didn't know, in APEX you can render page items in an SQL query. See the Application Express API Reference for more information.

3. Close the Editior.


4. Select the new column Buttons and change in properties  -> Security -> Escape Special Characters -> NO



Escape Special Characters no ensures that the returned html text is not displayed as text but as HTML. This ensures that e. g. buttons can be rendered.

5. Save Page and show the Preview. (It should look like this)



The buttons are now included in the report.

To get a better understanding of working with buttons in reports I added a text item here. This will be filled later when you click on the set button.

Textfield in Report

1. Open the SQL code from the report region again. (like in step 1)

2. Add the following to buttons in the SQL statement.(in my case, I included it as the first element of the column)

1
2
3
4
5
6
7
8
APEX_ITEM.TEXT(p_idx         => 50,
               p_value       => salary,
               --p_size        => ,
               --p_maxlength   => ,
               p_attributes  => 'data-id=' || q'["]' || empid || q'["]'
               --p_item_id     => ,
               --p_item_label  => 
               ) ||

Your code should look like this

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
select EMPLOYEES.EMPID as EMPID,
       EMPLOYEES.NAME as NAME,
       EMPLOYEES.SALARY as SALARY,
       APEX_ITEM.TEXT(p_idx         => 50,
                         p_value       => salary,
                         --p_size        => ,
                         --p_maxlength   => ,
                         p_attributes  => 'data-id=' || q'["]' || empid || q'["]'
                         --p_item_id     => ,
                         --p_item_label  => 
                        ) ||
       thdevelop_util_pkg.apex_item_button(p_item_label     => 'SET',
                                           p_item_class     => 'SET'
                                           ) ||
       thdevelop_util_pkg.apex_item_button(p_item_label     => 'SAVE',
                                           p_item_class     => 'SAVE'
                                           ) ||
       thdevelop_util_pkg.apex_item_button(p_item_label     => 'RESET',
                                           p_item_class     => 'RESET'
                                         ) buttons
 from EMPLOYEES EMPLOYEES

p_attributes is set so that I get to the PK of the row for the later update procedure. More about this later.

If you look at the preview now, the set button is very close to the texfield.

To fix this we use css.
Click on page -> properties -> CSS -> inline and enter the following


1
2
3
.SET{
    margin-left: 10px;
}

Now everything is ready to embed the logic.


Enliven buttons with action

1. SET Button
  1.1 Create Dynamic Action  (Click) with the following settings

The JQuery selector is used here because we gave the button a class name and we only want to trigger the event when one of the set buttons is clicked.

Eventscope Dynamic, because more than just one button is controlled with the class SET and it should always be the triggered element to execute the function. Not only the one I clicked on.

As you can see in the true branch Execute JavaScript Code is selected

1.1.2 Execute JavaScript Code 
Click on it and enter the Code
1
2
var set_value = $('#P1_TO_SET_VALUE').val();
$(this.triggeringElement).prev('input').val(set_value)

Line 1 give you the value from the top page item and save it in set_value.
(In figure 1.1 you can see another DA which has not been described.
This is used to save the value entered in the top item with the submit button in the session.)
Line 2 now the texfield which is before the set button is filled with the value of set_value. 

Test it!
If you have done everything right, the SET button works now.


2. SAVE Button
  2.1 Create Dynamic Action  (Click) with the following settings

   2.1.2 Execute JavaScript Code 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
var trigger_element = $(this.triggeringElement).prev().prev()
var empid = apex.jQuery(trigger_element).data('id');
var set_value = apex.jQuery(trigger_element).val();


apex.server.process ( 'save_value', 
  {
    x01:empid,
    x02:set_value
  },
  {
    success: function( pData )
    {        
//      if (pData.status == 'FAIL') { console.log('Error'); };
      apex.event.trigger("#REPORT-WITH-BUTTON", "apexrefresh");
    },
/*    error: function() */

  dataType: 'text'
  }
              );

Line 1: The input item what is before SET. (Textfield)
Line 2: PK for insert that i have set in prev step (Textfield in Report point 2)
Line 3: Value to be set. (previously filled with SET button)
Line 6: Run an APEX process from Javascript. (in this case save_value, we will create this process later)
Line 8: x01 is a collection variable wich  can use later in PL/SQL code. (this holds the value of the pk)
Line 9: x02 an other collection variable, this holds the value to be set
Line 12: Process successful 
Line 15: The specified region should be refeshed after the process has been successful.

2.2 APEX Process
  Create a AJAX Callback Process.(this is the Process that will be execute from predefined Dynamic Action)

  PL/SQL Code

1
2
3
update employees
set salary = apex_application.g_x02
where empid = apex_application.g_x01;

Line 2 and Line 3: the collection variables calls.

Test the Save Button!

3. RESET Button
  3.1 Create Dynamic Action  (Click) with the following settings

  3.1.2 Execute JavaScript Code 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
var trigger_element = $(this.triggeringElement).prev().prev().prev('input').val(0)
var empid = apex.jQuery(trigger_element).data('id');
var set_value = apex.jQuery(trigger_element).val();

apex.server.process ( 'save_value', 
  {
    x01:empid,
    x02:set_value
  },
  {
    success: function( pData )
    {        
//      if (pData.status == 'FAIL') { console.log('Error'); };
      apex.event.trigger("#REPORT-WITH-BUTTON", "apexrefresh");
    },
/*    error: function() */

  dataType: 'text'
  }
              );

This button has the same structure as the reset button. The only differences are in JavaScript. As you can see the prev calls for the textfield have gone up.

Final Test

We're finished and I hope you guys enjoyed it.
(Finished example)

Thank you for reading and having fun

Donnerstag, 16. Februar 2017

APEX Rendering Button in Report Column


Hi everybody,
I would like to show you how you can render a button in a report. For what? For a project that Im work on, a customer wants to assign a quantity or a difference of a quantity to something with a simple click on a button. APEX 5.0.3 and a Tabform is used here.
(you can use it with Classic and Interaktive Report too).
In the APEX API Reference, there is no function under APEX_ITEMS that has a button rendering. For this I wrote a function in Oracle APEX style. With that i can render this button in a simple SQL statement. With the setting (Escape Special Character on no) you get the button in a table column.

         QTY           ASSIGNT_QTY                  SUPPLIER 1                   SUPPLIER 2

This was made by @commi235 and @Tim_Halbach


Pretty cool?


How it works.

Only you need my package with the funktion apex_item_button
(Download here on Github).

In the next part i will show you how you get it like the gif.



Mittwoch, 18. Januar 2017

APEX 5.1 FadeOut Plugin

Hi together,
for a simple implementation to Fade out the Success message i wrote my first plugin.
The Download is available

Apex 5.1 FadeOut Success Message

or

https://github.com/THDevelop/APEX-5.1-FadeOut-Success-MSG

Installation:

Import the Plugin

Create a Dynamic action on Page load
go to the true block and choce by action APEX Fade Out
after that you can set your display Time in ms.

UPDATE:
I have got an info from Shakeep there is a function in the API Oracle Apex Api FadeOut to fade the succsess message.
I Would say this Plugin is for all they are not familia with jequery and want a simple quick implementation.


Dienstag, 17. Januar 2017

ORACLE APEX 5.1 Fade Success Message automaticly




APEX 5.1 Fade success message


We have all wait a long time of APEX 5.1.
One oft he most important feature of 5.1 is Interactive grid.

Were i was playing with 5.1 i see that the success message dont fade out like in 5.0.
For 5.0 i found a solution and i try the same one for 5.1, but it doesnt work.

So i inspect the HTML code and found the change with the APEX_SUCCESS_MESSAGE.

I search the div container to see the success message but i can find it.

Were he is?

After i start an save process i saw what i want the little green box in the upper right corner





I inspect the HTML again and saw that there is the div with the id t_Alert_Success.
Okay the div is didnt render if i come on the page the first time.
 
I need a function to check permanently the changes of APEX_SUCCESS_MESSAGE.
Because, APEX add classes to APEX_SUCCESS_MESSAGE.
So i found MutationObserver and it was what i need. 
I Implement it on a global page to have the outofade on all pages.

1. Create a Dynamic Action on Global page
  - Event: Page Load






2. True
  -Action: Execute JavaScript Code




 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
// The node to be monitored
var target = $( "#APEX_SUCCESS_MESSAGE" )[0];

// Configuration of the observer:
var config = { 
  attributes: true, 
  childList: true, 
  characterData: true 
};

// Create an observer instance
var observer = new MutationObserver(function( mutations ) {
  mutations.forEach(function( mutation ) {
    var newNodes = mutation.addedNodes; // DOM NodeList
    if( newNodes !== null ) { // If there are new nodes added
      var $nodes = $( newNodes ); // jQuery set
      $nodes.each(function() {
        var $node = $( this );
        if(  $('#APEX_SUCCESS_MESSAGE').hasClass( "u-visible" ) ) {
          // do something
            $('#t_Alert_Success').ready(function() { 
                setTimeout(function() { 
                   $('#t_Alert_Success .t-Button--closeAlert').click();
                     }, 3000); // here u can change the view time
            });
        }
      });
    }
  });    
});
 
// Pass in the target node, as well as the observer options
observer.observe(target, config);



Thats it!!

ORACLE APEX 5.0.x FadeOut Sucess Message automaticly

APEX 5.0.x Simple Solution to fade out success message



Some know the Problem with the success message in APEX they dont want fadeout automaticly. I have found a simple soulution to made it.




1. You have the desition if you want it for all Pages or for a single page.
    Set it on Global Page (0) for all pages.
    Set it on the page were you ant to fadeout the message
    The steps are the same.

2. Create a dynamic action
 - Event:  Custom
 - Selection type: Javascript Expression
 - JavaScript Expression:


1
   $("#t_Alert_Success").length > 0


3. True Block
- Action: Execute JavaScript Code


1
2
3
  $( document ).ready(function() {
      $( "#t_Alert_Success" ).fadeIn( 300 ).delay( 1500 ).fadeOut( 400 );
  });

- Selection Type: jQuery Selector
- jQuery Selector: #t_Alert_Success
- Fire On Page Load: YES