Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Archive for the ‘Application Express’ Category

APEX “Application Process” Bookmarklet

Posted by Tyler Muth on October 12, 2009

If you’re an APEX developer and doing a lot of AJAX work, you probably spend a lot of time clicking between pages and the “Application Process” section of APEX.   In APEX 3.1 and 3.2 it takes 2 clicks on links that are on very different parts of the page.  Unless you do this a lot, you probably think I’m crazy for complaining about this, but if you are doing a lot of AJAX development, it’s enough to drive you insane.  I even kept a separate browser (Safari) open just for App Processes.

Solution:  I wrote a super simple bookmarklet that reduces this to one click.  Since WordPress.com is blocking my ability to post a link with JavaScript code in it, you’ll have to manually create a new bookmark on your web browsers bookmark toolbar using the following for the URL:

javascript:location.href="f?p=4000:4207:"+$v('pInstance')

Now, once you’ve logged into the APEX builder that bookmark will take you straight to the Application Process section using the same APEX session.

Advertisements

Posted in APEX, Application Express, Oracle | 4 Comments »

Octagonal Peg in a Hexagonal Hole (APEX Backward Compatibility)

Posted by Tyler Muth on June 4, 2009

I was going to title this post “Hacking away at an APEX 3.2 app until you can wedge it into a 3.1 instance”, but I shortened it to the current title.  This week I went to import an application I built in APEX 3.2 for an internal customer, only to find that their APEX instance was still on 3.1.  They support a ton of mission critical apps on that instance so upgrading wasn’t an option, no matter how hard I begged.  So, I began the tedious task of editing the export file until I was able to import it.

Just to be clear, this process is NOT SUPPORTED in any way, shape or form by Oracle or myself, so proceed with caution. DON’T EVEN THINK of calling support with issues introduced by this technique. If you start down this path, you are on your own! I’m posting this to try and help some of the more advanced developers in this community and I do not want to introduce a support nightmare for Oracle Support or the APEX Dev Team.

Import Hacking the Slow Way

I started by trying the import in SQL*Plus as it’s much faster and more informative than the web interface for repetitive imports.  The key things that change from version to version of APEX are the procedures and their parameters of WWV_FLOW_API.  Once I edited the SECURITY_GROUP_ID, SET_VERSION, and FLOW_ID (more on these later), I began the Import > Find Error > Edit File > Repeat sequence. One thing that really helps here is a text editor that supports split-screen, synchronized scrolling such as Notepad++ or UltraEdit.  Notepad++ screenshot here.  After a few minutes I came to the conclusion that there has to be a better way.

Import Hacking the Faster Way

I started by querying the ALL_ARGUMENTS view and saving the results in my own table:

create table apex_api_parameters as
	select '3.2' version, object_name,argument_name
	  from all_arguments
	 where package_name='WWV_FLOW_API'
	   and owner = 'APEX_030200'
/

I repeated this step in each database to get the WWV_FLOW_API procedures and parameters for APEX 3.2 and 3.1.  Now I can use SQL set operations to compare them:

select object_name
  from apex_api_parameters
 where version = '3.2'
 minus
select object_name
  from apex_api_parameters
 where version = '3.1'
with common_objects as (
			select object_name
			  from apex_api_parameters
			 where version = '3.2'
		 intersect
			select object_name
			  from apex_api_parameters
			 where version = '3.1')
 select p.object_name,p.argument_name
   from apex_api_parameters p,common_objects c
  where version = '3.2'
	and p.object_name = c.object_name
  minus
 select p.object_name, p.argument_name
   from apex_api_parameters p,common_objects c
  where version = '3.1'
    and p.object_name = c.object_name

Based on the results of the first query, I know the procedures in WWV_FLOW_API that exist in 3.2 but not in 3.1.  These almost always equate to new features.  If I were comparing 3.1 to 3.0 I would see the CREATE_WORKSHEET procedure which equates to interactive reports. The second query shows the parameters to procedures that exist in 3.2 but not in 3.1.

Step by Step Hacking

  1. Make a backup copy of the app you plan to edit.
  2. Using the APEX Application Builder in the newer (source) instance, edit the application to remove any major features that you know don’t exist in the older (target) instance, such as interactive reports.
  3. Export your application from the source instance.
  4. In the target instance, create a new, 1 page dummy application and export it.
  5. Use the SECURITY_GROUP_ID, SET_VERSION, and FLOW_ID from the dummy application in your actual application.
  6. Run my Parameter Diff application (below) and keep it open to use in the next two steps.
  7. Continue editing the application export file to remove any procedures that are not supported in the target instance.
  8. Now find all of the unsupported parameters for existing procedures and delete them.
  9. Test your import.  Repeat steps 7-9 if needed.

To make this process easier, I created an APEX app with all of the API parameters from 3.2, 3.1, 3.0, and 2.2.  You can see it in action here, or download it here.  Keep in mind the supporting objects to 8,000+ inserts, so it may take a minute or two to install.  Happy hacking!!!

Posted in APEX, Application Express, Oracle | Tagged: , , | 9 Comments »

PL/SQL LDAP over SSL – Please Test

Posted by Tyler Muth on January 30, 2009

I’ve been meaning to write this for quite a while now (measured in years), but never got around to it.  None of the DBMS_LDAP code samples support SSL, including the APEX LDAP Authentication Scheme.  Most LDAP directories require SSL for authentication, as you would otherwise send usernames and passwords in clear text.  Depending on your network topography, the risk of someone capturing that data might be very low, but just the same, it should really be SSL. 

One perceived barrier to making this work was my belief that this required an Oracle Wallet which are a bit of a hassle to setup.  There was also some confusion on my part that Oracle Wallets required a license for the Advanced Security Option (ASO), so this would limit the use of this code to only the subset of our customers that have purchased this option.  I’m still looking for the email on this but I believe we confirmed that the Oracle Wallet Manger by itself (such as for DBMS_LDAP or UTL_HTTP) does not require ASO.  Only when you are using the explicitly defined features of ASO such as encrypted column / tablespaces / backups, network encryption, and a few others I can’t remember.  If someone has more info on this, please feel free to comment as I’m too [ busy | lazy | (select adjective from adjectives where id = round(dbms_random.value(1, 100)))] to find it [today | tomorrow | (select sysdate + round(dbms_random.value(1,365)) from dual) ].  When I finally sat down to write this for my current project, I quickly discovered that for SSL Mode 1 (which is what 99.999% of people want), no wallet is required!  Can’t believe I put this off for so long based on a false assumption.

So what does this code do? It can:

  • Bind to an LDAP directory using SSL or non-SSL
  • Authenticate a user using their exact Distinguished Name (DN) such as: cn=tyler.muth, l=amer, dc=mycompany, dc=com
  • Given a starting point (search base) and a username (tyler.muth), search for that user, retrieve their DN, then authenticate.  This handles users in multiple sub-trees.

This is NOT production code! This is NOT production code! This is NOT production code!  Why am I posting it here then?  Because I want people to test it.  I’ve tested it on the project I’m working on, but that doesn’t mean it’s bug free.  I want people who are familiar with PL/SQL and LDAP to review and test my code.  As I receive suggested changes to the code, I’ll update it.  When I feel comfortable that enough people have tested, I’ll blog again and “release it” to the community to use.  My goal is make this as flexible and easy as possible so it just becomes a utility that people can use when they need LDAP.  I can’t make any promises, but if it makes it back into APEX, then you will have helped to improve a product.  If you are not all that familiar with PL/SQL and LDAP, or are looking for a production ready solution, this is probably not for you.  When it’s ready, this blog post will be mostly empty and point to the new location of the code.

I’ve only tested this against OID using an 11.something database, so I’d love to have people test this against Active Directory and any other LDAP directories they work with and at least 10gR2 and XE.  Mark Wilcox is testing it with Oracle Virtual Directory in the next few days as well.  I also ran Wireshark on the database server to capture the communitcation between the database and the LDAP directory.  As expected, in non-ssl mode, my username and password were in the clear.  With SSL enabled, everything was garbage.   Please use Wireshark with caution as it may violate network policies of your organization, get you fired, and accelerate global warming.  It’s a great tool when used ethicly.

I really appreciate the feedback I’ve recieved in the past from this community, especially in pointing out errors with my code.  Thanks in advance for your help.

Customize the ldap_globals package with data from your environment. For most environments, you’ll only need to change g_host and g_search_base.

create or replace package ldap_globals
authid definer
is

	g_host         	constant varchar2(255)   := 'ldap.mycompany.com';
    g_port          constant number          := 636;
    g_search_base   constant varchar2(255)   := 'dc=mycompany,dc=com';
    g_search_filter constant varchar2(255)   := 'cn=';
	g_exact_dn      constant boolean   		 := false;
	g_use_ssl       constant boolean   		 := true;

	-- if g_exact_dn is false, then we will start at the search base and find the user to get their DN.
	--   if true then we will just bind with the DN and you should pass the fully qualified user DN into p_username

	-- SSL is hard-coded to use mode 1, as modes 2 and 3 are highly unlikely.  See the DBMS_LDAP doc for more info.
	-- No wallet is needed for mode 1

end ldap_globals;
/
show errors

There’s no need (that I know of) to customize any code in this function.

create or replace function  ldap_authenticate(
	p_username        in    varchar2,
	p_password        in    varchar2)
return boolean
authid definer
is
	l_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_dn          varchar2(1000);
	l_user_handle dbms_ldap_utl.handle;
	l_return      pls_integer;
	l_user_type   pls_integer;

	l_filter      varchar2(256);

	function do_connect
		return pls_integer
	as
		l_ldap_user   varchar2(256) := NULL;
		l_ldap_pwd    varchar2(256) := NULL;
	begin
		dbms_ldap.use_exception := true;
		l_session := dbms_ldap.init( ldap_globals.g_host , ldap_globals.g_port);

    if ldap_globals.g_use_ssl = true then
      l_retval := DBMS_LDAP.open_ssl(
					ld 				=> l_session,
                    sslwrl			=> NULL, -- wallet location, ie file:/etc/ORACLE/WALLETS/oracle
                    sslwalletpasswd	=> NULL, -- wallet password
                    sslauth			=> 1); -- NO_AUTH :1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3
    end if; 

		return dbms_ldap.simple_bind_s( l_session, l_ldap_user, l_ldap_pwd );
	end;

	procedure do_close
	as
	begin
		l_retval := dbms_ldap.unbind_s( l_session );
	end do_close;

	procedure handle_error(p_message in varchar2)
	as
	begin
		do_close;

		$IF $$logger $THEN
			logger.log_error(p_message);
		$END

		RAISE_APPLICATION_ERROR (-20001,p_message);
	end;

begin
	l_user_type := DBMS_LDAP_UTL.AUTH_SIMPLE;

	l_retval := do_connect;

	if ldap_globals.g_exact_dn = false then

		l_attrs(1)  := 'dn';
    l_filter := '('|| ldap_globals.g_search_filter|| p_username||')';
		l_retval := dbms_ldap.search_s( l_session, ldap_globals.g_search_base, dbms_ldap.scope_subtree, l_filter, l_attrs, 0, l_message );

		l_retval := DBMS_LDAP.count_entries(l_session, l_message);

		if l_retval > 1 then
			handle_error('More than one entry found when searching for users DN.');
		end if;

		if l_retval = 0 then
			handle_error('No entries were found when searching for user DN');
		end if;

		l_entry := dbms_ldap.first_entry( l_session, l_message );
		l_dn := dbms_ldap.get_dn( l_session, l_entry );
	else
		l_dn := p_username;
	end if; -- ldap_globals.g_exact_dn = false

	l_retval := null;
	l_retval := DBMS_LDAP_UTL.create_user_handle(
				user_hd     => l_user_handle,
				user_type   => DBMS_LDAP_UTL.TYPE_DN,
				user_id     => l_dn);

		begin
      dbms_ldap.use_exception := false;
      l_return := null;
			l_return := dbms_ldap.simple_bind_s(
				ld      => l_session,
				dn      => l_dn,
				passwd  => p_password
				);
      if l_return != 0 then
        do_close;
        return false;
      end if;

		exception when others then
			do_close;
			raise;
		end;

    dbms_ldap.use_exception := true;
	do_close;
	if l_return is null then
		handle_error('simple_bind_s did not return a value.');
		return false;
	end if;

	if l_return = 0 then
		return true;
	end if;

	exception when others then
		do_close;
		raise;
		return false;
end ldap_authenticate;
/
show errors

A simple test script. If using SQL Developer, make sure you enable output on the DBMS_OUTPUT tab.

set serveroutput on
declare
	l_return boolean;
begin
	l_return := ldap_authenticate(
					p_username	=> 'first.last',
					p_password	=> '');

	if l_return = true then
		dbms_output.put_line('Authenticated');
	else
		dbms_output.put_line('Fail!');
	end;
end;
/

Posted in Application Express, Oracle, PLSQL, Security | Tagged: , , , | 77 Comments »

jQuery Selectors Will Change Your Life

Posted by Tyler Muth on December 17, 2008

OK, maybe not your life but it will absolutely change the way you code JavaScript. If you haven’t used jQuery yet, here’s the description from the jQuery site:

jQuery is a fast and concise JavaScript Library that simplifies HTML document traversing, event handling, animating, and Ajax interactions for rapid web development. jQuery is designed to change the way that you write JavaScript.

jQuery is technology agnostic, so you can use it in APEX, J2EE, PHP, .Net, Rails, etc. I’ve been using it with APEX for the last few months and there’s a pretty good chance it will actually be included in the 4.0 release of APEX.

Why are selectors so great? Because they completely change the way you work with JavaScript. Traditionally, people add JavaScript such as onClick() events in the code that renders a page. This is easy for hand-coded HTML or other bare-bones technologies such as PHP (when not using a framework). But in the case of frameworks such as APEX, you actually don’t have access to all of the code that rendered the page. Using selectors, you can get very granular access to any element on the page after it is rendered. For example, the following code changes the style of EVERY link on your page:

$(document).ready(function() {
  $("a").css('text-decoration','none');
});

Or maybe you want to add an onClick event to every div that has a class named “clickme”:

$(document).ready(function() {
    $("div[class=clickme]").click(function() {
       alert('Yep, you clicked me');
    });
});

The following code turns any div on the page red when you hover over it, then white when you move your mouse out of it.

$(document).ready(function() {
    $("div").hover(function() {
       $(this).css('background-color','#ff0000');
    },
        function() {
       $(this).css('background-color','#ffffff');
    });
});

Getting a little more APEX specific, here’s a bit of code that prints all values from the “SAL” column out to the Firebug Console (you must have Firebug installed):

// find all TD's with a class of t20data and headers=SAL.  For each of those, run some function.  console.log is Firebug specific.
$("td[class=t20data][headers=SAL]").each(function(){
  console.log($(this).html());
});

Building on the previous example again, lets change the color of the data in a column based on its value:

// find all TD's with a class of t20data and headers=SAL.  For each of those, run some function.  console.log is Firebug specific.
$("td[class=t20data][headers=SAL]").each(function(){
  sal = parseFloat($(this).html());
  if (sal<1000){
     theColor='green';}
  else if (sal >= 1000 && sal < 2500){
     theColor='yellow';
  }
  else{
       theColor='red';}
   $(this).css('color',theColor);
});&#91;/sourcecode&#93;

Lets continue to build on this example by changing the color of every cell in a row based on the value of the SAL column.  Notice that we walk up the DOM to the TR, then down the DOM to every TD in the row.

&#91;sourcecode language='javascript'&#93;
$("td&#91;class=t20data&#93;&#91;headers=SAL&#93;").each(function(){
 sal = parseFloat($(this).html());
 if (sal<1000){
 theColor='green';}
 else if (sal >= 1000 && sal < 2500){
 theColor='yellow';
 }
 else{
 theColor='red';}
 //$(this)  is currently the TD of the sal column.
 // Walk up to its parent TR, then down to all child TDs.  This effectively loops over every TD in a row
 $(this).parent('tr').children('td').each(function(){
 // $(this) is now the curent TD that is a child of the TR
 $(this).css('color',theColor);
 });
});&#91;/sourcecode&#93;

My final example if from a question on the APEX Forum (<a href="http://forums.oracle.com/forums/message.jspa?messageID=3178134" target="_blank">here's the thread</a>) where someone wanted to replace all instances of the APEX Datepicker item with the jQuery UI Datepicker (I posted about this control <a href="https://tylermuth.wordpress.com/2008/07/16/jquery-datepicker-and-apex/" target="_blank">here</a>).  In APEX 3.1 or greater (not 100% which versions) all datepickers are wrapped in '&lt;fieldset class="datepicker"...'.  This code looks for any of those items on page load and replaces them.

$(document).ready(function(){ 
    $('fieldset[class=datepicker]').find('img').css('display','none');

    $('fieldset[class=datepicker]').find('input[type=text]').datepicker({ 
             showOn: "both", 
             buttonImage: "http://ui.jquery.com/repository/demos_templates/images/calendar.gif", 
             buttonImageOnly: true}); 
}); 

Luis Cabrel pointed out that previous versions of APEX did not have the fieldset code and posted his solution in that same thread:

$(document).ready(function(){
    $('img[title=Calendar]').hide();

    $('img[title=Calendar]').parent().parent().
            find('input[type=text]').datepicker({
            closeAtTop: true,
            dateFormat: "dd-M-yy",
            showOn: "both",
            buttonImageOnly: false,
            duration: 'fast',
            firstDay: 1});
            });

Imagine how much time that would save you if you had even a couple of applications with 10-15 datepickers per app.

The Firefox plugin “Firebug” makes it so much easier to test and develop selectors since you can use HTML > Inspect to find the DOM nodes you want to select, then use the console to test them. console.log() is also infinitely easier to use for debugging than alert() since you don’t have to click “OK” for each event.

I simply can’t stress enough how useful jQuery selectors are. I strongly encourage everyone to take some time to explore them. There is a great tutorial called “Getting Started With jQuery” that goes into more detail. If you have other cool jQuery solutions with APEX, I’d love to see them!

Posted in Application Express, JavaScript / AJAX | Tagged: , , | 19 Comments »