Add Code to Oracle Reports via Forms FormsAPI Master

For our migration to Oracle ADF we have to identify, which Forms and Reports modules are still really called. For Forms we already have implemented a call statistic. In this blog post I show you how you can automatically implement the same into over 200 reports.

For this we use FormsAPI Master from ORCL Toolbox, which brings us scripting for report changes.

How do you start with sripting there?

Look at Scripts → Tutorial Scripts in the program or on the Scripts page on the website, if there are already a script, which do what you want or something similar. For my use case I found here ReportsAPI_PLSQL.p2s, which find and change a AfterParameterForm trigger.
Search in the Forums on the website. For my use case I found here how I can create reports trigger, if it does not exist.
Via Scripts → Wizard you can create basic structures.

1 Here I get how I can open a parameter dialog for selecting a directory and then iterating over it.
If you not know how to find a specific object, open your module in Explore.

1 Then select your object.

2 Ensure, that Diplay Options → Show Forms API Info is selected.

3 Now you can copy the code to find the object.
In the sript editor itself you have access to

1 Script Builtin Functions,

2 Code Snippets and

3 Editor Quick Nodes

Now see my resulting code:

reports_call_statistic.p2s

DECLARE
  l_file, l_report, l_pu_aufstat, l_report_trigger, l_before_report_trigger, l_result: number;
  l_file_path, l_source : varchar2;
  l_param_screen        : TParamScreen;
  l_param_board         : TParamBoard;
  l_file_list           : tstringlist;

BEGIN
  l_param_screen := TParamScreen.create;
  try
     (1)
    l_param_board := l_param_screen.AddBoard('Options', picOptions);
    l_param_board.addparam(parPathname,'MYDIR', 'Directory', 'C:\reports', '');
    if l_param_screen.ShowParamScreen('Please select directory') then
    begin
      (2)
      l_file_list := GetFileList(l_param_screen.paramvalue('MYDIR'), '*.rdf', true);
      for l_file in 0 .. l_file_list.count-1 do
      begin
        try
          l_file_path := l_file_list.strings[l_file];
          logadd('processing ['|| (l_file+1) ||'/' || l_file_list.count ||'] - '||l_file_path, logInfo);
          l_report := RepAPI_LoadModule(l_file_path); (3)
          l_report_trigger := RepGeneric_GetObjProp(l_report, D2RP_REP_TRIGGER); (4)
          l_before_report_trigger := RepGeneric_GetObjProp(l_report_trigger, D2RP_REP_BREPORT_OBJ);
          if l_before_report_trigger != 0 then

          begin
            logadd('BEFOREREPORT trigger found' + IntToStr(l_before_report_trigger), logInfo);
          end else
          begin
            logadd('BEFOREREPORT trigger not found', logInfo);
            l_before_report_trigger := RepGeneric_Create(l_report, 'BEFOREREPORT', D2RRO_PROG_UNIT); (5)
            RepGeneric_SetNumProp(l_before_report_trigger, D2RP_PGU_TYPE, D2RC_PGTY_BEFOREREPORT);
            logadd('BEFOREREPORT trigger created: ' + IntToStr(l_before_report_trigger), logInfo);
          end;

          begin
            l_source := RepAPI_GetPLSQL(l_before_report_trigger); (6)
            // call WRITE_STAT before return
            l_result := Replace_PLSQL_word(
              l_source,
              'return (TRUE);',
              'WRITE_STAT; return (TRUE);');
            RepAPI_SetPLSQL(l_before_report_trigger, l_source);
            if l_result > 0 then
            begin
              logadd('WRITE_STAT ' + IntToStr(l_result) + '-time inserted into BEFOREREPORT trigger', logInfo);
            end else
            begin
              logadd('WRITE_STAT not inserted into BEFOREREPORT trigger: ' +  IntToStr(l_result), logError);
            end;
          end;

          l_file_path := 'C:\reports_new\' + extractfilename(l_file_path); (7)
          RepAPI_SaveModule(l_report, l_file_path);
          RepAPI_FreeModule(l_report);
          logadd('New File created: ' + l_file_path, logInfo);

        except
          logadd(SQLERRM, logError);
        end;
      end;
      l_file_list.free;

    end else
    begin
      --User must have canceled the parameter screen
      logadd('Script run canceled by user on the parameter screen', logWarning);
    end;
  finally;
    --free the parameter screen
    l_param_screen.free;
  end;

END;

1	First I define the parameter screen to select my source directory for my rdf files.
2	Then I loop over all found files in this directory.
3	Here I load on reports file into memory and
4	search for an existent Before Reports Trigger.
5	If I don’t find one, then I create one.
6	Then I modify the code with the call to statistic.

Now you can run your script directly from the tool or via CLI and this is the output:

add code to reports via forsm api master log

It’s up to you, if you put your statistic procedure in a new program unit into the form, or attach a library with the code or put your code directly into the database.

Depending what you need to record you should have a look into following or similar libraries

SRW.GET_REPORT_NAME
SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY')

That’s it!

1	Then select your object.
2	Ensure, that Diplay Options → Show Forms API Info is selected.
3	Now you can copy the code to find the object.

1	Script Builtin Functions,
2	Code Snippets and
3	Editor Quick Nodes