Data Unloader - Plsql utility

One thing SQLLDR does not do, and that Oracle supplies no tools for, is the unloading of data in a format understandable by SQLLDR. This would be useful for moving data from system to system without using EXP/IMP. We will develop a small PL/SQL utility that may be used to unload data on a server in a SQLLDR‐friendly format  (Also this utility has some limitations, like handling LOBS, RAW & BFile)

create or replace package pkg_unloader as

    procedure dump_data (
        p_query in varchar2,
        p_directory in varchar2 default 'DATA_PUMP_DIR',  /* default directory for data pump in oracle 10g */
        p_file_name in varchar2    default 'sample.dat',
        p_delimited in varchar2 default '|',
        p_status   out number );
   
end pkg_unloader;   
/
create or replace package body pkg_unloader as

        procedure dump_data (
            p_query in varchar2,
            p_directory in varchar2 default 'DATA_PUMP_DIR',
            p_file_name in varchar2 default 'sample.dat',
            p_delimited in varchar2 default '|',
            p_status   out number )        /* 0 - Sucess / other than 0 is error */
        as
            l_exec_status number;
            l_file_handle utl_file.file_type;
            l_desc_tab    dbms_sql.desc_tab;
            l_column_count number ;
            l_line varchar2(32760) := null;
            l_column_value varchar2(4000);           
            g_cursor number := dbms_sql.open_cursor;
            l_nls_values nls_database_parameters.value%type;
        begin
            p_status := 0;
            select value
            into l_nls_values
            from nls_database_parameters
            where parameter = 'NLS_DATE_FORMAT';
           
            execute immediate ' alter session set nls_date_format = ''ddmmyyyyhh24miss'' ';
           
            l_file_handle := utl_file.fopen (p_directory,p_file_name,'w',32760);
           
            dbms_sql.parse(g_cursor,p_query,dbms_sql.native);
            dbms_sql.describe_columns (g_cursor,l_column_count,l_desc_tab);
           
            for i in 1..l_column_count
            loop
                dbms_sql.define_column (g_cursor,i,l_column_value,4000);
            end loop;
           
            l_exec_status := dbms_sql.execute(g_cursor);
           
            while ( dbms_sql.fetch_rows(g_cursor) > 0 )
            loop
                l_line := null;
                for i in 1..l_column_count
                loop
                    dbms_sql.column_value(g_cursor,i,l_column_value);
                    l_line := l_line ||p_delimited||l_column_value;
                end loop;
                l_line := l_line || chr(13) || chr(10); /* line  terminator in windows */
                utl_file.put(l_file_handle,l_line);
            end loop;
           
            utl_file.fclose(l_file_handle);
           
            execute immediate ' alter session set nls_date_format = '''|| l_nls_values ||'''';
           
        exception
            when others then
                if dbms_sql.is_open(g_cursor) then
                    dbms_sql.close_cursor(g_cursor);
                end if;
               
                if utl_file.is_open (l_file_handle) then
                    utl_file.fclose(l_file_handle);
                end if;
                p_status := sqlcode;
                raise_application_error ( -20458,sqlerrm);
        end dump_data;
end pkg_unloader;
/

Sample output

scott@10G> declare
  2   l_status number;
  3  begin
  4     pkg_unloader.dump_data('select * from emp',
  5      'DATA_PUMP_DIR',
  6      'emp.dat',
  7      '|',
  8     l_status );
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
scott@10G> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\179818>d:

D:\>cd D:\oracle\product\10.2.0\admin\10g\dpdump

D:\oracle\product\10.2.0\admin\10G\dpdump>type emp.dat
|7782|CLARK|MANAGER|7839|09061981000000|2450||10
|7839|KING|PRESIDENT||17111981000000|5000|0|10
|7934|MILLER|CLERK|7782|23011982000000|1300||10
|7844|TURNER|SALESMAN|7698|08091981000000|1500|0|30
|7788|SCOTT|ANALYST|7566|19041987000000|3000||20
|7698|BLAKE|MANAGER|7839|01051981000000|2850||30
|7566|JONES|MANAGER|7839|02041981000000|2975||20
|7499|ALLEN|SALESMAN|7698|20021981000000|1600|300|30
|7521|WARD|SALESMAN|7698|22021981000000|1250|500|30
|7902|FORD|ANALYST|7566|03121981000000|3000||20
|7654|MARTIN|SALESMAN|7698|28091981000000|1250|1400|30
|7369|SMITH|CLERK|7902|17121980000000|800||20
|7876|ADAMS|CLERK|7788|23051987000000|1100||20
|7900|JAMES|CLERK|7698|03121981000000|950||30

D:\oracle\product\10.2.0\admin\10G\dpdump>