Example 1.0
Generate a table with 1000 rows for a table called orders with 2 columns,
- First column of type number that can up to the size 9999999 that is unique,
- Second of type varchar2 of length 5 with the respective column names orderno and purchase
Example 1.1
output to standard out
oragen --rows 1000 --table-name orders --gen-table \
--number --length 9999999 --unique --column-name orderno \
--varchar2 --length 5 -C purchase
output of example-1.1
example 1.1 src
Example 1.2
redirecting to file
redirect output to a file called orders.sql
oragen --rows 1000 --table-name orders --gen-table \
--number --length 9999999 --unique --column-name orderno \
--varchar2 --length 5 -C purchase \
> orders.sql
then to insert the data into the database doing
sqlplus username @order.sql
output of example1.2 sames as example-1.1 except would be in orders.sql
example1.2 src
Example 1.3
redirecting to a file with verbose information
oragen --rows 1000 --table-name orders --gen-table -V \
--number --length 9999999 --unique --column-name orderno \
--varchar2 --length 5 -C purchase \
> orders.sql
all verbose information goes to stderr, and all stdout goes to orders.sql
alternatively, will go to orders.sql by doing example-1.4
verbose information displayed
output of example1.3 sames as example-1.3 except would be in orders.sql and verbose information displayed
example1.3 src
Example 1.4
redirecting to a file with verbose information to file also, (stderr to stdout)
oragen --rows 1000 --table-name orders --gen-table -V \
--number --length 9999999 --unique --column-name orderno \
--varchar2 --length 5 -C purchase \
> orders.sql 2>&1
output of example1.4
example1.4 src
Example 1.5
piping straight to sqlplus, this saves the intermediate step of redirecting and
writing to a file and running sqlplus later
oragen --rows 1000 --table-name orders --gen-table \
--number --length 9999999 --unique --column-name orderno \
--varchar2 --length 5 -C purchase \
| sqlplus -S username/password
NOTE: be careful about specfying username and password on the command line,
also, sqlplus runs in silent mode with the -S
output goes directly to sqlplus which is executed immediately, no need to save to a file
example1.5 src
Example 2.0
This is a more in depth example that goes straight into it
Generate 500 rows for a table called employees with 5 columns, commit after every
20 rows that are inserted, and also generate the create table script
- First column called employee_number that is a unique number of maximum size 99999
- Second column called employee_name of varchar2 of maximum length 30 that can vary in size
- Third column called address of varchar2 of maximum length 40 that can vary in size with
a 20% percentage of NULLs and can also contain punctuation characters
- Fourth column called postcode of number with the maximum length of 99999 that can vary in size
- Fifth column called age of number with the maximum size 120 that can vary in size
oragen --rows 500 --table-name employees --commit 20 --gen-table \
--number --unique --length 99999 -C employee_number \
--varchar2 --length 30 -C employee_name \
--varchar2 --length 40 --Null 20 --cangrow --punctual -C address \
--number --length 99999 --cangrow -C postcode \
--number --length 120 --cangrow -C age \
> employees.sql
example 2.0 output
example 2.0 src
Example 3.0
generate a table with 300 rows called blah with 3 columns all of varchar2 and
let oragen name the columns for me and use the default sizes
oragen --gen-table --rows 300 --table-name blah \
--varchar2 -c --varchar2 -c --varchar2 -c
example 3.0 output
example 3.0 src
Example 3.1
the default option is varchar2 (unless you've changed the values in oragen.h)
so this would of worked also
oragen --gen-table --rows 300 --table-name blah -c -c -c
example 3.1 output same as example-3.0
example 3.1 src
Error handling examples
Example 4.0
generate a table with 1000 rows for table called keys with 2 columns and let
oragen autoname the columns
first column of number which is unique of max length 100
second column of varchar2 of length 30
oragen --gen-table --rows 1000 --table-name keys \
--number --unique --length 100 -c \
--varchar2 --unique --length 30 -c
what happens, the --length 100 is overridden and is set to the number of rows
to generate which is 1000. The point is that the user wants 1000 rows.
example 4.0 output
example 4.0 src
example continued ..
Example 5.0
generate a table with 1000 rows for a table called keys2 with 2 columns and let
oragen autoname the columns
first column of varchar2 which is unique of length 2 and in lowercase
second column of varchar2 of length 20
oragen --gen-table --rows 1000 --table-name keys2 \
--varchar2 --unique --lowercase --length 2 -c \
--varchar2 --unique --length 30 -c
*** ran out of unique values for column number 1, "col1"
-- generated 675 rows
example 5.0 output
example 5.0 src
Example 5.1
had col1 not been --lowercase then it would of generated 1000 rows. Creating
a primary key on the column had it all been uppercase and no --lowercase
would be permissable
oragen --gen-table --rows 1000 --table-name keys2 \
--varchar2 --unique --length 2 -c \
--varchar2 --unique --length 30 -c
SQL> alter table keys add constraint keys_primary_key primary key (col1);
Table altered.
SQL>
example 5.1 output
example 5.1 src
Final Note
"The possibilites are limiteless", I could go on forever with examples, but like most things in
programming, you won't learn till you try it practically.
The examples might not look like much, but until you load the data into the table and
view it you'll begin to realise how useful the tool is. I want to add more options to
it like support for more types and whatever comes to mind.
Author : Stellios Keskinidis
March 2001
Copyright (c)
back