PROGRAM AttDetach_Library;
{-- ***************************************************************************
 -- ** Descr. : Simple Tutorial script showing how to add and remove
 -- **          PL/SQL Libraries.
 -- **
 -- ** MINVERS:
 -- ***************************************************************************
 -- ** 18/03/02 1.001.00 Initial Creation, muellers@orcl-toolbox.com
 -- ***************************************************************************}

{*Global Variable Declaration*}
VAR
  v_filename       : varchar2;
  i,j              : number;
  frm              : number;
  att_lib          : varchar2;
  det_lib          : varchar2;
  files            : 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','This example shows how to add and remove PL/SQL Libraries.','','');
    pb.addparam(parFiles,'MODULES','Modules','','Forms Modules(*.fmb)|*.fmb');
    pb.addparam(parPathname,'SAVEPATH','Save Path','','');
    pb.addparam(parFilename,'ATT_LIB','Attach Library','','PLSQL Library (*.pll)|*.pll');
    pb.addparam(parFilename,'DET_LIB','Detach Library','','PLSQL Library (*.pll)|*.pll');
    pb.addparam(parDatabaseLogon,'MYDATABASE','DB Connection','scott/tiger','');

    //show the parameter screen and wait for inputs
    if ps.ShowParamScreen('Attach/Detach PL/SQL Libraries ...') then
    begin

      if ps.paramvalue('SAVEPATH') ='' then RaiseException('Please specify a path to save the modules to!');

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

      att_lib := upper(ChangeFileExt(ExtractFileName(ps.paramvalue('ATT_LIB')),''));
      det_lib := upper(ChangeFileExt(ExtractFileName(ps.paramvalue('DET_LIB')),''));

      //try to logon to the database
      try
        api_Connect(ps.ParamValue('MYDATABASE'));
      except
      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 PL/SQL Libraries in this formsmodule
          sl := API_GetSubObjects(frm, D2FP_ATT_LIB);

          //and loop through the whole list
          for i := 0 to sl.count-1 do
          begin
            //has the library to be deleted ?!
            if det_lib = API_GetObjectName( sl.objects[i] ) then
            begin
              PLSQLLib_Detach( sl.objects[i] );
            end;
          end;
          //remove the list of PL/SQL Libraries from memory
          sl.free;

          if att_lib <>'' then
          begin
            PLSQLLIB_Attach(frm,att_lib);
          end;

          //save the module to the new path
          API_SaveModule(frm,ps.ParamValue('SAVEPATH')+'\'+extractfilename(v_filename));

          //... 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;

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

END.