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     varchar2(10) := '  '; -- used to split "levels" in SQL
  v_Table       all_tab_cols.table_name%TYPE := upper('wf_local_user_roles');
  v_Owner       all_tab_cols.owner%TYPE := upper('APPLSYS');
  v_WhereClause varchar2(2048) := 'where user_name = '''' and role_orig_system_id = 22918';
  v_QuerySQL    varchar2(2048);
  v_Result      varchar2(512);
  v_RowID       ROWID;

  v_ColumnCount number := 0;

  TYPE ref_cur_typ IS REF CURSOR;
  ref_cur  ref_cur_typ;
  data_cur ref_cur_typ;

  cursor c_Columns is
    select atc.column_name, atc.data_type
      from all_tab_cols atc
     where atc.owner = v_Owner
       and atc.table_name = v_Table
     order by atc.column_id;
begin
  v_QuerySQL := 'select ROWID from ' || v_Owner || '.' || v_Table || ' ' ||
                v_WhereClause;

  open ref_cur for v_QuerySQL;
  loop
    -- Get the ROW ID (unique identifier) for each row we wish to add as an insert
    fetch ref_cur
      into v_RowID;
    EXIT WHEN ref_cur%NOTFOUND;
    dbms_output.put_line(v_Spacing || 'insert into ' || v_Owner || '.' ||
                         v_Table);
    dbms_output.put_line(v_Spacing || 'select');
    v_ColumnCount := 0;
    for v_Column in c_Columns loop
      -- Loop through the columns in the table, for each row
      v_ColumnCount := v_ColumnCount + 1;
      if v_Column.data_type in ('VARCHAR2', 'CHAR') then
        v_QuerySQL := 'select ' || v_Column.Column_Name || ' from ' ||
                      v_Owner || '.' || v_Table || ' where rowid = ''' ||
                      v_RowID || '''';
        open data_cur for v_QuerySQL;
        fetch data_cur
          into v_Result;
        close data_cur;
      
        dbms_output.put(v_Spacing || v_Spacing);
        if v_ColumnCount > 1 then
          dbms_output.put(',');
        end if;  
        dbms_output.put_line('''' || v_Result || '''');
      elsif v_Column.data_type in ('FLOAT', 'NUMBER') then
        v_QuerySQL := 'select to_char(' || v_Column.Column_Name || ') from ' ||
                      v_Owner || '.' || v_Table || ' where rowid = ''' ||
                      v_RowID || '''';
        open data_cur for v_QuerySQL;
        fetch data_cur
          into v_Result;
        close data_cur;
      
        dbms_output.put(v_Spacing || v_Spacing);
        if v_ColumnCount > 1 then
          dbms_output.put(',');
        end if;  
        dbms_output.put_line(v_Result);
      elsif v_Column.data_type in ('DATE') then
        v_QuerySQL := 'select to_char(' || v_Column.Column_Name || ', ''DD-MON-YYYY HH24:MI:SS'') from ' ||
                      v_Owner || '.' || v_Table || ' where rowid = ''' ||
                      v_RowID || '''';
        open data_cur for v_QuerySQL;
        fetch data_cur
          into v_Result;
        close data_cur;
      
        dbms_output.put(v_Spacing || v_Spacing);
        if v_ColumnCount > 1 then
          dbms_output.put(',');
        end if;  
        dbms_output.put_line('to_date(''' || v_Result || ''', ''DD-MON-YYYY HH24:MI:SS'')');
      end if;
    end loop;
    dbms_output.put_line(v_Spacing || 'from dual;');
  end loop;
  close ref_cur;
end;

This will only handle tables where all the columns are of one of the specified data types (DATE, VARCHAR2, NUMBER, etc). The resulting insert statements are written to the standard output channel so if you have a lot of records you might want to enlarge it beyond the 10,000 character (or so) default!

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 fan of the "start again" approach and so I wrote this little PL/SQL routine to find the source code of a package body in Oracle and remove *almost* all the comments. The comments it will keep are those that exist on a line of code. For example if we have the code block;

/* This is a standard block
comment */
-- prepare to increment loop counter
v_Int := v_Int + 1; -- increment loop counter

Then this routine will strip out the block comment and the point where the line starts with "--" but *not* the comment that comes after the line of code (the -- increment ...).

Of course you can tailor this to your heart's content.

Now, a little note on execution. I use PL/SQL Developer from All Round Automations to do my editing. This has a nice feature called a "Test Window". This allows you to pass parameters to/from a script. I've used this feature with this script to generate a script that populates two parameters, one with the original source code and the other with the "edited" version. If you don't use PL/SQL developer you'll need to find some other way of achieving this.

Anyway, here is the script, you'll need to replace &XXXXX with your package name:

declare 
  -- Local variables here
  v_CharNo number := 1;
  v_CharCount number;
  v_InComment boolean;
  v_AddChar boolean;
  v_SourceCode clob;
  v_Chars varchar2(2);
  
  v_Text all_source.text%TYPE;
  
  cursor c_GetSource is
    select text
    from all_source
    where name = '&XXXXX'
    and type = 'PACKAGE BODY';
    procedure addToCLOB(v_Text in varchar2) as
    begin
      dbms_lob.writeappend(v_SourceCode, length(v_Text), v_Text);
    end;
begin
  -- Test statements here
  dbms_lob.createtemporary(lob_loc => v_SourceCode, cache => False);
      
  :old_data := '';
  for v_Line in c_GetSource loop
    v_Text := trim(v_Line.Text);
    if substr(v_text, 1, 2) != '--' then
      addToCLOB(v_Line.Text);
    end if;
  end loop;
  :old_data := v_SourceCode;
    
  v_CharCount := length(:old_data);
  v_InComment := False;
  while v_CharNo <= v_CharCount loop
    if (not v_InComment) and (substr(:old_data, v_CharNo, 2) = '/*') then
      v_InComment := True;
    end if;
    
    v_AddChar := not v_InComment;
    if v_AddChar then
      :new_data := :new_data || substr(:old_data, v_CharNo, 1); 
    end if;
    if (v_InComment) and (substr(:old_data, v_CharNo-2, 2) = '*/') then
      v_InComment := False;
    end if;
    v_CharNo := v_CharNo + 1;
  end loop;
end;

Pretty simple stuff, if you have any questions drop me a comment ...

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 less than nothing, although someone called Margaret helped a great deal in relation to the SR I raised during this work!

Ok, a bit of background first. The aim here is to, using a PL/SQL script, create an Event-based Oracle Alert on a custom table within the Oracle database. The reasoning behind this is that I work in the Pharmaceutical industry and we use Oracle Manufacturing. Needless to say when you're dealing with drugs you can't have "random" changes being made to your database. Our environment is very tightly controlled.

At the moment we have a production server and five test/ development servers. We actually have an excellent cloning process that means if we need another copy from production it takes about a day to do.

The purpose behind this work was to enable us to write a script to implement an Oracle Alert that we could develop on our development instance, then run on our test instance and confirm it works before finally running it against production.

At the moment we are implementing Oracle Internet Expenses and are on our third refresh of our development server. so there will also be some time saving should we go onto our fourth or even fifth refresh!

Without this script we would have had to go in and make manual changes in the Oracle UI. This introduces human error and is very time consuming.

History lesson over, let's make a start.

First off let's assume we have the table CCL (stands for Credit Card Loader if anyone cares) which is created using the following script:

create table CCL
(
  PROCESS_ID NUMBER not null,
  LINE_ID    NUMBER not null,
  TEXT       VARCHAR2(2048) not null,
  VALID_BOO  VARCHAR2(1) default 'T'
);

This receives a credit card transaction in a Barclaycard feed (it's part of our i-Expenses implementation) - each line in the file is inserted into this table. A trigger populates the VALID_BOO with either T or F depending on the data in TEXT.

In order to register this table with Oracle applications (we're going to register it as part of payables - the same as Internet Expenses) use this script to perform the registration. The script uses Oracles AD_DD package (REGISTER_TABLE, REGISTER_COLUMN) to make APPS aware of the new table.

The key things for you to change to get this to work for you are both variables at the top of the file. v_TableName is the name of the table you wish to add, and v_AppShortName is the short name for the application (look in fnd_application_tl to get the application ID for your application, and then lookup the short code in fnd_application - Payables is SQLAP).

Whilst the script does remove existing registered columns if you change the delete a column from the table and then re-run the script you'll find yourself at the mercy of whatever it is Oracle does with the orphaned record ... Maybe delete? Maybe leave ... who knows? I for one have not tested that!

To keep this as simple as possible at this stage you need to create a concurrent request. It doesn't matter what it is just that you call it "CCL" and make sure it takes no parameters - you can do anything you like. Me personally I'd create one that writes a random record into a log table of some sort. Anyway, that part is down to you.

Next we use the ALR_ALERTS_PKG package to create an alert;

alr_alerts_pkg.load_row(x_application_short_name       => 'SQLAP',
                          x_alert_name                   => 'CCL_NEW',
                          x_owner                        => null,
                          x_alert_condition_type         => 'E',
                          x_enabled_flag                 => 'Y',
                          x_start_date_active            => sysdate,
                          x_end_date_active              => null,
                          x_table_application_short_name => 'SQLAP',
                          x_description                  => 'Starts the second credit card loading process after a successful load',
                          x_frequency_type               => 'O',
                          x_weekly_check_day             => null,
                          x_monthly_check_day_num        => null,
                          x_days_between_checks          => null,
                          x_check_begin_date             => null,
                          x_date_last_checked            => null,
                          x_insert_flag                  => 'Y',
                          x_update_flag                  => 'N',
                          x_delete_flag                  => null,
                          x_maintain_history_days        => 0,
                          x_check_time                   => null,
                          x_check_start_time             => null,
                          x_check_end_time               => null,
                          x_seconds_between_checks       => null,
                          x_check_once_daily_flag        => null,
                          x_sql_statement_text           => 'select process_id into &PROCESSID from ccl where text like ''TRLR%'' and rowid = :ROWID',
                          x_one_time_only_flag           => null,
                          x_table_name                   => 'CCL',
                          x_last_update_date             => null,
                          x_custom_mode                  => null);

Now this calls the LOAD_ROW package (rather than INSERT_ROW) because if the row already exists this will update it.

As you can see the huge bulk of values inserted are null, these are used mostly for periodic alerts rather than event-driven alerts. The values passed in are pretty self-explanatory - if you are lost for some of these values then you can always read this whole document, create the alert manually in Oracle (as the Alert Manager responsibility) and then look in the corresponding table and see what the values should be. Oracle have stuck with a fairly clear naming convention for these packages; ALR_ALERTS_PKG will manage data in the ALR_ALERTS table.

NB: The most likely causes of errors running this code are due to the table application short name and/or table being incorrect.

As far as I'm aware these next steps can be carried out in any order (but I have only tested them in the order they're listed here).

Setting up Alert Installations

This uses the following API;

  alr_alert_installations_pkg.load_row(x_application_short_name => 'SQLAP',
                                       x_alert_name => 'CCL_NEW',
                                       x_oracle_username => 'APPS',
                                       x_data_group_name => '&XXXX',
                                       x_owner => null,
                                       x_enabled_flag => 'Y',
                                       x_last_update_date => null,
                                       x_custom_mode => null);

This is very important, it took me quite a while to work out why my alerts weren't firing and it ended up being due to not having added the APPS user as an Alert Installation. In the example code above where it says "&XXXX" you need to specify the group name for the APPS login.

Now this ONLY work if you have the APPS environment initialised for the login. You can do this by executing the command;

  APPS.FND_GLOBAL.APPS_INITIALIZE(user_id      => 102,
                                    resp_id      => 2304,
                                    resp_appl_id => 17);

You'll need to specify your own values for user, responsibility id and responsibility application id. if everything else looks ok (and you can see the Check Event Alert Concurrent Request starting but not your request) then this is likely the cause of your problem.

Next use the ALR_ACTIONS_PKG package to create an action. This will be to run the concurrent request we configured earlier.

alr_actions_pkg.load_row(x_application_short_name   => 'SQLAP',
                           x_alert_name               => 'CCL_NEW',
                           x_action_name              => 'CCL_CONC_REQ',
                           x_action_end_date_active   => null,
                           x_owner                    => null,
                           x_action_type              => 'C',
                           x_enabled_flag             => 'Y',
                           x_description              => 'Run a concurrent request',
                           x_action_level_type        => 'D',
                           x_date_last_executed       => null,
                           x_file_name                => null,
                           x_argument_string          => null,
                           x_program_application_name => 'SQLAP',
                           x_concurrent_program_name  => 'CCL',
                           x_list_application_name    => null,
                           x_list_name                => null,
                           x_to_recipients            => null,
                           x_cc_recipients            => null,
                           x_bcc_recipients           => null,
                           x_print_recipients         => null,
                           x_printer                  => null,
                           x_subject                  => null,
                           x_reply_to                 => null,
                           x_response_set_name        => null,
                           x_follow_up_after_days     => null,
                           x_column_wrap_flag         => 'Y',
                           x_maximum_summary_message  => null,
                           x_body                     => null,
                           x_version_number           => 1,
                           x_last_update_date         => null,
                           x_custom_mode              => null);

As you can see all the options are here, again it might be easier if you created your alert in the database and then queried the tables directly to find the necessary values for all these API calls. the key things to note are that action type "C" is Concurrent Request and action level type "D" is Detail (as you would see in the GUI).

Next you need to create an action set for your action. This is done using the ALR_ACTION_SETS_PKG package;

  alr_action_sets_pkg.load_row(x_application_short_name    => 'SQLAP',
                               x_alert_name                => 'CCL_NEW',
                               x_name                      => 'CCL_REQSET',
                               x_owner                     => null,
                               x_end_date_active           => null,
                               x_enabled_flag              => 'Y',
                               x_recipients_view_only_flag => 'N',
                               x_description               => 'Request set for CCL_NEW',
                               x_suppress_flag             => 'N',
                               x_suppress_days             => null,
                               x_sequence                  => 1,
                               x_last_update_date          => null,
                               x_custom_mode               => null);

Now you have an alert, an action and an action set you can configure the outputs for all three together using these three API calls. Note that in the SQL for the alert I made use of a PROCESSID output value, these calls correctly register this with the system.

  alr_action_outputs_pkg.load_row(x_application_short_name => 'SQLAP',
                                  x_alert_name             => 'CCL_NEW',
                                  x_action_name            => 'CCL_CONC_REQ',
                                  x_action_end_date_active => null,
                                  x_action_out_name        => 'PROCESSID',
                                  x_owner                  => null,
                                  x_critical_flag          => 'N',
                                  x_end_date_active        => null,
                                  x_last_update_date       => null,
                                  x_custom_mode            => null);

  alr_alert_outputs_pkg.load_row(x_application_short_name => 'SQLAP',
                                 x_alert_name             => 'CCL_NEW',
                                 x_name                   => 'PROCESSID',
                                 x_owner                  => null,
                                 x_sequence               => 1,
                                 x_enabled_flag           => 'Y',
                                 x_start_date_active      => sysdate,
                                 x_end_date_active        => null,
                                 x_title                  => 'PROCESSID',
                                 x_detail_max_len         => null,
                                 x_summary_max_len        => null,
                                 x_default_suppress_flag  => 'Y',
                                 x_format_mask            => null,
                                 x_last_update_date       => null,
                                 x_custom_mode            => null);

  alr_action_set_outputs_pkg.load_row(x_application_short_name => 'SQLAP',
                                      x_alert_name             => 'CCL_NEW',
                                      x_name                   => 'CCL_REQSET',
                                      x_action_set_output_name => 'PROCESSID',
                                      x_owner                  => null,
                                      x_sequence               => 1,
                                      x_suppress_flag          => 'Y',
                                      x_last_update_date       => null,
                                      x_custom_mode            => null);

Next now the outputs are configured we can setup the action set members;

  alr_action_set_members_pkg.load_row(x_application_short_name => 'SQLAP',
                                      x_alert_name             => 'CCL_NEW',
                                      x_name                   => 'CCL_REQSET',
                                      x_owner                  => null,
                                      x_action_name            => 'CCL_CONC_REQ',
                                      x_group_name             => null,
                                      x_group_type             => null,
                                      x_sequence               => 1,
                                      x_end_date_active        => null,
                                      x_enabled_flag           => 'Y',
                                      x_summary_threshold      => null,
                                      x_abort_flag             => 'A',
                                      x_error_action_sequence  => null,
                                      x_last_update_date       => null,
                                      x_custom_mode            => null);

Now this is the alert setup and ready to go - you can go into the Oracle GUI and check and everything should be there. Unfortunately it's possible for some of these calls to "silently" fail so it's necessary to perform this basic validation.

The final thing that's missing is the system-generated trigger on the CCL table that will fire off the alert. This API uses a different format than all the others so you need to put together a block of code;

declare
    cursor c_AlertDetails is
      select a.application_id,
             a.alert_id,
             a.table_application_id,
             a.table_id
        from applsys.alr_alerts a
       where a.alert_name = 'CCL_NEW';
  begin
    dbms_output.put_line('Creating a tigger on CCL for the alert (on insert) ...');
    for v_Alert in c_AlertDetails loop
      alr_dbtrigger.create_event_db_trigger(appl_id     => v_Alert.application_id,
                                            alr_id      => v_Alert.alert_id,
                                            tbl_applid  => v_Alert.table_application_id,
                                            tbl_name    => 'CCL',
                                            oid         => null,
                                            insert_flag => 'Y',
                                            update_flag => 'N',
                                            delete_flag => 'N',
                                            is_enable   => 'N');
    end loop;
    dbms_output.put_line('... Done');
  end;

If you query the database using PL/SQL Developer, TOAD, etc then you'll see the trigger is now available on the table.

A quick test;

begin
  -- Test statements here
    APPS.FND_GLOBAL.APPS_INITIALIZE(user_id      => 102,
                                    resp_id      => 2304,
                                    resp_appl_id => 17);
  delete from ccl where process_id = 41 and line_id = 3804;
  insert into ccl (process_id, line_id,text)
  values (41, 3804, 
    'TRLR{');
  commit;
end;

If you log in as a System Administrator and look at concurrent requests you should see the CCL (Check Event Alert) start up and then a few second later your concurrent request should start to run.

Should you fancy looking at my script it's available here.

Hope this saves you the 2 days it took me to get this working!

NOTE: You may freely take my scripts and do whatever you want with them, so long as you don't try and pin anything they do (or don't do) on me.

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 it so that it could only be running once at any given time:

-- Setting up Notify Users of New Credit Card Transactions
  begin
    fnd_program.delete_incompatibility(program_short_name   => 'NUNCCT',
                                       application          => 'Payables',
                                       inc_prog_short_name  => 'NUNCCT',
                                       inc_prog_application => 'Payables');
  exception
    when others then
      null;
  end;
  begin
    fnd_program.delete_program(program_short_name => 'NUNCCT',
                               application        => 'Payables');
  exception
    when others then
      null;
  end;
  begin
    fnd_program.delete_executable(executable_short_name => 'NUNCCT',
                                  application           => 'Payables');
  exception
    when others then
      null;
  end;
  fnd_program.executable(executable          => 'Notify Users of New Credit Card Transactions',
                         application         => 'Payables',
                         short_name          => 'NUNCCT',
                         description         => 'Notify Users of New Credit Card Transactions',
                         execution_method    => 'PL/SQL Stored Procedure',
                         execution_file_name => 'APPS.CCL_MANAGER.NotifyUsersOfNewTransactions');
  fnd_program.register(program                => 'Notify Users of New Credit Card Transactions',
                       application            => 'Payables',
                       enabled                => 'Y',
                       short_name             => 'NUNCCT',
                       description            => 'Notify Users of New Credit Card Transactions',
                       executable_short_name  => 'NUNCCT',
                       executable_application => 'Payables',
                       print                  => 'N',
                       use_in_srs             => 'Y');
  fnd_program.incompatibility(program_short_name   => 'NUNCCT',
                              application          => 'Payables',
                              inc_prog_short_name  => 'NUNCCT',
                              inc_prog_application => 'Payables',
                              scope                => 'Set',
                              inc_type             => 'G');
  commit;

This script sets up a executable using FND_PROGRAM.EXECUTABLE, then uses FND_PROGRAM.REGISTER to setup a new concurrent request, and finally FND_PROGRAM.INCOMPATIBILITY to tell the system that only one copy of the concurrent request should be run at any given time.

The begin ... end blocks at the start are just there to delete any data already configured from previous runs of the script (useful in a test environment). The reason for putting them in exception blocks is that the first time you run you would be passing in invalid parameters (the program short code) because it doesn't exist yet.

In case you were wondering this was a concurrent request that would check to see if the user had any new credit card transactions following a load and send them a nice e-mail reminding them to fill in an expense claim (via Oracle i-Expenses).