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 with 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 Labels: Oracle APEX, tips
 « ... Read full posting ... »
If you ever wanted to remote debug the PL/SQL code which is called during execution of an Oracle APEX application, then have a look at the May/June issue of the Oracle Magazine. David Peake wrote an article titled The Power of Two on how to use SQL Developer to do that. On the German Oracle APEX Community Web-Site you will find a similar how-to in German. Update: In Oracle APEX 3.1.1 there will be a new debug mode called REMOTE to enable remove debugging in Oracle APEX without having to modify the application. See the related posting on the OTN Forum. Labels: Oracle APEX, Other blogs, tips
« ... Read full posting ... »
 On Friday I had to do some work on an Oracle Application Express (APEX) application of a client which I did some time ago. I had to create a new region next to an existing region on a page which had already several other regions on it. Normally no big problem, you would just use the "Column" property of the region and you are done. To make it short, it didn't work because of the layout of the template and the existing regions which are already using the "Column" property. So I had to work with the "Display Point" property of the region, to "reset/close" the HTML table of the previous regions and start with a new fresh HTML table which has it's own cells/columns, ... But what is the best "Display Point", especially if you are not that familiar with your page template position placeholders anymore?
Do I have to open the page template definition and check out the HTML code of the template and look for the #REGION_POSITION_XX# placeholders? No you don't have to!
On Friday I re-discovered a nice little feature of Oracle APEX which I'm pretty sure I have already seen when first looking at Oracle APEX, but which I totally forgot about. Every clicked on the "Flashlight" next to the "Display Point" select list? It shows you a rendered preview of your page template with the position of the different "Display Points" in your template. A nice feature!
There are so many useful little features built into the Oracle APEX Builder we are most time not aware of during our daily development work. Especially if you are working for a longer time with a tool, you think you know it and you are not that curios anymore to try out and search for features not yet used. But there are some many useful features built in which are sometimes a little bit hidden. I already wrote about some of them, like
to just name a few of them.
Do you know other nice features which are not used that often? Share your Oracle APEX Builder tips and tricks!
Labels: layout, Oracle APEX, productivity, tips
« ... Read full posting ... »
 Sometimes during development there is the requirement to "Comment out" a Process, a Validation, ... in an Oracle Application Express (APEX) application. To skip a step which doesn't work anymore, or where a user says he doesn't need that anymore but where you are sure that next week you are going to add it again... In Oracle Forms that's quite simple. You just have to rename the trigger to use underscores (eg. PRE-QUERY to PRE_QUERY) and the trigger doesn't fire anymore. But how can you do that in Oracle APEX? Sure you can comment out the PL/SQL code, if it's a PL/SQL process, but don't forget the NULL; so that you still have a valid code. But what to do for the more declarative process/validations/branches/...? You could set the "Condition Type" to "Never", but that will change probably an existing condition. And afterwards when you want to enable it again, you have to know what has been set before.
It think there is a much more elegant way to do it. It's called the "Build Option".
 - Go to Shared Component\Build Options and create a new one with the name "Commented out".
- Set the Status to "Exclude"
Now you are able to "disable" any object in your Oracle APEX application by assign the new build option with the "Configuration\Build Option" property. This property can be set for almost any object in Oracle APEX.
And the good news is, there is even a report which shows you all the objects which are "Commented Out". Have a look at the "Utilization" tab in Shared Components\Build Options.
Labels: Oracle APEX, tips
« ... Read full posting ... »
 Oracle Application Express (APEX) has a "hidden" gem (ok, there are a lot of others too) which in my opinion isn't widely used by the developers. Be honest, how often do you use the little "View" select list on page definition? You should, because it has several useful entries which can make your life easier! But let's have a quick look at the different options. EventsThat's a really useful feature for Oracle APEX beginners, because it shows you for the current page the flow of events when a page is processed or rendered. If you check the "View All" radio option it even shows you the events which would fire if you would have defined them. That helps you to identify which event you have to use so that it's executed at the right time. I think that's an invaluable feature to get used to the different events of Oracle APEX!
BTW, it also incorporates the events of page 0 and from application level. I'm not sure if that was always the case, but at least in 3.0.1.x everything is included to get a complete picture.
A screen capture of one of the pages of the ApexLib demonstration application showing the different events.
Objects
Need to know which tables, views, packages, ... are used/referenced by the current page? That option will show all of them. The function is really clever how it get the effected objects. It's creating temporary stored procedures for all the code blocks and checks the data dictionary to get the references to the different objects.
Another nice side effect of this function is that you can determine if all your PL/SQL and SQL code is compilable, because it will also show you all the code blocks where a parsing error occurred.
History
Quick info who last modified the page. Very useful in a multi developer environment.
Export
Have you screwed up the page? Use the export page feature in combination with the "As of x minutes ago" feature to get the old version back.
Enhancement request: Why not add an option "Undo" with the "As of x minutes ago" which restores the page to that point in time?
Groups
Are you using the page group feature? If you don't, you should! Create them with the "Page Groups" link in the "Tasks" sidebar of the overview of all the pages. The assignment is done in the page attributes. But back to that feature. It will allow you to quickly navigate to the other pages which are in the same group!
Referenced
Shows you all objects which are referencing this page. But be aware that it doesn't show all of them, there seems to be a bug with report column links and it will not show you the reference if you have created a manual link in an HTML region or in a SQL statement.
Last visited pages
The last few entries of the select list contain the last few visited pages for quick navigation.
So what do you think? Have I raise your attention to take a closer look at this feature so that you use it more often in your daily development?
Also see
Labels: events, history, Oracle APEX, productivity, tips, undo
« ... Read full posting ... »
Did you ever have had the requirement that a validation should only fire if the page item has been rendered (eg. if you have defined a condition or authorization)? As long as you set the "Associated Item" property in the validation to the page item you want to check, you are fine. Oracle Application Express (APEX) will do the check for you and only fire the validation if it's there. BTW, I didn't know that - really nice! :-) But if you create a page level validation it's up to you to determine if the item is there or not. So you would probably go and duplicate your condition/authorization code to the page level validation. Not good! Twice the code to maintain.
You don't have to!
There is a nice tip posted on the OTN forum by Scott Spadafo which uses the Apex_application.g_arg_names array to determine if the item is there or not.
Attached you will find a function which you can use in a PL/SQL based condition (eg. PL/SQL expression). Call it with your page item name and it will return TRUE if the item has been rendered.
CREATE OR REPLACE FUNCTION isPageItemRendered
( pName IN VARCHAR2
)
RETURN BOOLEAN
IS
vPageItemId NUMBER;
BEGIN
----------------------------------------------------------------------------
-- Get the internal id of the page item.
----------------------------------------------------------------------------
BEGIN
SELECT ITEM_ID
INTO vPageItemId
FROM APEX_APPLICATION_PAGE_ITEMS
WHERE APPLICATION_ID = WWV_Flow.g_flow_id
AND PAGE_ID = WWV_Flow.g_flow_step_id
AND ITEM_NAME = UPPER(pName)
;
EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001, 'Page item '||pName||' doesn''t exist!');
END;
----------------------------------------------------------------------------
-- If a page item is rendered the internal id is stored in a hidden field
-- called p_arg_names. During submit the values are stored into the
-- g_arg_names array by the WWV_Flow.accept procedure.
-- By checking for existence of the page item id in the array, we are able
-- to determine if APEX has rendered the item as "Saves state".
-- Note: A item which is normally enterable but which is rendered
-- "Read Only" is also considered rendered, because it still saves
-- state.
----------------------------------------------------------------------------
FOR ii IN 1 .. Apex_Application.g_arg_names.COUNT
LOOP
IF Apex_Application.g_arg_names(ii) = vPageItemId
THEN
RETURN TRUE;
END IF;
END LOOP;
--
RETURN FALSE;
--
end isPageItemRendered;
Labels: Oracle APEX, tips
« ... Read full posting ... »
 That's maybe an old tip and probably everybody already knows it, but because I just answered that question on the OraFAQ forum, I wanted to share it with you in case you didn't know. Maybe you have already noticed that in the Oracle APEX Builder at some pages a tooltip is used. For example for the icons on the Drag and Drop Layout page. This tooltip functionality can be reused in your own applications. You just have to add onmouseover="toolTip_enable(event,this,'This is a tooltip')" into the "Attributes" property of your HTML button or into the "HTML Form Element Attributes" property of your page item. In the end you can attach it to any HTML element. But I think you got the idea how it works. Labels: Oracle APEX, tips, UI improvement
« ... Read full posting ... »
In the past few weeks there have been a few interesting APEX related postings I want to share with you. Have fun reading them! Labels: Oracle APEX, Other blogs, tips
« ... Read full posting ... »
 During the DOAG SIG workshop I was asked how to create a treeview in Oracle APEX where the current selected tree node is highlighted. After a quick thought about the problem, it turned out that there is a quite simple solution for it if you combine the treeview query with some HTML code. - Create a regular tree region
- Create a form region which gets synchronized with your tree
- Change the tree query to contain the following CASE statement
SELECT EMPLOYEE_ID AS ID
, MANAGER_ID AS PID
, CASE
WHEN EMPLOYEE_ID = :P4_EMPLOYEE_ID THEN
'<span style="color:white;background-color:blue;">'||
LAST_NAME||
'</span>'
ELSE
LAST_NAME
END AS NAME
, 'f?p=&APP_ID.:4:'||:SESSION||'::NO::P4_EMPLOYEE_ID:'||EMPLOYEE_ID AS LINK
, NULL AS A1
, NULL AS A2
FROM #OWNER#.EMPLOYEES
What does it do?
When the tree is rendered the CASE compares the currently processed node (EMPLOYEE_ID) with the current active record (P4_EMPLOYEE_ID) which is displayed in the form.
If the IDs match, it wraps the LAST_NAME column with a SPAN which contains some styling information.
Quite simple, isn't it? :-)
View a working demo of the example.
Labels: APEX, Oracle, tips, Tree
« ... Read full posting ... »
Did you know that you can use the keyboard shortcut Ctrl+Return in the SQL Commands textarea of the Oracle APEX SQL Workshop application to execute the current SQL statement? That's really nice, especially if you are used to it from PL/SQL Developer/TOAD. Labels: APEX, Oracle, tips
« ... Read full posting ... »
You want to create a button or a navigation list entry where you want to open an URL in a new browser window? With plain HTML you would normally just write <a href="http://url/" target="_blank"> but how to specify the target="_blank" in Oracle APEX, where you just have the property "URL Target" which just allows you to enter the URL? Sure you could use some Javascript code to open a new window or something similar. Sounds a little bit complicated, or? There must be an easier way!
There is!
You can use the same technique as for SQL injection. Just writehttp://url/" target="_blank
into the "URL Target" property. APEX will add the closing quote after _blank for you, because it thinks the URL ends there. That way APEX generates a valid a href with a target for you.
For buttons where APEX generates a javascript:redirect('yoururl') you have to use.javascript:window.document.location.href='http://yoururl/'" target="_blank
Labels: APEX, Oracle, tips
« ... Read full posting ... »
A few days ago Lucas Jellema from AMIS posted a real nice SQL statement to generate a character based Gantt-chart. I think it's a good demonstration of the possibilities and the power of SQL. Have a look! Labels: chart, Oracle, SQL, tips
« ... Read full posting ... »
Today I read an article on Grant Rolands blog about sending a SMS from Oracle Forms through a Web-Service. I thought it would be nice to have the same in APEX and PL/SQL, too. For example if you have a system failure or get a new order in your shop... Grant is using a Web-Service provided by Esendex, but I'm sure there are others service providers available too. First I thought using the offered Web-Service and create a PL/SQL wrapper for it our use the Web-Service feature of APEX.
But I decided to go with the simple POST version which is also offered by them. Because why invoke all the Java stuff when there is also a simple version available. The APEX web-service couldn't be used, because then you would not be able to call it from PL/SQL.
The code is quite straight forward. Get a free account, replace the constant values in the example procedure and you are ready to go!
Note: If you have a proxy then you have to uncomment the proxy line. There is also a secure version available which uses HTTPS, but that requires a wallet, ... and I just wanted to demonstrate that it works. The error handling of the procedure could also be improved, but hey, it's just a demonstration. :-)
Have fun waking up your operators/DBAs in the night! :-)
To send a SMS just call the procedure withBEGIN
sendSMS('+436991812345','this is a test');
END;
Here comes the example procedure:
CREATE OR REPLACE PROCEDURE sendSMS
( pRecipient IN VARCHAR2
, pBody IN VARCHAR2
)
IS
ESENDEX_USERNAME CONSTANT VARCHAR2(40) := 'your_username';
ESENDEX_PASSWORD CONSTANT VARCHAR2(40) := 'your_password';
ESENDEX_ACCOUNT CONSTANT VARCHAR2(40) := 'your_account';
--
vRequest Utl_Http.req;
vPostText VARCHAR2(500);
vResponse Utl_Http.resp;
vResponseText VARCHAR2(2000);
vErrorText VARCHAR2(200);
BEGIN
----------------------------------------------------------------------------
-- Build text for the post action.
-- For a field description, see
-- http://www.esendex.com/secure/messenger/formpost/SendSMS.aspx
----------------------------------------------------------------------------
vPostText :=
'EsendexPlainText=YES' ||CHR(38)||
'EsendexUsername=' ||Utl_Url.escape(ESENDEX_USERNAME, TRUE)||CHR(38)||
'EsendexPassword=' ||Utl_Url.escape(ESENDEX_PASSWORD, TRUE)||CHR(38)||
'EsendexAccount=' ||Utl_Url.escape(ESENDEX_ACCOUNT, TRUE)||CHR(38)||
'EsendexRecipient='||Utl_Url.escape(pRecipient, TRUE)||CHR(38)||
'EsendexBody=' ||Utl_Url.escape(pBody, TRUE);
----------------------------------------------------------------------------
-- if you need to set a proxy, uncomment next line.
----------------------------------------------------------------------------
/* Utl_Http.set_proxy('proxy.it.my-company.com', 'my-company.com'); */
----------------------------------------------------------------------------
-- Send SMS through the Esendex SMS service.
----------------------------------------------------------------------------
vRequest := Utl_Http.begin_request
( url => 'http://www.esendex.com/secure/messenger/formpost/SendSMS.aspx'
, method => 'POST'
);
Utl_Http.set_header
( r => vRequest
, name => 'Content-Type'
, value => 'application/x-www-form-urlencoded'
);
Utl_Http.set_header
( r => vRequest
, name => 'Content-Length'
, value => LENGTH(vPostText)
);
Utl_Http.write_text
( r => vRequest
, data => vPostText
);
vResponse := Utl_Http.get_response(vRequest);
IF vResponse.status_code = '200'
THEN
Utl_Http.read_text(vResponse, vResponseText);
--
IF vResponseText NOT LIKE 'Result=OK%'
THEN
vErrorText := vResponseText;
END IF;
ELSE
vErrorText := 'HTTP status: '||vResponse.status_code||'-'||vResponse.reason_phrase;
END IF;
--
Utl_Http.end_response(vResponse);
--
IF vErrorText IS NOT NULL
THEN
RAISE_APPLICATION_ERROR(-20001, 'Sending SMS failed with '||vErrorText);
END IF;
END sendSMS;
Labels: APEX, Oracle, pl/sql, SMS, tips
« ... Read full posting ... »
Have you already used the new Flash Charts in Oracle APEX 3.0? As for SVG charts you can set them to Auto Refresh after x seconds. But it looks like that a small nice feature of the SVG charts is missing for the Flash charts. It doesn't automatically show when the last refresh occurred. But how can we add that?At the first look it doesn't seem to be that easy, because no full page refresh occurs, just the Flash chart is updated with an AJAX call. But at the second look it isn't that complicated if you know the dynamic features of JavaScript. Oracle APEX creates a JavaScript timer which calls the APEX JavaScript library function apex_RefreshChart every x seconds.
<script type="text/javascript" language="javascript">
function chart_r1258481816185669517_InitRefresh(pNow) {
setTimeout("chart_r1258481816185669517_InitRefresh(true)",5000);
if (pNow){apex_RefreshChart (1, '1258481816185669517', 'en-us');}
}
apex_SWFFormFix('c1258481816185669517');
addLoadEvent(chart_r1258481816185669517_InitRefresh(false))
</script> But how can we intercept the apex_RefreshChart so that we can update a "Last Refresh" timestamp on the page?
It's quite easy if you know how! :-)
In JavaScript a function can be accessed and created like a variable. For example withvar vOldFunction = apex_RefreshChart; you get a reference to the function apex_RefreshChart which you now can also call with vOldFunction. Nice, isn't it? But what has this to do with the above problem?
It allows us replace the existing apex_RefreshChart with a new version where we do our additional stuff, but which also calls the original function to get the original functionality APEX provides to refresh the chart.
- Open the Region Definition of your Flash chart.
- Go to Region Source and add the following JavaScript code in front of the existing <object classid=...
<script type="text/javascript">
var vOriginal_apex_RefreshChart = apex_RefreshChart;
apex_RefreshChart = function(page_id,region_id,browser_lang)
{
document.getElementById("last_refresh").innerHTML = Date();
vOriginal_apex_RefreshChart(page_id, region_id, browser_lang);
};
</script> - Go to the Region Footer and add the following HTML code which will be a placeholder for "Last Refresh" timestamp.
<br>
<center>
Chart will refresh every 5 sec.<br>
Last refresh: <span id="last_refresh"></span>
</center>
What does it do?
It will overwrite the existing APEX library function apex_RefreshChart with a new version which also sets the current timestamp in the span element identified by "last_refresh". BTW, if you don't like the default output of Date(), check out the different JavaScript resources on the Internet to get other formating options.
The above code is also a good example which demonstrates how you can extend/replace the existing JavaScript functions of APEX to add your own behavior.
Want to see an example? Visit my example page.
Labels: APEX, chart, Oracle, tips
« ... Read full posting ... »
Did you know that the Oracle APEX distribution contains some nice little tools? The command line export tool is probably well known, John Scott blogged about it some time ago. But did you know that there are two additional scripts which can get handy sometimes? - apxxepwd.sql in the root directory. This script can be used to set the password for the ADMIN user of APEX. Just in case if you don't remember it anymore. Jornica, thanks for the tip!
- \utilities\reset_image_prefix.sql to change the image prefix path for an existing APEX installation.
Labels: APEX, Oracle, password, tips
« ... Read full posting ... »
Have you ever asked yourself how Oracle APEX maps the columns of a Tabular Form to the Apex_Application.g_fxx (alias Htmldb_Application.g_fxx, alias WWV_Flow.g_fxx) array? I think most developers use one of the following methods to get the correct mapping:
- Look at the generated HTML output of the page and get the name attribute of the INPUT element
- Use eg. Firebird to inspect the column field and get again the value set for the name attribute.
But did you know that the algorithm which APEX is using is quite simple?
APEX takes your Tabular Form select statement and based on the order in which the columns are appearing in the select statement (not in the order you see on the Report Attributes tab!) it starts mapping- if the column is a "Saves state" column, that means it is rendered as HTML field and
- if the condition/authorization matches
it will increase the field counter.
Note: If you have a row selector, this column will always map to g_f01
Example:
SELECT EMPNO, EMPNO AS EMPNO_DISPLAY, FIRST_NAME, LAST_NAME, HIRE_DATE FROM EMP
Let's assume that all fields except of EMPNO_DISPLAY are rendered as fields and LAST_NAME has a condition which isn't TRUE.
EMPNO = g_f01
EMPNO_DISPLAY = not mapped
FIRST_NAME = g_f02
LAST_NAME = not mapped because of condition
HIRE_DATE = g_f03
In the case if we have a Row Selector.
Row-Selector = g_f01
EMPNO = g_f02
EMPNO_DISPLAY = not mapped
FIRST_NAME = g_f03
LAST_NAME = not mapped because of condition
HIRE_DATE = g_f04
If you don't want to hassle around with that mapping, where you also have to consider the conditions/authorizations, ..., you may should take a look at the Plug & Play Tabular Form Handling of the ApexLib Framework.
Labels: APEX, Oracle, tabular form, tips
« ... Read full posting ... »
 Maybe you have already read about Yahoo Pipes which you can use to build your own mashups. Today, I did a quick test. It has a nice Web/JavaScript interface and is really easy to use. After I finished my first "Pipe" I thought about how to integrate the output into Oracle APEX. It isn't really hard to do that, because the Oracle database has already everything build in to accomplish that.
1. Create your own pipeI created one which gets different RSS feeds, translates them with Babelfish into German and filters all which have the keyword APEX in the text. The German into which it is translating doesn't really make sens, but that's a other story... :-)
2. Run your pipeRun your pipe (you don't have to publish it) and look for "Tools: Get as RSS" at the bottom of the page. Get the URL, eg it looks like http://pipes.yahoo.com/pipes/0HpUBCq52xGdaeRPfOgC8A/run?_render=rss
3. Create a report in APEXCreate a SQL based report with the following SQL statement.SELECT EXTRACTVALUE(VALUE(ITEM), '/item/link') AS LINK
, EXTRACTVALUE(VALUE(ITEM), '/item/title') AS TITLE
, TO_DATE
( SUBSTR(EXTRACTVALUE(VALUE(ITEM), '/item/pubDate'), 6, 20)
, 'DD Mon YYYY HH24:MI:SS'
) AS PUBLISH_DATE
FROM TABLE
( XMLSEQUENCE
( EXTRACT
( HTTPURITYPE('http://pipes.yahoo.com/pipes/0HpUBCq52xGdaeRPfOgC8A/run?_render=rss').getXML()
, '/rss/channel/item'
)
)
) ITEM
ORDER BY 3 DESC NULLS LAST
Replace the URL with your stored URL
About the SQL Statement:
The Oracle build-in HTTPURITYPE is really powerful, it will retrieve the content of an URL and if you specify getXML, automatically convert the XML output into an Oracle XMLTYPE. As soon as it is a XMLTYPE you can use all the XML build-ins to extract the data from the XML stream.
Mo |
