Dynamic SQL-Loader control file

Question

I have 20 tables that are temp-tables where we load and validate data constantly and I have a control file for each table.

How can I have a unique control file that just changes the table the data is loaded into? Any suggestion?

Thanks in advance!

---Oracle info---

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

@tbone does it matter? I would love it if so! @BRabbit27; dynamic ctl files implies another language to scan the table and the file and create it for you, i.e. python (I'm biased) etc. — Ben
– Ben, Commented Jan 7, 2012 at 11:08
@Ben the o/s matters if your solution is outside sqlldr itself (one thought was to use sed/awk in linux script to dynamically create the ctl files), or windows use perl (or python if u really must ;) Still, a bit of a hack I agree — tbone
– tbone, Commented Jan 9, 2012 at 11:57

Andrew Wolfe · Accepted Answer · 2012-01-05 16:01:48Z

2

Suggest you write your control file load the data into a synonym rather than into the specific table. Begin each load run by redefining the synonym to the table you want.

answered Jan 5, 2012 at 16:01

Andrew Wolfe

2,11620 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrew Wolfe Over a year ago

A synonym is an alternative name referring to a table (or other Oracle DB object). You create it as follows: create synonym LOAD_HERE for TEMPTAB_07; See the CREATE SYNONYM page for additional details.

BRabbit27 Over a year ago

Ok got it. So in my app before loading data I redefine that Synonym to point to the table I want to load data and voilà ... yes I think this is a very nice solution ... I'll check this out right now... BTW is there an API to call SqlLoader from Java? Now I'm executing SqlLoader from Java using the exec() method (executing a command on console) thanks

Ben Over a year ago

@AndrewWolfe this'll only work if the data structures are identical or at least all tables have the same columns just some have more. Also any transformations on the data would have to be identical.

A.B.Cade · Accepted Answer · 2012-01-05 15:58:47Z

0

Maybe you can use multiple INTO TABLE clauses, and distinguish bitween them, somehow, with the WHEN clause.
Look here for more details

answered Jan 5, 2012 at 15:58

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

Collectives™ on Stack Overflow

Dynamic SQL-Loader control file

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related