Sunday, November 20, 2016

A fast way to load a Gitora repository with many objects

I find creating a Gitora repository for a large schema or for many schemas via the Gitora user interface a little tedious.
Luckily there is the Gitora API to help us out.

So here is a little example of how to bulk load your objects into a gitora repository.

First create your repositories in the Gitora UI..
Then use this script (and obviously replace 'YOUR_SOURCE_SCHEMAS' and  'TARGET_REPOSITORY_NAME').


begin
   api_gitora.login('username','password');
   
   for i in (select o.object_name
             ,      o.object_type
             ,      o.owner
             from  all_objects o
             where o.object_name not like 'Z_%'         -- some rules for which objects to load.
             and   o.object_name not in ('QMS_EXEC_SQL'
                                        ,'SC_SCRIPTEXECUTER'
                                        ,'CG$ERRORS'
                                        ,'BDF_SCRIPTEXECUTER'
                                        )
             and   o.object_type in (api_gitora.type_function
                                    ,api_gitora.type_package
                                    ,api_gitora.type_procedure
                                    ,api_gitora.type_synonym
                                    ,api_gitora.type_trigger
                                    ,api_gitora.type_view
                                    ,api_gitora.type_sql_type
                                    )
             and o.owner in ('YOUR_SOURCE_SCHEMAS')
             order by o.owner
             ,        o.object_type
             )
   loop
       begin
          api_gitora.adddbobject(in_schema_cd   => i.owner
                                ,in_type_cd     => i.object_type
                                ,in_name_tx     => i.object_name
                                ,in_reponame_tx => 'TARGET_REPOSITORY_NAME' -- to which respository need the objects go
                                );

          -- If you want lto log what is loaded, remember to increase your dbms_output buffer!
          dbms_output.put_line('inserted: '||i.object_type||' '||i.owner||'.'||i.object_name);

       exception
          when others 
          then 
             dbms_output.put_line('error on: '||i.object_type||' '||i.owner||'.'||i.object_name||'   sqlerrm: '||sqlerrm);
                                                                                        
       end;    
   end loop;
end;
Quick and easy. Have fun!

No comments:

Post a Comment

How to set up multiple Gitora installations on the same server

When you use Gitora on multiple databases (DEV, CI, TEST) you need to have multiple Gitora installations, one for each database. There are...