2

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

BRabbit27
  • 6,333
  • 17
  • 90
  • 161
  • 1
    are the 20 temp tables the same layout except for name? – tbone Jan 05 '12 at 15:44
  • yes they have the same layout – BRabbit27 Jan 05 '12 at 16:11
  • @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 Jan 07 '12 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 Jan 09 '12 at 11:57

2 Answers2

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.

Andrew Wolfe
  • 2,020
  • 19
  • 25
  • 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](http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7001.htm#CJAJCDDF) page for additional details. – Andrew Wolfe Jan 05 '12 at 16:16
  • 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 – BRabbit27 Jan 05 '12 at 16:21
  • @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. – Ben Jan 07 '12 at 11:07
0

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

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53