Archive for March, 2007

Tool to replace bind variables with v() syntax

March 24, 2007 4 comments

On quite a few occasions I’ve had a SQL query in ApEx such as:

FROM persons

And needed, for one reason or another, to replace all references to my items which are made using the bind variable syntax [e.g. :MY_ITEM] with the v() syntax [e.g. v(‘MY_ITEM’)]. For short queries like the one above, it doesn’t take long to do this by hand. For longer queries, however, this can be a pain.

So I made a (very) simple little tool using regular expressions and ApEx to do this automatically which can be found here:

Categories: ApEx, PL/SQL

Hide/Show columns only when exporting to .CSV

March 24, 2007 6 comments

This is a little trick that doesn’t seem that widely known. When using the “Export to Excel” functionality in ApEx to allow the generation of a .CSV file from a report region, you may want for certain columns not to be included in the .CSV. Equally, you may want certain columns to be included in the .CSV that aren’t visible in the rendered report region itself.

This is easy to do…
For those columns that you want to appear in the report region ONLY, set their condition to type “PL/SQL Expression” and the condition itself to:

WWV_FLOW.g_excel_format = false

And for those columns that you want to appear in the .CSV ONLY, set their condition to type ‘PL/SQL Expression” and the condition itself to:

WWV_FLOW.g_excel_format = true

And that’s it. You can see an example here:

Categories: ApEx

Ajax: Hello World

AjaxThere are lots of pre-built javascript functions you can get your hands on which offer Ajax functionality (such as ApEx’s htmldb_Get() function). On the one hand, these are great for productivity because they remove much of the complexity of writing Ajaxified javascript. On the other, they can make it very difficult to understand the basic underlying concepts: “What actually happens with all this Ajax stuff?”

Initially, to get a handle on what Ajax “does” and how, in code, it does it, I found it very useful to write the most basic function I could, working on the principle that one good example is as good as a hundred lines of explanation. So here it is, an Ajaxified “Hello World”.

You can see it in action here:

And the code:

function getXmlHttpRequestObject() {
if (window.XMLHttpRequest) {
return new XMLHttpRequest(); //Not IE
} else if(window.ActiveXObject) {
return new ActiveXObject("Microsoft.XMLHTTP"); //IE
} else {
alert("Your browser can't handle AJAX type stuff. Better upgrade to F1r3foks.");

function sayHello() {
if (receiveReq.readyState == 4 || receiveReq.readyState == 0) {'GET','hello_world.txt',true);
receiveReq.onreadystatechange = handleSayHello;

function handleSayHello() {
if (receiveReq.readyState == 4) {
document.getElementById('receiveDiv').innerHTML = receiveReq.responseText;

var receiveReq = getXmlHttpRequestObject();

<h3>Ajax Hello World</h3>
<a href=”javascript:sayHello();”>[ Hello World! ]</a>

<div name=”receiveDiv” id=”receiveDiv” class=”textDiv1″>

Categories: Ajax

Using CAST and COLLECT to aggregate strings

I think this is nifty:

SQL> select ename, deptno from emp;

---------- ----------

---------- ----------

14 rows selected.

SQL> create type table_of_names as table of varchar2(20);
2 /

Type created.

SQL> create or replace function to_string(p_table_in table_of_names)
2 return varchar2
3 is
4 l_return_string varchar2(2000);
5 begin
6 for x in 1..p_table_in.count
7 loop
8 l_return_string:=l_return_string||p_table_in(x);
9 if x <> p_table_in.count then
10 l_return_string:=l_return_string||',';
11 end if;
12 end loop;
13 return l_return_string;
14* end to_string;
SQL> /

Function created.

SQL> select to_string(cast(collect(ename) as table_of_names)) from emp
2 where deptno = 20;



Oracle Documentation on CAST: Documentation on COLLECT:

Categories: PL/SQL

Jumping directly to a detail page when a search returns just one result

Application Express Logo

It’s common to build a custom form and report in ApEx to allow users to search data. If the search returns just one result, it might, depending on the nature of the application, be sensible and more intuitive for the user to be taken directly to the details for that one hit rather than being shown a list of Search Results containing just one entry and then having to click that one entry to see its details. This is a method for doing just that: Jumping straight to the details for an entry if it is the only one returned by a search.

1) Add a hidden item to your Search Results page called: PX_SINGLE_MATCH_PERSON_ID

2) If the SQL for your query is currently something like:

SELECT person_id
, surname
, first_name
, city
, score(1)
FROM persons
WHERE contains(concat,:PX_SEARCH_STRING,1) > 0

Change it to be something like:

SELECT '<input type="hidden" id ="thePersonId" value="'||person_id||'">'||person_id
, surname
, first_name
, city
, score(1)
FROM persons
WHERE contains(concat,:PX_SEARCH_STRING,1) > 0

Essentially, we just need a HTML form item of some kind with a known ID to hold the primary key ID of the row returned so that in the next step we can reference it with javascript.

3) Add the following to the Region Footer of your Report Region (again on your Search Results page):

<script language="javascript">
if (#ROWS_FETCHED# == 1) {
$x('PX_SINGLE_MATCH_PERSON_ID').value = $x('thePersonId').value;
doSubmit('SINGLE_MATCH_FOUND');} </script>

4) Create a branch of type: Branch to Page or URL. Construct the URL it goes to (whatever your “Person Detail” page is. You can reference &PX_SINGLE_MATCH_ID. to set whatever item you need to on your “Person Detail” page).

E.g. If your “Person Details” page is page 10 and the item on page 10 which holds the unique ID for the person’s details to be viewed is :P10_PERSON_ID then your branch URL would be something like:


Make this branch conditional upon :REQUEST = ‘SINGLE_MATCH_FOUND’

That’s it.
You could achieve the same result in different ways (e.g performing a count on the same SQL query then conditionally redirecting). The advantage of the way outlined above, from a performance point of view, is that the SQL query never needs to be run twice.

Related forum thread here:

Categories: ApEx

ApEx Colour Picker Component

March 23, 2007 1 comment

Colour PaletteThis is a colour picker component for Apex that I’ve put together. When a user clicks a colour from the colour-picker, the popup window is closed and the hex value of the colour chosen is returned into the Apex item of the developer’s choosing.

(Update: Apex 3.0 now comes with a colour picker component “out-0f-the-box” so it renders this one useful only if you’re still on a pre- 3.0 release or if you want an alternative.)

See it in action here:

To use this component, you need to do the following:

  1. Download the code for the PL/SQL function from here. Copy all the code from this file, paste it into SQL Workshop and click “Run”.
  2. From SQL Workshop type: “GRANT EXECUTE ON colour_picker TO PUBLIC” and click “run”.
  3. Save the Colour Picker icon below (right click, save image) as cpicker.jpg. Go to shared components, images within Apex and upload the image as a workspace image (no associated application).
  4. Within Apex go to Shared Components > User Interface > Shortcuts.
    Create a shortcut of type HTML TEXT and give it a name of CPICKER.
    Set its source to be:

    <a href="javascript:popUp2('&OWNER..colour_picker?p_recv_item=#CURRENT_ITEM_NAME#', 190, 435)">
    <img src="#WORKSPACE_IMAGES#cpicker.jpg" alt="Colour Picker" title="Colour Picker" hspace="2"/>
  5. Then, for each page item that you want a colour picker put the following in its Post Element Text:”CPICKER”(Exactly as above, including quotes and in uppercase.)
Categories: ApEx