Table statistics for staging / interface tables

Let's say you have an interface table which is usually empty. Let's say you also have a dilligent DBA who runs Gather Schema Stats periodically. Chances are the stats for this table will indicate that it is empty. This can lead to atrocious performance of queries involving this table when you actually fill it with data and run the associated interface program.

In the past I'd resolve this by altering the interface program to explicitly gather stats for the table before executing. However, in 10g at least, there is a much more pleasant way to deal with this - at least for situations where there are only a few SQL operations over the data in the table (if there are lots then it might still be worth gathering stats once at the start of the operation).

If Oracle encounters a table with missing statistics it will usually automatically do dynamic sampling of the table as part of determining the execution plan (unless dynamic sampling is turned off) - which is great!

So all we want to do is:

• Delete the statistics for the interface table

DBMS_STATS.DELETE_TABLE_STATS (
  ownname          VARCHAR2,
  tabname          VARCHAR2,
  partname         VARCHAR2 DEFAULT NULL,
  stattab          VARCHAR2 DEFAULT NULL,
  statid           VARCHAR2 DEFAULT NULL,
  cascade_parts    BOOLEAN  DEFAULT TRUE,
  cascade_columns  BOOLEAN  DEFAULT TRUE,
  cascade_indexes  BOOLEAN  DEFAULT TRUE,
  statown          VARCHAR2 DEFAULT NULL,
  no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                    get_param('NO_INVALIDATE')),
  force            BOOLEAN DEFAULT FALSE);

• Lock the stats so that none are gathered the next time Gather Schema Stats is run

DBMS_STATS.LOCK_TABLE_STATS (
  ownname    VARCHAR2,
  tabname    VARCHAR2);

Now, every time our interface runs, Oracle will dynamically sample whatever data is in the interface table - which ought to result in a much better plan than one based on the assumption of an empty table.

Best of all no code change was required.

Changing a user password programmatically

Silly users - always locking themselves out - forgetting their passwords and so forth. As indicated in a previous post - EBS can now be configured so that users can reset their passwords - having the new password emailed to the email address associated with their user account. However, not all clients enable this functionality.

My current client is one such. Recently they managed to lock themselves out of all their System Administrator level accounts. These are the only accounts with the ability to reset user passwords. A bit of a catch-22 situation you might think???

Fortunately Oracle have made it surprisingly easy to do a password reset programatically. Simply call:

fnd_user_pkg.changepassword(, )

It returns a boolean indicating success or otherwise. The new password must obey the relevant password policy or the reset will fail. The user will *not* be prompted to change this password the next time they log in.

Fixing an Audit Tables cock-up

In the course of my current project I've been asked to set up some auditing against Budgets. To do this I'm going to use standard Oracle Apps AuditTrail functionality against the GL_BALANCES tables. The auditted columns will be those that make up the primary key of the table and also the PERIOD_NET_CR and PERIOD_NET_DR columns.

Whoever runs the Budget Import program will be the user recorded as having made the changes.

During my testing on our development environment it became necessary for me to remove one of the columns being audited from the GL_BALANCES_A table (the table holding the audit data for GL_BALANCES).

Oracle say you can't do this. Actually, as I found out later, Metalink Note 383053.1 actually describes how to do this - but I tried the brute force approach of removing the audit table wholesale. This wasn't as straightforward as it might have been so I'm recording the steps here in case they may be useful for anyone else.

As we're hitting the tables directly here - all usual disclaimers apply - use any of the below entirely at your own risk!

If the table being auditted is MY_TABLE then to completely remove all trace of it from Auditting:

Remove the triggers Oracle has created against MY_TABLE (probably called MY_TABLE_AC/AD/AH/AI/AT/AU)
Remove the Procedures Oracle has created - probably called MY_TABLE_ADP/AIP/AUP.

Execute:

SELECT table_id from fnd_tables where table_name = 'MY_TABLE';

and make a note of the result - you will use this to clear down the FND_AUDIT_xxx tables.

Drop the audit table itself - MY_TABLE_A;

Execute the following:

DELETE FROM FND_AUDIT_TABLES WHERE table_id = ;
DELETE FROM FND_AUDIT_COLUMNS WHERE table_id = ;

If you want to clear out the Audit Group you need to delete the relevant record from FND_AUDIT_GROUPS.

If you want to disable the relevant application for auditing then you need to delete the relevant record from FND_AUDIT_SCHEMAS (this is the table that is updated when you tick the application in the Sys Admin / Security / AuditTrail / Install form.

The above steps remove all trace of auditting from Oracle Apps and allow the developer to start again from a clean slate.

Good luck.

Creating CSV files the (well - an) easy way

Recently I've been asked to generate some GL extracts. Specifically:

• A GL balance extract
• An extract of the account segment values and descriptions
• An extract of the hierarchies for the various account segments.

Now, whilst something like a hierarchy might seem an ideal candidate for expressing in XML format, my employer wants this generated the old fashioned .CSV way. I knocked up the below function which let's you generate your CSV data record as simply as:

v_file_line := csvize(t_stringlist('Contents of Field 1','Contents of "Field 2"',etc...));

You can specify as many fields as you like and the function deals appropriately with strings containing the quote character (by double quoting them - which seems to be pretty standard).

TYPE t_stringlist IS TABLE OF VARCHAR2(256);

-- Format a list of strings as CSV
----------------------------------
function csvize(p_stringlist t_stringlist) return varchar2 is
deliminator constant char(1) := '"';
separator constant char(1) := ',';
line VARCHAR2(1000);
begin
FOR i IN p_stringlist.FIRST .. p_stringlist.LAST LOOP
line := line || deliminator || REPLACE(p_stringlist(i),deliminator, deliminator || deliminator) || deliminator;
IF i != p_stringlist.LAST THEN
line := line || separator;
END IF;
END LOOP;
RETURN line;
end;

If you find it useful, let me know!

Logging into the Application from PL/SQL

When you're writing PL/SQL code it's pretty normal to want to pretend to be a particular user in a particular responsibility. That way you can write your code 'correctly' against the application views etc but still be able to test it in fine detail from your chosen PL/SQL IDE.

BTW - my IDE of choice - and I've been using it for 10 years now - is the excellent PL/SQL Developer. It has an great integrated debugger for stepping line by line through your code.

Here's a bit of code you can compile onto your dev environment and use in test scripts to simulate being logged in as a particular user.

CREATE OR REPLACE PROCEDURE apps_login(p_user_name VARCHAR2, p_resp_name VARCHAR2) is

/* Shamless Plug!
Visit ebsconsultant.blogspot.com */

v_user_id BINARY_INTEGER;
v_resp_id BINARY_INTEGER;
v_app_id BINARY_INTEGER;
BEGIN
SELECT user_id
INTO v_user_id
FROM APPS.FND_USER
WHERE user_name like p_user_name;

select r.responsibility_id,
urg.responsibility_application_id
into v_resp_id,
v_app_id
from fnd_user_resp_groups urg,
fnd_responsibility_vl r
WHERE r.RESPONSIBILITY_NAME LIKE p_resp_name AND
r.responsibility_id = urg.responsibility_id AND
urg.user_id = v_user_id;

dbms_output.put_line('user_id=' || v_user_id || ' resp_id=' || v_resp_id || ' app_id=' || v_app_id);
FND_GLOBAL.APPS_INITIALIZE(v_user_id, v_resp_id, v_app_id);
FND_SIGNON.set_session(NULL);
COMMIT;
END;

Automating Password Resets

As a freelance EBS Consultant I can end up in all sorts of roles. Sometimes I'm the functional lead for a module, sometimes I'm doing some hard core SQL geekery, but usually, no matter what exciting bits and pieces I'll be working on there is always (unless I'm lucky enough to be working on a greenfield implementation) the background hum of disgruntled users who need supporting.

One common support task is resetting user passwords.

Most grown up websites let the user automatically request a password reset if they can't remember, or if they've locked themselves out of their account.

Since the reasonably early days of 11i Oracle Applications has also offered this functionality but at many clients it isn't enabled by default.

If, when logging in through the web page (as opposed to logging in directly to the forms - see my posting here on how to quickly get into the forms applications) you don't see an option to allow you to reset your password, you need to do the following:

• Find the system profile option called "Local Login Mask"
• Add 8 to it's current value at the Site level.
  
• Bounce the Application Server

Voila!

Oracle uses standard Workflow functionality and email notifications to ensure the user wants their password resetting and notifying them of their new password.

In order for the solution to work then, you need to make sure that Workflow Background engine is ticking along and that all your Users are mapped to email addresses - either directly through the User Define form in System Administrator, or through the user's mapping to a Person - and making sure the Person has an email address defined.

At most sites this will already be in place - so this is a quick win to reduce the volume of support calls you'll be dealing with.

A more detailed explanation can be found here.

Logging into the Forms based applications directly

Back in the good old days, before Oracle decided that HTML based applications was the way to go - you used to log directly in to the Forms based application.

You can still accomplish this - even I believe in R12 - but certainly all the way up to 11.5.10.2.

Simply use a URL like:

http://myappsserver.mycompany.com:[apps port number]/dev60cgi/f60cgi

This will take you directly to the forms version of the application. I've used this approach for years and the only problem I've really found is that, once logged in this way, you can't use any menu options which would jump you back out a HTML bit of the Application. (e.g. Workflow Administrator stuff).

I often just need to login to the Apps to make a targeted small change (e.g. change a system profile option). Having one desktop shortcut for each environment in the above format lets me dive in and out of the application much quicker than having to trawl through the web based login procedure.

Something else that is useful I find is to colour my environments by