REM smplpgm.sql

REM 

REM Insert module names and descriptions from an external repository.

REM External repository is seen here as table EXT_MODULE.

REM

set serveroutput on

create or replace procedure des2k_add_extern_module

    (i_appsys in varchar2, i_appver in number, i_module in varchar2) as



  module_to_add     ciomodule.data;  --- Property record for module

  activity_status   varchar2(1);     --- Status flag 

  activity_warning  varchar2(1);     --- Warnings flag



  cursor new_module(i_module_name in varchar2) is

    select short_name, name, purpose

     from  ext_module

     where short_name = i_module_name;



  cannot_init_appsys       exception;

  no_matching_module_found exception;



begin

  begin

    --- Initialize the API

    if not cdapi.initialized then   

       --- not already initialized

       cdapi.initialize(upper(i_appsys), i_appver);

    end if;

    exception when others then raise cannot_init_appsys;

  end;



  --- Open an activity

  --- This issues a savepoint, so incomplete changes can be rolled

  ---   back to here

  cdapi.open_activity;



  --- Get and assign values for the module property list

  open new_module(upper(i_module));



  fetch new_module into       --- Put the values into the data record

     module_to_add.v.short_name

    ,module_to_add.v.name

    ,module_to_add.v.purpose;



  if new_module%notfound then --- Our select didn't find a module to

                              ---   load

     close new_module;

     raise no_matching_module_found;

  else --- Set indicators for data we're adding



     if module_to_add.v.name is not null

        then module_to_add.i.name := true;

     end if;



     if module_to_add.v.short_name is not null then

        module_to_add.i.short_name := true;

     end if;



     if module_to_add.v.purpose is not null then

        module_to_add.i.purpose := true;

     end if;



     close new_module; --- Close the cursor used.

  end if;



  --- Call the insert procedure

  ciomodule.ins(null, module_to_add);



  --- Validate the activity

  cdapi.validate_activity(activity_status, activity_warning);



  --- Show violation messages here

  for each_violation in (select * from ci_violations) loop



      dbms_output.put_line

         (cdapi.instantiate_message(each_violation.facility

                                   ,each_violation.code

                                   ,each_violation.p0

                                   ,each_violation.p1

                                   ,each_violation.p2

                                   ,each_violation.p3

                                   ,each_violation.p4

                                   ,each_violation.p5

                                   ,each_violation.p6

                                   ,each_violation.p7)

                                   );

  end loop;



  --- Attempt to close the activity

  cdapi.close_activity(activity_status);



  --- If activity did not close, then abort and roll back changes

  if activity_status <> 'Y' then

     cdapi.abort_activity; --- rolls back to savepoint

  end if;



exception

  when cannot_init_appsys then

    dbms_output.put_line('Cannot Initialize API for Appl Sys '

                          ||i_appsys

                         );

  when no_matching_module_found then

    dbms_output.put_line('Module Name '

                          ||i_module

                          ||' not found'

                        );

    cdapi.close_activity(activity_status);

  when others then 

    dbms_output.put_line('API Session ending with Errors');

    if cdapi.activity is not null then

       cdapi.abort_activity;

    end if;

end;

/