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

--Grants
--grant select on dba_constraints to oradba;

create or replace package smpl_misc
is
function get_cons_type(p_owner in varchar2,p_cons_name in varchar2) return varchar2;
end smpl_misc;
/

create or replace package body smpl_misc
is
	--1.This function returns constraint type.
	function get_cons_type(p_owner varchar2, p_cons_name varchar2)
	return varchar2 as
		v_sql		varchar2(2000);
		v_type		char;
		v_text		varchar2(32767);
		v_result	varchar2(20);
	begin
		begin
			if p_cons_name is not null then
				v_sql:='select 
						constraint_type, 
						search_condition 
					from 
						dba_constraints 
					where 
						owner='''|| upper(p_owner) || ''' 
					and	constraint_name='''|| upper(p_cons_name)||'''';
				execute immediate v_sql into v_type,v_text;
				v_text:=substr(v_text,1,100);

				if v_type='P' then
					v_result:='Primary Key';
				elsif v_type='R' then
					v_result:='Foreign Key';
				elsif v_type='U' then
					v_result:='Unique';
				elsif v_type='C' then
					if instr(v_text,'NOT NULL') = 0 then
						v_result:='Check';
					else
						v_result:='Not Null';
					end if;
				end if;	
			end if;
		exception
			when others then
				dbms_output.put_line('Error@smpl_misc.get_cons_type:- '||sqlcode||':'||sqlerrm);
		end;
		return v_result;
	end;
end smpl_misc;
/
