Generating random dates is a bit more involved. Select trunc(dbms_random.value(1,60)) - lottery numbers now go up to 59 ! Therefore, if you want the possibility of including the maximum value in the result set you’ll need to add one to the maximum value you pass into the function : It’s worth noting that the function returns a value greater than or equal to the lower bound value but less than the upper bound value. This technique also applies when you want to generate a decimal to a set precision – e.g. However, a decimal value is returned :įortunately, it’s a SQL function which means that you can use other SQL functions to offer greater control over the output : Passing in the minimum and maximum permissable values does offer control over the range of the result. On the face of it, DBMS_RANDOM.VALUE also has it’s issues. this function is depracated in later versions of Oracle.you can’t control the range of numbers between which the result will fall.This is a reasonable start but there are a few drawbacks with DBMS_RANDOM.RANDOM, namely : When it comes to generating a random integer, you may first be tempted to do something like this : Fortunately, DBMS_RANDOM does have a few tricks up it’s sleeve… Think of a number Of course, we’re going to need more than just a single column of integers to build our test data file. Yep, a thousand rows of data (or 10000, or a million), very quickly and with little typing. This is one of those tricks that are easy when you know how, but may leave you scratching your head the first time you see it, here’s how to generate lots of rows using a single dual query in Oracle : It is, in fact, a new feed into the HR.EMPLOYEES table. The file will contain records with the following attributes : Attribute Use a set of pre-defined values randomly in your data generation.Generate random values for strings, numbers and dates.Meanwhile, you need to start writing your code to ingest the feed.įortunately, you have all the tools available to : You’ve managed to agree the file specification – what data will be included in the file, datatypes etc, but the developers working on the upstream system won’t be able to start providing test files for loading for weeks yet. There’s a requirement for a new feed into your Warehouse from another system. On the plus side, it is surprisingly useful when you find yourself in a situation which is all too common for a Data Warehouse developer. It consistently fails to return the correct set of lotter numbers.
0 Comments
Leave a Reply. |