Add Code to Oracle Reports via Forms FormsAPI Master

How to do Mass Changes with in your Oracle Reports with FormsAPI Master

Posted by Torsten Kleiber on March 04, 2021 Tags: Oracle Reports 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?

  1. 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.

  2. 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.

  3. Via Scripts → Wizard you can create basic structures.

    add code to reports via forsm api master wizard
    1 Here I get how I can open a parameter dialog for selecting a directory and then iterating over it.
  4. If you not know how to find a specific object, open your module in Explore.

    add code to reports via forsm api master explorer
    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.
  5. In the sript editor itself you have access to

    add code to reports via forsm api master libs
    1 Script Builtin Functions,
    2 Code Snippets and
    3 Editor Quick Nodes

Now see my resulting code:

  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;

  l_param_screen := TParamScreen.create;
    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
      l_file_list := GetFileList(l_param_screen.paramvalue('MYDIR'), '*.rdf', true);
      for l_file in 0 .. l_file_list.count-1 do
          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

            logadd('BEFOREREPORT trigger found' + IntToStr(l_before_report_trigger), logInfo);
          end else
            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);

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

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

          logadd(SQLERRM, logError);

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

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



That’s it!