Sunday, February 1, 2009

Oracle function that returns a table of random numbers or characters

This oracle function expects 3 Input Parameters.
argCount IN NUMBER : Total number of random numbers to be generated.
argMinLimit IN NUMBER : Random number sequence minimum value.
argMaxLimit IN NUMBER : Random number max value.
Sinec my oracle function has to return a TABLE of random numbers, I would like to create a type of table type.
Refer
Type in Prog. Findings for what type is?
I am creating a type of table as
CREATE TYPE tbl_Rand AS TABLE OF VARCHAR2(10)

I have created this type as varchar, though random numbers stored are number data type is because in future I can elaborate my oracle function to generate random varchar values.
Number can be stored in varchar data type but varchar cannot be stored in number data type.



My oracle function is:

--Expecting 3 Input parameters
CREATE OR REPLACE FUNCTION fun_Rand(argCount IN NUMBER,argMinLimit IN NUMBER,argMaxLimit IN NUMBER)
RETURN tbl_rand pipelined
IS
tmpVal VARCHAR2(10);
BEGIN
--Looping for given argCount number of times
FOR i IN 1 .. argCount
LOOP
--Generating random number between given argMinLimit and argMaxLimit
tmpVal:=ROUND(dbms_random.value(argMinLimit,argMaxLimit),0);
--Piping the generated random number into the table type
pipe ROW(tmpVal);
END LOOP
RETURN;
END;




Output:-
Query: select * from table(fun_Rand(5,10,100))
This mean that to generate 5 random numbers between 10 and 100.




Now I can elaborate my oracle function to generate random characters argCount IN NUMBER : Total number of random charaters to be generated.
argMinLimit IN NUMBER : Ascii code value to be considered from or minimum value.
argMaxLimit IN NUMBER : Ascii code value to be considered up to or maximum value.
Oracle function:

--Expecting 3 Input parameters
CREATE OR REPLACE FUNCTION fun_Rand(argCount IN NUMBER,argMinLimit IN NUMBER,argMaxLimit IN NUMBER)
RETURN tbl_rand pipelined
IS
tmpVal VARCHAR2(10);
BEGIN
--Looping for given argCount number of times
FOR i IN 1 .. argCount
LOOP
--Generating random number between given argMinLimit and argMaxLimit and
--getting character associated with this ascii code
tmpVal:=CHR(ROUND(dbms_random.value(argMinLimit,argMaxLimit),0));
--Piping the generated random number into the table type
pipe ROW(tmpVal);
END LOOP
RETURN;
END;





Query:
select * from table(fun_Rand(5,10,100))
This mean that to generate 5 characters whose ascii code is between 10 and 100.



No comments: