Oracle PL/SQL: Using Dynamic SQL to Build an INSERT ... INTO Statement From Any Query

At the moment I'm writing test script for an Oracle Internet Expenses implementation. A fairly simple need has arisen to take the result of a SELECT ... FROM statement and convert it into an INSERT ... INTO - basically this will allow some of the tests to be repeatable (i.e. they are creating users, assigning responsibilities, etc).The following PL/SQL script generates insert statements using the standard DBMS_OUTPUT package:declare -- Script to convert a SQL Statement into an INSERT statement -- (useful for generating test scripts) v_Spacing...

Read More

Oracle PL/SQL: Stripping Comments From PL/SQL Packages

Now I've worked in several places with different coding policies. Some have said "comment your code where the meaning isn't clear" (which makes sense) and others have said that your code should include a (commented out) complete history of changes. Clearly if you adopt the second approach after a few years and several changes your code is going to go from being 90% code 10% comments to 90% comments 10% code.It's at that stage you change your policy and switch to using a source control system to track changes - but what to do with code?!I'm a big...

Read More

Oracle EBS: Scripting the Creation of Event-based Oracle Alerts in Oracle e-Business Suite

This was possibly one of the hardest things I've ever had to do in a long time. Not because it's technically challenging, but because there was just not enough information out there. If you search for Oracle Alerts you can see that using the FND_LOAD package there are a couple of example of how to migrate alerts from one server to another ... But not really anything on scripting their creation from scratch. You know you're in trouble when a Google search for some of the API's you're using returns no results. Naturally Oracles Metalink provided...

Read More

Oracle EBS: How to Create Oracle Concurrent Requests using PL/SQL Scripts (Using FND_PROGRAM)

This was driving me mad. At the moment we tend to configure the Oracle e-Business Suite through the standard Java interface. The problem is that with big changes (like the i-Expenses implementation I'm currently working on) are first done on a development server, then on a test server, and finally on a live server. Each time the change has to be repeated and validated. This takes an age.It turns out that Oracle has a perfectly serviceable API for this, the FND_PROGRAM package.Running the script below setup a concurrent program, a request, and configured...

Read More