PROGRAM FormSearch;
{-- ***************************************************************************
 -- ** Descr. : This script searches all forms in a given path for a occurance
 -- **          of the searchstring in PLSQL code and logs the place where it
 -- **          found it. This script is just a simple demonstration how to do
 -- **          such a thing.
 -- **
 -- ** MINVERS:
 -- ***************************************************************************
 -- ** 12/01/02 1.001.00 Initial Creation, muellers@orcl-toolbox.com
 -- ***************************************************************************}


{*Variables Declaration*}
VAR
  i,j              : number;
  frm              : number;
  files            : TStringList;
  ps               : TParamScreen;
  pb               : TParamBoard;
  so               : TStringList;
  src, src_orig    : varchar2;
  s                : varchar2;
  spos             : number;
  flagnocomments   : boolean;
  firstline        : boolean;
  srchstring       : varchar2;


{*Main Program Block*}
BEGIN
    //build together a nice parameter screen ...
    ps := TParamScreen.create;
    pb := ps.AddBoard('Modules',picModules);
    pb.addparam(parLabel,'MYLABEL','This script will <B>search the given string</B> in all PL/SQL code.','','');
    pb.addparam(parPathname,'SRCPATH','Check Path','','');

    pb := ps.AddBoard('Options',picOptions);
    pb.addparam(parString,'SEARCHSTRING','Search String','','');
    ps.parambyname('SEARCHSTRING').isNewGroup := true;
    pb.addparam(parCheckbox,'IGNORECMNT','Ignore PL/SQL Comments','Y','');

    pb.addparam(parDatabaseLogon,'MYDATABASE','DB Connection','scott/tiger','');
    ps.parambyname('MYDATABASE').isNewGroup := true;

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

      //because it is nice in the log to see what parameters we have specified
      //we just write them out
      LogParamScreen(ps);

      // basic parametercheck ...
      if ps.ParamByName('SRCPATH').value ='' then raiseException('Missing Check Path!');
      flagnocomments := ps.ParamValue('IGNORECMNT')='Y';
      srchstring := upper(ps.ParamValue('SEARCHSTRING'));

      // try to connect but ignore any errors ...
      try
        api_Connect(ps.ParamByName('MYDATABASE').value);
      except
      end;

      //get the list of forms modules to process (with all subdirectories)
      //into a list
      files := GetFileList(ps.ParamByName('SRCPATH').value,'*.fmb;*.mmb;*.pll;*.olb', true);

      //loop through the list of files and process them
      for j := 0 to files.count-1 do
      begin
        logadd('Checking ('+to_char(j+1)+'/'+to_char(files.count)+' '+files.strings[j]);

        try
          // load the forms module
          frm := API_LoadModule( files.strings[j] );

          //get all sourcecode objects (like triggers, program units, menuitems)
          so := API_GetAllSourceObjects(frm);

          //loop thorough all sourcecode objects
          for i := 0 to so.count-1 do
          begin
            //get the PLSQL sourcecode of the object
            src_orig := API_GetPLSQL(so.objects[i]);

            //do we need to filter out comments ?
            if flagnocomments = true
              then src := blendout_plsql_comments(src_orig)
              else src := src_orig;

            //search all in upper case
            src := upper(src);

            //search until string not anymore found
            spos := 0;
            firstline := true;
            repeat
              spos := instr(src,srchstring,spos+1);
              //have we found something ?!
              if spos>0 then
              begin
                //if we found it the first time in this plsql-block, then we
                //also want to output the name of the source object first!
                if firstline=true then
                begin
                  firstline := false;
                  s := ' => '+api_getobjectpath(so.objects[i]);
                  logadd(s);
                end;

                //get a nice line where we found it and output it;
                s := '   '+
                     to_char(Get_LineNumberAtPos(src,spos)+1)+
                     ' : '+
                     ctrim(Get_LineAtPos(src_orig,spos));

                logadd(s);
              end;
            until spos=0; //repeat until no more occurences found
          end;

          //free the sourceobject list from memory
          so.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;

    end else
    begin
      //user must have pressed cancel on parameterscreen
      logadd('Canceled on parameterscreen!');
    end;

    // free the parameter screen
    ps.free;

END.