/*
 * Title  : smpl_util.sql
 * Author : EXADIME LLC
 * Created: 3-JUL-2017
 * Updated: 3-JUL-2017
 * Release: 1.0
 * Purpose: Sample Utilities
 * History: N/A
 */

create or replace package smpl_util
is
function spell_number(p_number in number) return varchar2;
function string_reverse(pstr in varchar2) return varchar2;
function get_vacant_key(p_table_name in varchar2,p_key_attribute in varchar2) return number;
end smpl_util;
/

create or replace package body smpl_util
is
	--1.Spell Number
	function spell_number( p_number in number )
	return varchar2
	as
		type myArray is table of varchar2(255);
		l_str    myArray := myArray( '',
					' thousand ', ' million ',
					' billion ', ' trillion ',
					' quadrillion ', ' quintillion ',
				        ' sextillion ', ' septillion ',
				        ' octillion ', ' nonillion ',
				        ' decillion ', ' undecillion ',
				        ' duodecillion ' );
	  	l_num   varchar2(50) default trunc( p_number );
		l_return varchar2(4000);
	begin
		for i in 1 .. l_str.count
		loop
			exit when l_num is null;
	  		if ( substr(l_num, length(l_num)-2, 3) <> 0 ) then
				l_return := to_char(to_date(substr(l_num, length(l_num)-2, 3), 'J' ),'Jsp' ) || l_str(i) || l_return;
			end if;
			l_num := substr( l_num, 1, length(l_num)-3 );
		end loop;
	  	return l_return;
	end spell_number;

	--2.Reverse String
	function string_reverse(pstr in varchar2) return varchar2 
	is
		rst varchar2(50);
		i char;
		l number(2):=0;
	begin
		l:=length(pstr);
		while(l<>0)
		loop
			i:=substr(pstr,l,1);
			rst:=concat(rst,i);
			l:=l-1;
		end loop;
		return rst;	
	end;
		
	function get_vacant_key(p_table_name in varchar2,p_key_attribute in varchar2) return number 
	is
		v_VacantKey 	number	:=0;
		v_SetKey 	number	:=0;
		v_StartKey 	number	:=1;
		v_GLB  		number 	:=0;
		v_count		number	:=0;
		strSQL		varchar2(1000):=null;

		--Declarations
		--V_StartKey: is the Starting Key defined in the getVacankey Method.
		--GLB : is the Starting key in the table.
		--V_SetKey: is the key returned by the above query.

		--Code Logic
		--The following Query returns the Vacant Key int Table Set wrt. GLB.
		--SQLQuery:select min(key)+1 from table where key+1 not in( select key from table)
		--This fails at two situations 1.When No Records found 2.if GLB>StartKey
		--In both the cases return StartKey.
	begin
		strSQL:='select count(*) from '||p_table_name;
		execute immediate strSQL into v_count;

		if v_count<>0 then
			strSQL:='select min(' || p_key_attribute || ') from ' || p_table_name;
			execute immediate strSQL into v_GLB;

			strSQL:='select min('|| p_key_attribute || ')+1 from '|| p_table_name || ' where ' || p_key_attribute || '+1 not in ';
			strSQL:=strSQL ||' (select ' || p_key_attribute || ' from ' || p_table_name || ')';

			--smpl_logger.write_log(1,'tt.sql','01',strSQL);

			execute immediate strSQL into v_SetKey;

			if v_GLB > v_StartKey then
				v_VacantKey := v_StartKey;
			else
				v_VacantKey := v_SetKey;
			end if;	
		else

			v_VacantKey:=v_StartKey;
		end if;

		return v_VacantKey;
	end;
	
end smpl_util;
/
