DBD::Oracle Perl Module returning data via refcursor bug - I have found a work around

DBD::Oracle Perl module, even with its latest version, still has a bug when we return data from Oracle to Perl via a refcursor. The problem is that it will be very slow because the driver does not perform the caching when fetching data from refcursor.

It will be very fast if the data are fetched through SQL SELECT statement, however.

I have found a workaround that you can apply it to your existing codes without having to modify your existing logics.

The workaround is to use Oracle's Global Temporary Table (GTT). And, inside your stored procedure, instead of fetching data into a refcursor, you change the code to fetch into your GTT instead. Then, in your Perl code, you modify the code so that it fetches the data from SQL SELECT statement. This way, you can fetch large data at a very fast speed.

GTT has a behavior that the data inside it are private for each different database transaction and will be emptied once your transaction ends or you force it to do so.

Woo Hoo!