Category: APEX

January 3rd, 2018 by mathias

To continue on the theme of help text in APEX, lets take a look at showing it in a modal popup dialog in this post and then finish off the subject in next post with cusomizing how help can be shown.

Screen Shot 2017-12-30 at 22.16.47

For completeness I’ll include a short section towards the end about how it works in non modal too.

In the last post I show how to show help text inline on a page and on the one before that how to set up a specific page to be the landing page for showing help text for any page in the application. In this post I’ll show how to get help text displayed in a modal dialog.

For this blog I used version 5.1.4.00.08 of APEX on apex.oracle.com.

As I’ve stated before, my preferred way to deal with help text in APEX applications is to do it with inline help as the last blog post discussed. But sometimes a dedicated page is preferable and sometimes a modal popup dialog is better. I think those cases might be preferred in cases where the help includes a lot of text and sdeeing the actual page at the same time is not needed. For example if the text more explains a concept than how to fill out the fields in the page.

We will set up another page with just a help region. We could make a copy of the page we created in the first post in this series, but to make this post more self contained we’ll create the page from scratch.

Creating the modal help page

Create a new blank page (I’ll use 4 in this example). Chose modal page type.

  • Add a “Help Text” region to the content body.
  • Name = Help
  •  If you did not get the page created as modal, go to the page attributes.
    • Find The Appearence group.
    • Page type = Modal Dialog

This is identical to the page 2 created in the post about help on a dedicated page with just the page type changed to be modal.

Create a button to show the modal help

We’ll first create a button just to show the machanics just because it is the easiest way to test it. After that we’ll finish off the access to help with a navigation bar entry, just as we did in the previous two posts.

Open the page from which you want to open the help. If you’ve followed along on the previous posts, it would be page 3. Any page for which yiou have some help text defined on the page attributes will do. That is needed just so we can see it work.

  • Add a text button to any region you have on the page.
  • Set the page to redirect to a page in this application
  • Set the page to be page 4
  • Open the advanced group in the popup
  • Request = &APP_PAGE_ID.

Test the page and see that when you click the button you get a modal popup page with the helptext for the page you button is on.

With this working we know the mechanics of this works, now we just need a navigation bar set up for it so it becomes available across the whole application.

Create a navigation bar entry

As before, we’ll finish off this version too with a navigation bar entry. It is by far the most natural way to provide a natural way for a user to request help on a page and for it to be available verywhere in an application.

Since the difference against the navigation bar entry for the dediczated page is largely that the page that is linked is defined as a normal or modal page, this pretty much a repeat of that.

Go to shared components and click on Navigation Bar List, and then on Desktop Navigation Bar. Click Create Entry and set:

  • Sequence = 50
  • List Entry Label = Modal Help
  • Target Type = Page in this Application
  • Page = Your modal help page (4)
  • Request = &APP_PAGE_ID.

If you run the application now you will see “Modal Help” up in the navigation bar, and clicking it takes has the same exact result as the button. It shows the help in a modal dialog by pulling up page 4 as a popup over the page your requested help from (3).

Now any new page you create for which you write help text will let the user click on the navigation bar and get the help text with no extra effort from you.

What about non modal?

Well, there is of course the option of a non modal page type too. The result of it is a stand alone window you can move around and keep referencing while navigating in the application.

To set it up we’ll just copy the page, button and navigationb bar entries.

Copy the page we created here (4) to a new one (5) and give it the name “Non Modal Help”, accept all other attributes and just click forward and create the page.

In page attributes:

  • Change the page type to  “Non-Modal Dialog”.
  • Set width to 700
  • Set height to 600

Go to your page in the application (3 if you have followed along) and copy the button “Modal”.

  • Name = “NonModal”.
  • Sequence = 80.
  • Target->Page = 5.

Test it to see your button creating a new window with the helptext.

Go to the shared components -> Navigation Bar List -> Desktop Navigation Bar

  • Click copy icon on the right side for “Modal Help”
  • Display Sequence = 60
  • New List Entry Label = Non Modal Help
  • Click “Copy List Entry”
  • Click “Non Modal Help”
  • Set Target->Page = 5

Now you’ve got a non modal help option in the navigation bar too. Try it out.

Watch it live

Take a minute and check out this live in my demo application to see for your self the effect of it before you build it yourself. Log in with demo/demo.

Posted in APEX

December 20th, 2017 by mathias

Setting up help text in APEX is not hard but I often see it not done at all or implemented using regions with static content and then toggled on or off. That is unfortunate when there is declarative support for providing help texts.

In this post I’ll show how to set up a specific page to be the landing page for showing help text for any page in the application. It is the precursor to the next post where I’ll take it a step further and show how to get help text displayed inline with a page the user is on.

For this blog I used version 5.1.4.00.08 of APEX on apex.oracle.com. The following has been the same since at least release 3.2 of Apex while where and how you enter the needed properties may not be identical in previous and future releases. I don’t think it has changed much over the years.

Show help using another page

The following information is to a large extent a click stream version of Oracles official documentation.

The number in the parenthesis are example page numbers just to make sure there is no confusion of what page is referenced. It is the page numbers you’d end up with in a brand new application

Set up two pages

Create a blank page (2) and then add a help text region to the content body of the page. This is the page that will be used to display the halp for any page in the application.

Create another blank page (3) and scroll down to the bottom and fill in help text about the page in the “Help Text” property.

Help text location

Now we have everything needed in place, we just have to add any means of navigation to get the help page (2) to be loaded with the helptext of this page (3). Typically this is done with a link in the navigation bar as we’ll see later, but it is often easier to just try out navigation with a plain button.

Add Navigation

Add a button and label it OtherPage. Set the target to be the page number of the help page you just created (2). Set the request (in 5.1 under the advanced catagory) to “&APP_PAGE_ID.”.

Run the page (3) and click the button. You will be sent to your help page (2) where the helptext you entered for the page (3).

If you add items on your page (3) the help text for those will also be shown on your help page. You will however not want to create a help button on every page in your application. It would both wast real estate on your page as well as time to set it up on every single page.

Navigation bar

To make the help for every page in your application be displayed with no additional work per page other than writing the help text, let’s set up an entry on the navigation bar.

Go to shared components and clock on Navigation Bar List, and then on Desktop Navigation Bar. Click Create Entry and set:

  • Sequence = 20
  • List Entry Label = Help Page
  • Target Type = Page in this Application
  • Page = Your help page (2)
  • Request = &APP_PAGE_ID.

Editing navbar for help page

If you run the application now you will see “Help Page” up in the navigation bar, and clicking it takes has the same exact result as the button. It navigates to he help page (3) and shows the help text for your page (2).

Now any new page you create for which you write help text will let the user clock on the navigation bar and get the help text with no extra effort from you.

Watch it live

I have just set up a demo-app I’ll use to show the effects when I blog about things APEX where it makes sense to have a an app to show the feature. For the above, take a look at it. Log in with demo/demo.

 

Posted in APEX, Uncategorized

June 19th, 2013 by mathias

You know how it is, when you have that feeling. You are on top of your game. You have a few quick brush strokes to add to a system to make it more dynamic. You have all the small needed changes in your head and you know it is just gonna work. You sit down for some quality time with your computer and with the application builder. It’s gonna be fun and you will get to bask in the glow of your success before the day is over. yup, that is the feeling I’m talking about.

Then there is that other feeling, then one we want to avoid. You know the kind. When you have an easy fix to do. It all goes well until it doesn’t. And then, nothing. You turn the know this way, nothing. You turn the know that way, nothing. You turn it up, you turn it down. No matter what. The same freaking result. And it is the wrong result. Time goes by, what was embarrassing after 15 minutes is annoyingly embarrassing after a couple of hours. Yes, that is the feeling I’m referring to.

Worst of all is of course when the first feeling turns into the second feeling. That is an afternoon that is sure to suck, and the more it sucks the more you get annoyed that you cannot see what is sure to be a very obvious mistake.

A day a few weeks ago I had this happen to me for the umpteenth time. Not with the same issue of course, but with one of those ridiculous things that just throws you out of your flow. What was a magical afternoon changed to one where I felt like a complete beginner.

It all started very innocent with me needing to add a table where I could store the location of different places a tab should point to. So I had an application item, I had a process that would populate it on new instances(sessions) and I had referenced that application item in the link target. The target returned was set to be “127.0.0.1:8080”. A target as good as any…

It didn’t show up in the link from the tab. It however was available so it could be displayed on a region on the same page. What the ….

Could it really be that APEX creates the HTML text for the tab before the application processes runs? It just doesn’t make sense, a test of hard coding the value to “abc” in the application process proved that it in fact ran before.

Could it be that a value passed in from the process was treated differently for tabs or application processes? I doubted it, but facing odd issues one tends to consider all kinds of illogical things. But hardcoding “abc” in the process once again showed that it came through to the tab.

What on earth. A friend tested the same page on his Mac and (fortunately?) got the same exact result. So it wasn’t my browser that was acting up.

Now we were really stretching and started looking at the page rendered in Safari and Firefox. What on earth, the link shows up just fine in them?

We’re considering a bug in APEX. I have read about things they have had to do to make IE, Chrome, Firefox, Safari, and Opera work well with APEX applications. Could there be a bug in there with how links with dynamic values were treated? It certainly was possible. If it wasn’t for the fact that some links showed up just fine, the “abc” one looked like a page on the same ip-address as the APEX server when looking at where Chrome wanted to send us. So it really wasn’t reasonable that APEX would have a bug related just to localhost adresses.

After some more coffe it dawned on me, maybe this wasn’t APEX at all. Maybe this was a “feature” of Chrome. After a peek in the source for the rendered page, the text “127.0.0.1:8080” was found in the link attribute for the tab. So APEX renders it like I expected, but Chrome didn’t honor it as a link.

It turns out that if there’s just text, then Chrome will assume it is a relative document located downstream from the DocumentRoot. However, if it is not a path that can be parsed as file system location, then Chrome will not allow the link to be used. What was needed was to put http:// in front of the address. That should of course be there to be a well formed URL. I just expected it to show up when hovering over the link anyway just as it does in other browsers.

It would be nice if Chrome rather than just linking to “blank” would link to a page somewhere that just said that it was a malformed URL. However, the solution took seconds to implement while the troubleshooting took WAY longer than it should have. Even worse two senior troubleshooters was stumped on this for a very long time.

So if you end up with an empty link in your APEX application, or any other web page for that matter, you know that it is most likely the result of having a malformed URL rendered in Chrome.

So yes I started of with the first feeling and feeling good about myself, quickly started the downward spiral of the second feeling. That day never really got back up to the first happy feeling again. Happy with having solved the issue, but not nearly feeling on top of my game when the day ended. Oh well, there will be more days to convince myself that I might be on top of my game (at least sometimes).

Posted in APEX

March 15th, 2013 by mathias

In this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this.

At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post we’ll complete the functionality by using the same form for insert, update, and delete functionality.

Let’s start with adding a mode page item to the form. We will use this to know if the form is invoked in insert or in update/delete mode.

  • Right-click on “Form on Stored Procedure” on the page where your form is (page 4 in my example)
  • Select “Create Page Item”
  • Select Hidden
  • Give the page item a name, like P4_MODE.
  • Click “Next”
  • Click “Next” (again)
  • Select “Create Item”

You now have a new page item which is not displayed, but whose value you can reference on this page.

To make this example easy, we’ll start with just implementing update functionality. But first we’ll go to the report and change this link column to pass in a “U” for the mode page item when it is clicked.

In the page with the report, perform the following steps.

  • Right-click “List of Employees”
  • Click “Edit Report Attributes”
  • Scroll down to “Link Column”
  • For Item 2, enter the name and value we want the newly created page item to be set to. P4_MODE and U in my case.
  • Apply Changes

Now with that done, let’s head back to the page with the form. But before we make any changes there, we need to change the package to add a procedure for updating a row in the emp table.

Here is the code for the procedure we’ll use.

create or replace package tb_access as
  type t_emp_rec is record (empno emp.empno%TYPE
                           ,ename emp.ename%TYPE);

  procedure read_emp(p_in_empno     in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec);

  procedure write_emp(p_in_empno    in emp.empno%type
                     ,p_in_emp_rec  in t_emp_rec);
end tb_access;
create or replace package body tb_access as
  procedure read_emp(p_in_empno in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec) is
    begin
      select empno
            ,ename
        into p_out_emp_rec.empno
            ,p_out_emp_rec.ename
        from emp
       where empno = p_in_empno;
  end read_emp;
----------------------------------------------------
  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_emp_rec in t_emp_rec) is
    begin
      update emp
         set ename = p_in_emp_rec.ename
       where empno = p_in_empno;
  end write_emp;
end tb_access;

This code adds the procedure write_emp wich also takes empno and the record used for read_emp, only this time they are both in parameters as we’ll not return any data from the procedure that writes to the table. The actual code in the procedure is a very simple update that sets the name to the ename in the record for the row that has the empno passed in.

With this in place we’re ready to change the form to call this procedure upon submit. We do this by taking the code used for reading in the record (The process in page rendering) and put it into the “Run Stored Procedure” process we commented out in page processing.

The code we copy looks like this:

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  tb_access.read_emp(p_in_empno    => :p4_empno
                    ,p_out_emp_rec => in_emp_rec);

  :p4_ename := in_emp_rec.ename;
end;

Replace the code in “Run Stored Procedure” with the above and change it to look like this:

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  in_emp_rec.ename := :p4_ename;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_out_emp_rec => in_emp_rec);
end;

In the “Run Stored Procedure” change the condition to “- Select condition type -” to not have a condition for when the process is executed. It is currently conditioned on running only when the save button is clicked, leave that condition on for now.

With that in place, it is time to test the functionality of the forms new functionality. Run the report, click on the link and update the name of an employee. Assuming you followed the above and got the changes made in the right places, the form will now update the name of the employee.

The next step in this will be to add insert functionality to the form. First off is to set up the package to support inserting an employee. As we’re now going to have both insert and update through the same procedure, we need to include the mode in the signature of the procedure.

Let’s first update the package specification so the write_emp looks like this:

  procedure write_emp(p_in_empno    in emp.empno%type
                     ,p_in_mode     in varchar2
                     ,p_in_emp_rec  in t_emp_rec);

The procedure in the package needs to be updated to look like this:

  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_mode    in varchar2
                     ,p_in_emp_rec in t_emp_rec) is
    begin
      case p_in_mode
        when 'U' then
          update emp
             set ename = p_in_emp_rec.ename
           where empno = p_in_empno;
        when 'I' then
          insert into emp
             (empno
             ,ename)
           values((select max(empno) + 1 from emp) 
                 ,p_in_emp_rec.ename);
      end case;
  end write_emp;

Before the form is functional again, we need to change the call in “Run Stored Procedure” to match the new signature of write_emp.

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  in_emp_rec.ename := :p4_ename;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_in_mode     => :p4_mode
                     ,p_in_emp_rec  => in_emp_rec);
end;

With that change in place the form now works and the mode we set on the link column is now passed to the procedure to make sure an update is performed and not an insert.

To introduce insert functionality, we want to add a new button on the page with the report that invokes the form in insert mode. To add the button follow these steps on the page with the report.

  • Right-click on “List of Employees”
  • Select “Create Region Button”
  • Set name and label to “Add”
  • Click Next
  • Set position to “Right of Interactive Report Search Bar”
  • Set Alignment to “Left”
  • Click Next.
  • Select action “Redirect to Page in this Application”
  • Set the page to your form-page (4 in my example)
  • Set Request to INSERT
  • Set “Clear Cache” to 4
  • Set “Set these items” to P4_MODE
  • Set “With these values” to I.
  • Click Next.
  • Click “Create Button”

If you test the page, you’ll see that it ends up immediately to the right of the search bar. Do not click on the button just yet.

Now go to the page with the form and open the process in rendering (Read Emp).

  • Set “Condition Type” to “Value of item / column in expression 1 = expression 2”.
  • Set Expression 1 to P4_MODE.
  • Set Expression 2 to U
  • Apply Changes

This ensures that we’re only reading a row from emp when updating data. When inserting, there is no data to be read and displayed.

You may also want to set the same condition on the page item for empno to hide it when inserting as there is no empno to display then.

Test the function now by running the report page and click on the “Add” button. It adds a row with the name you enter on the form. The rest of the columns will have null as their value. Adding more columns in the procedures and on the form is an exercise left to the reader.

With the function now supporting insert and update, it is time to add the delete function so we can get rid of some of the test employees we’ve created. This function will be implemented only on the form and by adding supporting code in the package. Thus, the delete will be performed by clicking on the link and then clicking on a delete button on the form.

To implement this we’ll update the implementation of the write_emp procedure once more.

  procedure write_emp(p_in_empno   in emp.empno%type
                     ,p_in_mode    in varchar2
                     ,p_in_emp_rec in t_emp_rec) is
    begin
      case p_in_mode
        when 'I' then
          insert into emp
             (empno
             ,ename)
           values((select max(empno) + 1 from emp) 
                 ,p_in_emp_rec.ename);
        when 'U' then
          update emp
             set ename = p_in_emp_rec.ename
           where empno = p_in_empno;
        when 'D' then
          delete from emp
           where empno = p_in_empno;
      end case;
  end write_emp;

With the package updated, our next step is to change the code in the “Run Stored Procedure” process on the form page.

declare
  in_emp_rec tb_access.t_emp_rec;
  v_mode     varchar2(1);
begin
  in_emp_rec.ename := :p4_ename;
  v_mode           := :p4_mode;

  if :REQUEST = 'DELETE' then
    v_mode := 'D';
  end if;

  tb_access.write_emp(p_in_empno    => :p4_empno
                     ,p_in_mode     => v_mode
                     ,p_in_emp_rec  => in_emp_rec);
end;

The code is now changed such that a click on the delete button (not implemented yet) will set the mode to D. This is done by checking the REQUEST which we will set to DELETE for the button we’ll create now. REQUEST is a standard attribute available on buttons and branches in APEX. By setting it to a unique value, we can check for different invocation methods in code. This allows us to deal with different scenarios with just one block of code.

To add the button, follow these steps.

  • On the form-page, right-click on “Form on Stored Procedure”.
  • Select “Create Region Button”
  • Set “Button Name” and “Label” to “Delete”.
  • Click Next
  • Set “Position” to “Top of Region”
  • Set Alignment to “Left”
  • Click Next
  • Click Next (again)
  • Click Create Button

The value of “Button Name” is used to set REQUEST when the page is submitted. With this in place our form should now support deleting rows also. Update the branch on the page to not have a condition on the button pressed. Take it out for a spin.

There is one thing left to do, hide the delete button when the form is invoked to add a row.

  • Right-click the delete-button under rendering.
  • Select “Edit”.
  • Scroll down to conditions.
  • Set “Condition Type” to “Value of item / column in expression 1 = expression 2”.
  • Set Expression 1 to P4_MODE.
  • Set Expression 2 to U
  • Apply Changes

Now you’ve got a CRUD solution based on an interactive report with a form that supports insert, Update, and Delete. The total amount of code required is very small and you still have isolated the APEX-application from changes to the table structure by using a view, even the record can have fields added to it without impacting the functionality of your application. Developing using the demonstrated methods here allows you to create these applications almost as fast as with making them table based, but you have much more control and can make a lot of changes without touching the application,

Posted in APEX, Oracle, PL/SQL, SQL

November 7th, 2012 by mathias

In this post I follow up on the post where I started talking about records in APEX. In this post I’ll show how to add a page in front of the form created in that post. Thus this post will show a report and let you navigate to the form where the selected record is displayed. Pretty basic stuff, but it allows better testing of the form and it sets the stage for showing how to make the form used for insert, update and delete in the third post on this subject.

An interactive report is based on a report. As using procedures to read data for the form to avoid having hardcoded SQL in the application, we’ll use a similar technique here. By having a view, we can change the table and columns with no impact on the application. So we start with creating a view specific for this report.

create view apex_vw_emp as
  select empno
        ,ename
        ,job
        ,mgr
        ,hiredate
        ,sal
        ,comm
        ,deptno
    from emp;

Now that we have the view, we just create a new page with an interactive report.

Go ahead and create a page with an interactive report that uses the SQL “select * from apex_vw_emp“. Choose to not have a link to single row view.

When the page is created, edit the report attributes and add a link to a custom target.

  • Target = Page in this application
  • Page = 4 (the page where the form created in the last post is located, happens to be 4 in my application)
  • Clear cache = 4 (same as Page)
  • Item 1 Name = p4_empno (the item on the form used to retrieve the row)
  • Item 1 Value = #empno#

It is worth noting that #empno# is a reference to the empno on the row in the report that was clicked.

After you apply those changes, the report now has a pen icon to the left of the rows. When you click it, it will take you to the form where the row you clicked is loaded into the form (showing the ename of the empno).

This makes it easier to test the form, but more importantly it will make it possible to set up and test full CRUD functionality in a coming post.

Posted in APEX

October 24th, 2012 by mathias

Do you use forms based on procedures in APEX? If not, why not?
Do you use records in the procedures you use for forms in APEX? If not, why not?

I like procedures and records and with APEX I like them even more. Why you say? Should you not just point forms against tables and let the APEX magic take care of it all? No, I do not think you should.

The reason is that I love that feature for prototyping, but not for production quality code. The reason is that I think the classic design pattern of separating presentation logic from business logic and data layer is as important with APEX as it is with any other technology.

That is, APEX applications should contain as much logic for presenting data as possible, and as little data about the data layer as possible. Thus, I think any table should be able to be renamed without making any change to the APEX application.

One key to that is to use procedures to hide what tables are updated or read. That allows the tables to be changed with just updating the procedure. No change to the APEX application would be needed and thus no new deploy of it would be required.

But what if a column is added or removed from the table, does that not require a change to the signature of the procedure? Sure it does, if every column in the table is reflected in the signature as individual fields. This is why using records and procedures together is such a powerful combination.

Lets take a look at a basic implementation using a form based on a procedure that uses a record.

Here is the code for the procedure we’ll use.

create or replace package tb_access as
  type t_emp_rec is record (empno emp.empno%TYPE
                           ,ename emp.ename%TYPE);

  procedure read_emp(p_in_empno     in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec);
end tb_access;
create or replace package body tb_access as
  procedure read_emp(p_in_empno    in emp.empno%type
                    ,p_out_emp_rec out t_emp_rec) is
    begin
      select empno
            ,ename
       into p_out_emp_rec.empno
           ,p_out_emp_rec.ename
       from emp
      where empno = p_in_empno;
  end read_emp;
end tb_access;

This is a procedure that just returns a row of a certain emp_no from the emp table. The columns emp_no and ename are returned in the record.

To create a page in APEX using this procedure, follow these steps.

  1. Create a new page
  2. Select Form
  3. Select Form on Procedure
  4. Select the procedure (tb_access-read_emp) + next
  5. Change page and region names if you want to. + next
  6. Next (Do not use tabs)
  7. Next (No invoking page)
  8. Select a page to brach to on cancel and submit. + next
  9. Select to include only the columns that are in the record (empno and ename).
  10. Select display only as the display type for empno. +next
  11. Create

That was pretty easy, pretty much the same amount of work as to create it based on a table. However, there is one more thing we need to do. The reason for this is a bug. Hopefully this step is not needed in future versions. You know you have encountered this if the page items on the new page has names such as p4_pno and p4_ame. That is the first two letters in the name of the fields in the record were lost when the page items were created. This does not happen for fields in the signature, only for fields in a record that is in the signature.

It is easy enough to change the names of the page items. It is of course not needed technically speaking, but you will save yourself a lot of grief by having page items you know what they are used for.

The next step is to update the call to the procedure. In the “page processing” section there is a “run stored procedure”. The code will look something like this.

#OWNER#.TB_ACCESS.READ_EMP(
EMPNO => :p4_PNO,
ENAME => :p4_AME);

Yes, the bug affects the name of the page items used to provide values to the call. Not only is that wrong, the call is not using a record so the call will not work. A third problem is that the call is in the page processing section, but we want it to read data to be displayed.

As we will not update any data in this post, just comment out this code and copy it. Set the condition to never to avoid the call to even be attempted. Now create a process in “before regions” in page rendering. Paste the code you copied and update it to look like this.

declare
  in_emp_rec tb_access.t_emp_rec;

begin
  tb_access.read_emp(p_in_empno    => :p4_empno
                    ,p_out_emp_rec => in_emp_rec);

  :p4_ename := in_emp_rec.ename;
end;

This code fetches a row from the emp table based on an empno that the page item p4_empno has been set to. This is of course assumed to be done by a page that branches to this page.

This code is of course something that is very much a template that once you have it set up the way you want in one place it is just a matter of adjusting it for the needs of different pages. It really does not take much more time than widget based page creation using a table. This however gives much more flexibility in changing the underlaying datamodel as well as having some people focus on developing the presentation logic and others focused on writing data access logic.

This form is now just a read-only form, but it takes very little work to make it a full fledged crud by supporting insert, update, and delete in the same form. I may build on this form in a future post to show one way of doing that.

As a last thing, let’s just show that this page is now immune to changes to the record. Lets change the package to add another field to the record and populate it in the SQL.

create or replace package tb_access as
  type t_emp_rec is record ( 
        empno  emp.empno%type
       ,ename  emp.ename%type
       ,sal    emp.ename%type);

    procedure read_emp(p_in_empno     in emp.empno%type
                      ,p_out_emp_rec out t_emp_rec);
end tb_access;
create or replace package body tb_access as
    procedure read_emp(p_in_empno     in emp.empno%type
                      ,p_out_emp_rec out t_emp_rec)  is
      begin
         select empno
               ,ename
               ,sal
           into p_out_emp_rec.emp_no
               ,p_out_emp_rec.ename
               ,p_out_emp_rec.sal
           from emp
          where empno = p_in_empno;
      end read_emp;
end tb_access;

This change is now available to any user of the procedure, while the existing page just keeps working. It does not even need a resave/compile. The signature of the procedure is the same and so all callers will keep working. This allows a change to be made without being forced to also change code or recompile all places where a call to the procedure is made.

Posted in APEX, PL/SQL

October 3rd, 2010 by mathias

Yes, this post is a little out of order as it clearly didn't take place after the Thursday afternoon sessions. I missed it during the conference so I had to catch up on it later on the on demand site. I thought it was interesting enough to write up a few notes.

It was held by Tom Kyte and the subject was "What's new in database development". It'd be more correct to say Oracle development than database development as he covered just about any area that Oracle focuses on.

He started with APEX and spent a lot of time on it. The coverage APEX gets in general sessions and the number of really good APEX sessions speaks volumes about how strong APEX is becoming within the Oracle development technologies.

Tom shared a little background of how APEX was built by Mike Hitchwa when Mike set next to Tom and wrote the initial code for APEX. Anyone that has been impressed with the way bind variables are handled in APEX got their explanation for why. Tom has relentlessly been preaching about how to to it and the issues caused when not done well. So it all makes sense when you hear that it was Tom who wrote the initial code for bind variable handling in APEX.

Tom went on to say that they are seeing a lot of momentum behind APEX right now. The catch phrase he used to explain why it grows fast in popularity was "Easy architecture, easy to deploy, and easy to run".

Tom then spent some time talking about performance of APEX as a lot of people still thinks it is not able to handle enterprise level system demands.

The site apex.oracle.com that is used to run all the applications people create on Oracles hosted apex "test and demo" site has a surprisingly small computer running it. The specifications for it is:

  • Poweredge 1950
  • 2 Dual core Xeon 2.33 Ghz
  • 32 GB Ram
  • Cost : $4,300 (in 2007)
  • Runs 11G database

Not a very big computer to serve a site that provides support to anone who wants to try APEX.

Tom shared som of the numbers for what apex.oracle.com is used for.

  • They had 3.5 million views (pages loaded and processed) last week.
  • Distinct users accessing during the week: 3028
  • Total workspaces :9062
  • Applications: 32776

I'd think most "enterprise" systems would have less demand than that.

Some of the biggest things on apex.oracle.com are:

  • SQL Developer updates 800K hits /week
  • Pro MED (Prodution system) 770K hits / week
  • Asktom 250K-1000K hits / week
  • APEX Appbuilder 238K hits / week
  • JDeveloper updates 175K hits / week

Impressive to serve all of that from a small(ish) computer.

Tom then shared some key features from the 4.0 release.

Websheets

Designed for the enduser and designed for webbased content sharing. A solution that allows endusers to stop mailing exceldocuments around would be a good thing…

Dynamic Actions

Declarative client-side behavior. It is integrated in the framework. Uses JQuery.

This allows using common javascript function for handling advanced user interface improvements. From validations to drag and drop and more.

Team Development

Allows you to manage the application development process within APEX. This makes APEX able to be used by large development projects. It has built in end-user feedback. When a user has a problem they can tell you directly in the application and it feeds into the team development features.

Oracle APEX Listener

Built in Java.

It is a good alternative to mod_plsql and it is certified against WLS and Glassfish.

After that long piece on APEX, Tom went through som improvements for other technologies.

SQL Developer

The datamodeller is now free. It will now come with the product and will be supported with the database (just like SQL Developer itself).

It has real-time SQL monitoring. It will allow you to see what part of the execution plan Oracle is currently spending time in for a SQL. This exists in Enterprise Manager, but this gives the insight to many more as EM often is just used by the DBAs.

It can now find the SQL trace file and display the contents graphically in SQL Developer. This ought to make trace files much more accessible to developers.

It has support for AWR and ASH reports and has added developer specefic things that are not available in the other AWR/ASH aware tools.

A hierarchical profiler is now also included to allow you to see what functions calls which in a run of PL*SQL code.

SQL Developer now used PL/SCOPE data to show information about where variables are defined and used, This is something that has been added to the database and can alays be accessed with SQL, but it is more convenient to have it hooked into the development tool.

They have also added a DBA Navigator that allows DBAs to access most things they deal with in the database. Most objects (such as extents and tablespaces) can be seen and modified.

An interface for DBMS_SCHEDULER has been added. It is supposedly a much nicer way to configure jobs than to do it by have, which can be both tedious and complicated.

Another neat feature is that auto tracing has been improved to not only allow tracing of a statement, you can now trace two statements and see a comparison report of the tracing so you can compare them. Since this usually has been done with Toms "Runstats" package, I'd assume this feature has Toms fingers all over it.

ODP.Net

It is free and provides native access to the database. It has statement caching (on the client side) and will support TimesTen in 11.2.0.2.

I'm not a .Net person at all , but it sounds as if this provides a lot of benefits both in use and in performance. If you work with .Net, then you will want to take a look at all the things Oracle provides in this area.

Java

There is a Universal Connection Pool for all technologies using connection pools to use. This is supposed to reduce the overhead caused by having several connection pools.

For Secure Files there has apparently been a performance issue dealing with very large files, my understanding from Toms presentation is that it resulted in double buffering. Now there is Zero Copy which sounded as if it would remove all buffering and thereby improve the speed significantly.

Database Improvements

Tom focused on the optimizer and on developer related topics as this was a Develop keynote.

Tom showed how the optimizer could detect a three-way join that was not needed. It was a fairly complicated situation, but the optimizer can apparently completely skip accessing tables now if they are not needed for the end result.

PL*SQL

Edition based redefinition is available in a editions of 11G. Tom thinks this is the killer feature of 11G, and says that it may be the first time the killer feature is available in all editions and with no additional licensing requirements.

It allows live changing of code (PL and views). Live changing of data can already be done with online redefinition and online actions on indexes.

The process now is:

  • Create new edition
  • Test new edition of the code while users still use the old.
  • Two options for rolling out
    • Let new sessions use the new one and allow old to keep using the old edition.
    • Switch everyone over to the new when the system is "idle". Takes just seconds.

That was the end of Toms presentation. He covered a lot of technologies, even some that are not mentioned above (like Ruby). WHat I think is interesting is what wasn't mentioned at all.

The fist one that comes to mind is JDeveloper. It has not become very popular with Java purists and it is not mentioned at all. There are some rumors of it being de-emphasized by Oracle as how you work with it seems to be very far from how most Java projects want to organize their builds and deployments.

Next interesting omission is the technology mostly integrated with JDeveloper. No mention at all on the Oracle Develop for their key Java development framework. ADF did not get any love at all and that is not true just for this presentations, there were very few mentions of it in any of the presentations I attended. True, I did not seek out ADF sessions, but virtually every single presentation on non APEX technologies found a way to talk about APEX, the oposite was true for ADF. Even when you felt that someone was building up to a plug for ADF, they never went there.

The lack of love for ADF and JDeveloper may mean that they are not considered critical to Oracles success anymore. I assume ADF was used to build the fusion applications which would mean that the tool and product is probably not going away, but could it mean that they are primarily being focused for large product development and not something that is good for custom development projects?

Personally it feels as if ADF is now essentially becoming a tool used when integrating with Oracle (fusion) Apps or Oracle SOA Suite. I'm not sure a custom development project would end up using ADF. Of course there will be exceptions to that, but they will probably be fewer and fewer.

It could also be that JDeveloper and ADF starts becoming less and less of something for the customers and more and more of an internal tool at Oracle. I'm sure they have very specialized versions and techniques used in development to help the development of the fusion applications.

On the other hand, it could just be that this wasn't the time for releasing improvements to JDeveloper or ADF. I have trouble convincing myself of that though. The reason is that Oracle now owns Java and if there ever would have nee a reason to show JDeveloper and ADF in its full glory, now would have been the time. By not releasing improvements to what is considered problems with JDeveloper and ADF, they have probably given more momentum to the competing tools. They of course ha another tool in this space now, maybe NetBeans will be the future migration path of JDeveloper and when ADF finds a new home.

Posted in APEX, DBA, OOW, Oracle, Performance, SQL

September 29th, 2010 by mathias

The day starts with a presentation b6 Tom Kyte about "What else can you do with system and session data".

Tom starts with reviewing the history of tuning an Oracle database.

The prehistoric era (v5) required writeing debug code as that was the only way to get any information about what the code did.

The dark ages followed (v6) and now Oracle introduces:

  • Counters/ratios
  • bstat/estat
  • SQL Trace
  • The first few (7?) v$-views are introduced

In the rainessance era (v7) introduced a few more things:

  • The wait event instrumentation was introduced
  • Move from counters to timers
  • Statspack

The modern era (v10) introduced the tools we prefer today

  • DB-Time tuning
  • Multiple scoping levels (AWR)
  • Always on, non-intrusive
  • Bult into the infrastructure – instrumentation, ASH, AWR, ADDM, EM

DBA_HIST_* views is where the historic data is located.

Tom contunued with discussing the metrics and the timemodel and how to join that to get data about them. However, all his presentations are available for everyone and you're better off reading them on his site than getting a summary of it here.

As usual, Tom places his presentations and other things under files on asktom.

The next presentation was Apex debugging and tracing with Doug Gault from Sumneva.

Debug mode is truned off on the app level by default. It neds to be enabled there before it can be used.

Debug data is now written to a table, and not dumped on the webpage as before. It is viewed by clicking view debug in the developer bar in the APEX interface.

Use conditional display to output things only in debugmode (using v('DEBUG') ).

Things that happens on a page after it has been rendered will not be captured. This is things like javascript running on this client.

APEX_DEBUG_MESSAGE is currently undocumented, but the code shows how to use it.

Debugging can be turned on/off on the page programmatically.

Logging from you own code allows you to see what parts of a package call it is that takes the time.

Doug then shows a few different ways to collect and report on the debugdata for a report.

I recommend looking at the presentation for ideas on how to use it and what can be done. Unfortunately this presentation does not seem to be available on the sumneva website at this point. If you are interested in it, send an email to them asking if they are willing to share it on their presenation page. You'd of course want to check their site first to make sure it isn't uploaded between the time I write this and the time you read it.

 

Posted in APEX, OOW, Oracle, Performance, SQL

September 23rd, 2010 by mathias

Wednesday starts off with a presentation about a new feature in APEX 4.0 that I have not had a chance to look into. It is how you use and develop plugins. The presentation was held by Patrick Wolf who is also the developer of the plugin feature.

Scott began by showing how a plugin is used (that is using a plugin compared to developing it).

You install a plugin by selecting plugins in the shared components section, and then it is just a standard APEX import.

Using a plugin has the benefit that it is declarative while using javascript directly requires codechanges and explicit inclusion of necessary code on every page it is used on.

Once installed, the use of it is identical to using other supplied item types in APEX. It is like the plugin was part of the product.

The productivity is much better than it is with just using javascript as once you have the plug in it is just a matter of selecting it to be used and not having to change code to match what you need.

There are already many plugins available and more are added all the time.

Patrick recommends starting with finding a JQueryplugin that performs the action you want and then truing it out on a static html page. When that works, you're ready to create an APEX plugin for it.

When developing an APEX plugin, you need to follow certain API guidelines to make sure your functions has the signature they are expected to accept.

Patrick them moved on to showing how a plugin is developed.

You create your plugin in shared components and then it is a matter of providing calls to the plsql code you write to generate the html and other things that are needed.

Patrick finished up with some recommendations for developing plugins and they are similar to what you would expect in any weboriented technology.

Always escape all data, both from the database and from the browser. Not doing that opens a risk of criss site scripting attacks. There is a function available for this, I believe it is sys.htf_escape_cs (possible typo as I wrote it from memory after he moved on to his next slide).

Reference apex.jquery in the code to make sure the apex supplied version of jquery is used.

Do not trust client side checks on data as the can easily be overridden. The same checks has to be performed on the server side also.

Look at Oracles Best Practice plugins to get a feeling for what to do and how to do it. They have lots of comments to guide you.

Use the packages apex_plugin_util, apex_javascript, apex_css.

Test your pliugins with more than one item using it on a page.

Patrick recommends avoiding inline css and javascript when it becomes a lot to make sure the page loads faster.

Patrick finished up with reminding us to share plugins when it is possible to do so.

The only other presentation for the morning was a handson allowing the participants to repeat the steps that Patrick had just done. As with many handson exercises it was too much of following a set of steps with instruction to click here or there and to type this or that into different places. Though useful it is not creative enough to allow me to fully understand the process. That can however be done some other time when I have a need to develop a real plugin.

Posted in APEX, OOW, Oracle

September 22nd, 2010 by mathias

The afternoon was kicked off with a presentation on using APEX in large projects by Dimitri Gielis. These re my notes for what Dimitri told us.

Many people feels that APEX cannot be used for anything larger than a singe developers app. 

Sumneva uses EC2 for both demo and development work with their clients.

They have readymade scripts to create new projects that sets up filesystem directories, APEX application, and subversion.

They're finding that the agile project methodology works very well for APEX-projects.

To automate testing, they're using Selenium. I find the number of times this product mentioned at OOW interesting. It is a product we have on the list of technologies to know/learn at work, but most people I've talked with have not been aware of it. I guess it differs between markets and it may also be a tool that more advanced web-developers are more familiar with.

To check consistency in a large projects with many developers, they recommend to use the APEX advisor to get a report on things to look into.

Sumneva has created their own PM tool to track tickets with. It is aware of APEX specific things such as the url to the app and the alias of the app.

Using the feedback features to collect data from the users works very well for them in their projects. They use the provided features when possible, and a custom version when they cannot access production environments that allos the users to log data into an application on their servers.

Tools they find useful includes:

  • Selenium
  • Firebug
  • SQL Developer
  • Subversion
  • Jira or other ticketing system
  • Reusable APEX components
  • Publish defaults to apps via a master app
  • Use interface defaults

While the presentation didn't really tell me what it takes on the technical side to run a large project, it was clear that it is very possible. But, as with all large projects it requires planning for both functional and technical aspects of the project.

The next presentation was moving from mod_plsql in Oracle E-Business suite to APEX. The reason it is needed is that mod_plsql is not supported in the latest version of ebiz.

Unfortunately the presentation focused so much on the solution they had and the specifics of what they needed that it was hard to extract any APEX specific knowledge from the presentation. Most of the presentation was centered on the fact that they wanted to have SSO between ebiz and the APEX application and how they needed links to go from ebiz to the apex application so it felt like one application for the end-users. They succeeded with that, but the presentation did not provide enough details for what they did or even what they main hurdles they encountered was.

Posted in APEX, OOW, Oracle