codemonth.dk

One project every month - making stuff better ...

GITHUB_UTL Tutorial 6: Comparing database code with GitHub code.

In this tutorial I will show you how to compare live code with the code you have in your github repository. We can use tis to verify that what we have running in our database, is also what we have checked into our Git repository.

As always my parameters for this session is:
ParameterValue
Wallet location/home/oracle/wallet
Wallet passwordWalletPasswd123
GitHub.com usernamegithub-user
GitHub.com passwordgitPass123

Let us imagine that we have a procedure called live_proc:

create or replace procedure live_proc

as

begin

dbms_output.put_line('Proc I am');

end live_proc;
/


So the first thing we need do is to register our code into our repository.
declare
our_ddl clob;
begin
-- Set wallet parameters for the session
github.set_session_wallet('file:/home/oracle/wallet', 'WalletPasswd123');

-- Set github account for session
github.set_logon_info('github-user', 'gitPass123');

our_ddl := dbms_metadata.get_ddl('PROCEDURE', 'LIVE_PROC');

-- Now we can create the file with the contents of our procedure
github_repos_content.create_file (
git_account => 'github-user'
, repos_name => 'github_utl_test'
, path => 'live_proc.sql'
, message => 'Just a commit message'
, content => github.encode64_clob(our_ddl)
);
end;
/


So now we have a baseline for how our procedure in our database should look like. So let us change the code in the database, to the following:
create or replace procedure live_proc

as

begin

dbms_output.put_line('Proc I am, and I do not like green sql');

end live_proc;
/


So now the code in the database is different than what we have registered in our Git repository, so what can we do to see if it has changed? Well, when we uploaded the original ddl, we converted it to a base64 encoded string, so we can simply base64 encode the code and compare it with the base64 we can get from the github repository.
So lets download the content of the object path, that we want to compare:

declare

myjson github.call_result;
githubBase64 clob;
dbBase64 clob;

begin

-- Set wallet parameters for the session
github.set_session_wallet('file:/home/oracle/wallet', 'WalletPasswd123');

-- Set github account for session
github.set_logon_info('github-user', 'gitPass123');

-- Get the contents of an object path
myjson := github_repos_content.get_content(
git_account => 'github-user'
, repos_name => 'github_utl_test'
, path => 'live_proc.sql'
);

githubBase64 := json_ext.get_string(myjson, 'content');
-- Let us remove chr(10) chr(13) in chunked content
githubBase64 := replace(replace(githubBase64,chr(10)),chr(13));

dbBase64 := dbms_metadata.get_ddl('PROCEDURE', 'LIVE_PROC');
dbBase64 := github.encode64_clob(dbBase64);

if githubBase64 = dbBase64 then
dbms_output.put_line('No change');
else
dbms_output.put_line('Change');
end if;

end;
/


So let us change it back to the original and verify that our compare works as it should:

create or replace procedure live_proc

as

begin

dbms_output.put_line('Proc I am');

end live_proc;
/


So now run the compare one more time:

declare

myjson github.call_result;
githubBase64 clob;
dbBase64 clob;

begin

-- Set wallet parameters for the session
github.set_session_wallet('file:/home/oracle/wallet', 'WalletPasswd123');

-- Set github account for session
github.set_logon_info('github-user', 'gitPass123');

-- Get the contents of an object path
myjson := github_repos_content.get_content(
git_account => 'github-user'
, repos_name => 'github_utl_test'
, path => 'live_proc.sql'
);

githubBase64 := json_ext.get_string(myjson, 'content');
-- Let us remove chr(10) chr(13) in chunked
githubBase64 := replace(replace(githubBase64,chr(10)),chr(13));

dbBase64 := dbms_metadata.get_ddl('PROCEDURE', 'LIVE_PROC');
dbBase64 := github.encode64_clob(dbBase64);

if githubBase64 = dbBase64 then
dbms_output.put_line('No change');
else
dbms_output.put_line('Change');
end if;

end;
/


As you can see from the result, we now have the same code as the one checked into github.com.

Tune in for the next tutorial where I will automate the comparison, and show you how to run a simple select to verify that all your code in the database is the same as the code checked into github.

Tagged in : Advanced Queuing, DBMS_NETWORK_ACL_ADMIN, DBMS_SCHEDULER, Database Triggers, UTL_ENCODE, UTL_HTTP, UTL_RAW, sys_context