APEX - 创建一个包含多个表格的页面链接到多个相关表格......所有这些都通过一个按钮提交？ [英] APEX - Creating a page with multiple forms linked to multiple related tables... that all submit with one button?

问题描述

我在APEX中有两个通过主键链接的表。一张表（APEX_MAIN）保存我们系统中文档的基本元数据，另一张表（APEX_DATES）保存与该文档处理有关的重要日期。

对于我的团队，我有创建了一个控制面板，他们可以与所有这些数据进行交互。现在的问题是，他们现在在一个页面上更改APEX_MAIN中的信息，然后在另一个页面上修改APEX_DATES。我真的希望能够将这些表格放在同一页面上，并将更新提交给它们各自的表格&具有单个提交按钮的行。我已经在同一页面上使用两个不同的区域设置了这一点，但是我得到的错误都是初始获取行（哪个行被提取第二个似乎工作，但是然后在第一个获取的表单中的页面项是空）？并提交（它提供了一些关于自更新请求发送以来数据库中的信息已被更改的错误）。任何人都可以帮助我吗？

解决方案

这是内建Apex表单的局限性，您只能拥有一个自动行取指每页进程不幸。每个页面可以有多个表单区域，但是如果你这样做，你必须自己编写所有的提取和提交处理（并不是很难，但你也需要自己考虑乐观锁等）。

即使使用内置的表单功能，将表格的表格分割为几个区域也是完全可能的，因为区域本身只是一个布局对象，并没有与其相关的功能。

手动构建表单非常直截了当，但工作量更大一些。

项目

这些应该有源设置为静态文本，而不是数据库列。

按钮

您需要按钮，如创建，应用更改，删除提交页面。这些需要唯一的请求值，以便你知道哪个表正在被处理，例如， CREATE_EMP。您可以使按钮有条件地显示，例如仅当PK项为空时创建。

行获取进程

这将是一个简单的PL / SQL过程例如：

pre $ select ename，job，sal
into：p1_ename，：p1_job，：p1_sal
from emp
其中empno =：p1_empno;


它必须是有条件的，以便只在进入表单时触发，而不是在每页之后触发加载 - 否则如果存在验证错误，则任何编辑都将丢失。这可以由隐藏项目控制，该项目最初为空，但在页面加载时设置为非空值。如果隐藏的项目为空，则只获取该行。

提交过程（es）

插入，更新，与按钮相关联的删除的单独流程，或者查看：request 值的单个流程，以查看需要执行的操作。无论哪种方式，过程将包含简单的DML，如：

  insert into emp（empno，ename，job，sal）
值（：p1_empno，：p1_ename，：p1_job，：p1_sal）; 
  

乐观锁定

为简单起见，但内置表单为您做的一件事是处理乐观锁定，以防止2个用户同时更新相同的记录，并且一个人的更新覆盖另一个人的记录。有多种方法可以用来做到这一点。一个常见的方法是使用OWA_OPT_LOCK.CHECKSUM比较记录，因为它在选择时与在提交更新时的位置相同。

在获取过程中：

  select ename，job，sal， owa_opt_lock.checksum（'SCOTT'，'EMP'，ROWID）
转换为：p1_ename，：p1_job，：p1_sal，：p1_checksum 
 from emp 
 where empno =：p1_empno; 
  

在提交过程中进行更新：

 update emp 
 set job =：p1_job，sal =：p1_sal 
其中empno =：p1_empno 
和owa_opt_lock.checksum（'SCOTT'，'EMP' ，ROWID）=：p1_checksum; 
 
如果sql％rowcount = 0那么
  - 处理更新失败的事实例如raise_application_error 
 end if; 
  

I have two tables in APEX that are linked by their primary key. One table (APEX_MAIN) holds the basic metadata of a document in our system and the other (APEX_DATES) holds important dates related to that document's processing.

For my team I have created a contrl panel where they can interact with all of this data. The issue is that right now they alter the information in APEX_MAIN on a page then they alter APEX_DATES on another. I would really like to be able to have these forms on the same page and submit updates to their respective tables & rows with a single submit button. I have set this up currently using two different regions on the same page but I am getting errors both with the initial fetching of the rows (Which ever row is fetched 2nd seems to work but then the page items in the form that was fetched 1st are empty?) and with submitting (It give some error about information in the DB having been altered since the update request was sent). Can anyone help me?

解决方案

It is a limitation of the built-in Apex forms that you can only have one automated row fetch process per page, unfortunately. You can have more than one form region per page, but you have to code all the fetch and submit processing yourself if you do (not that difficult really, but you need to take care of optimistic locking etc. yourself too).

Splitting one table's form over several regions is perfectly possible, even using the built-in form functionality, because the region itself is just a layout object, it has no functionality associated with it.

Building forms manually is quite straight-forward but a bit more work.

Items

These should have the source set to "Static Text" rather than database column.

Buttons

You will need button like Create, Apply Changes, Delete that submit the page. These need unique request values so that you know which table is being processed, e.g. CREATE_EMP. You can make the buttons display conditionally, e.g. Create only when PK item is null.

Row Fetch Process

This will be a simple PL/SQL process like:

select ename, job, sal
into :p1_ename, :p1_job, :p1_sal
from emp
where empno = :p1_empno;

It will need to be conditional so that it only fires on entry to the form and not after every page load - otherwise if there are validation errors any edits will be lost. This can be controlled by a hidden item that is initially null but set to a non-null value on page load. Only fetch the row if the hidden item is null.

Submit Process(es)

You could have 3 separate processes for insert, update, delete associated with the buttons, or a single process that looks at the :request value to see what needs doing. Either way the processes will contain simple DML like:

insert into emp (empno, ename, job, sal)
values (:p1_empno, :p1_ename, :p1_job, :p1_sal);

Optimistic Locking

I omitted this above for simplicity, but one thing the built-in forms do for you is handle "optimistic locking" to prevent 2 users updating the same record simultaneously, with one's update overwriting the other's. There are various methods you can use to do this. A common one is to use OWA_OPT_LOCK.CHECKSUM to compare the record as it was when selected with as it is at the point of committing the update.

In fetch process:

select ename, job, sal, owa_opt_lock.checksum('SCOTT','EMP',ROWID)
into :p1_ename, :p1_job, :p1_sal, :p1_checksum
from emp
where empno = :p1_empno;

In submit process for update:

update emp
set job = :p1_job, sal = :p1_sal
where empno = :p1_empno
and  owa_opt_lock.checksum('SCOTT','EMP',ROWID) = :p1_checksum;

if sql%rowcount = 0 then
    -- handle fact that update failed e.g. raise_application_error
end if;

这篇关于APEX - 创建一个包含多个表格的页面链接到多个相关表格......所有这些都通过一个按钮提交？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！