Image Prefix changes in Oracle Application Express 4.2.2Most often, patch sets for Oracle Application Express are used to correct defects (i.e., fix bugs) identified in a release. It's rare for features to be introduced or behavior to change. But in the case of Oracle Application Express 4.2.2 and later, there is a change in behavior that is worth discussing, namely, the change in behavior to the Image Prefix application attribute. This has been raised by Oracle Support three times in the past couple weeks, so we have certainly created some confusion amongst our customers. In a Nutshell- In Application Express 4.2.2, if your image prefix application attribute equals the instance-wide image prefix attribute, it will be set to null in your application.
- At runtime, if the image prefix application attribute is null, the APEX engine will use the instance-wide image prefix attribute as your application's image prefix.
- This was done to facilitate ease of movement of applications across varying instances.
BackgroundEvery instance of Oracle Application Express has an instance-wide image prefix. This image prefix is used by Oracle Application Express to specify the directory location of the Application Express images directory. This image prefix ultimately correlates to a virtual directory or equivalent on the Web server, and this virtual directory directly maps to the 'images' directory shipped with an APEX distribution and staged on the Web server. This image prefix i s specified at APEX installation time, but it can be adjusted later if necessary. This image prefix is usually always /i/. For example, if you go to the Login page of apex.oracle.com and look at the source of that page, you'll see numerous references to images and files of the form /i/... The HTML for the logo in the upper left is from the HTML:
<img alt="Application Express" height="20" src="/i/apex_ui/apexlogo.png" width="280" />
There is a virtual directory on the Web server of apex.oracle.com, which maps /i/ to a virtual directory. This virtual directory maps to a physical directory on the Web server, which contains the entire 'apex/images' directory from the APEX distribution. The 'apex/images' directory changes from release to release. The 'apex/images' directory from an APEX 4.1 release is only usable with APEX 4.1. The 'apex/images' directory fron an APEX 4.2 release is only usable with APEX 4.2. You can try and use it with APEX 4.1, but you'll definitely see odd results, if anything at all. The image prefix is also an attribute of the user interface of an application. This image prefix is used during the runtime of the APEX application, and is used to determine the image prefix for that application. 99% of the time, this image prefix application attribute equals the instance-wide image prefix (typically, /i/), but it could be different too. It could even be wrong! If you change your image prefix attribute to '/foobar/' and then run your application, you'll see that all the references to images and static files in the HTML source of your application all reference '/foobar/'. I suspect you'd get a page with broken images and references everywhere, and in the worst case, you may get what appears to be a blank page.
Using the Image PrefixNow here's where it gets interesting. Even though the documentation recommends an image prefix of /i/, it isn't absolutely mandatory. So let's say that for my APEX 4.1 instance, I use an image prefix of /i_old/, and for my APEX 4.2.2 instance, I use an image prefix of /i_new/. And also let's assume that these instances use totally distinct Web servers. If I export an application from my APEX 4.1 instance and import it into my APEX 4.2.2 instance, I will need to update the image prefix application attribute before I can run it in the APEX 4.2.2 instance. Why is that? Because my application image prefix is /i_old/, and that isn't defined on the Web server for my APEX 4.2.2 instance. For those who have participated in the Application Express Early Adopter program before, you'll remember that the image prefix was /i_ea/ or something like that. When you imported your existing APEX application into the Early Adopter instance, you had to adjust the image prefix of your application to be equal to the instance-wide image prefix, /i_ea/. For enterprises which host multiple APEX instances via one Web server, they have an equally difficult problem. They may have image prefixes of /i40/, /i41/, /i42, and wish to freely move and test applications without having to adjust the image prefix attribute every time they import an application. It's cumbersome and unnecessary. What has changed in APEX 4.2.2 and LaterUpon upgrade to Application Express 4.2.2 or application of the 4.2.2 patch set, the image prefix attribute of applications will be set to null if their value is equal to the instance-wide image prefix. In the simplest example, if the instance-wide image prefix is /i/ and your application's image prefix attribute is /i/, the application attribute will be set to null in 4.2.2. If you try to edit the image prefix application attribute and set it to the instance-wide image prefix (e.g., /i/), it won't be saved and it will be null. Only if you change it to something other than the instance-wide image prefix will a non-null value be saved. The utility script to adjust the image prefix instance-wide setting, apex/utilities/reset_image_prefix.sql, now only adjusts the instance setting and nothing more. No image prefix application attributes are touched. It's unnecessary, because a null image prefix application attribute means "use the setting of the APEX instance". Now, if you use a non-standard image prefix for your APEX instance, you can import your application into any other instance without having to be aware of what the image prefix is on the target instance. Now with that said.....I wish you a nice Memorial Day. APEX Conditions and PerformanceThanks to a tweet of Scott Wesley (@swesley_perth), I stumbled upon this blog post by Tony Andrews. In that blog Tony lists a few examples of APEX Conditions and concludes that the declarative approach must be the best way of doing it. Although his assumptions and conclusions makes a lot of sense, I wondered whether it was actually true and what the difference in performance would be. So I created a very similar test myself and take a look at the debug output. And here are the results (in microseconds) ...
Type
|
Statement
|
Run 1 |
Run 2 |
| Declarative |
...<nothing here>... |
653
|
667
|
| PL/SQL Expression |
"...Execute Statement: begin wwv_flow.g_boolean := :P3_JOB_ID = 'AD_PRES'; end;” |
1533
|
1823 |
| SQL Exists |
...Execute Statement: select count(*) from sys.dual where exists (SELECT NULL FROM DUAL WHERE :P3_JOB_ID = 'AD_PRES') |
1684 |
2023 |
| PL/SQL Function |
"""...Execute Statement: declare function x return boolean is begin begin"
return :P3_JOB_ID = 'AD_PRES';
end;
" return null; end; begin wwv_flow.g_boolean := x; end;"" |
2537 |
1528 |
So from the table above you can safely conclude that Tony's assumptions and conclusions are actually true. Using a declarative condition is on average two to three times faster than one of the other options. And although it are just very small numbers, when you have a lot of conditions and a lot of users hitting the page - it might add up in the end!
When can't use the declarative approach and you have to choose between one of the others, it doesn't really seem to matter ... in this case. But you have to check that in your situation yourself! PDF Reports with APEX at NYOUG The New York Oracle User Group Summer Meeting takes place on June 5th at St. John's University, right next to the World Trade Center in Downtown Manhattan. This looks like it’s going to be a very interesting meeting for Oracle DBAs and Oracle Developers. There are two session scheduled covering the Oracle Database 12c and also two APEX sessions. There’s going to be a session on building mobile apps with APEX, which looks to be very hands-on. And I’ll be showing how to build custom PDF reports with APEX. I’ll walk you step by step through creating the report in APEX, downloading the XML, creating a report layout with tools like Stylevision, loading the layout back into APEX and associating the layout with your report. If you have a laptop running APEX 4.2.2 and the APEX Listener 2.0.2, you’re welcome to bring it and follow along – you will need at least a trial version of either Altova Stylevision or Stylus Studio.
Details about this event can be found on the NYOUG website:
ODTUG Hyperion SIG NewsletterA Note from the Hyperion SIG President By Alice Lawrence, Hyperion SIG President and Eric Helmer, Editor The Hyperion SIG is busy looking for ways to bring value to you our EPM/Hyperion members. We hope that you will take a few moments to read through this newsletter and find some useful Dynamic Sampling – 2I’ve written about dynamic sampling in the past, but here’s a little wrinkle that’s easy to miss. How do you get the optimizer to work out the correct cardinality for a query like (the table creation statement follows the query):
select count(*)
from t1
where n1 = n2
;
create table t1
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4
)
select
mod(rownum, 1000) n1,
mod(rownum, 1000) n2
from
generator v1,
generator v2
where
rownum <= 1e6 ;
If you’re running 11g and can changed the code there are a couple of easy options – adding a virtual column, or applying extended stats and then modifying the SQL accordingly would be appropriate.
-- Virtual Column alter table t1 add ( n3 generated always as ( case n1 when n2 then 1 end) virtual ) ; execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for columns n3 size 1')
-- Extended Stats
begin
dbms_output.put_line(
dbms_stats.create_extended_stats(
ownname => user,
tabname => 'T1',
extension => '(case n1 when n2 then 1 else null end)'
)
);
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T1',
block_sample => true,
method_opt => 'for columns (case n1 when n2 then 1 else null end) size 1'
);
end;
/
select count(*)
from t1
where (case n1 when n2 then 1 else null end)= 1
;
If you can’t change the SQL statement, there’s always the option for bypassing the problem by fixing a suitable execution plan with an SQL Baseline, of course. Alternatively, if you can think of the right hint you could create an “SQL Patch” for the statement – but what hint might be appropriate ? I’ll answer that question in a minute.
Here’s another option, though: get Oracle to use dynamic sampling. (You probably guessed that from the title of the post.) So which level would you use to make this work ? Left to its own devices, Oracle would calculate the selectivity of the predicate n1 = n2 as the smaller of the two separate predicates “n1 = unknown” and “n2 = unknown”. So you might hope that level 3 (Oracle is “guessing”) or level 4 (more than one predicate on a single table) might be appropriate. It’s the latter that works. If you execute “alter session set optimizer_dynamic_sampling=4;” before executing this query, Oracle will sample the table before optimising.
The method works, but can you apply it ? Possibly not, if you’re not allowed to inject any extra SQL anywhere – after all, you probably don’t want to set the parameter at the system level (spfile or init.ora) because it may affect lots of other queries – introducing more work because of the sample, and then risking unexpected changes in execution plans. Setting the parameter for a session is often no better. And this brings me back to the SQL Patch approach – if you don’t want to create a baseline for the query then perhaps a patch with the hint /*+ opt_param(‘optimizer_dynamic_sampling’ 4) */ will do the trick. Don’t forget all the doubling of single quotes that you’ll need, though (this is the code fragment I used):
begin
sys.dbms_sqldiag_internal.i_create_patch(
sql_text =>
'
select
count(*)
from t1
where n1 = n2
',
hint_text => 'opt_param(''optimizer_dynamic_sampling'' 4)'
);
end;
/
For more analysis and commentary on the SQL Patch mechanism, you might like to read Dominic Brooks’ mini-series:
  Using LDAP for Authentication and Authorization within APEXOne of my current customers would like to use their LDAP (Microsoft Active Directory) server for authentication and authorization of APEX applications. Of course we tried to set up a standard LDAP Authenication that's available within APEX. But we couldn't get that to work. Maybe it has to do with the fact that the client stored their Users within Groups within Groups within .... . Or maybe it doesn't do a full tree walk in the directory. Or maybe it is just because it is Microsoft - and not Oracle Internet Directory (OID). So we moved to a custom Authentication using the DBMS_LDAP functions (and some examples from the Pro Oracle Application Express book and Tim Hall - a.k.a. Oracle Base).
One of the issues we encountered that we wanted to use the user's login name, like "jdoe" and not his full name ("John Doe"). And the login name is stored in the "sAMAccountName" attribute. But authenticating using just "jdoe" didn't work. I don't whether it is particular for this set up, but we had to prefix the username with the domain, like "USERS\jdoe". See the code snippet below:
-- Authenicate the user -- raises an exception on failure
retval := dbms_ldap.simple_bind_s
( ld => l_session
, dn => l_dn_prefix || p_username
, passwd => p_password );
Once authenticated we needed to check whether the user was a member of a particular group. Authorization was done by defining a group in AD containing the string APEX_<APP_ID>, so for instance "Users for APEX_101". So we had to read the AD tree and scan it for the "memberOf" attribute. This attribute contains a string with the complete group information. Therefore we used the dbms_ldap_search_s function defining a specific filter using the "sAMAccountName" attribute.
-- Get all "memberOf" attributes
l_attrs(1) := 'memberOf';
-- Searching for the user info using his windows loginname
retval := dbms_ldap.search_s
( ld => l_session
, base => ldap_base
, scope => dbms_ldap.scope_subtree
, filter => '(&(objectClass=*)(sAMAccountName='|| p_username || '))'
, attrs => l_attrs
, attronly => 0
, res => l_message );
Then we could scan the results on the existence of the "APEX_101" string.
One additional request was to show the user why his login failed - if it did. By default APEX just returns "Invalid login credentials", but in the case where he is just not authorized (because he is not in the correct "application group"), another message should appear. And there the APEX builtin function apex_util.set_custom_auth_status came to the rescue! Although it has been there for ages - at least since version 3.1 - I had never used it and wasn't aware of it's existence. With this function you can override the standard message on the login screen. So pretty useful stuff.
The next step will be to implement a more fine grained authorization (for read / write) using the same technique. This will be implemented using a (real) Authorization scheme, based on the same code.
So for the interested - and for my own documentation ;-) - the full code is below:
create or replace
function ldap_auth( p_username in varchar2
, p_password in varchar2 )
return boolean
is
retval PLS_INTEGER;
l_session dbms_ldap.session;
l_attrs dbms_ldap.string_collection;
l_message dbms_ldap.message;
l_entry dbms_ldap.message;
l_attr_name varchar2(256 );
l_vals dbms_ldap.string_collection;
l_ber_element dbms_ldap.ber_element;
ldap_host varchar2(256) := '<your LDAP server>';
ldap_port varchar2(256) := '389'; -- default port
ldap_base varchar2(256) := 'OU=<base OU>,DC=<dc1>,DC=<dc2>,DC=<dc3>';
l_dn_prefix varchar2(100) := '<prefix>\'; -- domain, like 'USERS\'
l_not_authenticated varchar2(100) := 'Incorrect username and/or password';
l_not_authorized varchar2(100) := 'Not authorized for this application';
l_authed boolean;
l_memberof dbms_ldap.string_collection;
BEGIN
-- Raise exceptions on failure
dbms_ldap.use_exception := true;
-- Connect to the LDAP server
l_session := dbms_ldap.init( hostname =>ldap_host
, portnum => ldap_port );
-- Authenicate the user -- raises an exception on failure
retval := dbms_ldap.SIMPLE_BIND_S( ld => l_session
, dn => l_dn_prefix || p_username
, passwd => p_password );
-- Once you are here you are authenticated
-- Get all "memberOf" attributes
l_attrs(1) := 'memberOf';
-- Searching for the user info using his samaccount (windows login )
retval := dbms_ldap.search_s( ld => l_session
, base => ldap_base
, scope => dbms_ldap.SCOPE_SUBTREE
, filter => '(&(objectClass=*)(sAMAccountName=' || p_username || '))'
, attrs => l_attrs
, attronly => 0
, res => l_message );
-- There is only one entry but still have to access that
l_entry := dbms_ldap.first_entry( ld => l_session
, msg => l_message );
-- Get the first Attribute for the entry
l_attr_name := dbms_ldap.first_attribute( ld => l_session
, ldapentry => l_entry
, ber_elem => l_ber_element );
-- Loop through all "memberOf" attributes
while l_attr_name is not null loop
-- Get the values of the attribute
l_vals := dbms_ldap.get_values( ld => l_session
, ldapentry => l_entry
, attr => l_attr_name );
-- Check the contents of the value
for i in l_vals.first..l_vals.last loop
-- A user gets access to APP 101 when he is assigned to a group where the name contains "APEX_101"
l_authed := instr(upper(l_vals(i)), 'APEX_'||v('APP_ID')) > 0 ;
exit when l_authed;
end loop;
exit when l_authed;
l_attr_name := dbms_ldap.next_attribute( ld => l_session
, ldapentry => l_entry
, ber_elem => l_ber_element );
end loop;
retval := dbms_ldap.unbind_s( ld => l_session );
if not l_authed
then -- Although username / password was correct, user isn't authorized for this application
apex_util.set_custom_auth_status ( p_status => l_not_authorized );
end if;
-- Return Authenticated
return l_authed;
EXCEPTION
when others then
retval := dbms_ldap.unbind_s( ld => l_session );
-- Return NOT Authenticated
apex_util.set_custom_auth_status ( p_status => l_not_authenticated );
return false;
END;
Create a Success Message using Dynamic ActionThis simple example is showing how to create a success message using dynamic actions. One thing needs to be mentioned though. The last one of the three actions depends on your current template: var success_message = $('#P299_MESSAGE').val();
$('.t10messages').empty();
$('.t10messages').append('<div class="t10messages"><div class="t10success"
style="display: block;"></div></div>');
$('.t10success')
.append(success_message)
.slideDown('slow');The best thing is either to open the template and have a look at the structure of the success message part or to use firebug and inspect the HTML structure on your page. Enjoy. DOAG 2013 Development in Bonn am 19. JuniAm 19.Juni 2013 findet die nächste DOAG Development Konferenz in Bonn statt, diesmal mit dem Thema: „Agile and Beyond – Projektmanagement in der Oracle-Softwareentwicklung“Das Motto der zweiten Auflage der Community Konferenz steht fest: Die effektive Durchführung von Softwareprojekten steht thematisch im Zentrum der Konferenz für Entwickler, Softwarearchitekten und Projektleiter. Gerade Oracle APEX eignet sich hervorragend für die agile Durchführung von Softwareprojekten, ich habe damit seit Jahren hervorragende Ergebnisse erzielt. Mit Oracle APEX kann man in extrem kurzen Iterationszyklen von 1-4 Wochen sehr beachtliche Funktionalitäten implementieren. Gerade die Lieferung von Funktionalitäten in kurzen Abständen hilft, das Vertrauen der Fachseiten zu gewinnen. Dennoch gibt es einige Fallstricke zu beachten, damit auch alles rund läuft ;). Weitere Informationen zur Konferenz finden Sie hier. Bitte beachten: der Frühbucher-Rabatt endet heute! Viele Grüße, ~Dietmar. get the Developer Toolbar back for mobile APEX ApplicationsWhen developing mobile applications with APEX 4.2 you’ll find the developer toolbar on the page bottom is missing.
This can be a little bit annoying when investigating misbehaving applications. Oracle recognizes this as Issue 14749955 with the workaround to access the toolbar functions through a desktop page…
Of course this is a bit annoying, so i spend some time and thought of a solution.
Here it is, a simple way to get the developer toolbar for mobile applications.
create a PL/SQL Region on the Global Page for mobile UI
Give the region a very high sequence number, something like 999999, to ensure it will be the last region in the body. As region template we are going to use “No Template”, since you don’t want to see any title for this region.
The region source will construct a nice horizontal button-group in mobile-friendly styling:
BEGIN
htp.p('<div data-role="controlgroup" data-type="horizontal" data-mini="true" id="apex-dev-toolbar">');
htp.p(' <a data-role="button" rel="external" href="f?p=4500:1000:'||APEX_APPLICATION.g_edit_cookie_session_id||'" title="Application Express Home">Home</a>');
htp.p(' <a data-role="button" rel="external" href="f?p=4000:1:'||APEX_APPLICATION.g_edit_cookie_session_id||'::NO:1,4150,RP:FB_FLOW_ID,FB_FLOW_PAGE_ID,F4000_P1_FLOW,F4000_P4150_GOTO_PAGE,F4000_P1_PAGE:&APP_ID.,&APP_PAGE_ID.,&APP_ID.,&APP_PAGE_ID.,&APP_PAGE_ID." title="Application &APP_ID.">Application &APP_ID.</a>');
htp.p(' <a data-role="button" rel="external" href="f?p=4000:4150:'||APEX_APPLICATION.g_edit_cookie_session_id||'::NO:1,4150:FB_FLOW_ID,FB_FLOW_PAGE_ID,F4000_P1_FLOW,F4000_P4150_GOTO_PAGE,F4000_P1_PAGE:&APP_ID.,&APP_PAGE_ID.,&APP_ID.,&APP_PAGE_ID.,&APP_PAGE_ID." title="Edit Page &APP_PAGE_ID.">Edit Page &APP_PAGE_ID.</a>');
htp.p(' <a data-role="button" rel="external" href="f?p=4000:336:'||APEX_APPLICATION.g_edit_cookie_session_id||'::NO::FB_FLOW_ID,FB_FLOW_PAGE_ID,F4000_P1_FLOW,F4000_P4150_GOTO_PAGE,F4000_P1_PAGE:&APP_ID.,&APP_PAGE_ID.,&APP_ID.,&APP_PAGE_ID.,&APP_PAGE_ID." title="Create">Create</a>');
htp.p(' <a data-role="button" rel="external" target="_blank" href="f?p=4000:34:'||APEX_APPLICATION.g_edit_cookie_session_id||':PAGE:NO:34:F4000_P34_SESSION,F4000_P34_FLOW,F4000_P34_PAGE,FB_FLOW_ID:&SESSION.,&APP_ID.,&APP_PAGE_ID.,&APP_ID.">Session</a>');
htp.p(' <a data-role="button" rel="external" href="f?p=4000:14:'||APEX_APPLICATION.g_edit_cookie_session_id||'::::FB_FLOW_ID,FB_FLOW_PAGE_ID,F4000_P1_FLOW,F4000_P4150_GOTO_PAGE,F4000_P1_PAGE:&APP_ID.,&APP_PAGE_ID.,&APP_ID.,&APP_PAGE_ID.,&APP_PAGE_ID." title="Caching">Caching</a>');
htp.p(' <a data-role="button" rel="external" target="_blank" href="f?p=4000:19:'||APEX_APPLICATION.g_edit_cookie_session_id||':::RIR,19:IR_APPLICATION_ID,IR_PAGE_ID:&APP_ID.,&APP_PAGE_ID." title="View Debug">View Debug</a>');
--
IF :DEBUG IS NULL OR :DEBUG = 'NO'
THEN
htp.p(' <a data-role="button" rel="external" href="f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.::YES" title="Debug">Debug</a>');
ELSE
htp.p(' <a data-role="button" rel="external" href="f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.::NO" title="Debug">End Debug</a>');
END IF;
htp.p('</div>');
END;
Don’t forget to set the Condition to Type “PL/SQL Expression” and APEX_APPLICATION.g_edit_cookie_session_id IS NOT NULL, to guarantee this toolbar will show only for development session.
The end result is pretty slick, i think:
Of course this isn’t the “real” developer toolbar and it doesn’t support all the functions you are used, but it provides the necessary minimum (at least for me, it does).
Please let me know if this was helpful for you.
 Announcing the EPM Oracle Mic Night at Kscope13We want to hear from you! You'll have ten minutes to show everyone something awesome that you've done. Come out Monday night at Kscope and join us for a fun-filled, informative night! Have you ever thought to yourself? "I have this really neat thing that I am doing in Essbase (Planning, HFM, O |
