Data Loading and Unloading - Expert Oracle Database Architecture - page 753

The package body follows. We use UTL_FILE to write a control file and a data file. DBMS_SQL is used to dynamically
process any query. We use one datatype in our queries: a VARCHAR2(4000) . This implies we cannot use this method to
unload LOBs, and that is true if the LOB is greater than 4,000 bytes. We can, however, use this to unload up to
4,000 bytes of any LOB using SUBSTR . Additionally, since we are using a VARCHAR2 as the only output data type, we can
handle RAW s up to 2,000 bytes in length (4,000 hexadecimal characters), which is sufficient for everything except
LONG RAW s and LOB s. Additionally, any query that references a nonscalar attribute (a complex object type, nested table,
and so on) will not work with this simple implementation. The following is a 90 percent solution, meaning it solves the
problem 90 percent of the time:
EODA@ORA12CR1> create or replace package body unloader
2 as
3
4
5 g_theCursor integer default dbms_sql.open_cursor;
6 g_descTbl dbms_sql.desc_tab;
7 g_nl varchar2(2) default chr(10);
8
These are some global variables used in this package body. The global cursor is opened once, the first time we
reference this package, and it will stay open until we log out. This avoids the overhead of getting a new cursor every
time we call this package. The G_DESCTBL is a PL/SQL table that will hold the output of a DBMS_SQL.DESCRIBE call.
G_NL is a newline character. We use this in strings that need to have newlines embedded in them. We do not need to
adjust this for Windows— UTL_FILE will see the CHR(10) in the string of characters and automatically turn that into a
carriage return/linefeed for us.
Next, we have a small convenience function used to convert a character to hexadecimal. It uses the built-in
functions to do this:
9
10 function to_hex( p_str in varchar2 ) return varchar2
11 is
12 begin
13 return to_char( ascii(p_str), 'fm0x' );
14 end;
15
Finally, we create one more convenience function, IS_WINDOWS , that returns TRUE or FALSE depending on if we are
on the Windows platform, and therefore the end of line is a two-character string instead of the single character it is on
most other platforms. We are using the built-in DBMS_UTILITY function, GET_PARAMETER_VALUE , which can be used to
read most any parameter. We retrieve the CONTROL_FILES parameter and look for the existence of a \ in it—if we find
one, we are on Windows:
16 function is_windows return boolean
17 is
18 l_cfiles varchar2(4000);
19 l_dummy number;
20 begin
21 if (dbms_utility.get_parameter_value( 'control_files', l_dummy, l_cfiles )>0)
22 then
23 return instr( l_cfiles, '\' ) > 0;
24 else
25 return FALSE;
26 end if;
27 end;