Updating a table with the hidden primary key

APEX_ITEM is a PL/SQL package that you can use to programmatically put items on the screen. The package also contains a function to put hidden items on the screen. Hidden items are items that are placed in a webpage but are not visible. However, they can contain a value. In this way, you can make each row in a report unique. We will make an updatable report which makes use of the APEX_ITEM API.

Getting ready

Make sure you have access to the table APP_EVENTS.

How to do it...

First, we will make a classic report based on the APP_EVENTS table.

  1. In the application builder, click the Create page button.
  2. Select Report.
  3. Select Classic report.
  4. Enter a name for the page and click Next.
  5. Select Do not use tabs and click Next.
  6. In the text area, enter the following query:
    select apex_item.hidden(1,id)||apex_item.text(2,event) appevent
, apex_item.date_popup(3,rownum,event_date,'dd-mm-yyyy') event_date
, apex_item.text(4,location) location
, apex_item.text(5,event_type) event_type
from app_events;

    [1346_07_01.txt]

  7. Click Next.
  8. You can enter a region name. After that, click Next.
  9. Click Finish. The report has been created and we are now going to edit it.
    How to do it...
  10. Click the Edit Page icon.
  11. Click on the Report link in the regions section to edit the report attributes.
  12. Click on the Edit icon (the pencil) to edit the first column.
  13. In the column attributes section, select Standard Report Column in the Display As listbox.
    How to do it...
  14. Click the Apply changes button.
  15. Repeat these steps for the other three columns and click the Apply changes button to return to the page definition.

Now we will make a button and a page process to enable the saving of data.

  1. In the buttons section, click the Add icon.
  2. Select a region for the button and click Next.
  3. Select Create a button in a region position and click Next.
  4. Enter a name for the button, for example Save. Enter a label for the button. You can enter here Save as well. Click Next.
  5. Select Bottom of region and click Next.
  6. In the action list box, select Submit Page. Click Create button.
  7. In the Processes section, click the Add icon.
    How to do it...
  8. Select PL/SQL.
  9. Enter a name for the page process. You can enter here save_results. Click Next.
  10. In the text area, enter the following code:
    begin
for i in 1..apex_application.g_f01.count
loop
update app_events
set event = apex_application.g_f02(i)
, event_date = to_date(apex_application.g_f03(i), 'dd-mm-yyyy')
, location = apex_application.g_f04(i)
, event_type = apex_application.g_f05(i)
where id = to_number(apex_application.g_f01(i));
end loop;
end;

    [1346_07_2.txt]

  11. Click the Create process button.
  12. The page is ready. Run it and try to change some values in the page. Then try to save by clicking the Save button.

How it works...

With APEX_ITEM you can put an item on the screen. The first parameter in the function (hidden, text, or date_popup) is the index which you can use to find the item back. Let's say you use the following command to put an item on the screen:

APEX_ITEM.TEXT(2,'COLUMN_NAME')

When submitted, the values of the APEX items will be stored in the PL/SQL array APEX_APPLICATION.G_F0X. Then you can find this item back with:

APEX_APPLICATION.G_F02(i).

Where i is the row indicator in case you are working with a multi row tabular form.

Furthermore, you can use the first variable to do some aggregate functions like count. Use it in the following way:

APEX_APPLICATION.G_F01.COUNT.

Note that the example in this recipe only handles existing rows.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.28.179