Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQLLDR Question

Re: SQLLDR Question

From: RSH <RSH_Oracle_at_worldnet.att.net>
Date: Thu, 07 Mar 2002 09:35:06 GMT
Message-ID: <evGh8.19333$gK2.1472881@bgtnsc04-news.ops.worldnet.att.net>


Ryan, wasn't picking on you or anything like that, I hope you didn't take it as such.

"The client wants it this way" has made my blood run cold many times; generally when I hear of something complex and odd, it's "because the client wants it that way".

Okay.

So we have a nightly feed of a flat file that has to be loaded into a table, with indices but no PK or UI's, let's call it L.

We have a, well call it P for production table, which must have unique rows. Right? (?)

The user wants to choose which of the one or many records in the nightly L table to copy over (replace) an existing P row; by doing what? A manual process? Or something that can be coded into some logic that can run by itself?

I recently was through something much like this hence my questions. It currently sounds like a classic "suspense file" (accounting thing) where incomings are held "suspended" until manual disposition via some screen. In fact A/R in particular ends up with suspense file situations, come to think of it.

So the user can retain current rows in P, or choose amongst one or more candidates to replace a particular row? And he/she/it would traverse the suspense table and decide on a row basis to replace the P counterpart with a particular selected row in L that they like better, and at the end of it, L is discarded and reloaded that night??

Is there a manual interface? A simple screen built in anything could group up and display rows from L with the same key values as the P row, display it too, and let them pick, oh whatever options you want, like:

n and ENTER, move on to next batch of rows from the L table and their corresponding row from P, doing nothing to P a number 1 through x corresponding to a row they fancy better than the current P row, and ENTER, replacing the current P row with the one selected by number

that sort of thing; i'd normally drive it off the P table and grab the similar rows from L, but mightn't there be brand new rows in L that would be plain new inserts, but if so, if there is more than one such, they'd have to pick which one of those they wanted to put in P, right?

If'n ah wuz you, I'd have a trigger on the P table on insert that copies the original P row, and the row from L that replaced it, into some audit table,along with a SYSDATE populated column and a column to identify the user who made the change
and of course the trigger would take care of removing the existing row from P and then inserting the desired row from L, thus not annoying the PK mechanism

Or am I still way off? I am trying, trust me. Oh at least say something encouraging...

RSH. "Ryan Gaffuri" <rkg100_at_erols.com> wrote in message news:a6632r$r48$1_at_bob.news.rcn.net...
> This is what the client wants.
>
> They have a specific set of records in a table. There is another record
set
> that can be FTPd. This may contain new records or records with the same
> primary key that are slightly different in the rest of the record. The
> client wants to be able to choose which one to keep. Their's or the new
one.
>
> I thought about doing a primary key, then run a cursor to check for dupes
> and delete the dupes. Also it would put a flag on the ones that were left
to
> say whether its a new record or a changed record.
> "RSH" <RSH_Oracle_at_worldnet.att.net> wrote in message
> news:Twsh8.18090$gK2.1352773_at_bgtnsc04-news.ops.worldnet.att.net...
> > Uh, not simple!
> >
> > I'd have to practically write the thing in pseudocode to answer all your
> > questions.
> >
> > But yes, triggers will fire and homemade PL/SQL functions will run etc
if
> > you run SQLLDR in conventional path and don't do tricky things like
> > PARALLEL. Your load will not be lighting fast, but you have all this
stuff
> > you want to do, either direct path load a scratch table and have a
package
> > of procedures that you call afterward, or conventional path and a whole
> > bunch of triggers.
> >
> > In any case you should be enforcing duplicate record problems by using a
> > primary key on the tables where you do not want dupes, that does not
need
> > triggers or anything else-- unless you want to do something fancy like
> > conditionally replace a row based on something or other, then you'd need
> > something that would delete the old row first before the replacement row
> > comes in, or it will bounce. But keep the PK! Trust me.
> >
> > If this secondary table may have dupe records and the users can pick one
> to
> > replace in the other table or something like that, you ought to have a
> > composite non-unique index that corresponds with the columns in the PK
of
> > the other table, that will ease selects.
> >
> > Lastly, this seems really complicated; ask yourself if simpler ways
would
> do
> > or does your business process absolutely require this kind of mechanism?
> >
> > RSH.
> > "Ryan Gaffuri" <rkg100_at_erols.com> wrote in message
> > news:6757365f.0203060743.48867b58_at_posting.google.com...
> > > I need to run a daily batch to load data into a table. This table will
> > > be an exact duplicate in structure to another table that already
> > > exists.
> > >
> > > The user will then have the option to move the New or Changed records
> > > over to the original table.
> > >
> > > Im wondering the best way to do this. First off since the batch runs
> > > every night and the user can perform the process when they feel like
> > > it we may get duplicate records in the load table.
> > >
> > > So one way to do this would be to load the data into a temp table and
> > > as part of the patch only insert the records that are not duplicates
> > > to the load table.
> > > As part of this I also want to check the original table to make sure
> > > that these are not duplicate records. I also want to flag a column as
> > > new or changed based on whether one column in the load table matches
> > > the original table, but one or more of the other tables does not. New
> > > will be if the one column is different and changed will be if the one
> > > column is the same.
> > >
> > > So for example.
> > >
> > > What is the fast way to run this? Im gonna run it from a dos batch and
> > > have it log in to do sqlddr and run the pl/sql script. So no need for
> > > triggers?
> > >
> > > Also, will an insert trigger fire off of sqlldr? If so could I use
> > > statement level trigger to enforce rules when data is loaded into the
> > > table from sqlldr?
> >
> >
>
>
Received on Thu Mar 07 2002 - 03:35:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US