oracle apex list of values - select value and use in SQL query in next field

2
votes

I'm using Apex 4.0.2 with Oracle XE 11.

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

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;

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. 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

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.

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

1
oracle-apex
Have you considered using a dynamic action instead? Easier and more efficient that reloading the page. – Tony Andrews
The problem I have is actually being able to use the value I select for P200_CARD_TYPE for P200_DESC. For my source for P200_DESC I have SELECT CARD_DESC FROM CARDTYPE WHERE card_type = :P200_CARD_TYPE; – Jon T
The problem I have is actually being able to use the value I select for P200_CARD_TYPE for P200_DESC. For my source for P200_DESC I have SELECT CARD_DESC FROM CARDTYPE WHERE card_type = :P200_CARD_TYPE; which I thought should be fine. I have set P200_DESC's Source Used to "Always, replacing any existing value in session state" and Source Type is "SQL query". The only reason why I mentioned reloading the page was I seemed to be able to access the value that way but I think that's a red herring – Jon T

1 Answers

5
votes

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.

From the docs:

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.

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.

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.

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

  • 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;
  • 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.