Re: sqlldr Question

Perhaps the question is, "If I load a file into a table, and later on, the table structure must change, how does this affect what I do with SQLLDR?"

The answer to that, presuming that is the question, is that you can define standard interface tables, which only change when the structure of the feeding file changes, and then derivative tables, that change only when your database logical data requirements change.

By this level of abstraction, you can guard against external systems changing and wrecking your processes.

And so, one might have a flat , simple table loaded as simply as possible that has an incoming file and that you've build a table structure around, like:

BILLING_TELEPHONE_NUMBER
BILLING_MASTER_NAME
BILLING_ADDR1
BILLING_ADDR2
BILLING_CITY
BILLING_STATEPROV
BILLING_POSTCODE
BILLING_CNTRY_CODE

and columns are added, switched around, and such (oh no, this never happens) in your feed, by providing a layer of abstraction between the feeder and the next step, you can use CTAS (Create Table As Select), or PL/SQL if necessary, the data transformation and migration can occur and be shielded from the core systems you are feeding by a second set of tables organized in the manner your application expects.

Your feeder scripts [the .ctl scripts etc that drive SQLLDR, and the feeder table schemas] may need to be changed as need be, depending on what magnitude your data feeds have changed, to load columns you might not even want, or to ignore them and keep the same structure.

Suppose the feeder systems want to send BILLING_CARRIER_PIC (primary intexchange carrier field) and your systems don't need or want it. By doing a CTAS or selective update and insert, that column can exist in your feeder tables, and you can cheerfully ignore it, just by not including it in your list of SELECT columns. You cheerfully receive your feeders with the new column, ensure the feeder tables and scripts can handle it, and just TRUNCATE the initial feeder tables after your SQL or PL/SQL copies the data to your secondary tables.

Advice, if you have this sort of flux in your feeder systems, make sure all incoming and outgoing data streams are reliably saved to tape or whatever you use; because someday they might want it over at the application end and it's nice to have the data as well as the structures. All they need to tell you is what they want your systems to do with this new datum.

RSH. "Sunil" <sunil_franklin_at_hotmail.com> wrote in message news:pYuG8.3$sg6.34_at_news.oracle.com...
> I need to load some data into a table from a file.
> Later The file may undergo some changes and these changes will have to be
> reflected in the table. There is a pk column in the table (name) .
> How can I go about this.
>
> Thanks,
> Sunil.
>
>
>
Received on Tue May 21 2002 - 13:35:25 CDT