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:
Post a Comment