SQL loader: join on other table

Question

I just went through the online documentations, and found that when using SQL loader to load flat files, if you wish to make join on another table in order to get values from that table. It is not possible. We need to use external tables in this approach. Please confirm if this is true. Is there any way we can join another table when using SQL loaders?

score 1 · Accepted Answer · answered Jan 17 '13 at 15:17

1

It is true. You could load into a staging table, and then after the load run a procedure that selects from that and joins to another table. Or you could change to using an external table, which is generally to be preferred.

answered Jan 17 '13 at 15:17

Tony Andrews

129,880
21
220
259

Thanks for confirming this understanding :) – user613114 Jan 17 '13 at 15:56

score 1 · Answer 2 · answered Jan 18 '13 at 10:59

1

There is one workaround for cases with small number of foreign key's values. And values are predefined :) For example, we have reference table with records

ID Color
--------
1  Red
2  Blue
3  Green

and load a flat file that has the column "Color" with string values (Red, Blue and Green). We want to make column "color_id" in a destination table and use IDs of colors.

color FILLER,
:color_id "decode(:field2, 1, 'Red', 2, 'Blue', 3, 'Green')",

answered Jan 18 '13 at 10:59

knagaev

2,897
16
20

Thanks knagaev for the help. But this is not my requirement. I need to make join on another table and fetch values from that table. – user613114 Jan 19 '13 at 05:12

SQL loader: join on other table

2 Answers2