sql loader log file result

Question

I am using SQLloader in ASP.NET appliaction to automate the bulk upload data to oracle db from CSV/EXCEL. Sqlloader creates a log file that shows the result of import that is created on server.

I want to show some info to my users

How many rows read? How many successfully imported? on an aspx page

How do I do that?

Answer 1

You can create an external table to read the log files. The virtue of this is that external tables can be used in SQL queries.

Firstly you need to create a directory object to identify the OS directory path. This needs to be done by a user with the CREATE ANY DIRECTORY privilege (probably a DBA account)....

SQL> create or replace directory sqlldr_log_dir as 'C:\your\directory\path'
  2  /

Directory created.


SQL> grant read , write on directory sqlldr_log_dir to apc
  2  /

Grant succeeded.

SQL> 

Next we create the table. Note the placeholder name for the logfile in the location clause....

SQL> create table sqlldr_logfiles (
  2      text_line varchar2(1024)
  3  )
  4  organization external
  5  (
  6      type oracle_loader
  7      default directory sqlldr_log_dir
  8      access parameters
  9          (records delimited by newline
 10              fields (text_line char(1024)
 11          )
 12      )
 13      location ('changeme.log')
 14  )
 15  /

Table created.

SQL>

Now out to the OS to do an import ...

C:\temp>imp apc file=apc_20100204.dmp log=apc_20100204.log tables=PTEST6,A

Import: Release 11.1.0.6.0 - Production on Thu Feb 4 11:51:07 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing APC's objects into APC
. importing APC's objects into APC
. . importing table                            "A"         12 rows imported
. . importing table                       "PTEST6"         19 rows imported
IMP-00009: abnormal end of export file
Import terminated successfully with warnings.

C:\temp>

This directory should be the same diectory you used before. Back into SQL. First we point the external table at the log file we used earlier and then query from it ...

SQL> alter table sqlldr_logfiles location ('apc_20100204.log')
  2  /

Table altered.

SQL> select * from sqlldr_logfiles
  2  where text_Line like '. . importing table%'
  3  /

text_Line
--------------------------------------------------------------------------------
. . importing table                            "A"         12 rows imported
. . importing table                       "PTEST6"         19 rows imported

SQL>

Formatting the output is easy, especially if you have 10g or higher, and so can use Regex functions.