One project every month - making stuff better ...

PLSQL test data from cursor to table or from cursor to csv

When you are creating test data it is usually either to create a quick test table for a piece of code, or because you need to simulate data extraction to another system. That is why any generator created with testdata_ninja includes methods to do that quickly. Every generator created includes a to_table method and a to_csv table function.

To demonstrate I will use the generator that I created in my previous entry about test data reference fields. Here is an example of the output from that generator:

SQL> column ename format a30
SQL> select * from table(tdg_t_bref.t_bref(10));

EMPLOYEE_ID CO G ENAME                          HIREDATE               SALARY
--------------------------------------- -- - ------------------------------ ------------------ ----------
        500 BR F Ea                             31-MAR-11 15:11:33      33037
        510 SH F Luna                           15-APR-09 14:18:35      36590
        520 CV F Cherry                         20-MAY-12 14:34:20      33120
        530 AI F Ivy                            07-OCT-08 13:56:51      32772
        540 CR M Louis                          08-JUN-07 07:18:29      49008
        550 TV M David                          15-SEP-14 22:42:32      33804
        560 NR M Otto                           02-FEB-15 22:19:41      36208
        570 DE M Birk                           07-SEP-10 23:01:51      33333
        580 IR M YAO                            21-FEB-08 15:58:01      32603
        590 FI M Bentley                        05-APR-11 22:37:44      36732

10 rows selected


If I quickly wanted to create a 100000 row test table, I can simply execute

SQL> set timing on;

SQL> begin
  tdg_t_bref.to_table('totable_test', 100000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.302

SQL> select count(*) from totable_test;


Elapsed: 00:00:00.041


Another reason to create test data is sometimes to create test data for other systems or test batch creations. For that all generators have a pipelined function to get the data directly in CSV format. So imagine we wanted to create a csv file with 10 rows of data, we could simply run the following sql

SQL> select * from table(tdg_t_bref.to_csv(10));

500,LV,F,Daisy,10-jan-2013 01:47:03,33372                                    
510,BS,F,angel,13-may-2012 12:26:31,36293                                 
520,FJ,F,Vigga,30-jul-2016 04:49:29,33595                                     
530,CF,M,Adrian,13-jul-2008 00:54:41,39830                                  
540,PL,M,Lukas,09-mar-2014 06:20:15,35688                                
550,ER,M,Simon,22-nov-2011 16:56:27,36316                                
560,BJ,M,Sofus,02-jun-2012 09:50:41,30814                                  
570,SL,M,Nikolaj,16-jun-2008 14:05:48,42679                                
580,TV,F,Esther,27-jul-2009 08:01:44,32710                                  
590,IS,F,Luna,09-apr-2014 23:52:50,33371                                    

 11 rows selected 

Elapsed: 00:00:00.182


Notice the extra row. By default there will be a header row for the CSV output.

So go ahead, create some test data!

Tagged in : RANDOM_NINJA