SQL Method to generate random Date or text in Oracle SQL

This function will provide a random string with uppercase and lowercase characters

create or replace function random_str(v_length number) return varchar2 is
    my_str varchar2(4000);
begin
    for i in 1..v_length loop
        my_str := my_str || dbms_random.string(
            case when dbms_random.value(0, 1) < 0.5 then 'l' else 'x' end, 1);
    end loop;
    return my_str;
end;
/
 
select random_str(30) from dual;

Source: StackOverflow Question

Function to get Random Data in range of 2 dates:

CREATE OR REPLACE FUNCTION random_date(
    startdate date,
    enddate   date)
  RETURN DATE
IS
  my_str DATE;
BEGIN
  my_str:=TO_DATE( TRUNC( DBMS_RANDOM.VALUE(TO_CHAR(to_date(startdate),'J') ,TO_CHAR(to_DATE (enddate),'J') ) ),'J' );
  RETURN my_str;
END;
/

About Vineet Verma

Developer/Blogger/Gamer/Lazy Couch Potato...:P Need PDF Books: Knowledge Base
This entry was posted in Oracle PL/SQL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s