Feb 10, 2009

Standard objects in Oracle ;///; Generate Series

To get the list of standard objects in Oracle

SQL> desc sys.standard;

-----------------------------------
To generate Series


CREATE OR REPLACE FUNCTION generate_series ( p_start number, p_end number, p_step number )
RETURN number
AS
BEGIN
/* select generate_series(10,10,9) from dual*/
DECLARE
v_i number;
begin
v_i := CASE WHEN p_start IS NULL THEN 1 ELSE p_start END;

DECLARE v_step number;
begin
v_step := CASE WHEN p_step IS NULL OR p_step = 0 THEN 1 ELSE p_step END;

DECLARE
v_terminating_value number;
begin
v_terminating_value := p_start + ABS( p_start- p_end) / ABS( v_step) * v_step;

-- Check for impossible combinations
IF NOT ( ( p_start > p_end AND SIGN( p_step) = 1 )
OR
( p_start < p_end AND SIGN( p_step) = -1 )) then

-- INSERT INTO Integers ( [IntValue] ) VALUES ( v_i )
IF ( v_i = v_terminating_value ) then

v_i := v_i + v_step;
end if;
end if;

RETURN v_i;
END;
END ;
END;
end ;

---------------------------------------------------------------

No comments: