oracle apex值列表-选择值并在下一个字段的SQL查询中使用 [英] oracle apex list of values - select value and use in SQL query in next field

问题描述

我正在将Apex 4.0.2与Oracle XE 11一起使用.

I'm using Apex 4.0.2 with Oracle XE 11.

我有一个窗体，其中从值列表中设置了字段P200_CARD_TYPE.我想要发生的是使用在设置仅显示"字段P200_DESC

I have a form on which a field, P200_CARD_TYPE, is set from a List of Values. What I want happen is to use that value I select in a query that sets a "Display Only" field, P200_DESC

在P200_DESC的源"部分中，我将源类型"设置为"SQL查询":

In the Source section for P200_DESC, I have "Source Type" set to "SQL Query":

SELECT CARD_DESC
  FROM CARDTYPE
 WHERE card_type = :P200_CARD_TYPE;

我遇到的问题是使Apex以这种方式运行. 对于P200_CARD_TYPE，如果我将值更改时的页面操作"设置设置为重定向并设置值"，则P200_CARD_TYPE变量确实被设置了(我将其写到表中)，但是

The problem I have is getting Apex to behave in this way. For P200_CARD_TYPE, if I have "Page Action when value changed" settings to "Redirect and Set Value", the P200_CARD_TYPE variable does appear to be set, (I write it out to a table) but

1. 它不会保留在字段显示中(返回到我的空显示设置)
2. P200_DESC无法识别P200_CARD_TYPE绑定变量

1. It does not remain in the field display, (goes back to my null display setting)
2. P200_DESC does not recognize the P200_CARD_TYPE bind variable

如果在P200_CARD_TYPE上具有更改值时的页面操作"设置，则设置为无，我的选择将保留在屏幕上，但我无法坚持到表，并且P200_DESC似乎仍然无法识别它.

If on P200_CARD_TYPE, I have for "Page Action when value changed" setting so of none, my selection is retained on screen but I cannot persist to the table and P200_DESC still doesn't seem to recognise it.

我会以为一旦从LOV中进行选择，就会设置P200_CARD_TYPE，并且可以在其他地方使用它.

I would've thought once I've made my selection from the LOV, P200_CARD_TYPE is set and I can use it elsewhere.

推荐答案

似乎您尚未正确理解会话状态.这种问题经常在此处和OTN顶点论坛上不断出现.

It seems that you do not yet understand session state correctly. This sort of question keep regularly popping up both here and on the OTN apex forums.

来自文档:

HTTP是最常传递HTML页面的协议，它是 无状态协议. Web浏览器仅连接到服务器 下载完整页面所需的时间.另外，每个 服务器将页面请求视为独立事件， 与先前发生的任何页面请求无关，或者可能与 将来发生.要访问在页面上一页上输入的表单值 在后续页面中，这些值必须存储为会话状态.甲骨文 Application Express透明地维护会话状态并提供 能够从中获取和设置会话状态值的开发人员 应用程序中的任何页面.

HTTP, the protocol over which HTML pages are most often delivered, is a stateless protocol. A web browser is only connected to the server for as long as it takes to download a complete page. In addition, each page request is treated by the server as an independent event, unrelated to any page requests that happened previously or that may occur in the future. To access form values entered on one page on a subsequent page, the values must be stored as session state. Oracle Application Express transparently maintains session state and provides developers with the ability to get and set session state values from any page in the application.

在您的情况下，这意味着您认为从该LOV中选择一个值会更改该项目的会话状态.它不是.您在客户端选择一个值，该值仅在以某种方式推送到服务器之前才在那里可用.

What this means in your case is that you thought that choosing a value from that LOV would alter the session state of that item. It does not. You choose a value on the client side, and that value will only be available there until it is pushed to the server somehow.

这对于理解顶点至关重要！在继续之前，您需要了解这一点，因为您将再次遇到这种情况.

This is crucial to understand in apex! You need to understand this before you move on, because you will encounter situations like this again.

因此，现在我们已将其抛在脑后，让我们继续解决该问题的方法.

So now we have that behind us, let's move on to how you could remedy this.

将值推入服务器的最基本方法是什么?执行页面提交.所有项目的会话状态都将使用它们在客户端保存的值进行设置(有例外情况，但现在我们忽略它).
这是您选择列表操作出错的地方:您执行了重定向.重定向不是页面提交，而只是重定向.通过设置目标项目的值，您只能更改该项目的会话状态.
因此，并不是说SQL无法识别变量，而是因为它没有任何值，因此无法呈现任何内容.
当然，仅设置字段的值可能不需要您提交页面.提交可能会导致验证，分支或流程被触发.

The most basic way of pushing values to the server? Perform a page submit. The session state of all the items will be set with the values which they held on the client side (exceptions apply but let's ignore that for now).
Here is where you went wrong with your select list action: you performed a redirect. A redirect is not a page submission but just a redirect. By setting a value of a target item you only alter the session state of that one item.
So it is not that the SQL is not recognizing the variable, it's just that it has no value so nothing can be rendered.
Of course, submitting the page might not be something you want to do simply to set a field's value. Submitting might cause validations, branches or processes to fire.

这就是托尼·安德鲁斯(Tony Andrews)的建议:使用动态动作来动态获取值.例如，您可以将其设置为:

That is what Tony Andrews suggests: use a dynamic action to dynamically retrieve the value. For example, you can set it up as such:

• 将选择列表设置为值更改后不执行任何操作.
• 右键单击树形视图中的选择列表，然后选择创建动态操作"
• 作为事件，选择更改"事件
• 对于真正的操作，请选择设置值"
• 设置类型"应为"sql语句"

• 使用您也定义为desc项源的sql:

• set the select list to do nothing when the value has changed.
• Rightclick on the select list in the tree view and select "create dynamic action"
• As event choose the "Change" event
• For the true action choose "Set Value"
• The "Set type" should be "sql statement"

• Use the sql you also defined as source for the desc item:

SELECT CARD_DESC
  FROM CARDTYPE
 WHERE card_type = :P200_CARD_TYPE;

最后且非常重要:在要提交的页面项目"字段中，添加P200_CARD_TYPE.这将导致将P200_CARD_TYPE的当前值提交给服务器，从而设置其会话状态，从而使查询正常工作.

Lastly and VERY IMPORTANT: in the "Page items to submit" field, add P200_CARD_TYPE. This will cause the current value of P200_CARD_TYPE to be submitted to the server and thus set its session state, causing the query to work.

现在，当在选择列表的值发生变化，一个电话将进行到服务器/数据库和值将在说明书项目被退回并集.结果 还有更多的东西，但这应该可以使您朝着正确的方向前进.

Now when the value on the select list changes, a call will be made to the server/database and a value will be returned and set in the description item.
There is more to it all but this should set you off in the right direction.

这篇关于oracle apex值列表-选择值并在下一个字段的SQL查询中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！