Home » RDBMS Server » Server Utilities » Loading Multiple Input files into multiple tables (Oracle10g)
Loading Multiple Input files into multiple tables [message #560009] Mon, 09 July 2012 03:35 Go to next message
naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad
Junior Member
 
NGFID;RECTYPE;RECNAME
25;7;POLES
	PARENT
	CHILD;1401;9845075;2020	
817;8;SUPPORT
	PARENT
	CHILD


Required output:-

AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'


File :-mauri.export.ngf 
NGFID;RECTYPE;RECNAME
257;7;POLES
	PARENT
	CHILD;1401;9845075;2020	
8174;8;SUPPORT
	PARENT
	CHILD

Required output:-

AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc

CREATE TABLE NGF_REC_LINK
    (
      AREA_SRNO  NUMBER(2),
      AREA_NAME  VARCHAR2(40),
      NGFID	 NUMBER(20),
      TABLENAME  VARCHAR2(40),
      PARENT	 VARCHAR2(200),
      CHILD	 VARCHAR2(200)
    )
    /

CREATE TABLE POLES_7
 (
   AREA_SRNO       NUMBER(2),
   AREA_NAME       VARCHAR2(50),
   NGFID	   NUMBER(20)   
  )
  /

 CREATE TABLE SUPPORT_8
   (
     AREA_SRNO       NUMBER(2),
     AREA_NAME	     VARCHAR2(50),
     NGFID	     NUMBER(20)
   )
   /


Can you Please find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirment.

Thanks in advance..

[mod-edit: code tags added by bb; please start adding them yourself; see the forum guidelines for how]
  • Attachment: ngf_test.ctl
    (Size: 0.96KB, Downloaded 3041 times)

[Updated on: Mon, 09 July 2012 14:36] by Moderator

Report message to a moderator

Re: Loading Multiple Input files into multiple tables [message #560016 is a reply to message #560009] Mon, 09 July 2012 14:48 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I'm completely lost.

Report message to a moderator

Re: Loading Multiple Input files into multiple tables [message #560017 is a reply to message #560016] Mon, 09 July 2012 14:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I'm completely lost.

^
|
North is that way. (Just follow the arrow.)

Report message to a moderator

Re: Loading Multiple Input files into multiple tables [message #560021 is a reply to message #560009] Mon, 09 July 2012 15:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Since you need to use your file names as column values, you will need to make them constants in your control file, so you will need two separate control files. Since the area_srno is also per file, that will need to be a constant as well. The following shows the data files, control files, tables, loads, and results. As stated in your previous thread, you cannot pass the file name to your control file to be used as a column value, so you will need to find a way to create your control file with those values as constants.

-- data file 3rivieres.export.ngf:
NGFID;RECTYPE;RECNAME
25;7;POLES
	PARENT
	CHILD;1401;9845075;2020	
817;8;SUPPORT
	PARENT
	CHILD


-- data file mauri.export.ngf:
NGFID;RECTYPE;RECNAME
257;7;POLES
	PARENT
	CHILD;1401;9845075;2020	
8174;8;SUPPORT
	PARENT
	CHILD


-- SQL*Loader control file ngf_test1.ctl:
options(skip=1)
load data
infile '3rivieres.export.ngf'
append
continueif next preserve (1:1) = x'09'
into table ngf_rec_link
FIELDS TERMINATED BY X'9'
trailing nullcols
(area_srno CONSTANT "1",
area_name CONSTANT "3rivieres.export.ngf",
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
parent  "ltrim (:parent, 'PARENT;')",
child  "ltrim (:child, 'CHILD;')",
tablename ":filler2 || '_' || :filler1")
into table poles_7
when (filler1 = '7') and (filler2 = 'POLES')
FIELDS TERMINATED BY X'9'
trailing nullcols
(area_srno CONSTANT "1",
area_name CONSTANT "3rivieres.export.ngf",
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller ,
filler3 filler ,
filler4 filler )
into table support_8
when (filler1 = '8') and (filler2 = 'SUPPORT')
FIELDS TERMINATED BY X'9'
trailing nullcols
(area_srno CONSTANT "1",
area_name CONSTANT "3rivieres.export.ngf",
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller ,
filler3 filler ,
filler4 filler)


-- SQL*Loader control file ngf_test2.ctl:
options(skip=1)
load data
infile 'mauri.export.ngf'
append
continueif next preserve (1:1) = x'09'
into table ngf_rec_link
FIELDS TERMINATED BY X'9'
trailing nullcols
(area_srno CONSTANT "2",
area_name CONSTANT "mauri.export.ngf",
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
parent  "ltrim (:parent, 'PARENT;')",
child  "ltrim (:child, 'CHILD;')",
tablename ":filler2 || '_' || :filler1")
into table poles_7
when (filler1 = '7') and (filler2 = 'POLES')
FIELDS TERMINATED BY X'9'
trailing nullcols
(area_srno CONSTANT "2",
area_name CONSTANT "mauri.export.ngf",
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller ,
filler3 filler ,
filler4 filler )
into table support_8
when (filler1 = '8') and (filler2 = 'SUPPORT')
FIELDS TERMINATED BY X'9'
trailing nullcols
(area_srno CONSTANT "2",
area_name CONSTANT "mauri.export.ngf",
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller ,
filler3 filler ,
filler4 filler)
options(skip=1)
load data
infile 'mauri.export.ngf'
append
continueif next preserve (1:1) = x'09'
into table ngf_rec_link
FIELDS TERMINATED BY X'9'
trailing nullcols
(area_srno CONSTANT "2",
area_name CONSTANT "mauri.export.ngf",
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
parent  "ltrim (:parent, 'PARENT;')",
child  "ltrim (:child, 'CHILD;')",
tablename ":filler2 || '_' || :filler1")
into table poles_7
when (filler1 = '7') and (filler2 = 'POLES')
FIELDS TERMINATED BY X'9'
trailing nullcols
(area_srno CONSTANT "2",
area_name CONSTANT "mauri.export.ngf",
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller ,
filler3 filler ,
filler4 filler )
into table support_8
when (filler1 = '8') and (filler2 = 'SUPPORT')
FIELDS TERMINATED BY X'9'
trailing nullcols
(area_srno CONSTANT "2",
area_name CONSTANT "mauri.export.ngf",
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller ,
filler3 filler ,
filler4 filler)


-- tables:
SCOTT@orcl_11gR2> CREATE TABLE NGF_REC_LINK
  2  	 (
  3  	   AREA_SRNO  NUMBER(2),
  4  	   AREA_NAME  VARCHAR2(40),
  5  	   NGFID      NUMBER(20),
  6  	   TABLENAME  VARCHAR2(40),
  7  	   PARENT     VARCHAR2(200),
  8  	   CHILD      VARCHAR2(200)
  9  	 )
 10  	 /

Table created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> CREATE TABLE POLES_7
  2   (
  3  	AREA_SRNO	NUMBER(2),
  4  	AREA_NAME	VARCHAR2(50),
  5  	NGFID		NUMBER(20)
  6    )
  7    /

Table created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2>  CREATE TABLE SUPPORT_8
  2  	(
  3  	  AREA_SRNO	  NUMBER(2),
  4  	  AREA_NAME	  VARCHAR2(50),
  5  	  NGFID 	  NUMBER(20)
  6  	)
  7  	/

Table created.


-- loads:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=ngf_test1.ctl LOG=test1.log

SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=ngf_test2.ctl LOG=test2.log


-- results:
SCOTT@orcl_11gR2> COLUMN area_name  FORMAT A20
SCOTT@orcl_11gR2> COLUMN tablename  FORMAT A9
SCOTT@orcl_11gR2> COLUMN parent     FORMAT A6
SCOTT@orcl_11gR2> COLUMN child	    FORMAT A17
SCOTT@orcl_11gR2> SELECT * FROM ngf_rec_link
  2  /

 AREA_SRNO AREA_NAME                 NGFID TABLENAME PARENT CHILD
---------- -------------------- ---------- --------- ------ -----------------
         1 3rivieres.export.ngf         25 POLES_7          1401;9845075;2020
         1 3rivieres.export.ngf        817 SUPPORT_8
         2 mauri.export.ngf            257 POLES_7          1401;9845075;2020
         2 mauri.export.ngf           8174 SUPPORT_8

4 rows selected.

SCOTT@orcl_11gR2> SELECT * FROM poles_7
  2  /

 AREA_SRNO AREA_NAME                 NGFID
---------- -------------------- ----------
         1 3rivieres.export.ngf         25
         2 mauri.export.ngf            257

2 rows selected.

SCOTT@orcl_11gR2> SELECT * FROM support_8
  2  /

 AREA_SRNO AREA_NAME                 NGFID
---------- -------------------- ----------
         1 3rivieres.export.ngf        817
         2 mauri.export.ngf           8174

2 rows selected.

Report message to a moderator

Re: Loading Multiple Input files into multiple tables [message #560022 is a reply to message #560021] Mon, 09 July 2012 16:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
As to how to generate the control files, if you have your filenames in a table, for example:

SCOTT@orcl_11gR2> select * from files
  2  /

FILENAME
------------------------------------------------------------
3rivieres.export.ngf
mauri_export.ngf

2 rows selected.


-- then you can create a sequence and generate the control files, like so:
SCOTT@orcl_11gR2> create sequence ngf_area_srno_seq
  2  /

Sequence created.

SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> declare
  2    v_file  utl_file.file_type;
  3    v_seq   number;
  4  begin
  5    for r in
  6  	 (select * from files)
  7    loop
  8  	 select ngf_area_srno_seq.nextval into v_seq from dual;
  9  	 v_file := utl_file.fopen ('MY_DIR', 'ngf_test' || v_seq || '.ctl', 'W', 32767);
 10  	 utl_file.put_line (v_file, 'options(skip=1)');
 11  	 utl_file.put_line (v_file, 'load data');
 12  	 utl_file.put_line (v_file, 'infile ' || r.filename);
 13  	 utl_file.put_line (v_file, 'append');
 14  	 utl_file.put_line (v_file, 'continueif next preserve (1:1) = x''09''');
 15  	 utl_file.put_line (v_file, 'into table ngf_rec_link');
 16  	 utl_file.put_line (v_file, 'FIELDS TERMINATED BY X''9''');
 17  	 utl_file.put_line (v_file, 'trailing nullcols');
 18  	 utl_file.put_line (v_file, '(area_srno CONSTANT "' || v_seq || '",');
 19  	 utl_file.put_line (v_file, 'area_name CONSTANT "' || r.filename || '",');
 20  	 utl_file.put_line (v_file, 'ngfid position (1) terminated by '';'',');
 21  	 utl_file.put_line (v_file, 'filler1 boundfiller terminated by '';'',');
 22  	 utl_file.put_line (v_file, 'filler2 boundfiller terminated by whitespace,');
 23  	 utl_file.put_line (v_file, 'parent  "ltrim (:parent, ''PARENT;'')",');
 24  	 utl_file.put_line (v_file, 'child  "ltrim (:child, ''CHILD;'')",');
 25  	 utl_file.put_line (v_file, 'tablename ":filler2 || ''_'' || :filler1")');
 26  	 utl_file.put_line (v_file, 'into table poles_7');
 27  	 utl_file.put_line (v_file, 'when (filler1 = ''7'') and (filler2 = ''POLES'')');
 28  	 utl_file.put_line (v_file, 'FIELDS TERMINATED BY X''9''');
 29  	 utl_file.put_line (v_file, 'trailing nullcols');
 30  	 utl_file.put_line (v_file, '(area_srno CONSTANT "' || v_seq || '",');
 31  	 utl_file.put_line (v_file, 'area_name CONSTANT "' || r.filename || '",');
 32  	 utl_file.put_line (v_file, 'ngfid position (1) terminated by '';'',');
 33  	 utl_file.put_line (v_file, 'filler1 boundfiller terminated by '';'',');
 34  	 utl_file.put_line (v_file, 'filler2 boundfiller ,');
 35  	 utl_file.put_line (v_file, 'filler3 filler ,');
 36  	 utl_file.put_line (v_file, 'filler4 filler )');
 37  	 utl_file.put_line (v_file, 'into table support_8');
 38  	 utl_file.put_line (v_file, 'when (filler1 = ''8'') and (filler2 = ''SUPPORT'')');
 39  	 utl_file.put_line (v_file, 'FIELDS TERMINATED BY X''9''');
 40  	 utl_file.put_line (v_file, 'trailing nullcols');
 41  	 utl_file.put_line (v_file, '(area_srno CONSTANT "' || v_seq || '",');
 42  	 utl_file.put_line (v_file, 'area_name CONSTANT "' || r.filename || '",');
 43  	 utl_file.put_line (v_file, 'ngfid position (1) terminated by '';'',');
 44  	 utl_file.put_line (v_file, 'filler1 boundfiller terminated by '';'',');
 45  	 utl_file.put_line (v_file, 'filler2 boundfiller ,');
 46  	 utl_file.put_line (v_file, 'filler3 filler ,');
 47  	 utl_file.put_line (v_file, 'filler4 filler)');
 48  	 utl_file.fclose (v_file);
 49    end loop;
 50  end;
 51  /

PL/SQL procedure successfully completed.


The above code will generate the two control files in my previous response, using the file names in the table.

Report message to a moderator

Re: Loading Multiple Input files into multiple tables [message #560023 is a reply to message #560022] Mon, 09 July 2012 16:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
As to how to get the list of files in a table, so you can loop through them in the above code, there are various methods. The following is one method by Tom Kyte. Another method would be to list the files to a text file from your operating system, then create an external table that points to that file. There are other methods as well.

SCOTT@orcl_11gR2> create global temporary table DIR_LIST
  2    ( filename varchar2(255) )
  3  	 on commit preserve rows
  4  /

Table created.

SCOTT@orcl_11gR2> create or replace and compile java source named "DirList"
  2  as
  3  import java.io.*;
  4  import java.sql.*;
  5  
  6  public class DirList
  7  {
  8  public static void getList(String directory)
  9  			throws SQLException
 10  {
 11  	 File path = new File( directory );
 12  	 String[] list = path.list();
 13  	 String element;
 14  
 15  	 for(int i = 0; i < list.length; i++)
 16  	 {
 17  	     element = list[i];
 18  	     #sql { INSERT INTO DIR_LIST (FILENAME)
 19  		    VALUES (:element) };
 20  	 }
 21  }
 22  
 23  }
 24  /

Java created.

SCOTT@orcl_11gR2> create or replace procedure get_dir_list ( p_directory in varchar2 )
  2  as language java
  3  name 'DirList.getList( java.lang.String )';
  4  /

Procedure created.

SCOTT@orcl_11gR2> exec get_dir_list( 'c:\my_oracle_files' );

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select * from dir_list
  2  where  lower (filename) like '%.ngf'
  3  /

FILENAME
--------------------------------------------------------------------------------
3rivieres.export.ngf
mauri.export.ngf

2 rows selected.

Report message to a moderator

Re: Loading Multiple Input files into multiple tables [message #560162 is a reply to message #560023] Wed, 11 July 2012 01:12 Go to previous message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
If your operating system is Windows, then see the following thread for another way to create control files, based on data files in a directory, and run SQL*Loader using each control file:

http://www.orafaq.com/forum/t/182654/43710/

Report message to a moderator

Previous Topic: Schema level export with particular partition tables
Next Topic: Import table with different name
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Feb 07 01:30:09 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.