1DBI::Dumper(3)        User Contributed Perl Documentation       DBI::Dumper(3)
2
3
4

NAME

6       DBI::Dumper - Dump data from a DBI datasource to file.
7

SYNOPSIS

9               << in test.ctl >>
10               OPTIONS (export=100,rows=100)
11               EXPORT DATA REPLACE INTO FILE 'test.dat'
12               FIELDS TERMINATED BY TAB
13                       ENCLOSED BY '"' AND '"'
14                       ESCAPED BY '\'
15               WITH HEADER FROM
16               SELECT * FROM MY_TABLE
17
18               my $dumper = DBI::Dumper->new(
19                       -dbh => $dbh,
20                       -control => 'test.ctl',
21                       -output => 'test.dat',
22               );
23
24               $dumper->prepare;
25               $dumper->execute;
26
27
28               # have DBI::Dumper login to database
29               my $dumper = DBI::Dumper->new(
30                       -userid => 'user/pass@sid',
31                       ...
32               );
33
34
35               # send a statement handle instead of database handle
36               my $sth = DBI->connect()->prepare("SELECT * FROM MY TABLE");
37               $dumper->execute($sth);
38

DESCRIPTION

40       Dumps data from a select statement into an output file. dbidumper tries
41       to mirror the functionality and behavior of sql*loader. The control
42       file syntax is similar, and DBI::Dumper utilizes a subset of the
43       sql*loader options.
44
45       Configuration options can be set either in the control file, passed to
46       the new() method, or by calling the option's accessor.
47
48   Options
49       userid=username/password@sid
50           Login information for database connection.
51
52           If the sid includes a colon, the full string will be used as the
53           DBI dsn. For example:
54
55                   userid=username/password@mysql:database
56
57           Will connect to mysql's 'database' database as username.
58
59           Otherwise, DBI::Dumper assumes a dbi:Oracle connection and prefixes
60           the dsn with dbi:Oracle:. If no dsn is passed, DBI::Dumper first
61           looks in $ENV{DBI_DSN} then $ENV{ORACLE_SID}.
62
63       control=filename
64           Input control filename. Defaults to standard input. See "CONTROL
65           FILE" for layout and description.
66
67       output=filename
68           Output filename for data. Defaults to standard output. If rows is
69           given, can contain template consisting of three or more Xs. The Xs
70           will be replaced with the file sequence number. If the template
71           does not contain three or more Xs, the sequence number will be
72           appended to the filename with a dot. Examples:
73
74       rows=n
75           Number of rows per output file. Defaults to all rows in one output
76           file.
77
78       export=n
79           Total number of rows to export. Use to limit output or restart
80           dump.
81
82       skip=n
83           Number of rows to skip from beginning. File sequence number will be
84           preserved, so if rows=n is set, this can be used to restart a job.
85
86       bindsize=n
87           Block size to write file. Defaults to write each record as returned
88           from database. If set, dbidumper will collect rows into a buffer at
89           most n bytes large before writing to file.
90
91       silent=true
92           Suppress normal logging information. dbidumper will only report
93           errors.
94
95   Exporting to Multiple Files
96       rows=1000 output=outputXXXXX.dat
97           Data will be written to output00001.dat, output00002.dat, etc.
98
99       rows=1000 output=output.dat
100           Data will be written to output.dat.0001, output.dat.0002, etc.
101
102       output=outputXXXXX.dat
103           Data will be written to outputXXXXX.dat
104

DEPENDENCIES

106       This program depends on the following perl modules, available from a
107       CPAN mirror near you:
108
109       Parse::RecDescent - Recursive parser
110       DBI - Standard database interface
111

CONTROL FILE

113       The control file used for dbidumper is very similar to sql*loader's.
114       The full specification is:
115
116               [ OPTIONS ([option], ...) ]
117               EXPORT DATA [ REPLACE | APPEND ] [ INTO FILE 'filename' ]
118               [ FIELDS
119                       [ TERMINATED [BY] {TAB | 'string' | X'hexstring'} ] |
120                       [ ENCLOSED [BY] {'string' | X'hexstring'}
121                               [AND] ['string' | X'hexstring'] ]
122                       [ ESCAPED [BY] {'string' | X'hexstring'} ]
123               ]
124               [ WITH HEADER ]
125               FROM
126               select_statement
127

AUTHOR

129       Written by Warren Smith (warren.smith@acxiom.com)
130

BUGS

132       None yet.
133
134
135
136perl v5.36.0                      2023-01-20                    DBI::Dumper(3)