ut.md

UT

• Variables

• Exceptions

• VERSION Function

• SET_NLS Procedure

• RESET_NLS Procedure

Variables

Name	Code	Description
g_nls_date_format	g_nls_date_format varchar2(4000);
pragma	pragma exception_init (e_package_invalidated, -04068);
raise	raise e_package_invalidated;
end	end if;
l_paths	l_paths ut_varchar2_list := ut_varchar2_list(sys_context('userenv', 'current_schema'));
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
l_paths	l_paths ut_varchar2_list := ut_varchar2_list(sys_context('userenv', 'current_schema'));
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
l_paths	l_paths ut_varchar2_list := ut_varchar2_list(coalesce(a_path, sys_context('userenv', 'current_schema')));
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
l_paths	l_paths ut_varchar2_list := ut_varchar2_list(coalesce(a_path, sys_context('userenv', 'current_schema')));
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
end	end if;
end	end if;
end	end if;
execute	execute immediate 'alter session set nls_date_format = '''
end	end if;
g_nls_date_format := null;
end	end ut;

Exceptions

Name	Code	Description
g_nls_date_format	g_nls_date_format varchar2(4000);
pragma	pragma exception_init (e_package_invalidated, -04068);
raise	raise e_package_invalidated;
end	end if;
l_paths	l_paths ut_varchar2_list := ut_varchar2_list(sys_context('userenv', 'current_schema'));
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
l_paths	l_paths ut_varchar2_list := ut_varchar2_list(sys_context('userenv', 'current_schema'));
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
l_paths	l_paths ut_varchar2_list := ut_varchar2_list(coalesce(a_path, sys_context('userenv', 'current_schema')));
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
l_paths	l_paths ut_varchar2_list := ut_varchar2_list(coalesce(a_path, sys_context('userenv', 'current_schema')));
l_lines	l_lines sys_refcursor;
l_line	l_line varchar2(4000);
loop	loop fetch l_lines into l_line;
exit	exit when l_lines%notfound;
pipe	pipe row(l_line);
end	end loop;
close	close l_lines;
end	end if;
end	end if;
end	end if;
end	end if;
execute	execute immediate 'alter session set nls_date_format = '''
end	end if;
g_nls_date_format := null;
end	end ut;

VERSION Function

Returns version of installed utPLSQL code

Syntax

function version return varchar2

Parameters

Name	Description
return	varchar2 VersionNumber: Version number installed in database

Example

select ut.version from dual;

SET_NLS Procedure

Helper procedure to set NLS session parameter for date processing in refcursor.
It needs to be called before refcursor is open in order to have DATE data type data in refcursor
properly transformed into XML format as a date-time element.
If the function is not called before opening a cursor to be compared, the DATE data is compared using default NLS setting for date.

Syntax

procedure set_nls

RESET_NLS Procedure

Helper procedure to reset NLS session parameter to it's original state.
It needs to be called after refcursor is open in order restore the original session state and keep the NLS date setting at default.

Syntax

procedure reset_nls