Application Express:匿名PL/SQL块和绑定变量 [英] Application Express: Anonymous PL/SQL Block and Bind Variables

问题描述

在匿名PL/SQL块过程中，将页面项的值绑定到声明的变量时遇到问题.

I'm having an issue binding the value of a page item to a declared variable in an anonymous PL/SQL block process.

问题是在满足条件之前，页面项目(:P4550_REQUESTOR)不会填充值.看起来，PL/SQL阻止过程在页面加载后立即将变量绑定为空值，尽管该过程只有在单击特定按钮后才会触发.

The problem is that the page item (:P4550_REQUESTOR) is not populated with a value until a conditional is met. It appears that the PL/SQL block process is binding the variable to an empty value as soon as the page is loaded, despite the fact that the process does not fire until a specific button has been clicked.

这是我的代码:

DECLARE 

v_email_to   app_user.email%type;
v_requestor  VARCHAR2(15);

BEGIN

  v_requestor := :P4550_REQUESTOR;

BEGIN
    SELECT email INTO v_email_to
    FROM app_user 
    WHERE userid = v_requestor;
END;

SEND_APEX_MAIL (
                 v_email_to,

                 'Your vacancy request has been rejected.'
                 || chr(10)
                 || 'Emailed to: ' || v_email_to
                 || chr(10)
                 || 'Requestor: ' || v_requestor,

                 'Vacancy Request Rejected' 
               );

END; 

有人对此有任何想法吗?

Does anyone have any thoughts on this?

如果我将值硬编码到v_requestor，则该块可以正常工作.如果在页面加载后尝试获取P4550_REQUESTOR的值，则该页面为空.单击编辑按钮后，将填充P4550_REQUESTOR.

The block works just fine if I hard code a value to v_requestor. If I try to get the value of P4550_REQUESTOR after the page has loaded, it is empty. After clicking the edit button, P4550_REQUESTOR is populated.

** **更多详细信息** **

P4550_REQUESTOR是位于空缺请求"区域内的页面项目，仅当满足条件时才显示.具体地说，条件是与页面加载时创建的表行关联的编辑按钮.单击编辑按钮将显示详细信息区域，并填充相关的页面项目.

P4550_REQUESTOR is a page item that resides within the Vacancy Request region which is only displayed when a conditional is met. Specifically, the conditional is an edit button associated with a table row that is created on page load. Clicking the edit button causes the details region to display, and the associated page items to be populated.

空缺请求"区域中的页面项目值是通过在标题后触发"的自动行提取"填充的.

The page item values in the Vacancy Request region are populated via an Automated Row Fetch which fires After Header.

P4550_REQUESTOR具有数据库列"的源类型.

P4550_REQUESTOR has a Source Type of DB Column.

触发上面代码的过程设置为在提交时-计算和验证之后"触发

The process that fires the code above is set to fire On Submit - After Computations and Validations

如果在页面加载时记录了P4550_REQUESTOR的值，则显示为空.如果在单击编辑"按钮后记录该值，则将获得预期的字符串值.

If I log the value of P4550_REQUESTOR when the page loads, it shows null. If I log the value after clicking the edit button, I get the expected string value.

推荐答案

Oracle APEX中的流程控制

(在其他编程学科和环境中考虑这实际上很有用.)

Process Flow Control in Oracle APEX

(This is actually useful to think about in other programming disciplines and environments.)

问题是在满足条件之前，页面项目(:P4550_REQUESTOR)不会填充值.看起来，PL/SQL阻止过程在页面加载后立即将变量绑定为空值，尽管该过程只有在单击特定按钮后才会触发.

The problem is that the page item (:P4550_REQUESTOR) is not populated with a value until a conditional is met. It appears that the PL/SQL block process is binding the variable to an empty value as soon as the page is loaded, despite the fact that the process does not fire until a specific button has been clicked.

问题陈述以Apex术语重新措词并以实际问题的形式呈现:

The problem statement reworded in Apex terminology and presented in the form of an actual question:

1. 页面上有一个REPORT REGION，其中包含直接引用数据表/视图的结果.此报告由称为自动提取"的Apex流程管理，并由页面标题的加载自动启动.
2. 页面上有一个FORM ITEM，它由用户选择的BUTTON ITEM有条件地填充. BUTTON ITEM是报告结果的一部分.
3. 有多个按钮项.每个都与每个报告记录的值相关联.
4. 如果用户未从REPORT REGION中选择BUTTON ITEM，则FORM ITEM保持未分配状态，并包含空"值.

1. There is a REPORT REGION on the page which contains the result of a direct reference to a data table/view. This report is managed by an Apex process called "Automated Fetch" and is initiated automatically by the loading of the page headers.
2. There is a FORM ITEM on a page which which is populated conditionally by a BUTTON ITEM selection made by the user. The BUTTON ITEM is part of the report results.
3. There are multiple button items. Each is associated with a value for each report record.
4. If the user does not select the BUTTON ITEM from the REPORT REGION, the FORM ITEM remains unassigned and contains a "null" value.

有一个已定义的PL/SQL代码块，可以将其设置为在按下SUBMIT BUTTON项目时执行(也在同一页面上).为什么我的代码块(定义的页面进程)在没有先从REPORT REGION首先按下BUTTON ITEM的情况下被触发时以空值运行?

There is a defined PL/SQL block of code which is set to execute when a SUBMIT BUTTON item is pressed (also on the same page). Why does my code block (defined page process) run with a null value when it is triggered without first pressing a BUTTON ITEM from the REPORT REGION first?

如果您以过程语言的范式思考，答案并不明显.在不进行有关该主题的演讲的情况下，这是我制作的OP的问题空间的直观布局，以说明如何使问题变得更加明显:

The answer is not obvious if you think under the paradigm of a procedural language. Without diving into a lecture on the topic, here's a visual layout of the problem space of the OP that I cooked up to illustrate how the problem can be made more obvious:

这是我正在实施的Apex页面设计.它足够通用，可以用作其他Apex设计的模板.此图上没有流程箭头，因为它是一个有状态的系统.一件事会导致另一件事发生，依此类推……但并非总是如此，并非同时发生.

This is my Apex page design in implementation. It's generic enough to use as a template for other Apex designs. There are no flow arrows on this diagram because it's a stateful system. One thing causes another thing to happen and so on... but not always and not all at the same time.

尝试遍历几个用例，以了解图中分解的元素如何一起运行.每个用户可以进行任意数量的点击组合和互动，但是有一个共同点:

Try walking through a few use cases to understand how the elements broken down in the diagram operate together. Each user may take any number of click combinations and interactions, but there is a commonality:

1. 它们在页面加载时都输入了相同的初始化条件.
2. 他们都通过以下方式离开页面:导航到其他地方或通过提交"按钮事件.

用例#1

1. 用户从{MyPage:SQLReport}
中的一条记录中选择{MyPage:SQLReport:ThisButton}
2. 根据{MyPage:SQLReport:ThisButton} #3，将报表记录和按钮项之间关联的值传递给:{MyPage:HTML-Region:ThisItem}
3. 表单项状态已更新，并且已从初始null值更改.
4. 用户选择{MyPage:HTML-Region:ThisSubmit}按钮通知系统继续运行.
5. 提交"按钮执行定义的PL/SQL过程块:{MyPage:RunCodeBlock}

1. User chooses {MyPage:SQLReport:ThisButton} from one of the records in {MyPage:SQLReport}
2. According to {MyPage:SQLReport:ThisButton} #3, the value associated between the report record and the button item is passed to: {MyPage:HTML-Region:ThisItem}
3. The form item state has been updated and changed from the initial null value.
4. User selects {MyPage:HTML-Region:ThisSubmit} button to inform the system to continue on.
5. The submit button executes the defined PL/SQL procedure block: {MyPage:RunCodeBlock}

用例2

1. 用户进入页面并查看在{MyPage:SQLReport}区域中显示的结果.
2. 用户认为不需要其他输入，然后选择{MyPage:HTML-Region:ThisSubmit}按钮通知系统继续运行.
3. (注意:此时，表单项{MyPage:HTML-Region:ThisItem}的状态尚未从初始null值更改...选择了提交按钮之后)
4. 提交"按钮执行定义的PL/SQL过程块:{MyPage:RunCodeBlock}

1. User enters page and reviews results displayed in the {MyPage:SQLReport} region.
2. User decides no additional input is necessary and then selects the {MyPage:HTML-Region:ThisSubmit} button to inform the system to continue on.
3. (a note: the state of form item {MyPage:HTML-Region:ThisItem} has not been changed from the initial null value at this point... after the submit button has been selected)
4. The submit button executes the defined PL/SQL procedure block: {MyPage:RunCodeBlock}

用例#3

1. 用户从{MyPage:SQLReport}
中的一条记录中选择{MyPage:SQLReport:ThisButton}
2. 根据{MyPage:SQLReport:ThisButton} #3，将报表记录和按钮项之间关联的值传递给:{MyPage:HTML-Region:ThisItem}
3. 表单项状态已更新，并且已从初始null值更改.
4. 用户从{MyPage:SQLReport}中的一条记录中的不同选择项中选择{MyPage:SQLReport:ThisButton}.
5. 根据{MyPage:SQLReport:ThisButton} #3，将报表记录和按钮项之间关联的值传递给:{MyPage:HTML-Region:ThisItem}
6. 表单项状态已从步骤(2)中存储的初始值进行了更新和更改.
7. 用户选择{MyPage:HTML-Region:ThisSubmit}按钮通知系统继续运行.
8. 提交"按钮执行定义的PL/SQL过程块:{MyPage:RunCodeBlock}

1. User chooses {MyPage:SQLReport:ThisButton} from one of the records in {MyPage:SQLReport}
2. According to {MyPage:SQLReport:ThisButton} #3, the value associated between the report record and the button item is passed to: {MyPage:HTML-Region:ThisItem}
3. The form item state has been updated and changed from the initial null value.
4. User chooses {MyPage:SQLReport:ThisButton} from a different selection from one of the records in {MyPage:SQLReport}.
5. According to {MyPage:SQLReport:ThisButton} #3, the value associated between the report record and the button item is passed to: {MyPage:HTML-Region:ThisItem}
6. The form item state has been updated and changed from the initial value stored in step (2).
7. User selects {MyPage:HTML-Region:ThisSubmit} button to inform the system to continue on.
8. The submit button executes the defined PL/SQL procedure block: {MyPage:RunCodeBlock}

每种情况之间的差异应说明为什么一个用例与另一个用例相比，依赖值(ThisItem，或更具体地说，页面项P4550_REQUESTOR)为空.

The difference between each case should illustrate why the dependent value (ThisItem, or more specifically, page item P4550_REQUESTOR) is null in one use case vs. the other.

我使用的表称为STAR_EMPS.它类似于EMP表，但只有三列:ename，deptno和salary.尽管它不是很重要，但这是我用来填充STAR_EMPS的数据集:

The table I used is called STAR_EMPS. It is similar to the EMP table but has only three columns: ename, deptno and salary. Although it is not super important, this is the data set I used to populate STAR_EMPS:

我使用了一个名为STAR_EMPS_LOG的简单两列表来捕获成功执行的过程调用的输出.您只用一列就可以完成相同的操作，但是我想要一个顺序ID来跟踪每个事件的记录顺序-用于运行多个测试用例.该过程是此页面上保留的几个已定义过程之一:

I used a simple two-column table named STAR_EMPS_LOG for capturing the output of a successfully executed procedure call. You could accomplish the same with just one column, but I wanted a sequential id for tracking the order each event was recorded- for running multiple test cases. The procedure is one of several defined processes kept on this page:

包含在:{MyPage:RunCodeBlock}中:

contained in: {MyPage:RunCodeBlock} is below:

    DECLARE
      -- output from this procedure will be recorded in the star_emps_log
      -- table.  {MyPage:RunCodeBlock}

      mycelebrity  star_emps.ename%TYPE:= :P17_CELEBRITY_NAME;
      mylogmessage star_emps_log.log_message%TYPE;

    BEGIN
      -- Conditional; changes message based on the value set for the
      -- page item.

         if mycelebrity is null then
         mylogmessage:= 'No button was pressed on the previous page.';
     else 
         mylogmessage:= 'The user selected: ' || mycelebrity ||
         ' from the report list.';
     end if;

     -- populate value from the page item.
        INSERT INTO star_emps_log (log_message)
           VALUES (mylogmessage);
        commit;

    END;

这是设置页面布局的方式:

This is how the page layout was set up:

>

1. 在您的示例中，我创建了一个{MyPage:SQLReport}区域及其支持元素. SQL报表代表针对源数据表的查询.
2. {MyPage:Form}已被 重命名 到{MyPage:HTML-Region}.
3. {MyPage:SQLReport}由SQL查询定义，还有一个模拟列用作放置编辑"按钮的占位符.
4. {MyPage:SQLReport:ThisButton}按钮详细说明如下:

两个页面"过程:过程"和分支"需要与引用按钮触发项的相同设置相链接.

The TWO Page processes: PROCESS and BRANCH need to be linked with the same settings referencing a BUTTON triggering Item.

运行三种建议的方案以开始使用.验证系统是否正确解释了请求.这是页面布局的样子:

Run through the three suggested scenarios to get started. Verify that the system is interpreting the requests correctly. This is what the page layout looks like:

系统上的两个进程的定义在前面的讨论中没有提到，可能会解决我们眼前的原始问题:

The two processes on the system have a definition that wasn't mentioned in previous discussions may solve our original problem at hand:

这是一件好事，一旦分解，这将是一件微不足道的案件.此处描述的图表绘制方法应可扩展到复杂程度不同的其他Apex应用程序.远离代码，限制术语并尝试在没有实际代码的情况下描述系统和过程具有相当大的实用性.如果这种方法有助于您应对自己的Oracle Apex设计挑战，请确保分享任何故事.

It is a good thing this turns out to be a trivial case once broken down. The diagramming method described here should scale to other Apex applications of varying complexity. There is considerable utility in stepping away from the code, locking down on terminology and trying to describe systems and processes without actual code. Please be sure to share any stories if this approach helps with your own Oracle Apex design challenges.

前进！

这篇关于Application Express:匿名PL/SQL块和绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！