Database Reference
In-Depth Information
the IS_WINDOWS function does rely on you using the \ in your CONTROL_FILES parameter. Be aware that you
may use / , but it would be highly unusual.
Note
The following is a procedure to create a control file to reload the unloaded data, using the DESCRIBE table
generated by DBMS_SQL.DESCRIBE_COLUMNS . It takes care of the operating system specifics for us, such as whether the
operating system uses a carriage return/linefeed (this is used for the STR attribute):
28
29 procedure dump_ctl( p_dir in varchar2,
30 p_filename in varchar2,
31 p_tname in varchar2,
32 p_mode in varchar2,
33 p_separator in varchar2,
34 p_enclosure in varchar2,
35 p_terminator in varchar2 )
36 is
37 l_output utl_file.file_type;
38 l_sep varchar2(5);
39 l_str varchar2(5) := chr(10);
40
41 begin
42 if ( is_windows )
43 then
44 l_str := chr(13) || chr(10);
45 end if;
46
47 l_output := utl_file.fopen( p_dir, p_filename || '.ctl', 'w' );
48
49 utl_file.put_line( l_output, 'load data' );
50 utl_file.put_line( l_output, 'infile ''' ||
51 p_filename || '.dat'' "str x''' ||
52 utl_raw.cast_to_raw( p_terminator ||
53 l_str ) || '''"' );
54 utl_file.put_line( l_output, 'into table ' || p_tname );
55 utl_file.put_line( l_output, p_mode );
56 utl_file.put_line( l_output, 'fields terminated by X''' ||
57 to_hex(p_separator) ||
58 ''' enclosed by X''' ||
59 to_hex(p_enclosure) || ''' ' );
60 utl_file.put_line( l_output, '(' );
61
62 for i in 1 .. g_descTbl.count
63 loop
64 if ( g_descTbl(i).col_type = 12 )
65 then
66 utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
67 ' date ''ddmmyyyyhh24miss'' ');
68 else
 
 
Search WWH ::




Custom Search