Automatically generate test data in large batches in Oracle

Method 1:
SQL> Create Table B as select 1 ID from dual connect
Level <= 100;

Method 2:

SQL> Create Table A (ID INT );

Table created.

SQL> insert into a select 1 from dual connect by level <= 100;

100 rows created.

Method 3:

Create Table test_big as select * From all_objects;
Insert into test_big as select * From test_big;
Insert into test_big as select * From test_big;
Insert into test_big as select * From test_big;
Insert into test_big as select * From test_big;

Method 4:

Create Table big_table (A int, B char (3 ));
Declare
I int;
Begin
For I in 1 .. 10000 Loop
Insert/* + append */into big_table nologging values (I, 'sex ');
If Mod (I, 1000) = 0 then
Commit;
End if;
End loop;
End;

 

The instructions in PLSQL develope manual are for reference. You can refer to this manual.

Data Generator
The data generator allows you to create demo and test data. This may be helpful for testing applications and viewing how they execute a large amount of data. You can find the data generator under the tool menu.
Basically, the definition consists of one or more tables, the number of records you want to generate, and the field data definition. The upper left button allows you to open and save the definition.
You can see the deptemp demo provided above. This demo creates data for the deptdemo and empdemo tables, which is similar to the Dept
And EMP tables. On this page, you will find the following items:
• Table-table name.
• Number of records-the number of records to be generated. This can be a number or a range like 10 .. 100.
• Name-field name.
• Type-Data Type of the field.
• Size-field size (when appropriate ). For numeric fields, this will be the Numerical range and precision.
• Data-field data definition (see below ).
• Primary column-if the table is details of another table, you can set the primary column. For each generated record, a specified number of detailed records are generated.

You can use the "add existing table" button to add an existing table. You can also drag and drop a table from the Object Browser.
Data Definition
Data Definition determines the generated data. If you want to create a simple character, you can enter the character definition between two square brackets: [data] 166 PL/SQL developer
7.0 User Guide
Data can be a mixture of the following predefined sets:
• A: A. Z (lowercase)
• A: A. Z (uppercase)
• @: A. Z and A. Z (all characters)
• #: A. Z and A. Z and 0. 9 (all characters and numbers)
• *: #33 .. #126 (all ASCII characters)
• 0: 0 .. 9 (all numbers)
• 1: 1 .. 9 (all numbers except 0)
• 9: 0 .. 9 (all numbers)

For example:
[Aaa00] generate strings such as gxe21 and liy05.
You can also add text between two single quotes.
For example:
[AA '-' 1000] generates strings such as: CX-4903, SY-1044, etc.
In the definition, space characters are ignored unless they are in quotation marks.
If you want to repeat a character multiple times, you can add the number of times (n) between the two parentheses after the character ). You can also add a random number (minimum... maximum ).
For example:
The result of [AA (5 .. 15) ''aa (8 .. 20)] is as follows: "masfae qwwecdsadif"
The text in the definition does not have to be enclosed in brackets. In other words, ['hello'] is equivalent to 'Hello. Text without quotation marks is considered a function.
There are several specific functions available:
• Signal (Min, Max, Delta, noise) returns technical measurements (such as temperature ). Min (minimum) and max (maximum) determine the range,
Delta (△) is the maximum change volume. You can also add noise ). For example: Signal (-10, 20, 0.1, 0.1 ).
• Random ([Min], max) returns a random number between min (min) and max (max. If only Max is specified, Min is set to 0.
. For the date field, you can enter the min (min) and max (max) dates.
• Sequence (START, [INC], [withinparent]) returns the sequence number. Start is the start value, Inc is the increment (default is
1 ). For a schedule, you can specify the withinparent keyword to indicate that the sequence should be rescheduled for each parent record.
• List ('item' (weight), 'item' (weight ),...)
Returns one of the specified items randomly. Weight can be added between two parentheses, allowing a specific project to appear more likely than other projects.
Example: List ('wheel' (50), 'salesman' (30), 'manager' (10 ))
PL/SQL developer 7.0 User Guide 167

• List (select statement) is like the previous list function, but the project uses SQL SELECT statement (select
Statement.
• Text ([style], maxcharacters, [wordsperline, linesperparagraph])
This function returns text. The optional style (font) parameter can be
Lorumipsum (default) (suspected to be Dutch. -- Translator Jiang Hua dongnote), English, German or Japanese. It can also be like [AA]
In this case, words are generated from the specified character set. Maxcharacters determines the maximum size of the generated text, wordsperline and
Linesperparagraph determines the size of rows and paragraphs. The specified size can be a specified number or range (minimum... maximum ).
• File (path, path ,...)
Randomly select a file from a specific path and insert the content. Allows you to input binary data (such as images) to the database. The path can use wildcards, such
D:/images/*. BMP.

You can use the following function to change the text result of the preceding function:
• Uppercase ()
• Lowercase ()
• Initcaps ()

Example: initcaps (List (select ename from EMP ))
There are also several predefined datasets that can be used to generate more or less real data. You can use the following definitions:
• Firstname-name in the General List
• Lastname-name of the General List
• Company-company name (random list of existing companies)
• Address1-address Row 1
• Address2-address Row 2
• Zip-zip code
• City-City
• State-saving
• Country-Country
• Email (associated with firstname, lastname, and country)

There are also some available instance custom Datasets:
• Components. Code-General item: item code
• Components. Description-item description (computer part)
• Components. Price-product price
• Elements. Name-chemical element (name)
• Elements. symbol-chemical elements (symbols)

These datasets can be found in the datagenerator/userdata directory. The file names are elements.txt and components.txt.

. If needed, you can add your own set. It is easy to add a file separated by commas (,). The description is saved between square brackets in the first line of the file. You can specify "file name. Description" to use your file
As in the two examples. 168 PL/SQL developer 7.0 User Guide
All the functions and data mentioned above can be added together, for example, random (10 .. 99) + '-' + [A (4)].
"+" Is optional, but there should be at least one space as the separator.
Option
The options Tab allows you to set some preferences, especially the definition of the data generator. You can specify a date format (
Functions are used in the same way ). You can specify the number of records to be submitted (set to 0 ).
Yes ). Latency preferences are only used for some real-time tests. here you need to insert data at the specified speed.
The custom part allows you to define or cross the character set used for [data] definition. A project is always a single character, and the value is a range of one or more characters separated by spaces, just like this: A. z A. Z
0 .. 9 #200 .. #220. For example, to define a hexadecimal character set, you can specify H as the project, and 0. 9 a. f as the value.
The "initialization script" section allows you to specify the SQL statement that should be executed before data is inserted into the database.
Statement. In typical cases, this is a statement for creating a table or truncating a table, selecting a rollback segment, and so on. Multiple statements must be separated by semicolons.
By clicking the "preference" button on the left, general options for all data generators can be defined by default. For more details, see section 16.23
Chapter. If the corresponding preferences in the data generator definition are also set, these preferences will be rejected.
Generate data
There are three buttons at the bottom left to generate the actual data:
• Start test run-this will generate data and display results in the table on the results tab. By right-clicking a table, you can export the results in different formats.
• Create data as SQL-generate data as an SQL script. This only works when you do not need the file function to add data from the file.
PL/SQL developer 7.0 User Guide 169

• Create data in the database-generate data to the Oracle database.