RE: Perl again

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Thu, 10 Mar 2005 16:40:00 -0700
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B562A45@fiji.arraybp.com>

Zoran,

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Martic Zoran
> Sent: Thursday, March 10, 2005 2:51 PM
> To: oracle-l_at_freelists.org
> Subject: RE: Perl again

>=20
>=20

> Ron,

>=20

> Thanks a lot for helping me.

>=20

> When I said it is hard, I was thinking about the more
> complex things then just successful DML, like
> exception handling and other things. It is easy to
> send something to something else. Like from Perl do
> some piping to SQL*Loader, but then what. What to do
> with errors/discards, ....

Sorry, my misread there.

>=20

> It is not good enough. You need to have the
> environment where you can create more complex business
> logic.
> SQL*Loader output are the table rows + multiple files
> for errors, discards.=20

In D/W apps prior to the ETL changes in 9i and 10g, I used SQL*Loader in = this way. Wrote a lot of shell code to handle these files, but it is do = able.

>=20

> When you are using bulk DML in C or Perl or whatever
> you get errors back then deal with them properly.

Yes, but then you need to write your own error handlers and/or logging = facilities. Granted this is not rocket science, but it is something = else you would need to write.

>=20

> SQL*Loader is great but as PL/SQL or anything else is
> not for all purposes.

>=20
> Why do you think you cannot beat SQL*Loader speed?

Try it for yourself. =20

Test 1 - SQL*Loader direct load logging and nologging (make sure that = the process feeding your sqlldr process has I/O buffering turned off). =20 Test 2 - Pro*C/Perl/whatever direct load logging and nologging (pick = different array sizes).

> At the end it is based on C OCI, is not it?

Yes, but I believe the methods for direct load are different. = SQL*Loader uses a different API for direct loads than the "normal" OCI = libraries.

> And do not believe it they are updating that piece of
> SW all the time :)
> Like exp/imp.

>=20

> They said in the past use PL/SQL, but PL/SQL did not
> have bulk DML for a long time. They fixed it properly
> only from 9i.

>=20

> We had proper bulk DML in 8i too and beat PL/SQL in
> many things.
> As I found that PL/SQL implemented prefetching in 10g
> while you have that in OCI from Oracle.

>=20

> Would you believe that. Flagship PL/SQL could not
> compare with Pro*C or OCI some time ago.

Of course I belive it. Oracle marketing is almost as bad as M$ :)

>=20

> Oracle is not perfect, neither their applications.
> What is new about Oracle is that they are now
> listening around for good advices and implementing
> them.

Obviously not perfect.

>=20

> Regards,
> Zoran

>=20
>=20

> --- "Reidy, Ron" <Ron.Reidy_at_arraybiopharma.com> wrote:

>=20


> > Zoran,

> >=20

> > Even though it sounds like it would be hard to use

> > Perl and sqlldr together, it is not.  Named pipes

> > have been around for (it seems) decades and command

> > piping (putting one program's STDOUT onto another

> > program's STDIN) has been around forever.  After you

> > get the hang of it, you will be amazed at what you

> > can do with the combination of the your favorite

> > shell and Perl.

> >=20

> > I have not used execute_array, but you may also find

> > that no matter how hard you try, you will not beat

> > sqlldr's speed for bulk inserts.

> >=20

> > -----------------

> > Ron Reidy

> > Lead DBA
> > Array BioPharma, Inc.

>=20

Ron Reidy
Lead DBA
Array BioPharma, Inc.

This electronic message transmission is a PRIVATE communication which = contains
information which may be confidential or privileged. The information is = intended=20
to be for the use of the individual or entity named above. If you are = not the=20
intended recipient, please be aware that any disclosure, copying, = distribution=20
or use of the contents of this information is prohibited. Please notify = the
sender of the delivery error by replying to this message, or notify us = by
telephone (877-633-2436, ext. 0), and then delete it from your system.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 10 2005 - 18:41:42 CST