Oracle limitation with Perl

Today is Mother's day in Bangkok. It's a holiday here, though, my mom has to work, still. I will have a dinner with her in the evening.

I just spent time today with nothing other than lying on bed and enjoying my new phone, the E71. This is my second E71 though. I lost my first one at a food stall. There are a lot of thieves in my country. I don't know whom to blaim. We just have to take care of our own safety all the time.

Now, it's time for writing down my next short note.

Oracle is a very expensive software. Its performance is very high, hence. However, when it works with other software component, we cannot expect to see the same result.

In my case, I use Perl with Oracle. Although the stored procedure in Oracle executes code very fast, when a large result is sent to Perl, who makes call to that stored procedure, it is crapping slow. The problem is that if a large result is transferred from Oracle to Perl by Oracle refcursor, it will be slow based on what kind of sql select query we use to fill that refcursor. Supposedly, I think the sql select query that at least one column is a result from a call to a function which inside that function has a call to something that also uses refcursor. This problem does not occur with Java though, for example. It looks like there are some bugs in DBD::Oracle perl module and the API Oracle provides to Perl. This issue has long been there for ages and noone is going to fix it yet.

To resolve this problem, there is a workaround that I also have applied to my project. By using a single sql select statement sending directly from Perl to Oracle to get a table through DBD::Oracle binding with data types other than refcursor. Then port the code in PL/SQL (stored procedure) into Perl. This way, the data transferring from Oracle to Perl will be extremely fast by comparing with returning data from stored procedure through refcursor. And that Perl is also super fast from its origin, the performance issue is resolved!