How to create a Oracle PL/SQL function that returns random numbers?

How to create a function that returns random numbers?

Can you imagine an Oracle PL/SQL Function that returns Random Numbers? This is very useful for testing features in multiple scenarios.
Oracle comes with a built-in package “dbms_random.value” for this purpose. The function will return a random float number between a given range.

Eg:
Create a input with YES and NO answers. Test both without changing any underlying criteria. To test the changes within a webpage, just refresh the page a few times without any change to the database or inputs.

CREATE OR REPLACE FUNCTION GET_RANDOM
RETURN NUMBER
IS
      P_RETVALUE    NUMBER;
BEGIN

      P_RETVALUE := 0;
    
      SELECT floor(mod(dbms_random.value(3,10),2))
      INTO P_RETVALUE
      FROM DUAL;
    
      RETURN P_RETVALUE;

EXCEPTION
WHEN OTHERS THEN
      RETURN 0;
END;
/

COMMIT
/


The function is called using the below call:
select get_random() from dual

By changing the value in the MOD function, it is possible to have more than 2 values. Also by adding an IF .. THEN ELSE loop after the SELECT STATEMENT, it is possible to have non-numeric values.

Eg:
IF P_RETVALUE = 0 THEN
      RETURN "Equal"
ELSE IF P_RETVALUE = 1 THEN
      RETURN "Less"
ELSE
      RETURN "More"
END IF
 

Oracle Database - Identify locking sessions

Here is a simple SQL to identify locking sessions within an Oracle Database:
SELECT 'SID ' || L1.SID ||' is blocking ' || L2.SID BLOCKING
FROM V$LOCK L1, V$LOCK L2
WHERE L1.BLOCK =1 AND L2.REQUEST > 0
AND L1.ID1=L2.ID1
AND L1.ID2=L2.ID2

Here is a sample output:
SID 776 is blocking 465

All locking sessions are stored in the table V$LOCK.

Note: The locking can happen due to any reason. It is not necessarily caused due to Applications or User Activity. Look everywhere possible to identify the problem. Likely places to look first ... Concurrent program, running for a long time, user FORM Sessions, SQL*Plus or TOAD sessions with ROWID selection or UPDATEs and the current session is not COMMITed, Running SQL Scripts, DataLoad jobs, etc are just to name a few.