Posts tonen met het label Apex. Alle posts tonen
Posts tonen met het label Apex. Alle posts tonen

maandag 16 juli 2018

APEX 5.0 Cascading reports

Business case

You want to create a page with two or more reports that are connected with each other.  A kind of master-detail for reports.
In this example we show the list of departments and as a child report we show the employees of that department.

Solution

We start with building a blank page for our reports.  Add the different report regions on the page.  Whether you are using interactive reports or classic ones, it doesn't matter.  The solution works for both.


Once the reports are present and working independently, we can start making them connected.
The first report is called master and the second one is called the detail report.

Follow these steps to connect the reports:

  1. Create the necessary page items to hold the information to be passed on to the detail report.  In my case this is only the primary key.  So I create a page item called P7_DEPT_PK_ID.  These page items can be set to hidden, since they have no added value for the end-user.  But for testing purposes, we lease them visible so we can see what is being passed on.  You can make them hidden, once everything is working correctly.
  2. Now we need some javascript-code to pass the values of the current selected master record to these page items.  The easiest way is by using a link column on the master report.  As target for this link-column, we use an URL with the following destination:
    javascript:$s('P7_DEPT_PK_ID','#DEPARTMENT_ID#');
    You can see here that we are using a method called "$s", which can be used to put values in items.  As parameters we give the name of the destination parameter and the value, which in this case is the DEPARTMENT_ID from the currently selected master record.

    Some nice explanation on these javascript functions can be found in this blog: https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6341/index-en.html.
  3. Once the value of the PK is now in this page item, we still need to refresh the detail report.  This can be done through the use of a dynamic action.  In this dynamic action we indicate that whenever the page item P7_DEPT_PK_ID is changed, we need to refresh the detail report.
  4. The only remaining thing to do is to use the page item in the query of the detail report.
  5. Because we are using javascript to fill in the page item, we need to submit the page item to the server, when we are refreshing the detail report.  This is done through the property 'Page Items to Submit' on the detail report.
  6. Now you can run your solution




vrijdag 6 juli 2018

APEX 5.0 Example use of collections

This is my first attempt in using collections within Oracle APEX.
I just save this code for later use.

I used APEX 5.0 on the Oracle cloud.

I created 2 pages:

  • Overview Page
  • Edit Page
In the Overview page I load through a select-statement the data into a collection during the Pre-Rendering > Before Header > Processes > PL/SQL action, with the following code:
Declare
l_query varchar2(300) := 'select department_id, department_name, manager_id from departments';
l_coll_name varchar2(50) := 'DEPARTMENTS';
Begin
 APEX_DEBUG.ENABLE();
 APEX_DEBUG.INFO('Start fetchData');
    if not APEX_COLLECTION.COLLECTION_EXISTS(l_coll_name)
    then
    APEX_DEBUG.INFO('Collection ' ||l_coll_name||' does not exist, creating a new one');
      -- Create the collection from the query 
      APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B (
        p_collection_name => l_coll_name, 
        p_query => l_query);
    end if;
 APEX_DEBUG.INFO('End fetchData');
End;

I'm creating the collection only the first time that I'm entering the page.  So when changes are made on the second page, these changes are immediately shown on this page on returning.

To show the data in a report, you can use a classic report with a query which looks like this one:
select 
    c001,
    c002,
    c003,
    c004,
    c005
  from apex_collections
  where collection_name = 'DEPARTMENTS'
;

In this simple example, I just copy the values from the collections to the editable fields on the Edit Page through the link-functionality.
When showing the Edit page, I add a save button, which on submitting the page saves the changes in the collection and the db if needed.  These operations are performed in the Processing > Processes > PL/SQL code with the following code:

declare
  cursor c_get_seq
  is
  SELECT seq_id
   FROM APEX_collections
  WHERE collection_name = 'DEPARTMENTS'
    AND c001 = :P6_DEPTID
  ;
  r_seq c_get_seq%rowtype;
begin
   open c_get_seq;
   fetch c_get_seq into r_seq;
   if c_get_seq%found
   then
   APEX_DEBUG.INFO('Collection found, updating);
      APEX_COLLECTION.UPDATE_MEMBER (
        p_collection_name => 'DEPARTMENTS',
        p_seq => r_seq.seq_id,
        p_c001 => :P6_DEPTID,
        p_c002 => :P6_DEPTNAME,
        p_c003 => :P6_MANAGER);
        commit;
     -- update departments set department_name = :P6_DEPTNAME, manager_id = :P6_MANAGER where department_id = :P6_DEPTID;
   else
     APEX_DEBUG.ERROR('Collection not found!!');
   end if;
   close c_get_seq;
end;

After clicking on the save-button, the collection gets updated and the user returned to the previous page.

dinsdag 15 april 2014

ContributeGroep Sessions for OOW14 & JavaOne14

In the spirit of sharing the wonderful things our consultants do at customers, we entered a few of their efforts as proposals for Oracle Open World and JavaOne.

Here is the list of our entries:


ID Proposed Session Title Type Event
CON4630 Integration of Neo4j with Webcenter Content Conference Session OOW
Had enough of the linear searching into your documents?  Needing a dynamic search path in runtime?  Welcome to the world of graph databases.  Using a graph database to perform searches, allows you to add functionality from a totally different order.  Think about searches on relationships between documents or show recommendations of other users into your information model, just to name a few.  Basically, we add relationships between metadata, content, persons, applications, even if they didn’t exist before.  Graph databases allow for a dynamic search structure in runtime, providing more flexibility and agility to your portal.
CON3759 Securing web services - How to make a complex task look easy? Conference Session JavaOne
Not many developers like to implement or expose secured web services.  This is probably because securing web services is a complex matter.  Next to the complexity, the client/server architecture comes into play.  Both sides, client and server, will have their own implementation.  In an ideal world your backend code will not be impacted with the technologies in which it is build, nor which type of security you would choose. Since we are not living in a perfect world, those differences do have a big impact on your approach in bridging those 2 worlds. During this session we will explain how an enterprise service bus could help you out.  Not only in solving this predicament, also in adding clarity in your separation of concerns design.
CON3690 Single-click deployment in Application Express Conference Session OOW
Software development with Oracle Application Express is by no means affiliated with enterprise web development. Oracle itself positions APEX as a so-called rapid application development tool, but recommends other products as soon as projects get larger and more complex. I honestly believe, that APEX has the potential to cope with these large-scale projects. Needless to say, you need more than just plain APEX...

We have managed to incorporate a set of powerful tools in our day-to-day development process. These tools in combination with APEX allow teams to build high-quality database applications in a professional way. It takes no more than a single click on a button to fully deploy your application(s) to a target environment.
CON4662 Taskflows on Sale Conference Session OOW
Get ready for an alternative way of navigating between taskflows within ADF.  Instead of providing only the valid navigation route, we allow for a more flexible system avoiding dependency problems. This opens the way for a free form navigation keeping into account security and his history.  One of the possible implementations of this solution is the creation of a menu that is based on beans or configuration files. Introducing history to this system can overcome the use of the browser back button.
The pattern is already in use at a Belgian customer in providing web(ADF)-based solutions. This customer will be present to explain the business value and agility this brings to their application.