Creating an automated row fetch with a page process

When you create a Single Record form, you probably would like to see that the form automatically retrieves data on startup. You can do that with an automated row fetch. In this section, we will create a page process with an automated row fetch. We're going to make a form where users can update their data.

Getting ready

We will use the EMP table so make sure it exists, together with the Primary Key. Create a simple form page:

1. In the Application Builder, click on the Create Page button.

2. Select the Blank page and click on Next.

3. Assign a number and enter a name in the Page Alias field. The page number is automatically filled by APEX.

4. In the next step, enter a name for this page and click on Next.

5. In the Tabs dialog, select No and click on Next.

6. In the Page Confirmation dialog, click on Finish.

The page is now ready and we can start creating a region with items.

1. Click on the icon of the new page.

2. Create a region by right-clicking on the Regions link and clicking on Create.

3. Select HTML and click on Next.

4. In the Type Of HTML dialog, select HTML and click on Next.

5. In the next step, enter a title for the region and click Next.

6. In the HTML text region source step, leave all options and click on Create Region.

   A region is created and now we will add some items to this region. We will start with the Primary Key of the table.

7. Right-click on the name of the region and select Create Page Item.

8. Select Text Item and click on Next.

9. In the next step, select Text Field and click on Next.

10. Next, enter a name for this item. Since this item will display the Primary Key we give it the same name as the Primary Key column, empno. So, the item will be named P11_EMPNO (11 is the page number, this can be different depending on your situation). Enter this name in the text field and click on Next.

11. In the next step, you can enter a label for this text item. Enter empno and click on Next.

12. In the Source dialog, in the Source Type listbox, select Database Column and enter EMPNO in the Item Source Value textarea. Mind the capitals, otherwise it will not work.

13. Click on the Create Item button.

The item has now been created.

Next, create another item. Let's say Ename.

1. Right-click on the name of the region and select Create Page Item.

2. Select Text Item and click on Next.

3. In the next step select Text Field and click on Next.

4. Next, enter a name for this item. Enter P11_ENAME (again, change 11 to the page number in your situation) in the text field and click on Next.

5. In the next step you can enter a label for this text item. Enter ename and click on Next.

6. In the Source dialog, in the Source Type listbox, select Database Column and enter ENAME in the item Source Value textarea. Mind the capitals, otherwise it will not work.

7. Click on the Create Item button.

The item has now been created.

How to do it...

1. In the page you just created, expand the After Header section and right-click the Processes link. Now click on Create.

2. Select Data Manipulation and click on Next.

3. Select Automated Row Fetch and click on Next.

4. Enter a name for this process. In the Point listbox, select On Load – After Header. Click on Next.

5. In the next step, enter the table name, the item containing the Primary Key column value, and the Primary Key column. In our case, the table name is EMP and the item containing the Primary Key column value is P11_EMPNO. The Primary Key column is the column in the table that is part of the Primary Key. In our case it is EMPNO. Again, mind the capitals, otherwise it won't work.

6. Click on the Create Process button. The automated row fetch has been created and you can run the page to see what happens.

How it works...

The automated row fetch is executed when the page is run but after the page header section has been generated by APEX. A row is fetched by using the Primary Key value stored in the page item P11_EMPNO. If this item is empty in the session state, no row will be fetched at all.

When a row is fetched, the column values are put in internal arrays (memory cache), but will not be committed into session state itself. The session state is only populated when the page is submitted (using the values stored in the page items). This has to do with performance (insert/delete session state) for example, clicking a "cancel" link.

In this case, running the page without P11_EMPNO having a value will produce a page with empty items.