In the last chapter, we created a simple “Editable Interactive Grid,” which is APEX-talk for an Excel-like grid we can use to edit data from a database table. In this chapter, we will make the two edits to this grid which our hypothetical end-user “requested”.
Change 1: See the currently logged in user profile
APEX uses something called “substitution strings.” The Oracle documentation makes for great leisure reading (https://docs.oracle.com/database/121/HTMDB/concept_sub.htm#HTMDB03022), but for our application, here’s what you need to know. The currently logged-in user is in a built-in substitution string called “APP_USER.” This string is used differently based on where you want to use the content. We will use it as what is called a “bind variable.”
- First, go back to the “App Builder” and make sure you are on the “Layout” view, and then scroll down to “Content Body” (the screenshot with highlights should help):
- The “Content Body” section contains most of our content. Now, let’s add an element that will hold our user name. From the section below, select an “Item” called “Display Only,” and drag it to the “Items” section in the “Content Body” (again refer to the screenshot for help).
- As you might have guessed, our new element is called “P4_NEW.” It should already be selected, but if not just click on this element. Now the pane on the right side, the “Property Editor,” (https://docs.oracle.com/cd/E59726_01/doc.50/e39147/pg_designer002.htm#HTMDB29506) shows the properties for “P4_NEW.” Our next few steps will be within this bar.
- You can change the name if you like. I will keep it as is for now.
- “Type” should remain as “Display Only.”
- Change “Label” to “Logged in as”
- Now scroll down to the “Source” section:
- Then make these changes:
- Type: SQL Query (return single value)
- SQL Query: “select : APP_USER from dual” (minus the quotes)
- We are using the SQL query to use the “APP_USER” bind variable, which gets us the user name of the currently logged-in user into the element we need. But, obviously, we could do a lot more if we wanted to. We could use the “APP_USER” value to read some data from a database table. We could extract some other completely unrelated value as well. But enough hypotheticals; let’s save and run this form now. Click on “Save” and then on the “Run” button.
- You should now see the logged-in user at the top of your page. Because we have set the application to maintain no authentication, it uses the public user by default:
Change 2: The ID field should be titled “Cost Center ID” and should be read-only
This is a very typical requirement. It looks very simple but raises issues it does not address. It makes sense for the ID field to be read-only, as the user should not have to generate it or be responsible for ensuring it is unique. This means that it is our responsibility as developers to ensure this works. So, this requirement will actually be executed in multiple steps:
Step 1: Make the column read-only
- Go back to APEX where you last edited the page. This screen is called the “Page Designer: << href: https://docs.oracle.com/cd/E59726_01/doc.50/e39147/pg_designer001.htm#HTMDB29497 >>, and is essentially your Integrated Development Environment (or IDE) for APEX application pages.
- In the left pane, ensure that you are on the first tab (“Rendering”). Then, under “Page 4: Maintain Cost Centers” –> “Regions” –> “Content Body” –> “Maintain Cost Centers” –> “Columns,” you will find each column in our grid. The first entry is the checkbox, the second is the “Row Actions” menu, the third is the “ROWID” (which is hidden), and the last two are “ID” and “Description,” which is our data. Click on “ID,” and its properties are available to edit in the Property Editor in the right pane.
change the “Type” field from “Number” (as in the screenshot) to “Display Only.”
Then click “Save” and “Run.”
Now, when you click on “Edit,” you will see that the “ID” field is not editable anymore. So, there we are. Except that when we create a new cost center, the “ID” field will be null and we have no way to fix that. Or do we?
Step 2: Default the ID field for a new record
- So far in the “Central” pane of your page designer, we have been working on only the “Layout” tab. Now let’s click on the second tab, called “Component Layout.” Notice that this view contains three sections, one of which is entitled “Page Processing.” Saving your data is handled by a process, so it would be under this section. Also, it has been conveniently named for us: “Save Interactive Grid Data.” Click on this, so that we can edit this content in the “Property Editor.”
- In the “Property Editor,” note that the type is currently “Interactive Grid – Automatic Row Processing (DML).” This means that APEX handles all the data manipulation in the database by itself. Change this to “PL/SQL Code” so we can control how it does this as we whim or fancy. And our whim is, “Whenst thine user createth new cost center record, thou shall create a new ID.”
Here’s what goes into the PL/SQL code field:
when ‘C’ then
insert into app_cost_centers (
(select max(id) + 1 from app_cost_centers) ,
select rowid into :ROWID from app_cost_centers
where CODE = :CODE;
when ‘U’ then
DESCRIPTION = :DESCRIPTION
where rowid = :ROWID;
when ‘D’ then
delete from app_cost_centers
where rowid = :ROWID;
- The code essentially executes an INSERT, UPDATE or DELETE statement depending on whether the
“:APEX$ROW_STATUS” bind variable is ‘C’, ‘U’ or ‘D’. Once you paste this code into the “PL/SQL Code” field you can pull up the editor. You should prefix your table name with the schema name if it is different from the schema your APEX is installed in (like in the screenshot).
Save and run, and you should be good to go. You can test out the edit and insert and delete functions to make sure they work. Our hypothetical end-user will thank you profusely.
- As practice, you can repeat Chapters 3 and 4 for the table “app_expense_types.” In chapter 5, we will enhance the report we created in Chapter 2 by replacing the “ID” and “CODE” fields with the descriptions from tables “app_cost_centers” and “app_expense_types.”