Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Perl again

RE: Perl again

From: Reidy, Ron <>
Date: Thu, 10 Mar 2005 16:40:00 -0700
Message-ID: <>


> -----Original Message-----
> From:
> []On Behalf Of Martic Zoran
> Sent: Thursday, March 10, 2005 2:51 PM
> To:
> Subject: RE: Perl again


> Ron,

> Thanks a lot for helping me.

> 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.


> 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.


> 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.


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

> 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.


> 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.

> 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.

> 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$ :)


> 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.


> Regards,
> Zoran

> --- "Reidy, Ron" <> wrote:

> > 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.


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.

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

Original text of this message