Database Reference
In-Depth Information
We are ready to dump the actual data out to disk. We begin by defining every column to be a
VARCHAR2(4000)
for
fetching into. All
NUMBER
s,
DATE
s,
RAW
s—every type will be converted into
VARCHAR2
. Immediately after this, we execute
the query to prepare for the fetching phase:
144 for i in 1 .. l_colCnt loop
145 dbms_sql.define_column( g_theCursor, i, l_columnValue, 4000);
146 end loop;
147
148 /*
149 Run the query - ignore the output of execute. It is only
150 valid when the DML is an insert/update or delete.
151 */
Now we open the data file for writing, fetch all of the rows from the query, and print it out to the data file:
152 l_cnt := dbms_sql.execute(g_theCursor);
153
154 /*
155 Open the file to write output to and then write the
156 delimited data to it.
157 */
158 l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w',
159 32760 );
160 loop
161 exit when ( dbms_sql.fetch_rows(g_theCursor) <= 0 );
162 l_separator := '';
163 l_line := null;
164 for i in 1 .. l_colCnt loop
165 dbms_sql.column_value( g_theCursor, i,
166 l_columnValue );
167 l_line := l_line || l_separator ||
168 quote( l_columnValue, p_enclosure );
169 l_separator := p_separator;
170 end loop;
171 l_line := l_line || p_terminator;
172 utl_file.put_line( l_output, l_line );
173 l_cnt := l_cnt+1;
174 end loop;
175 utl_file.fclose( l_output );
176
