Learn about R12 integration with OID and OAM for the price of a latte!

How often do you hear, "I sat down to write a blog post, and ended up with an eBook?" Probably not every week. But that's just what one of the the Oracle EBS Team Leads at Pythian has done. My colleague Subhajit has just published an eBook that covers the steps required to integrate Oracle Applications Release 12.1 with Oracle Internet Directory 11.1.1.6 and Oracle Access Manager 11.1.2.

At over 300 pages, this is a comprehensive guide to integrating 3 large and increasingly important pieces of the Oracle Applications technology stack, complete with screenshots and blow-by-blow examples of an actual configuration and deployment. No hand-waving theoretical stuff here; you get to watch the sandbox environment spring to life as you read! I'm still in the "browse the steps and download the software" phase, myself, but so far everything is clear and easy to follow. Subhajit has also offered up some troubleshooting tips for common issues encountered during deployment.

If you're an Apps DBA who's trying to get up to speed on the new look of identity management for E-Business Suite environments, you owe it to yourself to check out this eBook. Think of it as buying Subhajit a coffee (or a beer) in return for saving you the work of researching all those pre-requisites and puzzling out the various dependencies by yourself. This represents a serious jump-start to any effort to update your knowledge of E-Business Suite application server integration from the old 10gAS stack to the new 11g Fusion Middleware components.

Subhajit also promises more fun stuff in the near future, so stay tuned to his corner of the Pythian blog to see what's coming down the line! If you've been limping along on a barely-supported version of 10g SSO and OID to for your E-Business Suite identity management, his promised upgrade guide is sure to be of interest.

Finally here: Oracle Instant Client 11gR2 for OS X

See the blog post over at Oracle for details, and be sure to send Christopher Jones an email (via the address in his post) stating you're an active user of the client. As Christopher mentions in his post,

You can really help us justify resources for the OS X bundle if you email me letting me know you are a fan. Since Instant Client is a free download, it is tricky for us to know how widely it gets used.

Jump to OTN to download, but please remember to email Christopher as well, if you're interested in seeing the client for future releases. :-)

Build an 11gR2 RAC cluster in VirtualBox in 1 Hour using OVM templates

[I originally posted this over at the Pythian blog. If you're not following it, you should! Way more content, by far smarter people than lil ol' me.]

After reviewing my blog post about running EBS OVM templates in VirtualBox, two of my teammates suggested that I work on something with potentially broader appeal. Their basic message was, "This is really cool for us EBS nerds, but what about the Core DBAs?"

So how does "11gR2 RAC in an hour" sound? :-) In this post, I'll demonstrate how to deploy the pre-built Oracle VM templates to create a two-node 11gR2 RAC cluster in Oracle VirtualBox.
Read More »

crontab backups: a simple, time-saving holiday gift idea for DBAs and sysadmins

[I originally posted this over at the Pythian blog. If you're not following it, you should! Way more content, by far smarter people than lil ol' me.]

It's holiday season in many parts of the world, but it's not all parties and egg-nog. Caretakers of critical IT systems often have significant work to do as those systems roll over into a new year. In some cases, we're just monitoring to make sure nothing unusual happens. In other cases, however, we're making the unusual happen: running those batch jobs and backups that only fire once per year.

So what happens when you update the crontab on a critical system to accommodate year-end processing? What happens when, despite all your diligence and devotion to human reliability guidelines, you perform a simple slip, and instead of typing crontab -e, you type crontab -r? Well, the documentation tells you what happens: Read More »

Build an E-Business Suite 12.1.3 sandbox in VirtualBox with one hour of work

[I originally posted this over at the Pythian blog. If you're not following it, you should! Way more content, by far smarter people than lil ol' me.]

Self-directed learning about a product as complex as Oracle E-Business Suite can be challenging. Oracle helpfully provides a pre-built environment (called 'Vision') for training purposes. Configuring a viable training or "sandbox" environment on a workstation can be pretty daunting, however, considering:

  1. The memory and storage resources required to run a "small" Vision instance
  2. The need to install and configure the OS and a list of pre-requisite patches and packages
  3. Learning about the installer itself, which is straightforward but can be a little quirky, especially without careful attention to item #2!
  4. Learning enough about basic EBS administration to turn the darn thing on and start playing

Over time, improved specs for workstations have made item #1 less of a problem. There is ample documentation to cover items 2-4, and several people (including yours truly) have written basic bootstrap guides to help people get up and running quickly. Then Oracle went a step further and released Oracle VM templates for EBS (and updated them earlier this year), which eliminate the need for all of the fiddly stuff in steps 2-3, except for one small detail: They only run in Oracle VM Server, and maintaining an Oracle VM environment is a resource-intensive endeavor by itself.

This blog post aims to change all that. Are you ready to learn how to deploy the Oracle VM templates in Oracle VirtualBox, so you can run them on your own workstation without the need to run Oracle VM Server? I hope you are, because let me tell you, I've done a lot of typing. Whew.

Oh, and that "One hour of work" line in the title is not a mere advertising gimmick! There are long steps at the beginning, but they're mostly hands-free (downloading and converting the OVM templates, steps 1-3 below). After you've gone through that, you really can have a working EBS 12.1.3 Vision instance up and running in a little over an hour. Let's begin...
Read More »

Oracle Apps 11i: Back to the future

A while ago, I posted a few references for installing an 11i Vision instance, noting that it was "something you'll hopefully only need to do once." The fun doesn't stop with installing the software, though. Once you're done with the easy part you're left with:

  1. A desupported version of the database
  2. A desupported version of the Developer 6i tools (yes, even more obsolete than 6i's inherent obsolescence)
  3. JInitiator, which is also, you guessed it, desupported in favor of the standard Sun JDK.

What's the big deal? After all, 11i is in Extended Support mode, so why should it matter if an installed Vision instance has technical components that are a bit long in the tooth? Extended Support or not, there are probably one or two (hah) 11i instances still stubbornly kicking around out there. Presumably those instances have been patched up at least a little bit. If you're installing an 11i Vision instance for educational purposes, it might help to have something that (hopefully) more closely matches the modern state of affairs.

Below are some of my notes from when I dragged my 11i Vision instance forward from 2005 to 2011. Well, sort of. It is still on 32-bit Linux (OEL 5.5 in this case) ;-). But the latest Developer 6i patch set has been applied, the database is at 11.2.0.2, Jinitiator's been replaced by JDK 6, and the AD tools are a bit more up-to-date. If you decide to use these notes, please understand that I intend them to be a rough guide, to save you some research time. I can't commit to detailed support of your effort. This is a reference, not a cookbook.

Reference Notes

Not surprisingly, upgrading 11i components to this decade requires a lot of preparatory reading. Even if you're really familiar with the contents, it's worth revisiting these docs; some of them were changed as recently as July 2011. Here's a list that should cover everything for 32-bit OEL:

Database

Apps

Forms, Java, and friends

Patches

While you're reading, if you want to get a jump on downloading the patches listed in these documents, I've listed them below. Please validate that the patch numbers still match the documents; the Developer 6i patch document in particular are subject to relatively frequent updates.

Database

Apps

Order of operations

Tying all those notes and patches together looks daunting, but it's not actually that bad. Here's a quick summary of what needs to happen. Make sure to reference the relevant notes for detail. Please recall that we're working on a Vision "playground" here; some of my suggestions for speeding things up may not be comfortable in production.

  1. Start downloading all those fun patches! Here's a script to get you started, though you might need to make adjustments if patch requirements have changed.
  2. Upgrade the database from version 9.2.0.6 to 9.2.0.8, per note 216550.1 to make the 11gR2 upgrade smoother. Doesn't take long, and while the scripts are running, you can work on the next five steps. Just do the database pieces (Section 2, steps 3-9 & 12 should be sufficient) for now.
  3. Set s_discostatus=disabled in the context file to be picked up by the next Autoconfig run; this is 2011 for heaven's sake.
  4. Install the JDK version 6 rpm as root.
  5. Install 11.2.0.2 software and examples, per Note 881505.1
  6. Create NLS data directory per note 881505.1
  7. Apply database patches (9776940,10149223, 10165223, and 10229719) to the 11gR2 ORACLE_HOME.
  8. Bring the newly-patched 9.2.0.8 database up and apply the AD.I.7 patch set. Use the nocompileb option; you have a utlrp step coming later.
  9. Adjust adrelinknew.sh script, per Note 316806.1.
  10. To avoid an error during the next round of patches, make an adjustment to the IGW.IGW_AWARD_BUDGET_S sequence. This can also be fixed with a patch, but why go through the trouble of getting a password-protected patch for this exercise?
    SQL> alter sequence IGW.IGW_AWARD_BUDGET_S increment by 10000;
    SQL> select  IGW.IGW_AWARD_BUDGET_S.nextval from dual;
    SQL> alter sequence IGW.IGW_AWARD_BUDGET_S minvalue 10000;
    
  11. Apply the rest of the Apps patches (Merge them all. Yes, seriously). Again, use the nocompiledb option, and noautoconfig as well; the JDK step below will take care of that for you.
  12. Apply Forms 19 patchset and the small pile of related Forms and 8.0.6 ORACLE_HOME patches as documented in Doc ID 125767.1 -- don't forget the relink steps afterward
  13. Run txkrun.pl per section 5.1 of note 401561.1 (use jdk_top=/usr/java/latest)
  14. Regenerate jar files for jdk6 (use force option)
  15. Deploy the JRE to the apps tier per note 290807.1
  16. Perform the database upgrade per note 881505.1. There's a lot to do there; follow the note closely.
  17. Find something else to do with your day; recompiling 140K invalid objects might take a while.

Next steps ("Exercise for the Reader")

What, you thought you were done? Now that all of the technical bits are more-or-less up-to-date, you can turn your attention to security patches and applications updates. That may seem like a lot for a playground Vision instance, and maybe it is. It's also too much to cover in this blog post, but check the following notes if you're an overachiever:

Changes

Fall is my favorite time of year. In part, it's because of the weather where I grew up: muggy Pennsylvania summer and late-summer thunderstorms give way to crisp mornings, sometimes-warmish afternoons, and cool evenings. The air smells of wood smoke and dried leaves, and the colors of the sky and the trees are fantastic.

Autumn weather is a bit less epic where I live now, but there's another, non-weather-related reason that I like this time of year: for me, it's a time of new starts. I know springtime is supposed to be the time when everything's renewed, but for me it's all about fall. I was one of those insufferably nerdy kids who actually looked forward to the start of school, from grade 1 through university: new books, new courses, new people (okay, maybe that last one is a bit of a stretch ;-)). That carried through to my so-called adult life as well: A lot of the biggest and best changes in the last decade or two (with two very notable exceptions) have also taken place at this time of year, including the starts of many of my jobs.

Hey look, I'm getting to the point after only two paragraphs! New record! This fall, I'm making another transition: Starting today, I begin the "on-boarding" process at Pythian, as a member of one of their ERP teams. In a way, it feels as much like starting school as it does a new job. This move represents my biggest career challenge to date, and I look forward to learning a ton (and contributing a ton) in the company of a lot of smart, talented people. Since I'm going to be packing my brain full of new things over the next 3 weeks, I expect that the only blog activity here will be from pre-scheduled posts, drafts that I'd been putting off finishing until recently. Once I'm up to speed on the new system, you'll hopefully be hearing more from me on the Pythian blog!

Man, this is exciting. I sure hope the big kids don't shove me in a locker or something. ;)

Fun with EBS APIs, part II: Users and responsbilities (FND_USER_PKG)

In my previous post, I demo'ed some simple scripts to make changes to E-Business Suite profile options, using the API provided by the FND_PROFILE package. This post will look at the FND_USER_PKG package (no relation to the "ABS brake system"). While my examples only reference user creation and responsibility assignment, it's really worth reviewing the contents of the rest of the package; there's lots of useful stuff in there.

First things first

Disclaimer time! Are you surprised? I hope you're not surprised. Am I really copying this section from the previous post? Yup, I am!

  1. The example code presented in this series of blog posts uses APIs written by Oracle to manipulate data in the E-Business Suite database. This does NOT imply that the code in these posts is endorsed or supported by Oracle.
  2. The examples in this series of blog posts are presented as demonstration code only, and are neither guaranteed to work in your environment, nor in all cases. Since my environments are not the same as yours, I cannot offer support in the event that this code does not work for you. You are encouraged to make whatever modifications are necessary to make these examples work for you.
  3. The scripts used in these examples were developed and implemented in test and development environments, where change management rules are often more lax. Be sure to test thoroughly, and to consult your organization's change management procedures, before attempting to use this code in production.

Short form: There's data-changing code in these posts. Oracle won't support you if you use the code; I can't support you either. Before running this code in production without testing and approval, you should probably prepare a resumé and, optionally, schedule an appointment with a psychiatrist.

Example 1: Duplicating a user's responsibilities

This is probably most relevant in test/dev/CRP environments, where it's not uncommon to be asked, "Can you please set up Fred to have the same responsibilities as Jane in the UAT environment?" or "Can you please set up 12 users in the Training environment, all with the same responsibilities?" I've written a script called ebs_dupresp that will duplicate all of a user's directly-assigned responsibilities to a target user, optionally creating the user record:

Here's a two-part demo. For the first part, I've assumed that I just got the following email from my boss:

John, Please create an EBS account in the test environment for the new DBA, Devnul Larson. Just give him the same responsibilities that you have, for now.

APPS@R12VIS(11.1.0.7)>@ebs_dupresp johnp devnull
Source user: johnp
Target user: devnull
CTRL-C might be a good idea if these are incorrect/reversed.
Create devnull if non-existent? (y/N) y
User DEVNULL created with id 1013423
Adding responsibility System Administrator
Adding responsibility Oracle Diagnostics Tool
APPS@R12VIS(11.1.0.7)>@lresp johnp
Currently active responsibilities for user johnp

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Diagnostics		      NONE
FND		     Application Diagnostics		      NONE
JTF		     Oracle Diagnostics Tool		      NONE
SYSADMIN	     System Administrator		      NONE
APPS@R12VIS(11.1.0.7)>@lresp devnull
Currently active responsibilities for user devnull

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Diagnostics		      NONE
FND		     Application Diagnostics		      NONE
JTF		     Oracle Diagnostics Tool		      NONE
SYSADMIN	     System Administrator		      NONE

Later that day, another email arrives:

On second thought, just assign all of SYSADMIN's responsibilities to the new guy. BTW, why are you assigning all the new tickets to DEVNULL?

APPS@R12VIS(11.1.0.7)>@ebs_dupresp sysadmin devnull
Source user: sysadmin
Target user: devnull
CTRL-C might be a good idea if these are incorrect/reversed.
Create devnull if non-existent? (y/N) y
User DEVNULL already exists.
Adding responsibility Application Developer
Adding responsibility System Administrator
Adding responsibility Application Developer Common Modules
Adding responsibility System Administration
Adding responsibility Preferences SSWA
Adding responsibility Workflow
(I'll spare you the full list)

It's worth noting that this is a fairly primitive approach, particularly when it comes to the user creation piece (no LDAP integration, no association of user record with an HR person/party record, etc). The ebs_dupresp script also doesn't touch things like profile options, so it shouldn't be considered a "user clone" script. You could probably accomplish this work with FNDLOAD or a third-party tool like DataLoader, but I'd rather write SQL. ;-)

Example 2: Adding a list of responsibilities to a user

Another common need is adding responsibilities to multiple users. Maybe you want to add the Application Developer responsibility to a handful of new consultants, or grant the Oracle Diagnostics responsibility to some freshly trained business analysts and power users. With a little bit of setup, it's pretty easy to do bulk responsibility assignments in a way that's easily repeated across multiple instances. Here's an example script, ebs_bulkresp, that does the job:

As a demo, here's another theoretical work request:

Please assign Application Developer and Oracle Diagnostics responsibilities to users SBERG, YWATSON, and WPARKER. SBERG will also need Functional Administrator. WPARKER will be rolling off the project at the end of the year, so please end-date his responsibilities accordingly. These changes are approved for both development environments and all three CRP instances.

After loading the necessary information into a "staging table" (actually a simply-formatted external table), away we go:

APPS@R12VIS(11.1.0.7)>@ebs_bulkresp validate
Working with following records in table xxrespload:

USER_NAME	     APP	RESP_NAME			    END_DATE
-------------------- ---------- ----------------------------------- ---------------
SBERG		     FND	Application Developer		    NONE
SBERG		     FND	Functional Administrator	    NONE
SBERG		     JTF	Oracle Diagnostics Tool 	    NONE
WPARKER 	     FND	Application Developer		    31-DEC-2011
WPARKER 	     JTF	Oracle Diagnostics Tool 	    31-DEC-2011
YWATSON 	     FND	Application Developer		    NONE
YWATSON 	     JTF	Oracle Diagnostics Tool 	    NONE
Starting validation...
All users valid.
All app-responsibility pairs valid
Invoke script with APPLY to add responsibilities
APPS@R12VIS(11.1.0.7)>@ebs_bulkresp apply
Working with following records in table xxrespload:

USER_NAME	     APP	RESP_NAME			    END_DATE
-------------------- ---------- ----------------------------------- ---------------
SBERG		     FND	Application Developer		    NONE
SBERG		     FND	Functional Administrator	    NONE
SBERG		     JTF	Oracle Diagnostics Tool 	    NONE
WPARKER 	     FND	Application Developer		    31-DEC-2011
WPARKER 	     JTF	Oracle Diagnostics Tool 	    31-DEC-2011
YWATSON 	     FND	Application Developer		    NONE
YWATSON 	     JTF	Oracle Diagnostics Tool 	    NONE
Starting validation...
All users valid.
All app-responsibility pairs valid
Adding responsibilities...
User: YWATSON App: FND Resp: Application Developer
User: WPARKER App: FND Resp: Application Developer
User: SBERG App: FND Resp: Application Developer
User: SBERG App: FND Resp: Functional Administrator
User: YWATSON App: JTF Resp: Oracle Diagnostics Tool
User: WPARKER App: JTF Resp: Oracle Diagnostics Tool
User: SBERG App: JTF Resp: Oracle Diagnostics Tool
APPS@R12VIS(11.1.0.7)>@lresp ywatson
Currently active responsibilities for user ywatson

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      NONE
FND		     Application Diagnostics		      NONE
PSB		     Budget User - Accounting Dept, Progress  NONE
SQLGL		     General Ledger Super User, Progress S&L  NONE
ICX		     Internet Procurement, Progress S&L       NONE
JTF		     Oracle Diagnostics Tool		      NONE
SQLAP		     Payables Manager, Progress S&L	      NONE
ICX		     Preferences SSWA			      NONE
FND		     Workflow User, Progress S&L	      NONE
APPS@R12VIS(11.1.0.7)>@lresp sberg
Currently active responsibilities for user sberg

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      NONE
FND		     Application Diagnostics		      NONE
FND		     Functional Administrator		      NONE
IBE		     IBE_CUSTOMER			      NONE
JTF		     Oracle Diagnostics Tool		      NONE
ICX		     Preferences SSWA			      NONE
APPS@R12VIS(11.1.0.7)>@lresp wparker
Currently active responsibilities for user wparker

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      31-DEC-2011
FND		     Application Diagnostics		      31-DEC-2011
JTF		     Oracle Diagnostics Tool		      31-DEC-2011
ICX		     Preferences SSWA			      NONE
WMS		     Whse Mgmt Mobile User, Vision Operations NONE
		      (USA)

It's rare that you'll get a request that also includes the name of the application to which a responsibility belongs. If you don't keep a mental inventory of valid pairs of application and responsibility names (or maybe that's just me?), here's a quick way to look up that sort of info:

Fun with EBS APIs, part I: Profile options (FND_PROFILE)

The E-Business Suite APIs are not just the realm of developers and data migration specialists. There are a number of APIs, particularly in the FND module, that can make life a lot easier for Apps DBAs. In my next two (maybe three, if I'm feeling ambitious) posts, I'll provide some examples of how to use PL/SQL to perform tasks that normally require slogging around in the EBS Forms interface.

First things first

Disclaimer time! Are you surprised? I hope you're not surprised.

  1. The example code presented in this series of blog posts uses APIs written by Oracle to manipulate data in the E-Business Suite database. This does NOT imply that the code in these posts is endorsed or supported by Oracle.
  2. The examples in this series of blog posts are presented as demonstration code only, and are neither guaranteed to work in your environment, nor in all cases. Since my environments are not the same as yours, I cannot offer support in the event that this code does not work for you. You are encouraged to make whatever modifications are necessary to make these examples work for you.
  3. The scripts used in these examples were developed and implemented in test and development environments, where change management rules are often more lax. Be sure to test thoroughly, and to consult your organization's change management procedures, before attempting to use this code in production.

Short form: There's data-changing code in these posts. Oracle won't support you if you use the code; I can't support you either. Before running this code in production without testing and approval, you should probably prepare a resumé and, optionally, schedule an appointment with a psychiatrist.

Okay, moving on…

Now that I have all that out of the way, here are two basic examples demonstrating the use of FND_PROFILE. As with most PL/SQL code in the E-Business Suite database, FND_PROFILE is owned by the APPS schema. Browse through it, there's interesting stuff in there. Please note that the set_ebs_profile function referenced in these examples is my hackwork (defined in ebs_def_setprof.sql), not part of the Oracle-supplied API.

Example 1: Setting profile options after cloning

Autoconfig takes care of setting a lot of site-level profile options after cloning, but there may be a few instances when you need to change settings manually before releasing the instance to users. The following script is a template that I use, adding calls to set_ebs_profile as needed.

/* 
   ebs_postclone_prof.sql
   Contact/blame: John Piwowar
   Purpose: Set Site-level profile options after an EBS clone
   Requires: ebs_def_setprof.sql
   Notes: * Uses internal name for profile option, rather than user-readable 
            value (e.g. "SITENAME", not "Site Name"). Consult 
            fnd_profile_options_tl for appropriate values.
          * Site name and color scheme are the obvious ones; add others as 
            needed
*/

ACCEPT new_SID PROMPT "New SID (e.g. MYCLONE,NEWVIS, etc): "
ACCEPT new_color PROMPT "New Color Scheme (TEAL, RED, KHAKI, OLIVE, PURPLE, TITANIUM): "
ACCEPT env_type PROMPT "Instance type (e.g. Test, Dev, Sandbox): "
ACCEPT data_src PROMPT "Data source note (e.g. 'Prod clone, data as of DD-MON-YYYY'): "

set serveroutput on size 1000000 format word_wrapped;
set verify off feedback off 
 
spool ebs_postclone_prof_&&new_SID..log

DECLARE
@ebs_def_setprof
BEGIN
set_ebs_profile('FND_COLOR_SCHEME','&&new_color', TRUE);
set_ebs_profile('SITENAME','&&env_type (&&new_SID.) - &&data_src', TRUE);
END;
/

Example run:

APPS@R12VIS(11.1.0.7)>@ebs_postclone_prof
New SID (e.g. MYCLONE,NEWVIS, etc): JPR12VIS
New Color Scheme (TEAL, RED, KHAKI, OLIVE, PURPLE, TITANIUM): PURPLE
Instance type (e.g. Test, Dev, Sandbox): Visionary
Data source note (e.g. 'Prod clone, data as of DD-MON-YYYY'): Timeless data
Previous value of FND_COLOR_SCHEME: KHAKI
SUCCESS: Set FND_COLOR_SCHEME to PURPLE
Previous value of SITENAME: Vision Applications
SUCCESS: Set SITENAME to Visionary (JPR12VIS) - Timeless data

Example 2: Setting multiple profile options for a user

In a development environment, you may be asked to set debug profile options for particular modules. It can be handy to have a script that does this for a particular user, ensure that no one "accidentally" sets debug options at the site level (oh, I know, it's rare, but still…). It's ridiculously easy to put together a quick script to take care of this:

/*
   om_debug.sql
   Contact/blame: John Piwowar
   Purpose: Set Order Management debug values for a given user
   Usage: @om_debug <username> <debug level>
   Requires: ebs_def_setprof.sql
   Notes: I'm sure I'll think of something...
*/

SET serveroutput on size 1000000 format word_wrapped
SET verify off feedback off 

DECLARE
   outdir APPLSYS.FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
   uname APPLSYS.FND_USER.USER_NAME%TYPE := '&1';
   dlvl APPLSYS.FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE := '&2';
   @ebs_def_setprof
BEGIN
   --find a writable directory the lazy way, same way FND_FILE does it. ;-)
   SELECT substr(value,1,instr(value,',')-1)
     INTO outdir
     FROM v$parameter
    WHERE name = 'utl_file_dir';

   dbms_output.put_line('Setting OM debug for ' || uname);
   set_ebs_profile('OE_DEBUG_LOG_DIRECTORY', outdir, TRUE);
   set_ebs_profile('ONT_DEBUG_LEVEL', dlvl, TRUE, uname);
END;
/

Example run:

APPS@R12VIS(11.1.0.7)>@om_debug jpiwowar 5
Setting OM debug for jpiwowar
Previous value of OE_DEBUG_LOG_DIRECTORY: /usr/tmp
Skipping OE_DEBUG_LOG_DIRECTORY, no change
Target user: jpiwowar
Previous value of ONT_DEBUG_LEVEL: 0
SUCCESS: Set ONT_DEBUG_LEVEL to 5

The interesting bits

The code that defines the procedure set_ebs_profile can be found below. I've purposely written the script to be plugged into anonymous PL/SQL blocks instead of creating a procedure, because deployment of custom code is not always permitted.

/* 
   ebs_def_setprof.sql
   Contact/blame: John Piwowar
   Purpose: Braindead wrapper for setting site and user-level profile options 
            with fnd_profile.save. 
   Notes: * Call as part of the declare section of an anon PL/SQL block.
            That's my sad attempt at reusability without creating stored procs
          * Assumes a great many things, including that you're running as 
            a user with exec privileges on FND_PROFILE, and that you don't 
            need fancy error reporting if things go wrong. ;-)
          * Only handles site/user-level settings, per protocol El-Ay-Zed-Why
          * Still somewhat primitive, could probably use more of API for 
            success/failure messages, etc.
*/

PROCEDURE set_ebs_profile (
          prof_option APPLSYS.FND_PROFILE_OPTIONS.PROFILE_OPTION_NAME%TYPE
        , prof_value  APPLSYS.FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE 
        , override BOOLEAN DEFAULT FALSE -- controls behavior when already set
        , uname FND_USER.USER_NAME%TYPE DEFAULT NULL -- if no username passed, set at site level
        )
IS
   uid FND_USER.USER_ID%TYPE := NULL;
   prof_level VARCHAR2(8) := 'SITE'; --Current longest val is 'SERVRESP'
   set_result BOOLEAN := FALSE;
   prev_val_defined BOOLEAN := TRUE; --assume yes until we learn otherwise
   prev_val FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
BEGIN
   IF uname IS NOT NULL THEN
      dbms_output.put_line('Target user: ' || uname);
      prof_level := 'USER';
      BEGIN --validate username
         SELECT user_id
           INTO uid
           FROM applsys.fnd_user
          WHERE user_name = upper(uname); --do as I think, not as I type
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
           dbms_output.put_line('ERROR: No userid found for ' || uname || '.');
           RETURN; --Whoops. Here's your bucket, bail away.
      END; --validate username
   END IF;

   --check for previously-set value
 
   fnd_profile.get_specific( name_z => prof_option
                           , user_id_z => uid
                           , val_z => prev_val
                           , defined_z => prev_val_defined
                           ); 
  IF NOT prev_val_defined  THEN 
      dbms_output.put_line(prof_option || ' not previously defined');
   END IF; --NOT prev_val

   IF prev_val_defined THEN
      dbms_output.put_line('Previous value of ' || prof_option || ': ' || 
                            prev_val
                          );
   END IF; --prev_val_defined

   IF (NOT override) AND prev_val_defined THEN --do nothing
      dbms_output.put_line('Skipping ' || prof_option || ', OVERRIDE=FALSE');
   ELSIF override AND (prev_val = prof_value) THEN --do nothing
      dbms_output.put_line ('Skipping ' || prof_option || ', no change');
   ELSE --try to set new profile
      set_result := fnd_profile.save( x_name => prof_option
                                    , x_value => prof_value
                                    , x_level_name => prof_level
                                    , x_level_value => uid
                                    );
      IF set_result THEN
         dbms_output.put_line( 'SUCCESS: Set ' || prof_option || ' to ' || 
                               prof_value 
                             );
         COMMIT; --may want to move outside function for large batch updates
      ELSE
         dbms_output.put_line( 'FAILED to set ' || prof_option || ' to ' || 
                               prof_value 
                             );
         dbms_output.put_line('Check option name and value and try again');
         ROLLBACK; --For safety only; current implementation of
                   --fnd_profile.save does not attempt a write before
                   --returning false
      END IF; --set_result
   END IF; --NOT override AND prev_val_defined 
END set_ebs_profile;

Note that set_ebs_profile expects the internal name for a profile option, instead of the "user-readable" name you'd normally see in the Forms interface. It's useful to become familiar with a query similar to the following. Pay attention to the user_enabled_flag, or you might spend time wondering why your changes aren't sticking.

APPS@R12VIS(11.1.0.7)>@iprof "OM: Debug"

PROF_CODE	              PROF_NAME                                USER_EN
------------------------- ---------------------------------------- -------
ONT_DEBUG_LEVEL           OM: Debug Level                          Y
OE_DEBUG_LOG_DIRECTORY    OM: Debug Log Directory                  N

Oracle Instant Client on OS X Lion: 32-bit only, for now

This is already making the rounds, but I figured I'd add my voice to the chorus, to maximize the likelihood that people find the information that they need.

If you're an ORACLENERD and a relatively early adopter of new versions of Mac OS X, you might notice that the 64-bit version of the OS X Instant Client does not work under OS X Lion. Attempts to use the 64-bit sqlplus binary and libraries result in a segmentation fault. Boo. The only known solutions for command-line sqlplus from Lion at the this point seem to be use the 32-bit client, or stay on Snow Leopard. We're still in the early days of general availability for Lion, so perhaps another solution will surface.

I suspect that this doesn't mean good things for the full-blown database install, but I haven't checked that out yet (and I don't know when/if I will; my native OS X install of 10g had been gathering dust for quite a while, and did not make the move to my new MBP).

References: