PROGRAM Procedures_2_Library;
{-- ***************************************************************************
 -- ** Descr. : Checks if the selected formsmodules have procedures with same
 -- **          name and sourcecode like in a given PLSQL Library and reports
 -- **          them so that you can check them out and delete those.
 -- **
 -- **          Tip:  The FormsCompare utility can compare Forms & PLSQL Libraries
 -- **                together, this way you can easily compare whats different
 -- **                in the sourcecode of those program units with the same name!
 -- **
 -- **          Note: This script does not work with procedure type of packages!
 -- **
 -- ** MINVERS:
 -- ***************************************************************************
 -- ** 15/01/02 1.001.00 Initial Creation, muellers@orcl-toolbox.com
 -- ***************************************************************************}

{*Global Variable Declaration*}
VAR
  v_filename       : varchar2;
  i,j              : number;
  frm              : number;
  lib              : number;
  prg              : number;
  prgname          : varchar2;
  prgsrc           : varchar2;
  files            : TStringList;
  pl               : TStringList;
  sl               : TStringList;
  ps               : TParamScreen;
  pb               : TParamBoard;

{*Main Program Block*}
BEGIN
    //build together a nice parameter screen ...
    ps := TParamScreen.create;
    pb := ps.AddBoard('Modules',picModules);
    pb.addparam(parLabel,'MYLABEL','Checks for unnecessary duplicates in local program units and a PLSQL Library.','','');
    pb.addparam(parFiles,'FILES','Source Files','','Forms Modules (*.fmb)|*.fmb|All Files (*.*)|*.*');
    pb.addparam(parFilename,'PLSQLLIB','PLSQL Library','','PLSQL Library (*.pll)|*.pll');
    pb.addparam(parDatabaseLogon,'MYDATABASE','DB Connection','scott/tiger','');

    //show the parameter screen and wait for inputs
    if ps.ShowParamScreen('Procedures 2 Library ...') then
    begin

      //get all the selected files into a list
      files := tstringlist.create;
      files.text := ps.paramvalue('FILES');

      //logon to the database
      api_Connect(ps.ParamValue('MYDATABASE'));

      //Load the PLSQL library and get the list of Procedures
      logadd('Loading PLSQL Library ...');
      lib := API_LoadModule( ps.paramvalue('PLSQLLIB') );
      pl := API_GetAllItemObjects(lib);

      //now loop through the list and get the objects names (=procedurename!)
      for j := 0 to pl.count-1 do
      begin
        pl.strings[j] := API_GetObjectName( pl.objects[j] );
      end;

      //loop through the list of selected files
      for j := 0 to files.count-1 do
      begin
        v_filename := files.strings[j];
        logadd('Checking '+to_char(j+1)+'/'+to_char(files.count)+' '+v_filename);
        try
          // load the forms module
          frm := API_LoadModule(files.strings[j]);

          //get a list of all objects in this formsmodule
          sl := API_GetSubObjects(frm, D2FP_PROG_UNIT);

          //and loop through the whole list
          for i := 0 to sl.count-1 do
          begin
            //read out the source object from the list
            prg := sl.objects[i];

            //get the name of our forms program unit
            prgname := API_GetObjectName(prg);

            //do we have such a name in the plsql library?
            if pl.IndexOf(prgname) > -1 then
            begin
              //get the sourcecode
              prgsrc := api_getPLSQL(prg);

              //is the forms sourcecode equal to the plsql sourcecode ?
              if prgsrc = api_getPLSQL( pl.objects[ pl.IndexOf(prgname) ] ) then
              begin
                logadd('  => '+prgname+' is same as in library!!!');
              end else
              begin
                logadd('  => '+prgname+' is not same as in library!!!');
              end;

            end;

          end;

          //remove the list of object from memory
          sl.free;

          //... and finally release the module from memory
          API_DestroyModule(frm);

        except
          // ups! an error happened, so just log it and proceed to the next module
          logadd(' =>'+GetError,LogError);
        end;
      end;

      //free the file list from memory;
      files.free;

      //free the plsql procedure list from memory;
      pl.free;

      //free the plsql library from memory
      API_DestroyModule(lib);


    end else
    begin
      //user must have pressed cancel on parameterscreen
      logadd('Canceled on parameterscreen!');
    end;
    // free the parameter screen
    ps.free;

END.