It does RANDOM data - not within bounds like you have. If ( x.data_type in ('NUMBER', 'FLOAT' )) L_insert := 'insert /*+ append */ into ' || p_tname ||įor x in ( select data_type, data_length, Another technique (some slow but effective) is:Ĭreate or replace procedure gen_data( p_tname in varchar2, p_records in number )ĭbms_application_t_client_info( 'gen_data ' || p_tname )
Which generates 440 rows - and comes up with every combination of them. Is there a better way of doing this, of have I just answered my own question? I can then MOD, TRUNC, TO_DATE() +, these values to generate the real data set. ,(select rownum t from all_objects where rownum <= 11 ) c ,(select rownum s from all_objects where rownum <= 4 ) b I've come up with a select that looks like this:įrom (select rownum r from all_objects where rownum < 11 ) a
How can I do this in one SQL statement? Is it possible? That is, ID is a number, every ID has three ID_TYPE (1,2,3) and each ID,ID_TYPE combo has ten VERSIONS (1,2.10) I want to generate data that looks like this:
It seems to work well for simple data setups. Select rownum, mod(rownum,100), trunc(rownum/10)Īpproach as well. Instead of dbms_random, you could use your own values, but this is as good as anything create or replace procedure clone( p_tname in varchar2, p_records in number )Ĩ execute immediate 'create table clone_' || p_tname ||ġ2 l_insert := 'insert into clone_' || p_tname ||ġ5 for x in ( select data_type, data_length,ġ6 rpad( '9',data_precision,'9')/power(10,data_scale) maxvalġ8 where table_name = 'CLONE_' || upper(p_tname)Ģ1 if ( x.data_type in ('NUMBER', 'FLOAT' ))Ģ3 l_insert := l_insert || 'dbms_random.value(1,' || x.maxval || And I think about using pl/sql table for information in: That is the reason for my thoughts of dynamic procedure, which should (depended of column type in tables) insert number of test records. To me is often send only description of tables, I need to create and some business logic to put in stored packages.
0 kommentar(er)
