Hi,

I discovered a rounding issue with the floor function with PL/SQL, so
what happened was 00:08:00 (hh:mm:ss) and 00:08:60 (hh:mm:ss) both
existed in my table. The original script to pull this TPP metric was
to_char(FLOOR((time_per_person)/3600),'09') || ':' ||
to_char(FLOOR(mod((time_per_person),3600)/60),'09') || ':' ||
to_char(mod(mod((time_per_person),3600), 60),'09')||' '
time_per_person,
(time_per_person was a number )

So I digged into the code somewhere and found a procedure in a package.

PROCEDURE get_time_person(p_duration in number, p_exception_required
in boolean default false, p_time_person out nocopy varchar2) IS
v_tmp number;
v_hour number;
v_min number;
v_sec number;

BEGIN
if p_exception_required or p_duration < 0 then
p_time_person := 'N/A';
elsif p_duration = 0 or p_duration is null then
p_time_person := '--';
else
v_hour := FLOOR(p_duration / 3600);
v_tmp := p_duration - v_hour * 3600;
v_min := FLOOR(v_tmp / 60);
IF v_min = 60 THEN
v_hour := v_hour + 1;
v_min := 00;
END IF;
v_sec := v_tmp MOD 60;
IF ROUND(v_sec) = 60 THEN
v_min := v_min + 1;
v_sec := 00;
END IF;
p_time_person := TO_CHAR(v_hour, '99999') || ':' ||
LTRIM(TO_CHAR(v_min, '09')) || ':' || LTRIM(TO_CHAR(v_sec, '09'));
end if;
EXCEPTION
WHEN OTHERS THEN
p_time_person := 'N/A';

END get_time_person;

And I changed it to a function like the following

CREATE FUNCTION get_time_person (
p_duration IN NUMBER
p_exception_required IN BOOLEAN DEFAULT FALSE
)
RETURN VARCHAR2
AS
v_tmp NUMBER;
v_hour NUMBER;
v_min NUMBER;
v_sec NUMBER;
p_time_person VARCHAR2 (100);
BEGIN
IF p_exception_required OR p_duration < 0
THEN
p_time_person := 'N/A';
ELSIF p_duration = 0 OR p_duration IS NULL
THEN
p_time_person := '--';
ELSE
v_hour := FLOOR (p_duration / 3600);
v_tmp := p_duration - v_hour * 3600;
v_min := FLOOR (v_tmp / 60);

IF v_min = 60
THEN
v_hour := v_hour + 1;
v_min := 00;
END IF;

v_sec := v_tmp MOD 60;

IF ROUND (v_sec) = 60
THEN
v_min := v_min + 1;
v_sec := 00;
END IF;

p_time_person :=
TO_CHAR (v_hour, '99999')

RETURN p_time_person;
EXCEPTION
WHEN OTHERS
THEN
p_time_person := 'N/A';

END get_time_person;


So, my question is, how would I go about using this function above
incorporating with create table (+ nested select statements, which I
didn't paste here) ?
Is it true that I am not allowed to call user-defined functions with a
select statement ?

On 26 May 2006 11:21:10 -0700, dennis.pong (AT) gmail (DOT) com wrote:


Just do it
This is NOT true. On older version you may need to use PRAGMAs in your
function, but this doesn't apply to 9i and higher.
This is one of the reasons why you *always* need to specify your exact
version when posting.

--
Sybrand Bakker, Senior Oracle DBA