how to get table create script in oracle

SQL> SET LONG 9999999
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TEST') FROM DUAL;

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!

autocompletion for sqlplus

Looking for an autocompletion and probably command history for sqlplus? sqlplus is a text based Oracle client though it lacks of some minimum required feature like autocompletion and command history. But there is always a workaround. "rlwrap", a unix tool. It can add to any text tool programs the ability to have autocompletion and command history.

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!

how to rename a table in oracle (sqlplus)

alter table
   table_name
rename to
   new_table_name;

how to set default schema in sqlplus - oracle

SQL> alter session set current_schema=some_schema_name;

DBD::Oracle Perl Module and Oracle - Versions

The truth is that DBD::Oracle 1.19 will work with Oracle 9i only.

Newer version of DBD::Oracle module will not be able to be compiled with Oracle 9i, unfortunately.

oracle sid length

The maximum length of ORACLE_SID is 8 characters.

І can confirm only with Oracle 9i.