2011-03-25

Greatest Common Divisor

Need a greatest common divisor function? Vadim Tropashko has written a SQL implementation of such. Here it is wrapped inside a function.



create or replace function greatest_common_div(n1 number, n2 number)
return number
as
ret number;
begin
with NumberSet as (
select column_value Element from table(sys.odcinumberlist( n1, n2 ))
), Integers as (
select level num# from dual connect by level <= least( n1, n2 )
)
select max(Divisor) into ret
from (
select Divisor
from (
select num# as Divisor from Integers
where num# <= (select min(Element) from NumberSet)
), NumberSet
where mod(Element, Divisor)=0
group by Divisor
having count(*) = (select count(*) from NumberSet)
);
return ret;
end;
/


select greatest_common_div(15,123) from dual;

3



And a 11.2 version. Idea borrowed from http://wiki.postgresql.org. This is a better performing one as it handles only two numbers.


create or replace function greatest_common_div2(n1 number, n2 number)
return number
as
ret number;
begin
WITH t(a,b) AS (
select n1, n2 from dual
UNION ALL
SELECT b, mod(a,b) FROM t
WHERE b > 0
)
SELECT a into ret
FROM t
WHERE b = 0
;
return ret;
end;
/



And the same without pl/sql to sql context switch. Added 12.9.2011



create or replace function greatest_common_div3(n1 number, n2 number)
return number
as
ret number;
begin
ret := n1;
if (n2 != 0) then
ret := greatest_common_div3(n2,mod(n1,n2));
end if;
return ret;
end;
/

with numbers as (select level num from dual connect by level < 100)
, test as (
select a.num a
, b.num b
, greatest_common_div(a.num,b.num) gcd
, greatest_common_div2(a.num,b.num) gcd2
, greatest_common_div3(a.num,b.num) gcd3
from numbers a, numbers b
)
select * from test where gcd!=gcd2 or gcd!=gcd3
;




Update 28.3.2011
Added an user defined aggregate also.



CREATE OR REPLACE TYPE t_gcd_agg AS OBJECT
(
g_number number,
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_gcd_agg)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_gcd_agg,
value IN number )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_gcd_agg,
returnValue OUT number,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_gcd_agg,
ctx2 IN t_gcd_agg)
RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY t_gcd_agg IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_gcd_agg)
RETURN NUMBER IS
BEGIN
sctx := t_gcd_agg(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_gcd_agg,
value IN number )
RETURN NUMBER IS
BEGIN
SELF.g_number := greatest_common_div3(case when self.g_number is null then value else self.g_number end, value);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_gcd_agg,
returnValue OUT number,
flags IN NUMBER)
RETURN NUMBER IS
BEGIN
returnValue := SELF.g_number;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_gcd_agg,
ctx2 IN t_gcd_agg)
RETURN NUMBER IS
BEGIN
SELF.g_number := greatest_common_div3(case when self.g_number is null then ctx2.g_number else self.g_number end
, case when ctx2.g_number is null then self.g_number else ctx2.g_number end);
RETURN ODCIConst.Success;
END;
END;
/

CREATE OR REPLACE FUNCTION gcd (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_gcd_agg;
/

select gcd(column_value) from table(sys.odcinumberlist(4,8,34));

2

with test as (
select 'a' a, 4 n from dual union all
select 'a' a, 6 n from dual union all
select 'b' a, 30 n from dual union all
select 'b' a, 60 n from dual union all
select 'b' a, 96 n from dual
)
select a,gcd(n)
from test
group by a;

a 2
b 6

2011-03-16

grep in windows

Missing grep in windows.
Power shell to the rescue.

findstr -s "search_string" files

2011-03-11

Merge ignores check constraint

It was a while ago when we noticed that our enabled and validated constraints were not respected by our 11.2.0.1 database. The reason for this appeared to be a merge clause updating the rows and ignoring the constraints. A bug 9285259 was created.
Now the bug will be fixed in version 12.1 and hopefully 11.2.0.3 also. There is a patch available on top of 11.2.0.2 version. The patch installed online. Now we are receiving ORA-02290: check constraint violated as supposed. Also workaround was introduced. Our merge clause had only update part. By adding insert or delete part to the merge the constraint is noticed without the patch.

2011-03-09

Removing duplicates

From asktom one can find an example removing duplicates. Here is another.


create table duplicates (n int);

insert into duplicates select level from dual connect by level < 100;

insert into duplicates select level from dual connect by level < 50;

delete from duplicates where rowid in (
select rid from (
select rowid rid, first_value(rowid)over(partition by n) frid, dup.*
from duplicates dup
) where frid != rid
)
;

2011-03-03

Deprecation

During last two weeks I have noticed twice the use of oracle.jdbc.driver.OracleDriver with 11g version. It has been almost five years ago when the Oracle 9i jdbc driver package "oracle.jdbc.driver." deprecated.

With 11g please use oracle.jdbc.OracleDriver instead of the deprecated oracle.jdbc.driver.OracleDriver.

This might be found in your application server connection pool settings, project jdbc.properties or hard coded something like java.lang.Class.forName("oracle.jdbc.driver.OracleDriver");
as it should be
java.lang.Class.forName("oracle.jdbc.OracleDriver");

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.