Loading Data into Oracle Tables

Loading Data into Oracle Tables

( sp_get_set_and_load )

This doc details the procedure evolved to automate the process of loading the data into Oracle database using SQL*Loader.

The functionality of the procedure:

(1) Go to the destination directory and get the list of the .dat files.

(2) Verify whether the file name (without) extension tallies with one of the names of the tables in the given user schema.

(3) If the file name tallies with the name of one of the tables of the given schema, then

(a) Start the SQL*Loader and load data into that table

(b) Populate a specified user table with the results of the loading, such as

i. Date and time of loading,

ii. Table name,

iii. Source directory and file name,

iv. Loader log file,

v. Bad data log,

vi. Discarded data log and

Assumptions:

(1) you have the directory structure and par files, .dat files and and controls files created and stored them in the respective destination directories.

(2) only the .dat file are to be over written by copying the new data file into the directory and all the data files you receive either by FTP or by any other mode have the table name as their name and ‘.dat’ is their extension.

If these assumptions are to change:

(3) if the destinations are to change the hard coded paths are to be modified suitably to reflect the respective paths and structures.

Code:

(1) Granting file permissions to the user:

Connect as system to grant permissions to schema owner

begin

dbms_java.grant_permission

('USER',

'java.io.FilePermission',

'*',

'read');

dbms_java.grant_permission

('USER',

'java.io.FilePermission',

'*',

'execute');

dbms_java.grant_permission

('USER',

'java.lang.RuntimePermission',

'*',

'writeFileDescriptor');

end;

(2) Create table to hold the list of files. This is a permanent table. One may try with temporary table also with options to retain data or delete data when committed.

drop table dir_list;

create table dir_list

(filename varchar2(255));

(3) Create table to hold output data for SQL Load Job:

drop table tab_load_data_detail cascade constraints ;

create table tab_load_data_detail (

datetime date default sysdate,

table_name varchar2(31),

file_name varchar2(255),

discard_dir varchar2(255),

bad_dir varchar2(255),

log_dir varchar2(255))

tablespace <tablespace name

storage(initial 1M

next 1M

pctincrease 0

);

(4) Create java source procedure for picking up the list of files from OS

drop java source "DirList";

create or replace

and resolve java source named "DirList"

import java.io.*;

import java.sql.*;

public class DirList

{

public static void getList(java.lang.String directory)

throws SQLException

{

File path = new File(directory);

String[] list = path.list();

String element;

for (int i=0; i < list.length; i++)

{

element = list[i];

#sql { insert into dir_list(filename)

values(:element)

};

}

(5) Create a PLSQL procedure to use the java source:

drop procedure get_dir_list;

create or replace procedure get_dir_list(p_directory in varchar2)

language java

name 'DirList.getList( java.lang.String)';

(6) Create a java source procedure for executing command line commands:

create or replace and resolve

java source named "Util"

import java.io.*;

import java.lang.*;

public class Util extends Object

{

public static int RunThis(String[] args)

{

Runtime rt = Runtime.getRuntime();

int rc = -1;

try

{

Process p = rt.exec(args[0]);

int bufSize = 16384;

BufferedInputStream bis =

new BufferedInputStream(p.getInputStream(), bufSize);

int len;

byte buffer[]=new byte[bufSize];

while((len=bis.read(buffer,0,bufSize)) != -1)

System.out.write(buffer,0,len);

rc=p.waitFor();

}

catch (Exception e)

{

e.printStackTrace();

rc=-1;

}

finally

{

return rc;

}

(7) Create a PLSQL function to call that java source:

create or replace function run_cmd(p_cmd in varchar2) return number

language java

name 'Util.RunThis(java.lang.String[]) return integer';

(8) create a procedure to execute the command:

create or replace procedure exec_cmd(p_cmd in varchar2)

x number;

v_date_format varchar2(255);

begin

v_date_format:='mon-dd-yyyy hh24:mi:ss';

execute immediate ' alter session set nls_date_format ='''|| v_date_format||'''';

x:=run_cmd(p_cmd);

if x = 0 then

insert into tab_load_fail_detail (

datetime,

result,

discard_dir,

bad_dir,

log_dir

)

values (

sysdate,

'succeeded',

'h:\oracle\ldr_discard\',

'h:\oracle\ldr_bad\',

'h:\oracle\ldr_log\'

);

elsif x <> 0 then

insert into tab_load_fail_detail (

datetime,

result,

discard_dir,

bad_dir,

log_dir

)

values (

sysdate,

'failed',

'h:\oracle\ldr_discard\',

'h:\oracle\ldr_bad\',

'h:\oracle\ldr_log\'

);

end if;

end;

(9) create a pl sql procedure to perform the job

CREATE or replace PROCEDURE SCOTT.SP_GET_SET_AND_LOAD_DATA
as
cursor tab is select table_name from dba_tables where owner = user;
v_file   varchar2(255);
v_date_format varchar2(255);
v_cmd_string varchar2(255);
begin
Execute immediate' delete from dir_list';
get_dir_list('H:\Oracle\ldr_par_files');
v_date_format:='mon-dd-yyyy hh24:mi:ss';
Execute immediate ' alter session set nls_date_format ='''|| v_date_format||'''';
for tab_rec in tab
loop
     for x in (select upper(substr(filename,1,instr(filename,'.')-1)) filename from dir_list)
     loop
           v_file := x.filename;
           if     tab_rec.table_name = v_file then
              INSERT INTO TAB_LOAD_DATA_DETAIL
                VALUES ( sysdate,tab_rec.table_name,'H:\Oracle\loader_scripts\'||v_file||'.dat');
                dbms_output.put_line(tab_rec.table_name ||'   '||v_file||'    '||' You Got that');
                        dbms_output.put_line(''''||'sqlldr parfile=H:\Oracle\ldr_par_files\'||tab_rec.table_name||'.PAR'||'''');
                Exec_Cmd(''''||'sqlldr parfile=H:\\Oracle\\ldr_par_files\\'||tab_rec.table_name||'.PAR'||'''');
           end if;
     end loop;
end loop;
end;
/

(10) set up a job under dbms_job (per requirement)