q Function in APEX: Escape Single Quotes:

Reference: http://www.talkapex.com/2009/03/q-function-escape-single-quotes.html 

Instead of writing out a long description here's an example:
   
DECLARE
  v_sql VARCHAR2 (255);
  v_result VARCHAR2 (255);
BEGIN
  v_sql := 'select ''hello'' into :a from dual';

  EXECUTE IMMEDIATE v_sql
               INTO v_result;

  DBMS_OUTPUT.put_line (v_result);
END;

Notice how I had to put 2 single quotes around "Hello" to escape the single quote characters?

Now using the q function I don't need to do that:
   
DECLARE
  v_sql VARCHAR2 (255);
  v_result varchar2(255);
BEGIN
  v_sql := q'!select 'hello' into :a from dual !';

  EXECUTE IMMEDIATE v_sql
               INTO v_result;

  DBMS_OUTPUT.put_line (v_result);
END;

Notice now how "Hello" is wrapped as it would appear if it were not in variable definition function?

This can save you a lot of time by avoiding having to escape single quotes in strings!

Saving Current Values with Cascading LOV's:

Saving Current Values with Cascading LOV's:

Say you've got two LOVs...STATES and CITIES. They both default to 'ALL' and 'ALL'. Since CITIES is dependent on STATES, as soon as STATES is changed, CITIES is blanked out. What should happen is that CITIES gets re-evaluated as in the following example... let's say STATES is ALL and CITIES is "Houston". If one then changes STATES to "Texas", CITIES should remain "Houston" as that is a valid value for CITIES.

So basically, is it possible to maintain the selected value of an item if that same value exists in the list of values after refreshing?

Click here to see the demo but continue reading to learn how it all works…

There are a three main events you need to be concerned with when it comes to cascading selects:

• change
• apexbeforerefresh
• apexafterrefresh

The change event is a standard part of JavaScript and the DOM. This event fires when the user manually changes the value of the select list but can also be triggered programmatically via JavaScript. The apexbeforerefresh and apexafterrefresh events are custom events in the APEX framework. They fire just before and just after AJAX requests refresh something on the page. The events work with many items and regions that utilize this technology.

In this example we have two select lists: parent and child. If you change the value of the child select list then the change event will fire and that’s it. But if you change the value of the parent select list a lot more happens to the child select. Here are some of the highlights:

1. The current LOV values are cleared out
2. The apexbeforerefresh event is triggered
3. An AJAX request brings back new values. This only happens if
   1. optimize refresh is set to false
   2. optimize refresh is set to true and all parent items are not null
4. The apexafterrefresh event is triggered
5. The change event is fired

Now, knowing all of this, how can we utilize the sequence of events to solve the original problem of keeping selected values? The answer lies in creating two dynamic actions.
The first dynamic action will store the current value of the select list so that we can access it later. This will typically happen when the change event fires but it will also happen when the page first loads. Here’s how to create the first Dynamic Action:

1. Right click the child select list and select Creation Dynamic Action.
2. Select Advanced.
3. Click Next >.
4. Enter a name for the first Dynamic Action.
5. Click Next >.
6. Set Event to Change.
7. Click Next >.
8. Set Action to Execute JavaScript Code.
9. Set Code to: $(this.triggeringElement).attr('data-last-value', $(this.triggeringElement).val());
10. Click Next >.
11. Click Create.

The second Dynamic Action will take advantage of the apexafterrefresh event and access the previously stored value. That value will then be used to look through the new options and if a match is found it will be selected. Here’s how to create the second Dynamic Action.

1. Right click the item (again) and select Create Dynamic Action.
2. Select Advanced.
3. Click Next >.
4. Enter a name for the second Dynamic Action.
5. Click Next >.
6. Set Event to After Refresh.
7. Click Next >.
8. Set Action to Execute JavaScript Code.
9. Set Code to: $(this.triggeringElement).children('option[value="' + $(this.triggeringElement).attr('data-last-value') + '"]').attr('selected','selected');
10. Click Next >.
11. Click Create.

So two lines of actual code, not bad at all! Hope this is useful....

Hidden option for Static List of Values in APEX:

Did you know that the STATIC and STATIC2 keyword used to define Static List of Values for Oracle Application Express (APEX) contains an undocumented option?

The online help documents the usage which is mentioned below

STATIC[2]:Display Value[;Return Value],Display Value[;Return Value]

But what to do if one of your values contains the semi colon or the comma in the text? You will get a problem with the predefined separators!

In such a case you can use

STATIC[2](lov-entries-sep,display-return-sep) : Display Value[<display-return-sep>Return Value]
<lov-entries-sep>Display Value[<display-return-sep>Return Value]

For example:
STATIC2(~,*):Cat, Dog*1~Nemo, Shark*2