Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <p>"I just want to specify number of columns with type NUMBER, number of columns with type VARCHAR2 etc and the fields should be generated automatically."</p> </blockquote> <p>The following procedure does just that. Note that it is rather basic; you might want to make it more sophisticated, for example by varying the length of the varchar2 columns:</p> <pre><code>SQL&gt; create or replace procedure bld_table 2 ( p_tab_name in varchar2 3 , no_of_num_cols in pls_integer 4 , no_of_var_cols in pls_integer 5 , no_of_date_cols in pls_integer 6 ) 7 as 8 begin 9 execute immediate 'create table '||p_tab_name||' (' 10 ||' pk_col number not null' 11 ||', constraint '||p_tab_name||'_pk primary key (pk_col) using index)'; 12 &lt;&lt; numcols &gt;&gt; 13 for i in 1..no_of_num_cols loop 14 execute immediate 'alter table '||p_tab_name||' add ' 15 ||' col_n'||trim(to_char(i))||' number'; 16 end loop numcols; 17 &lt;&lt; varcols &gt;&gt; 18 for i in 1..no_of_var_cols loop 19 execute immediate 'alter table '||p_tab_name||' add ' 20 ||' col_v'||trim(to_char(i))||' varchar2(30)'; 21 end loop varcols; 22 &lt;&lt; datcols &gt;&gt; 23 for i in 1..no_of_date_cols loop 24 execute immediate 'alter table '||p_tab_name||' add ' 25 ||' col_d'||trim(to_char(i))||' date'; 26 end loop datcols; 27 end bld_table; 28 / Procedure created. SQL&gt; </code></pre> <p>Here it is in action:</p> <pre><code>SQL&gt; exec bld_table ('T23', 2, 3, 0) PL/SQL procedure successfully completed. SQL&gt; desc t23 Name Null? Type ----------------------------------------- -------- ---------------------------- PK_COL NOT NULL NUMBER COL_N1 NUMBER COL_N2 NUMBER COL_V1 VARCHAR2(30 CHAR) COL_V2 VARCHAR2(30 CHAR) COL_V3 VARCHAR2(30 CHAR) SQL&gt; </code></pre> <p>We can also use dynamic SQL to populate the table with rows of random data. </p> <pre><code>SQL&gt; create or replace procedure pop_table 2 ( p_tab_name in varchar2 3 , p_no_of_rows in pls_integer 4 ) 5 as 6 stmt varchar2(32767); 7 begin 8 stmt := 'insert into '||p_tab_name 9 || ' select rownum '; 10 for r in ( select column_name 11 , data_type 12 , data_length 13 from user_tab_columns 14 where table_name = p_tab_name 15 and column_name != 'PK_COL' ) 16 loop 17 case r.data_type 18 when 'VARCHAR2' then 19 stmt := stmt ||', dbms_random.string(''a'', '||r.data_length||')'; 20 when 'NUMBER' then 21 stmt := stmt ||', dbms_random.value(0, 1000)'; 22 when 'DATE' then 23 stmt := stmt ||', sysdate + dbms_random.value(-1000, 0)'; 24 end case; 25 end loop; 26 stmt := stmt || ' from dual connect by level &lt;= '||p_no_of_rows; 27 execute immediate stmt; 28 end pop_table; 29 / Procedure created. SQL&gt; </code></pre> <p>Note that the primary key is populated with the ROWNUM so it will most likely fail if the table already contains rows. </p> <pre><code>SQL&gt; exec pop_table('T23', 4) PL/SQL procedure successfully completed. SQL&gt; select * from t23 2 / PK_COL COL_N1 COL_N2 COL_V1 COL_V2 COL_V3 ---------- ---------- ---------- ------------------------------ ----------------------------- ------------------------------ 1 913.797432 934.265814 NUtxjLoRQMCTLNMPKVGbTZwJeYaqnXTkCcWu WFRSHjXdLfpgVYOjzrGrtUoX jIBSoYOhSdhRFeEeFlpAxoanPabvwK 2 346.879815 104.800387 NTkvIlKeJWybCTNEdvsqJOKyidNkjgngwRNN PPIOInbzInrsVTmFYcDvwygr RyKFoMoSiWTmjTqRBCqDxApIIrctPu 3 93.1220275 649.335267 NTUxzPRrKKfFncWaeuzuyWzapmzEGtAwpnjj jHILMWJlcMjnlboOQEIDFTBG JRozyOpWkfmrQJfbiiNaOnSXxIzuHk 4 806.709357 857.489387 ZwLLkyINrVeCkUpznVdTHTdHZnuFzfPJbxCB HnoaErdzIHXlddOPETzzkFQk dXWTTgDsIeasNHSPbAsDRIUEyPILDT 4 rows selected. SQL&gt; </code></pre> <p>Again, there are all sorts of ways to improve the sophistication of the data.</p> <hr> <p>As an aside, using these sorts of data pools for load testing is not always a good idea. Performance problems are often caused by skews in the distribution of data values which you just aren't going to get with DBMS_RANDOM. This is particularly true of some date columns - e.g. START_DATE - which would tend to be clustered together in real life but the above procedure will not generate that pattern. Similarly maxing out the varchar2 columns will lead to tables which take up more storage than they wlll under real-life usage.</p> <p>In short, randomly generated data is better than nothing but we need to understand its weaknesses. </p>
    singulars
    1. This table or related slice is empty.
    1. POHow do you create a table with random number of fields in Oracle using PL/SQL?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAPC
    1. USAPC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow do you create a table with random number of fields in Oracle using PL/SQL?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COExactly what I needed! Thanks for the detailed answer. I agree that are limitations with randomly generated data. I will be limiting the amount of randomization for data that I know will be similar. This should be possible for numbers and strings. Not sure about dates though
      singulars
      1. PO
      1. USChandam
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload